Formula Sheets: SQL

 Page 1


SQL
SQL Commands
Data Definition Language (DDL)
• CREATE : Define new database ob ject: CREATE TABLE table_name (column1 datatype, ...) .
• ALTER : Mo dify structure: ALTER TABLE table_name ADD column datatype .
• DROP : Delete ob ject: DROP TABLE table_name .
• TRUNCATE : Remo v e all records: TRUNCATE TABLE table_name .
Data Manipulation Language (DML)
• INSERT : A dd record: INSERT INTO table_name VALUES (value1, ...) .
• UPDATE : Mo dify records: UPDATE table_name SET column = value WHERE condition .
• DELETE : Remo v e records: DELETE FROM table_name WHERE condition .
Data Query Language (DQL)
• SELECT : Retriev e data: SELECT column1, ... FROM table_name WHERE condition .
Data Con trol Language (DCL)
• GRANT : Assign privileges: GRANT SELECT ON table_name TO user .
• REVOKE : Remo v e privileges: REVOKE SELECT ON table_name FROM user .
T ransaction Con trol Language (TCL)
• COMMIT : Sa v e c hanges: COMMIT .
• ROLLBACK : Undo c hanges: ROLLBACK .
• SAVEPOINT : Set c hec kp oin t: SAVEPOINT savepoint_name .
Joins
T yp es of Joins
• INNER JOIN : Matc hes ro ws from b oth tables: SELECT * FROM A INNER JOIN B ON A.key = B.key .
• LEFT (OUTER) JOIN : All ro ws from left table, matc hed ro ws from righ t: SELECT * FROM A LEFT
JOIN B ON A.key = B.key .
• RIGHT (OUTER) JOIN : All ro ws from righ t table, matc hed ro ws from left: SELECT * FROM A RIGHT
JOIN B ON A.key = B.key .
• FULL (OUTER) JOIN : All ro ws from b oth tables: SELECT * FROM A FULL JOIN B ON A.key = B.key .
• CROSS JOIN : Cartesian pro duct: SELECT * FROM A CROSS JOIN B .
Sub queries
Non-Correlated Sub query
• Executes indep enden tly: SELECT * FROM table WHERE column IN (SELECT column FROM table2) .
Correlated Sub query
• Dep ends on outer query: SELECT * FROM table t1 WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE
t2.key = t1.key) .
1
Page 2


SQL
SQL Commands
Data Definition Language (DDL)
• CREATE : Define new database ob ject: CREATE TABLE table_name (column1 datatype, ...) .
• ALTER : Mo dify structure: ALTER TABLE table_name ADD column datatype .
• DROP : Delete ob ject: DROP TABLE table_name .
• TRUNCATE : Remo v e all records: TRUNCATE TABLE table_name .
Data Manipulation Language (DML)
• INSERT : A dd record: INSERT INTO table_name VALUES (value1, ...) .
• UPDATE : Mo dify records: UPDATE table_name SET column = value WHERE condition .
• DELETE : Remo v e records: DELETE FROM table_name WHERE condition .
Data Query Language (DQL)
• SELECT : Retriev e data: SELECT column1, ... FROM table_name WHERE condition .
Data Con trol Language (DCL)
• GRANT : Assign privileges: GRANT SELECT ON table_name TO user .
• REVOKE : Remo v e privileges: REVOKE SELECT ON table_name FROM user .
T ransaction Con trol Language (TCL)
• COMMIT : Sa v e c hanges: COMMIT .
• ROLLBACK : Undo c hanges: ROLLBACK .
• SAVEPOINT : Set c hec kp oin t: SAVEPOINT savepoint_name .
Joins
T yp es of Joins
• INNER JOIN : Matc hes ro ws from b oth tables: SELECT * FROM A INNER JOIN B ON A.key = B.key .
• LEFT (OUTER) JOIN : All ro ws from left table, matc hed ro ws from righ t: SELECT * FROM A LEFT
JOIN B ON A.key = B.key .
• RIGHT (OUTER) JOIN : All ro ws from righ t table, matc hed ro ws from left: SELECT * FROM A RIGHT
JOIN B ON A.key = B.key .
• FULL (OUTER) JOIN : All ro ws from b oth tables: SELECT * FROM A FULL JOIN B ON A.key = B.key .
• CROSS JOIN : Cartesian pro duct: SELECT * FROM A CROSS JOIN B .
Sub queries
Non-Correlated Sub query
• Executes indep enden tly: SELECT * FROM table WHERE column IN (SELECT column FROM table2) .
Correlated Sub query
• Dep ends on outer query: SELECT * FROM table t1 WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE
t2.key = t1.key) .
1
Windo w F unctions
Rank F unctions
• RANK() : Assigns rank with gaps: SELECT column, RANK() OVER (PARTITION BY column ORDER BY
value) .
• DENSE_RANK() : Ranks without gaps: SELECT column, DENSE_RANK() OVER (PARTITION BY column
ORDER BY value) .
• ROW_NUMBER() : Unique sequen tial n um b er: SELECT column, ROW_NUMBER() OVER (PARTITION BY
column ORDER BY value) .
Aggregate Windo w F unctions
• SUM , AVG , COUNT , MAX , MIN : SELECT column, SUM(value) OVER (PARTITION BY column ORDER BY
value) .
Lead/Lag
• LEAD(column, n) : A ccess nextn -th ro w: SELECT column, LEAD(column, 1) OVER (ORDER BY value) .
• LAG(column, n) : A ccess previous n -th ro w: SELECT column, LAG(column, 1) OVER (ORDER BY
value) .
F rame Clause
• ROWS BETWEEN start AND end : Define windo w range, e.g., ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW .
Other Clauses
WHERE vs HA VING
• WHERE : Filters ro ws b efore grouping: SELECT column FROM table WHERE condition .
• HAVING : Filters groups after GROUP BY : SELECT column, COUNT(*) FROM table GROUP BY column
HAVING COUNT(*) > n .
GR OUP BY
• Groups ro ws b y column: SELECT column, COUNT(*) FROM table GROUP BY column .
ORDER BY
• Sorts result: SELECT column FROM table ORDER BY column ASC/DESC .
WITH Clause (Common T able Expression)
• Define temp orary result: WITH cte_name AS (SELECT * FROM table) SELECT * FROM cte_name .
Views
• Virtual table: CREATE VIEW view_name AS SELECT column FROM table WHERE condition .
2