SQL

• https://sqlbolt.com/
• https://www.reddit.com/r/SQL/comments/11wogjb/where_to_practice_sql/

Types of database languages

Data Definition Language (DDL)

• CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.
• All these commands are used for updating the data that’s why they are known as Data Definition Language.

Data Manipulation Language (DML)

• SELECT, UPDATE, INSERT, DELETE, etc.
• These commands are used for the manipulation of already updated data that’s why they are the part of Data Manipulation Language.

DATA Control Language (DCL)

• GRANT and REVOKE.
• These commands are used for giving and removing the user access on the database. So, they are the part of Data Control Language.

Transaction Control Language (TCL)

• COMMIT, ROLLBACK, and SAVEPOINT.
• These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML.

SQL constraints

• NOT NULL - Restricts NULL value from being inserted into a column.
• CHECK - Verifies that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has been specified for the field.
• UNIQUE - Ensures unique values to be inserted into the field.
• INDEX - Indexes a field providing faster retrieval of records.
• PRIMARY KEY - Uniquely identifies each record in a table.
• FOREIGN KEY - Ensures referential integrity for a record in another table.

JOIN

• The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.
  • (INNER) JOIN: A intersect B
  • LEFT (OUTER) JOIN: A + (A intersect B)
  • RIGHT (OUTER) JOIN: B + (A intersect B)
  • FULL (OUTER) JOIN: A + B + (A intersect B)

WHERE

• Used for filtering

Group By

• We think groupby as three steps (Hadley Wickham of Rstats fame):
• The split step involves breaking up and grouping a DataFrame depending on the value of the specified key.
• The apply step involves computing some function, usually an aggregate, transformation, or filtering, within the individual groups.
• The combine step merges the results of these operations into an output array

flowchart TD
A["split <br> (create different groups)"]
B["apply <br> (aggregate/transform/ <br> filtering on each group)"]
C[combine]
A --> B --> C

Having

• filters the aggregated data
• Because where can not be used with aggregate functions
• https://stackoverflow.com/questions/1130062/execution-sequence-of-group-by-having-and-where-clause-in-sql-server

SQL execution order

• https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver17#logical-processing-order-of-the-select-statement
  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP

DELETE vs TRUNCATE vs DROP

• DELETE: has WHERE clause, deletes rows of the table
  • DML
• TRUNCATE: doesn’t have WHERE clause and deletes contents of table
  • DDL
• DROP: deletes the table
  • DDL

Joins vs where

• https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause