SQL Lecture Notes
1. Basic SQL Commands
SELECT: Retrieve data from a table.
SELECT DISTINCT: Remove duplicate values.
WHERE: Filter records.
ORDER BY: Sort results.
AND, OR, NOT: Logical operators.
Example:
SELECT DISTINCT name FROM students WHERE age > 18 ORDER BY name;
INSERT INTO:
INSERT INTO students (name, age) VALUES ('Ali', 20);
NULL Values:
IS NULL / IS NOT NULL
UPDATE:
UPDATE students SET age = 21 WHERE name = 'Ali';
DELETE:
DELETE FROM students WHERE name = 'Ali';
TOP:
SELECT TOP 5 * FROM students;
Aggregate Functions:
MIN(), MAX(), COUNT(), SUM(), AVG()
2. Intermediate SQL
LIKE: Pattern matching
IN: Multiple values
BETWEEN: Range
Aliases: Rename columns/tables
UNION: Combine queries
GROUP BY: Group records
HAVING: Filter groups
EXISTS, ANY, ALL: Subquery conditions
SELECT INTO:
Copy table data
INSERT INTO SELECT:
Insert from another table
CASE:
Conditional logic
NULL Functions:
COALESCE(), ISNULL()
Stored Procedures:
Reusable SQL code
Comments:
-- Single line
/* Multi-line */
Operators:
Arithmetic, Comparison, Logical
3. Database Management
CREATE DATABASE db_name;
DROP DATABASE db_name;
BACKUP DATABASE:
BACKUP DATABASE db_name TO DISK = 'path';
CREATE TABLE:
CREATE TABLE students (id INT, name VARCHAR(50));
ALTER TABLE:
ADD, MODIFY, DROP columns
Constraints:
NOT NULL
Transactions:
COMMIT, ROLLBACK, SAVEPOINT
4. Advanced Database Concepts
Constraints:
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT
INDEX:
Improve query speed
AUTO INCREMENT:
Automatically generate IDs
Dates:
DATE, TIME, DATETIME
Views:
Virtual tables
SQL Injection:
Security risk from user inputs
Hosting:
Database deployment on servers
Data Types:
INT, VARCHAR, FLOAT, DATE
5. Joins and Queries
INNER JOIN:
Matching records
LEFT JOIN:
All left + matching right
RIGHT JOIN:
All right + matching left
FULL JOIN:
All records from both
SELF JOIN:
Join table to itself
Subqueries:
Query inside query
Grouping & Aggregation:
GROUP BY with COUNT(), SUM(), etc.
1. Count students in each department
SELECT department, COUNT(*) AS total_students
FROM students
GROUP BY department;
👉 Insight: distribution of students per department
🔹 2. Average marks per course
SELECT c.course_name, AVG([Link]) AS avg_marks
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name;
🔹 3. Total students enrolled in each course
SELECT c.course_name, COUNT(e.student_id) AS total_enrollments
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;
👉 Uses LEFT JOIN to include courses with zero students
🔹 4. Maximum and Minimum marks per course
SELECT c.course_name,
MAX([Link]) AS highest,
MIN([Link]) AS lowest
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name;
🔹 5. Students count per course (only if more than 1 student) → HAVING
SELECT c.course_name, COUNT(*) AS total
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name
HAVING COUNT(*) > 1;
👉 Key teaching point:
WHERE filters rows
HAVING filters groups
🔹 6. Average marks per student
SELECT [Link], AVG([Link]) AS avg_marks
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY [Link];
🔹 7. Total marks per student
SELECT [Link], SUM([Link]) AS total_marks
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY [Link];
🔹 8. Students with average marks above 80
SELECT [Link], AVG([Link]) AS avg_marks
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY [Link]
HAVING AVG([Link]) > 80;
🔹 9. Number of courses taken by each student
SELECT [Link], COUNT(e.course_id) AS courses_taken
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY [Link];
🔹 10. Department-wise average marks
SELECT [Link], AVG([Link]) AS dept_avg
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY [Link];
🔹 11. Highest average performing course
SELECT c.course_name, AVG([Link]) AS avg_marks
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name
ORDER BY avg_marks DESC
LIMIT 1;
🔹 12. Count of students scoring above 80 in each course
SELECT c.course_name, COUNT(*) AS high_scorers
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE [Link] > 80
GROUP BY c.course_name;