SQL

Song Jiaming | 09 Jun 2020

Disclaimer: This is just a summary of common syntax used in SQL

Basic 5 Commands

Create, Insert, Select, Update, Delete

1. Create table

CREATE TABLE TABLE_NAME (
    column1 integer primary key NOT NULL,
    column2 varchar(30),
    column3 char(2)
);

2. Insert rows to the table

INSERT INTO TABLE_NAME (column1, column2, column3)
VALUES 
    (1, 'varchar1', 'A1'),
    (2, 'varchar2', 'A2');

3. Select information from the table

SELECT * FROM TABLE_NAME;

SELECT column1, column2 FROM TABLE_NAME WHERE condition;

4. Update current information in the table

UPDATE TABLE_NAME
SET column1 = value1, column2 = value2, ...
WHERE condition;

5. Delete some information from the table

DELETE FROM TABLE_NAME WHERE condition;

# Delete the whole table:
DELETE FROM TABLE_NAME;
DROP TABLE TABLE_NAME;

More on SELECT queries

Select with pattern, ranges, sorting and grouping

We can simplify a `SELECT` statment by using: String patterns, ranges or sets of values

Retrieve for values with some string patterns, i.e. ‘R%’ - words starting with R

SELECT column1 FROM TABLE_NAME WHERE column1 LIKE string_pattern;

Retrive for a range

SELECT column1 FROM TABLE_NAME WHERE column1 > value1 AND column1 < value2;
SELECT column1 FROM TABLE_NAME WHERE column1 Between value1 AND value2;

Retrive for a value in a possible set of values

SELECT column1 FROM TABLE_NAME WHERE column1 = 'value1' OR column1 = 'value2';
SELECT column1 FROM TABLE_NAME WHERE column1 IN ('value1', 'value2');

Sorting Result Set

SELECT column1 FROM TABLE_NAME ORDER BY column1;
SELECT column1, column2 FROM TABLE_NAME ORDER BY 2; # 2 means order by column2

Grouping Result Set

Remove duplicates

SELECT DISTINCT(column1) FROM TABLE_NAME;
#OR
SELECT UNIQUE(column1) FROM TABLE_NAME;

Count the frequency of each unique entry

SELECT column1, COUNT(column1) as count  #name this COUNT(Column1) column as count
FROM TABLE_NAME 
GROUP BY column1

-Filter groupby result by count. HAVING Clause works only with GROUP BY clause

SELECT column1, COUNT(column1) as count  #name this COUNT(Column1) column as count
FROM TABLE_NAME 
GROUP BY column1 HAVING COUNT(column1) > 2

Built-in Functions

Aggregate or Column Functions

Input: collection of values (like one entire column)
Output: a single value
Such as SUM(), AVG(), MIN(), MAX()

SELECT SUM(PRICE) as SUM_OF_PRICE FROM MARKET;
SELECT AVG(PRICE / QUANTITY) FROM MARKET where type='meat;

SCALER or STRING Functions

Perform operations on every input value
Such as ROUND(), LENGTH(), UCASE(), LCASE()

SELECT ROUND(PRICE) FROM MARKET;
SELECT LENGTH(TYPE) FROM MARKET; # length of each value in "TYPE" column
SELECT UCASE(TYPE) FROM MARKET;
SELECT * from MARKET where LCASE(TYPE) = 'meat';
SELECT DISTINCT(UCASE(ANIMAL)) from MARKET;

Date and Time

DATE: YYYYMMDD (8 digits)
TIME: HHMMSS (6 digits)
TIMESTAMP: YYYYXXDDHHMMSSZZZZZZ (20 digits) (Year, month, day, hour, minutes, seconds and microseconds)
FUNCTIONS: YEAR(), MONTH(), DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), WEEK(), HOUR(), MINUTE(), SECOND()
Registers: CURRENT_DATE, CURRENT_TIME: automatically get the current timing when running the script

