04 SQL

SQL

Structured Query Language (SQL)

Not tested in grey

Uses of SQL:

Create the database and table structures

• Perform basic data management chores (insert, update and delete)
• Perform complex queries to transform raw data into useful information

4 Categories of SQL commands:

• Data Definition Language (DDL)
  • Defines database schema

Create the database, table structure, objects

• Data Manipulation Language (DML)
  • Use to retrieve and modify data
• Data Control Language (DCL)
  • Use to control access to the database
• Transaction Control Language (TCL)
  • Use to manage changes to a database, usually at transactional level

Data Definition Language (DDL)

KEYWORDS IN SQL TABLES

• NULL
  • Column can be empty (no value).
• NOT NULL
  • Column must have a value.
• AUTOINCREMENT
  • Automatically assigns a unique number to each new row (used with INTEGER PRIMARY KEY).
• PRIMARY KEY
  • Uniquely identifies each row.
  • Must be unique and not null.
• FOREIGN KEY
  • Creates a link to another table’s primary key.

DATA TYPES IN SQL

• INTEGER
  • Stores whole numbers (e.g. 1, 42, -10)
• REAL
  • Stores decimal numbers (e.g. 3.14, -0.5)
• TEXT
  • Stores text strings (e.g. ‘Alice’, ‘Hello world’)
• BLOB
  • Stands for Binary Large Object.
  • Stores files like images, audio, or any binary data.

CREATE
Create database, table and objects

ALTER
Alter the structure of the database

• Rename a table and add new column to a table are supported in SQLite.

DROP 
Delete database, table and objects
DROP TABLE in SQLite deletes a table permanently — this cannot be undone.

• If the table doesn’t exist, it gives an error — unless you use IF EXISTS, which safely skips the deletion.
• If the table has foreign key dependencies, it won’t be dropped and an error will occur.

Foreign Key

• Rows might be inserted into a table that do not correspond to any row in another table.
• Rows might be deleted from a table, leaving orphaned rows in another table that do not correspond to any of the remaining rows in the original table.

SQL foreign key constraints enforce relationships between tables by ensuring** referential integrity**. The DBMS prevents changes to the primary key table that would break links with the foreign key table, ensuring every student has a valid matching class.

When a foreign key constraint is in place:

• You cannot insert a row in a child table if the corresponding value does not exist in the parent table. The database will block the insert and show an error.
• You cannot delete a row from the parent table if any rows in the child table still reference it. You must delete or update those related child records first.
  

This ensures data consistency between related tables.

Data Manipulation Language (DML)

SELECT 

• Retrieve records from one or more table

🔍 SQL WHERE Clause Cheat Sheet

✅ Exact Match

WHERE column = value

✅ Multiple Values

WHERE column IN (value1, value2, …)

WHERE column NOT IN (value1, value2, …)

✅ Range Match

WHERE column BETWEEN low AND high

WHERE column NOT BETWEEN low AND high

❌ Common Mistake with OR

— Incorrect:

WHERE age = 17 OR 18

— Correct:

WHERE age = 17 OR age = 18

✅ Pattern Matching with LIKE

— Contains:

WHERE name LIKE ‘%TAN%’

— Starts with:

WHERE name LIKE ‘TAN%’

— Ends with:

WHERE name LIKE ‘%TAN’

🧮 SQL COUNT Cheat Sheet

✅ Count All Rows

SELECT COUNT(*) FROM table_name;

✅ Count Non-NULL Values in a Column

SELECT COUNT(column_name) FROM table_name;

✅ Count Distinct Values

SELECT COUNT(DISTINCT column_name) FROM table_name;

✨ SQL DISTINCT Cheat Sheet

✅ Select Unique Rows

SELECT DISTINCT expressions FROM table_name;

— Example:

SELECT DISTINCT ClassID, Name FROM Student;

🧾 SQL ORDER BY Cheat Sheet

✅ Sort Results

SELECT expressions FROM table_name

ORDER BY *expressions *ASC/DESC;

— Example:

SELECT Name, NRIC FROM Student

ORDER BY NRIC DESC, Name ASC;

• ASC = ascending (default)
• DESC = descending

➕ SQL SUM() Cheat Sheet

✅ Total of Values

SELECT SUM(column_name) FROM table_name;

— Example:

SELECT SUM(age) FROM Student;

📊 SQL AVG() Cheat Sheet

✅ Average of Values

SELECT AVG(column_name) FROM table_name;

🔼 SQL MAX() Cheat Sheet

✅ Highest Value

SELECT MAX(column_name) FROM table_name;

🔽 SQL MIN() Cheat Sheet

✅ Lowest Value

SELECT MIN(column_name) FROM table_name;

🔗 SQL Joins Cheat Sheet

✅ Cross Join

Returns every combination of rows from two tables (Cartesian product).

SELECT * FROM TableA, TableB;

✅ Inner Join

Returns only matching rows from both tables.

SELECT * 

FROM TableA

INNER JOIN TableB

ON TableA.key = TableB.key;

OR

SELECT * 

FROM TableA

INNER JOIN TableB

WHERE TableA.key = TableB.key;

• TableA.key is a foreign key (FK) referencing TableB.key
• TableB.key is a primary key (PK)
• This query returns only the rows where matching keys exist in both tables (i.e., related records)
  

✏️ SQL Insert, Update, Delete

✅ INSERT

Add a new record to a table.

INSERT INTO table_name (column1, column2)

VALUES (value1, value2);

✅ UPDATE

Update existing record(s).
UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

✅ DELETE

Delete record(s) from a table.
DELETE FROM table_name

WHERE condition;

📊 GROUP BY Clause

✅ COUNT with GROUP BY

SELECT COUNT(*), column_name

FROM table_name

GROUP BY column_name;

✅ AVG with GROUP BY

SELECT AVG(column_name), group_column

FROM table_name

GROUP BY group_column;

✅ SUM with GROUP BY

SELECT SUM(column_name), group_column

FROM table_name

GROUP BY group_column;

⚠️ GROUP Filters: HAVING vs WHERE

✅ WHERE filters individual rows before grouping

SELECT SUM(Age), ClassID 

FROM Student

WHERE ClassID <> 2

GROUP BY ClassID;

✅ HAVING filters grouped results after GROUP BY

SELECT SUM(Age), ClassID 

FROM Student

GROUP BY ClassID

HAVING ClassID <> 2;