SQL Course Explained From Scratch
1. Database Basics A database stores data in tables. Each table contains rows and columns. 2.
CREATE TABLE Used to create a new table. Example: CREATE TABLE Students ( id NUMBER(3)
PRIMARY KEY, name VARCHAR2(30) NOT NULL ); 3. PRIMARY KEY A column that uniquely
identifies each row. Rules: - Cannot be NULL - Cannot be duplicated 4. FOREIGN KEY Creates a
relationship between two tables. A value in the child table must exist in the parent table. 5.
Composite Primary Key When two or more columns together form the primary key. Example:
PRIMARY KEY(id_film, id_acteur) 6. ALTER TABLE Used to modify an existing table. Examples:
ALTER TABLE employes ADD salaire NUMBER(8,2); ALTER TABLE employes DROP COLUMN
salaire; ALTER TABLE employes MODIFY nom VARCHAR2(40); 7. DROP TABLE Deletes a table
completely. Example: DROP TABLE employes; 8. SELECT Displays data. Example: SELECT *
FROM Students; 9. WHERE Filters rows. Example: SELECT * FROM Students WHERE age = 20;
10. INNER JOIN Combines rows from two tables where matching values exist. Example: SELECT
name, program FROM Students INNER JOIN Programs ON [Link] = [Link]; 11.
LEFT JOIN Returns all rows from the left table even if there is no match. 12. Aggregate Functions
COUNT() : Counts rows SUM() : Calculates total AVG() : Calculates average MAX() : Largest value
MIN() : Smallest value 13. GROUP BY Groups rows before applying aggregate functions. Example:
SELECT codeequipe, COUNT(*) FROM joueurs GROUP BY codeequipe; 14. HAVING Filters
groups after GROUP BY. Example: SELECT codeequipe, COUNT(*) FROM joueurs GROUP BY
codeequipe HAVING COUNT(*) >= 3; Exam Revision Checklist - CREATE TABLE - PRIMARY
KEY - FOREIGN KEY - ALTER TABLE - DROP TABLE - SELECT - WHERE - JOINS - COUNT,
SUM, AVG, MIN, MAX - GROUP BY - HAVING Practice writing queries for each topic until you can
do them without looking at notes.
Quick SQL Examples
CREATE TABLE Students ( id NUMBER(3) PRIMARY KEY, name VARCHAR2(30) ); SELECT *
FROM Students; SELECT COUNT(*) FROM Students; SELECT AVG(salary) FROM Employees;
SELECT department, COUNT(*) FROM Employees GROUP BY department; SELECT department,
COUNT(*) FROM Employees GROUP BY department HAVING COUNT(*) > 5;