📘 What is SQL?
SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It's declarative, not procedural - you specify WHAT you want, not HOW to get it.
🔑 SQL Components
Data Definition Language (DDL): CREATE, ALTER, DROP
Data Manipulation Language (DML):
- Data Retrieval: SELECT (focus of this chapter)
- Data Modification: INSERT, UPDATE, DELETE
💡 Declarative vs Procedural
Declarative (SQL): "Give me all students with GPA > 3.5"
Procedural: "Loop through all students, check each GPA, if > 3.5 add to results"
SQL lets the database decide the most efficient way to execute your query!
📘 What is Projection?
Projection is selecting specific columns from a table. The SELECT clause determines which attributes appear in the result.
Select All Columns
💡 Using SELECT *
SELECT *
FROM Sailors;
The asterisk (*) retrieves ALL columns from the table
Select Specific Columns
💡 Specific Attributes
SELECT sname, rating
FROM Sailors;
Retrieves only sname and rating columns
Renaming Attributes (AS)
💡 Using AS for Aliases
SELECT sname AS sailor_name, rating AS skill_level
FROM Sailors;
Changes column names in the output for clarity
Arithmetic Operations
💡 Calculations in SELECT
SELECT Fname, Lname, 1.1 * Salary AS new_salary
FROM EMPLOYEE;
Shows salary with 10% raise
📘 What is Selection?
Selection is filtering rows based on conditions. The WHERE clause determines which tuples appear in the result.
Comparison Operators
⚙️ Available Operators
| Operator |
Meaning |
Example |
| = |
Equal to |
WHERE age = 25 |
| <> or != |
Not equal to |
WHERE major <> 'CS' |
| < |
Less than |
WHERE salary < 50000 |
| > |
Greater than |
WHERE rating > 5 |
| <= |
Less than or equal |
WHERE age <= 30 |
| >= |
Greater than or equal |
WHERE GPA >= 3.0 |
Logical Operators (AND, OR)
💡 Combining Conditions
SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;
Finds employees in department 5 with salary between $30K and $40K
Pattern Matching (LIKE)
📋 LIKE Wildcards
- % (percent): Matches zero or more characters
- _ (underscore): Matches exactly one character
💡 Pattern Matching Examples
SELECT *
FROM STUDENT
WHERE Name LIKE 'A%';
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE '%Houston,TX%';
BETWEEN Operator
💡 Range Queries
SELECT *
FROM Sailors
WHERE rating BETWEEN 5 AND 7;
Equivalent to: WHERE rating >= 5 AND rating <= 7
Date Comparisons
💡 Working with Dates
SELECT *
FROM events
WHERE date < to_date('1-1-1943', 'DD-MON-YYYY');
Finds events before January 1, 1943
📘 What is a Join?
A join combines rows from two or more tables based on a related column. It's how we query data from multiple tables simultaneously.
Cross Product (Cartesian Product)
⚙️ How Joins Work
Step 1: Create cross-product of all tables (all possible combinations)
Step 2: Apply WHERE conditions to filter rows
Step 3: Select desired columns
💡 Join Example
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103;
Finds names of sailors who reserved boat 103
💡 Multi-Table Join
SELECT *
FROM Student s, Takes t, Course c
WHERE s.sid = t.sid AND t.cid = c.cid;
Combines student, enrollment, and course information
⚠️ Important: Table Aliases
Use table aliases (e.g., S, R) to:
- Make queries more readable
- Distinguish between tables with same column names
- Enable self-joins (joining a table with itself)
📘 Duplicates in SQL
By default, SQL treats tables as multisets (bags), not sets. This means duplicate rows CAN appear in results.
🔑 SELECT vs SELECT DISTINCT
- SELECT: Returns all rows (implicit SELECT ALL)
- SELECT DISTINCT: Eliminates duplicate rows
💡 Without DISTINCT
SELECT sname
FROM Sailors s, Reserves r
WHERE s.sid = r.sid;
💡 With DISTINCT
SELECT DISTINCT sname
FROM Sailors s, Reserves r
WHERE s.sid = r.sid;
📘 Ordering Results
The ORDER BY clause sorts the query results by one or more columns.
Basic Ordering
🔑 Sort Orders
- ASC: Ascending order (default)
- DESC: Descending order
💡 Single Column Sort
SELECT *
FROM Sailors
ORDER BY age;
SELECT *
FROM Sailors
ORDER BY rating DESC;
Multi-Column Ordering
💡 Multiple Sort Keys
SELECT D.Dname, E.Lname, E.Fname, P.Pname
FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE D.Dnumber = E.Dno
AND E.Ssn = W.Essn
AND W.Pno = P.Pnumber
ORDER BY D.Dname, E.Lname, E.Fname;
First sorts by department name, then by last name, then by first name
📘 What are Set Operations?
Set operations combine the results of two or more SELECT statements.
UNION
🔑 UNION Operator
Combines result sets and eliminates duplicates by default.
💡 UNION Example
SELECT Name, Location
FROM facebook
UNION
SELECT Name, Location
FROM linkedin;
Combines names and locations from both platforms, removing duplicates
UNION ALL
💡 UNION ALL Example
SELECT Name, Location
FROM facebook
UNION ALL
SELECT Name, Location
FROM linkedin;
Keeps all rows including duplicates
INTERSECT
🔑 INTERSECT Operator
Returns only rows that appear in both result sets.
💡 INTERSECT Example
SELECT Name
FROM facebook
INTERSECT
SELECT Name
FROM linkedin;
Finds people who have accounts on BOTH platforms
EXCEPT / MINUS
🔑 EXCEPT Operator
Returns rows from the first query that are NOT in the second query.
💡 EXCEPT Example
SELECT Name
FROM facebook
EXCEPT
SELECT Name
FROM linkedin;
Finds people on Facebook but NOT on LinkedIn
INSERT - Adding Data
📘 INSERT Statement
The INSERT command adds new rows to a table.
💡 Basic INSERT
INSERT INTO Sailors
VALUES(22, 'dustin', 7, 45.0);
INSERT INTO Sailors(sid, sname, rating)
VALUES(22, 'dustin', 7);
💡 INSERT from Query
INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;
Inserts results of a query into a table
UPDATE - Modifying Data
📘 UPDATE Statement
The UPDATE command modifies existing rows.
💡 UPDATE Example
UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;
Gives 10% raise to all employees in department 5
⚠️ CASCADE Effects
Updating a primary key may propagate to foreign keys if ON UPDATE CASCADE is specified.
DELETE - Removing Data
📘 DELETE Statement
The DELETE command removes rows from a table.
💡 DELETE Example
DELETE FROM EMPLOYEE
WHERE Lname = 'Brown';
DELETE FROM Sailors
WHERE age > 50;
⚠️ CASCADE Effects
Deleting rows may propagate to other tables if ON DELETE CASCADE is specified in foreign key constraints.
🔑 Constraint Enforcement
All INSERT, UPDATE, and DELETE operations must satisfy:
- Entity Integrity: Primary keys cannot be NULL
- Referential Integrity: Foreign keys must reference existing primary keys
- Domain Constraints: Values must match data types
- CHECK Constraints: Custom conditions must be satisfied