Page 1: Database Design & DDL
1. Create a database named CollegeDB.
2. Create tables Students, Courses, and Enrollments with appropriate primary and foreign keys.
3. Define constraints: NOT NULL, UNIQUE, and CHECK on relevant columns.
4. Add a column email to Students with a UNIQUE constraint.
5. Modify the Courses table to add a course_fee column.
6. Drop a column from a table and recreate it.
7. Explain the difference between PRIMARY KEY and UNIQUE KEY.
8. What is a FOREIGN KEY and why is it used?
9. Write SQL to delete a table safely.
10. Explain normalization and identify normal forms.
Page 2: DML Operations
1. Insert 5 records into Students table.
2. Insert multiple records using a single INSERT statement.
3. Update student course enrollment.
4. Increase course fees by 10%.
5. Delete students who have not enrolled in any course.
6. Write a query to replace NULL values with 'Not Provided'.
7. Insert data using values from another table.
8. Explain TRUNCATE vs DELETE.
9. Restore deleted data using transaction control.
10. Demonstrate COMMIT and ROLLBACK.
Page 3: Basic SELECT Queries
1. Retrieve all student details.
2. Display only student names and emails.
3. Find students enrolled in course_id = 101.
4. Sort students alphabetically.
5. Display distinct course IDs.
6. Use WHERE clause with multiple conditions.
7. Find students whose names start with 'A'.
8. Retrieve records using BETWEEN.
9. Display top 5 records.
10. Explain ORDER BY vs GROUP BY.
Page 4: Aggregate Functions
1. Count total students.
2. Find average course fee.
3. Find maximum and minimum course fee.
4. Count students per course.
5. Display total revenue from course fees.
6. Use HAVING to filter grouped results.
7. Show courses with more than 3 students.
8. Difference between WHERE and HAVING.
9. Find sum of fees grouped by course.
10. Round average fee to 2 decimal places.
Page 5: SQL JOINS
1. Display student names with course names using INNER JOIN.
2. Show all students even if not enrolled (LEFT JOIN).
3. Show all courses even if no students enrolled (RIGHT JOIN).
4. Display unmatched records from both tables.
5. Use FULL JOIN (or simulate it).
6. Show students enrolled in multiple courses.
7. Join three tables to display full enrollment details.
8. Explain SELF JOIN with example.
9. Find students without valid course records.
10. Compare INNER JOIN and OUTER JOIN.
Page 6: Subqueries
1. Find students enrolled in the most expensive course.
2. Retrieve courses with fees above average.
3. Display students whose age is greater than average age.
4. Use subquery in WHERE clause.
5. Use subquery in FROM clause.
6. Difference between correlated and non-correlated subqueries.
7. Find second highest course fee.
8. Find students not enrolled in any course using subquery.
9. Use EXISTS to filter results.
10. Use NOT EXISTS example.
Page 7: Views & Indexes
1. Create a view showing student and course details.
2. Update data using a view.
3. Drop a view.
4. Explain advantages of views.
5. Create an index on student_name.
6. Explain clustered vs non-clustered index.
7. When should indexes be avoided?
8. Show query performance improvement using index.
9. Remove an index.
10. Explain materialized views.
Page 8: Transactions & Security
1. Explain ACID properties.
2. Write a transaction example using COMMIT and ROLLBACK.
3. Create a user and grant SELECT permission.
4. Revoke permissions from a user.
5. Explain database security best practices.
6. What is role-based access control?
7. Prevent SQL injection techniques.
8. Lock a table for updates.
9. Explain isolation levels.
10. Difference between GRANT and REVOKE.
Page 9: Real-World Case Study
1. Design a database for an online shopping system.
2. Write queries to find top-selling products.
3. Identify customers who never placed orders.
4. Find monthly revenue.
5. Show most active users.
6. Detect duplicate customer records.
7. Retrieve last 30 days orders.
8. Find abandoned carts.
9. Create reports for management.
10. Optimize queries for performance.
Page 10: Advanced SQL Concepts
1. Explain window functions.
2. Use ROW_NUMBER() to rank students.
3. Use RANK() vs DENSE_RANK().
4. Write a Common Table Expression (CTE).
5. Use recursive CTE example.
6. Pivot rows into columns.
7. Explain stored procedures.
8. Create a trigger example.
9. Explain partitioning.
10. Discuss SQL vs NoSQL differences.