SQL Notes

Updated at January 04, 2021

Data Query Language (DQL): SQL
Data Definition Language (DDL): CREATE, ALTER, and DROP
Data Control Language (DCL): GRANT, REVOKE
Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE

DML Operations

select <column_name, ...,  *>
from <table_name>
where <condition>
group by <column_name, ...>
having <condition>
order by <column_name | column_number, ...> [desc]
limit 10;

insert into <table_name> [(
	<column_name>, ...
)] values (
	<value_1>, ...
), ...;

update <table_name>
set <column_name> = <new_value>, ...
where <condition>;

delete from <table_name> where <condition>;

Description	Syntax
Execution order	`from` -> `where` ->`group by` -> `having` -> `select` -> `order by` -> `limit`
Unique	`distinct`
Aggregation functions	`count`, `sum`, `avg`, `min`, `max`, `len`
Rounding results	`round(<column, value>, <n>)`
Casting types	`cast(<column, value> as <type>)`
Case conversion	`lower()`, `upper()`
Conditional logic	`case when <expression> then <value1> [...] [else <value2>] end as <name>`
IN Operator	`<column, value> in (<values>)`
Joining data	`<inner, left, right, full (outer), cross> join on <condition>`
Combining rows	`union [all], intersect, except`
Null operations	`is [not] null`
Like pattern	`like '[pattern] [%]'`
Named subquery	`with <name> as <query> [...]`

DDL Operations

create table <table_name> (
	<column_name> <column_type> [primary key]
	,primary key (<column_name>, ...)
	,foreign key (<column_name>) references <table_name>(<column_name>)
);

alter table <table_name>
	add column <column_name> <column_type>;

Description	Syntax
SQLite column types	`text`, `integer`, `real`, `numeric`, `blob`
Creating a view	`create view <name> as <query>`
Removing an object	`drop <view, table> [if exists] <name>`

Normalization

First normal form: the values in each column of a table must be atomic
Second normal form: every non candidate-key attribute must depend on the whole candidate key, not just part of it
Third normal form: eliminating the transitive functional dependencies

SQLite

Shell	Command
Open database	`sqlite3 <dbname.db>`
Enable column headers	`.headers on`
Enable column output	`.mode column`
Help	`.help`
Tables list	`.tables`
Run in shell	`.shell <command>`
Quit	`.quit`
View the schema for a table	`.schema <table_name>`

Python API	Command
Import module	`import sqlite3`
Connect to database	`conn = connect(path)`
Close the connection	`conn.close()`
Create a cusror	`cursor = conn.cursor()`
Run the query	`cursor.execute(sql_query)`
Return one row	`cursor.fetchone()`
Return n rows	`cursor.fetchmany(n)`
Return the full results	`cursor.fetchall()`
No cursor shortcut	`conn.execute(sql_query).fetchall()`

SQL with Python

#!conda install -yc conda-forge ipython-sql

%%capture
%load_ext sql
%sql sqlite:///sqlite_file.db

%%sql
<query>

from sqlalchemy import create_engine
engine = create_engine(f'mysql://{LOGIN}:{PASS}@{URL}/{DB}?charset=utf8')
with engine.begin() as conn:
    cursor = conn.execute('''
        select value
        from table
        ''')
df = pd.DataFrame(cursor.all())

SQL Server Snippets

Date and Time Conversions Using SQL Server

select convert(varchar(8), getdate(), 112) as [DateKey]
select cast([YYYMMDD] as datetime) as [DateTime]