SELECT COUNT(*) FROM MARKET WHERE MONTH(SALEDATE) = '05';
SELECT (SALEDATE + 3 DAYS) FROM MARKET;
SELECT (CURRENT_DATE - SALEDATE) FROM MARKET;

Sub-queries and Nested Selects

Sub-query

A query inside another query, there are column expressions and Table Expressions (Derived Tables)
Column Expressions: Substitue column name with a sub-query

SELECT column1, column2, (select AVG(column3) from TABLE_NAME) as avg_col3
FROM TABLE_NAME;

Table Expressions: Substitue TABLE name with a sub-query

SELECT * 
FROM (SELECT column1, column2, column3 FROM TABLE_NAME) AS NEW_TABLE;

Multiple Tables

Ways to access multiple tables in the same query:

Sub-queries

SELECT * FROM TABLE1 
WHERE column1 IN (SELECT column1 FROM TABLE2);

SELECT * FROM TABLE!
WHERE column1 IN (SELECT column1 FROM TABLE2 WHERE column2 = 'value2');

Implicit JOIN The result is a full join (cartesian join), every row in the 1st table is joined with every row in 2nd tabel, the result set have more rows than both tables

SELECT * FROM TABLE1, TABLE2;

We can limit the result set:

SELECT * FROM TABLE1, TABLE2
WHERE TABLE1.column1 = Table2.column2;

SELECT * FROM TABLE1 A, TABLE2 B
WHERE A.column1 = B.column2;

SELECT A.column1, B.column2
FROM TABLE1 A, TABLE2 B
WHERE A.column1 = B.column2;

JOIN operators: INNER JOIN, OUTER JOIN, … Types of Joins:
- Inner join: returns only intercept (matches) of 2 or more tables

SELECT A.column1, A.column2, A.id, B.column1, B.id 
FROM TABLE1 A INNER JOIN TABLE2 B ON A.id = B.id;

SELECT A.column1, B.column2, C.column3
From TABLE1 A
	INNER JOIN TABLE2 B ON B.id = A.id
	INNER JOIN TABLE3 C on C.id2 = B.id2;

Outer join
- Left Outer Join: Takes all rows from the left table and the matching rows from right table

SELECT A.column1, A.column2, A.id, B.column1, B.id 
FROM TABLE1 A LEFT JOIN TABLE2 B ON A.id = B.id;

- Right Outer Join: Takes all rows from the right table and the matching rows from left table

SELECT A.column1, A.column2, A.id, B.column1, B.id 
FROM TABLE1 A RIGHT JOIN TABLE2 B ON A.id = B.id;

- Full Outer Join: All rows from 2 tables

SELECT A.column1, A.column2, A.id, B.column1, B.id 
FROM TABLE1 A FULL JOIN TABLE2 B ON A.id = B.id;

Access Database using Python

from dbmodule import connect
# create connection object
connection_db = connect('databasename', 'username', 'pswd')

# Create a cursor object
cursor = connect_db.cursor()

# Run queries
cursor.execute('select * from mytabel')
results = cursor.fetchall()

# Free resources
cursor.close()
connection_db.close()

Use pandas

import pandas as pd
import db_module
# create connection object
connection_db = db_module.connection(...)

df = pandas.read_sql('SELECT * FROM TABLE_NAME', connection_db)

Writing sql queries in jupyter notebook:

%% sql # the whole block will be interpreted as a sql query
%sql # the single line will be interpreted as a sql query

Real World Databases

May need to use string and exact letter for column names
Column names with spaces: spaces are mapped to underscores, similar for other characters
Access list of tables and their properties from query system catalog

SELECT TABSCHEMA, TABNAME, CREATE_TIME
FROM syscat.tables WHERE TABSCHEMA = 'xxx';

SELECT * FROM syscat.columns WHERE tabname = 'DOGS';

References

Databases and SQL for Data Science

SQL