Below is a practical guide for SQL that aligns with the syllabus and covers key concepts such as database creation, table manipulation, data querying, and normalization. Each exercise includes a description, syllabus coverage, aim, and SQL code.
Exercise 8.1: Creating a Database
Description : Write SQL commands to create a new database and use it.
Syllabus Coverage : Data Definition Language (DDL), creating and using databases.
Aim : Introduce students to creating and managing databases.
1 2 3 | -- SQL Code CREATE DATABASE school_db; USE school_db; |
Exercise 8.2: Creating Tables
Description : Write SQL commands to create a table with appropriate fields, data types, and constraints.
Syllabus Coverage : Creating tables, setting primary keys, and foreign keys.
Aim : Teach students how to define tables and enforce relationships.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- SQL Code CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT CHECK (age > 0), grade CHAR(2) ); CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL ); CREATE TABLE enrollments ( enrollment_id INT PRIMARY KEY, student_id INT, course_id INT, FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); |
Exercise 8.3: Inserting Data
Description : Write SQL commands to insert sample data into the tables created in Exercise 8.2.
Syllabus Coverage : Data Manipulation Language (DML), inserting data.
Aim : Teach students how to populate tables with data.
1 2 3 4 5 6 7 8 9 10 11 12 | -- SQL Code INSERT INTO students (student_id, name, age, grade) VALUES (1, 'Alice', 16, 'A'), (2, 'Bob', 15, 'B'); INSERT INTO courses (course_id, course_name) VALUES (101, 'Mathematics'), (102, 'Science'); INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES (1001, 1, 101), (1002, 2, 102); |
Exercise 8.4: Querying Data
Description : Write SQL queries to retrieve data from a single table and multiple tables using joins.
Syllabus Coverage : Extracting rows and columns from a single table and multiple tables using inner join operations.
Aim : Teach students how to query data effectively.
1 2 3 4 5 6 7 8 9 | -- SQL Code -- Retrieve all students SELECT * FROM students; -- Retrieve students enrolled in courses SELECT s.name, c.course_name FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id; |
Exercise 8.5: Updating and Deleting Data
Description : Write SQL commands to update and delete records in a table.
Syllabus Coverage : Modifying and deleting data.
Aim : Teach students how to modify and remove data from tables.
1 2 3 4 5 6 7 8 9 | -- SQL Code -- Update a student's grade UPDATE students SET grade = 'A+' WHERE student_id = 1; -- Delete a course DELETE FROM courses WHERE course_id = 102; |
Exercise 8.6: Altering Table Structure
Description : Write SQL commands to add and delete columns, and modify constraints.
Syllabus Coverage : Altering tables (inserting and deleting attributes, adding and deleting foreign keys/primary keys).
Aim : Teach students how to modify table structures dynamically.
1 2 3 4 5 6 7 8 9 | -- SQL Code -- Add a new column ALTER TABLE students ADD email VARCHAR(100); -- Delete a column ALTER TABLE students DROP COLUMN email; -- Add a foreign key constraint ALTER TABLE enrollments ADD CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(course_id); |
Exercise 8.7: Dropping Tables and Databases
Description : Write SQL commands to drop tables and databases.
Syllabus Coverage : Dropping tables and databases.
Aim : Teach students how to clean up resources.
1 2 3 4 5 6 | -- SQL Code -- Drop a table DROP TABLE enrollments; -- Drop a database DROP DATABASE school_db; |
Exercise 8.8: Normalization
Description : Normalize a given table to eliminate redundancies and anomalies.
Syllabus Coverage : Need for normalization, functional dependencies, levels of normalization (1NF, 2NF, 3NF).
Aim : Teach students how to design efficient and normalized databases.
Normalized Tables:
- Students Table :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Create the students table CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL ); -- Create the courses table CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL ); -- Create the enrollments table CREATE TABLE enrollments ( enrollment_id INT PRIMARY KEY, student_id INT, course_id INT, FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); |
Exercise 8.9: Functional Dependencies
Description : Identify functional dependencies in a given table and normalize it to 3NF.
Syllabus Coverage : Functional dependencies (full, partial, transitive), normalization levels.
Aim : Reinforce understanding of normalization principles.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- Create the Orders table CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Create the customers table CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL ); -- Create the products table CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL ); -- Create the order_details table CREATE TABLE order_details ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); |
Exercise 8.10: Aggregation and Grouping
Description : Write SQL queries to perform aggregation and grouping operations.
Syllabus Coverage : Advanced querying techniques.
Aim : Teach students how to summarize and analyze data.
1 2 3 4 5 6 7 8 9 10 | -- SQL Code -- Count the number of students in each grade SELECT grade, COUNT(*) AS student_count FROM students GROUP BY grade; -- Find the total quantity of products ordered SELECT product_id, SUM(quantity) AS total_quantity FROM order_details GROUP BY product_id; |
Exercise 8.11: Subqueries
Description : Write SQL queries that use subqueries to retrieve data.
Syllabus Coverage : Advanced querying techniques.
Aim : Teach students how to use nested queries for complex data retrieval.
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- SQL Code -- Find students who are enrolled in the course 'Mathematics' SELECT name FROM students WHERE student_id IN ( SELECT student_id FROM enrollments WHERE course_id = ( SELECT course_id FROM courses WHERE course_name = 'Mathematics' ) ); |
Exercise 8.12: Indexing
Description : Create an index on a table to improve query performance.
Syllabus Coverage : Performance optimization.
Aim : Introduce students to indexing for faster data retrieval.
1 2 3 | -- SQL Code -- Create an index on the student name CREATE INDEX idx_student_name ON students(name); |
Above exercises comprehensively cover SQL-related topics in your syllabus, including DDL , DML , normalization , aggregation , subqueries , and indexing . They are designed to progressively build students’ skills while aligning with the learning outcomes.
Below are advanced SQL exercises that go beyond the syllabus, exploring modern and advanced database concepts. These exercises will help you deepen your understanding of SQL and its capabilities in handling complex queries, optimization, and real-world applications.
Exercise 1: Advanced Joins
Description : Write SQL queries using advanced join types like LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
What You Learn : Advanced join techniques for combining data from multiple tables.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- SQL Code -- Retrieve all students and their enrolled courses (even if they are not enrolled) SELECT s.name, c.course_name FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id; -- Retrieve all courses and the students enrolled in them (even if no students are enrolled) SELECT c.course_name, s.name FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id LEFT JOIN students s ON e.student_id = s.student_id; -- Retrieve all students and courses, including those with no matches SELECT s.name, c.course_name FROM students s FULL OUTER JOIN enrollments e ON s.student_id = e.student_id FULL OUTER JOIN courses c ON e.course_id = c.course_id; |
Exercise 2: Window Functions
Description : Use SQL window functions to perform calculations across a set of rows related to the current row.
What You Learn : Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK().
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 | -- SQL Code -- Rank students by their age SELECT student_id, name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num, RANK() OVER (ORDER BY age DESC) AS rank, DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank FROM students; -- Calculate cumulative sum of quantities ordered per product SELECT product_id, quantity, SUM(quantity) OVER (PARTITION BY product_id ORDER BY order_date) AS cumulative_sum FROM order_details; |
Exercise 3: Common Table Expressions (CTEs)
Description : Use Common Table Expressions (CTEs) to simplify complex queries.
What You Learn : Writing reusable subqueries with CTEs.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- SQL Code -- Find students who are older than the average age using a CTE WITH AverageAge AS ( SELECT AVG(age) AS avg_age FROM students ) SELECT name, age FROM students, AverageAge WHERE students.age > AverageAge.avg_age; -- Find the top 3 most enrolled courses using a CTE WITH CourseEnrollments AS ( SELECT course_id, COUNT(*) AS enrollment_count FROM enrollments GROUP BY course_id ) SELECT course_name, enrollment_count FROM CourseEnrollments ce JOIN courses c ON ce.course_id = c.course_id ORDER BY enrollment_count DESC LIMIT 3; |
Exercise 4: Recursive Queries
Description : Write recursive SQL queries to handle hierarchical data (e.g., organizational charts).
What You Learn : Recursive CTEs for querying hierarchical structures.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- SQL Code -- Create an employees table with a self-referencing hierarchy CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(50), manager_id INT, FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ); INSERT INTO employees (employee_id, name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2); -- Retrieve the hierarchy of employees using a recursive CTE WITH RECURSIVE EmployeeHierarchy AS ( SELECT employee_id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.name, e.manager_id, eh.level + 1 FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM EmployeeHierarchy; |
Exercise 5: JSON Data Handling
Description : Work with JSON data stored in a relational database.
What You Learn : Querying and manipulating JSON fields in SQL.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- SQL Code -- Create a table with JSON data CREATE TABLE products ( product_id INT PRIMARY KEY, product_details JSON ); INSERT INTO products (product_id, product_details) VALUES (1, '{"name": "Laptop", "price": 1000, "specs": {"ram": "16GB", "storage": "512GB"}}'), (2, '{"name": "Phone", "price": 500, "specs": {"ram": "8GB", "storage": "128GB"}}'); -- Query JSON fields SELECT product_id, product_details->>'name' AS product_name, product_details->>'price' AS price FROM products; -- Filter based on JSON values SELECT product_id, product_details->>'name' AS product_name FROM products WHERE CAST(product_details->>'price' AS INT) > 700; |
Exercise 6: Index Optimization
Description : Analyze query performance and create indexes to optimize slow queries.
What You Learn : Indexing strategies and query optimization.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 | -- SQL Code -- Analyze query performance without an index EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20; -- Create an index on the age column CREATE INDEX idx_student_age ON students(age); -- Analyze query performance with the index EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20; |
Exercise 7: Stored Procedures
Description : Write a stored procedure to encapsulate a complex business logic.
What You Learn : Creating reusable database logic with stored procedures.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- SQL Code -- Create a stored procedure to enroll a student in a course DELIMITER // CREATE PROCEDURE EnrollStudent(IN student_id INT, IN course_id INT) BEGIN INSERT INTO enrollments (student_id, course_id) VALUES (student_id, course_id); END // DELIMITER ; -- Call the stored procedure CALL EnrollStudent(1, 101); |
Exercise 8: Triggers
Description : Write a trigger to automatically log changes to a table.
What You Learn : Automating actions with triggers.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -- SQL Code -- Create a log table CREATE TABLE student_logs ( log_id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, action VARCHAR(50), action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create a trigger to log deletions DELIMITER // CREATE TRIGGER before_student_delete BEFORE DELETE ON students FOR EACH ROW BEGIN INSERT INTO student_logs (student_id, action) VALUES (OLD.student_id, 'Deleted'); END // DELIMITER ; -- Test the trigger DELETE FROM students WHERE student_id = 1; SELECT * FROM student_logs; |
Exercise 9: Transactions
Description : Use transactions to ensure atomicity in multi-step operations.
What You Learn : Managing database transactions for consistency.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -- SQL Code -- Start a transaction START TRANSACTION; -- Perform multiple operations UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- Commit or rollback based on success COMMIT; -- Simulate a failure and rollback START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; ROLLBACK; |
Exercise 10: Materialized Views
Description : Create a materialized view to store the results of a complex query for faster access.
What You Learn : Materialized views for performance optimization.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- SQL Code -- Create a materialized view for student enrollments CREATE MATERIALIZED VIEW student_enrollment_summary AS SELECT s.name, c.course_name, COUNT(*) AS enrollment_count FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id GROUP BY s.name, c.course_name; -- Query the materialized view SELECT * FROM student_enrollment_summary; -- Refresh the materialized view when data changes REFRESH MATERIALIZED VIEW student_enrollment_summary; |
Exercise 11: Partitioning
Description : Partition a large table to improve query performance.
What You Learn : Table partitioning for scalability.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- SQL Code -- Create a partitioned table for sales data CREATE TABLE sales ( sale_id INT PRIMARY KEY, sale_date DATE, amount DECIMAL(10, 2) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023) ); -- Insert sample data INSERT INTO sales (sale_id, sale_date, amount) VALUES (1, '2020-05-10', 1000), (2, '2021-08-15', 1500), (3, '2022-03-20', 2000); -- Query a specific partition SELECT * FROM sales PARTITION (p2021); |
Exercise 12: Full-Text Search
Description : Implement full-text search for efficient text-based queries.
What You Learn : Searching unstructured text data efficiently.
Code Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- SQL Code -- Create a table with full-text indexing CREATE TABLE articles ( article_id INT PRIMARY KEY, title VARCHAR(255), content TEXT, FULLTEXT(title, content) ); INSERT INTO articles (article_id, title, content) VALUES (1, 'Introduction to SQL', 'SQL is a powerful language for managing databases.'), (2, 'Advanced SQL Techniques', 'Learn about joins, window functions, and more.'); -- Perform a full-text search SELECT * FROM articles WHERE MATCH(title, content) AGAINST('SQL'); |
These exercises explore advanced SQL concepts such as window functions , recursive queries , JSON handling , stored procedures , triggers , transactions , materialized views , partitioning , and full-text search . They provide practical, real-world applications of SQL and will help you explore areas beyond the syllabus.