[4] SQL Notes – Modify and Analyzing
Modify and Analyzing
Text String
Retrieve the data in the format you need
Support Joins
String Functions
Concatenations
SELECT
CompanyName
,ContactName
,CompanyName || '(' || ContactName || ')'
FROM customers;
SQL server supports + instead of ||
Trimming
Trims the leading or trailing space from a string
TRIM
RTRIM
LTRIM
SELECT TRIM(' You the best. ') AS TrimmedString;
output: You the best. (without space)
Substring
Returns the specified number of characters from a particular position of a given strings.
SUBSTR(string name, string position, number of characters to be returned)
SELECT firstname
,SUBSTR(firstname,2,3)
FROM employees;
Upper and Lower
Format string to Upper case or Lower case.
SELECT UPPER(column_name) FROM tablename;
SELECT LOWER(column_name) FROM tablename;
SELECT UCASE(column_name) FROM tablename;
Date and Time Strings
- Dates are stored as date types
- Each DBMS uses it’s own variety of date types
The Formats and Function showed below are all based on SQLite
Formats
DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD HH:MI:SS
TIMESTAMP: YYYY-MM-DD HH:MI:SS
If you query a DATETIME with:
WHERE PurchaseDate = '2016-12-12'
You will get no result because Hour, Minutes, Seconds should have been added.
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
Function
DATE(timestring, modifier,modifier,...)
TIME(timestring, modifier,modifier,...)
DATETIME(timestring, modifier,modifier,...)
JULIANDAY(timestring, modifier,modifier,...)
STRFTIME(format,timestring,modifier,modifier,...)
STRFTIME: STRing Format Time
Modifiers
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc
Examples
use 'now'
SELECT STRFTIME('%Y %m %d','now')
SELECT Birthdate
,STRFTIME('%Y',Birthdate) AS Year
,STRFTIME('%m',Birthdate) AS Month
,STRFTIME('%d',Birthdate) AS Day
,DATE(('now') - Birthdate) AS Age
FROM employees
Case
SELECT
trackid
,name
,bytes
,CASE
WHEN bytes < 300000 THEN 'small'
WHEN bytes < 300000 AND bytes <= 500000 THEN 'medium'
WHEN bytes >= 500001 THEN 'large'
ELSE 'other'
END bytescategory
FROM
tracks;
VIEW
What is VIEW
- A stored query
- Can add or remove columns without changing schema
- use it to encapsulate queries
- The view will be removed after database connection has ended
Create a view
CREATE VIEW my_view
AS
SELECT
r.id
,e.LastName
,e.FirstName
FROM Region r
INNER JOIN Employee e on r.id = e.id
SELECT LastName
,FirstName
FROM my_view
GRUOP BY Lastname, Firstname;
Reference：https://shipengfei92.github.io/2018/02/05/SQL-for-data-science/
