Library Management System
Tables
Books
{book_id, title, author, genre, year_published}
{1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 1925}
{2, '1984', 'George Orwell', 'Dystopian', 1949}
{3, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960}
{4, 'A Brief History of Time', 'Stephen Hawking', 'Science', 1988}
{5, 'The Art of War', 'Sun Tzu', 'Philosophy', -500}
Members
{member_id, name, join_date, city}
{1, 'Alice', '2022-01-10', 'New York'}
{2, 'Bob', '2021-03-05', 'Chicago'}
{3, 'Charlie', '2022-07-19', 'Los Angeles'}
{4, 'Diana', '2023-02-01', 'Boston'}
{5, 'Eve', '2021-11-11', 'Seattle'}
Borrowings
{borrow_id, member_id, book_id, borrow_date, return_date}
{1, 1, 1, '2023-03-01', '2023-03-15'}
{2, 2, 2, '2023-04-01', NULL}
{3, 3, 3, '2023-04-15', NULL}
{4, 1, 4, '2023-04-10', '2023-04-20'}
{5, 5, 5, '2023-03-21', '2023-04-01'}
Librarians
{librarian_id, name, hire_date, branch}
{1, 'Mr. Smith', '2020-05-20', 'Central'}
{2, 'Ms. Johnson', '2019-08-15', 'North'}
{3, 'Mr. Lee', '2021-01-10', 'East'}
BookStocks
{book_id, branch, total_copies, available_copies}
{1, 'Central', 5, 3}
{2, 'Central', 3, 2}
{3, 'North', 4, 1}
{4, 'East', 2, 2}
{5, 'Central', 6, 5}
Practice SQL Queries
1. Add a new column `email` to the `Members` table.
2. Update the city of 'Charlie' to 'San Francisco'.
3. Count how many books are available in the 'Central' branch.
4. Find all books currently borrowed (i.e., not yet returned).
5. List all members who have borrowed books, along with book titles.
6. Get product names and quantities ordered in order ID 1001.
7. List each customer and the number of orders they have placed.
8. List members who joined in 2022.
9. Get details of all books that were borrowed and returned.
10. List all members who have never borrowed a book.
11. Get all books written before the year 1950.
12. Count how many books fall into each genre.
13. Update available copies of book_id 2 in Central to 1.
14. List all borrowings along with member names and book titles.
15. Find all librarians hired before 2021.
Online Shop
Tables
Customers
{customer_id, name, email, city}
{1, 'Alice', 'alice@mail.com', 'New York'}
{2, 'Bob', 'bob@mail.com', 'Chicago'}
{3, 'Charlie', 'charlie@mail.com', 'San Francisco'}
{4, 'Diana', 'diana@mail.com', 'Boston'}
{5, 'Eve', 'eve@mail.com', 'Seattle'}
Products
{product_id, product_name, category, price, stock_quantity}
{101, 'Laptop', 'Electronics', 999.99, 10}
{102, 'Smartphone', 'Electronics', 499.99, 25}
{103, 'Coffee Maker', 'Appliances', 79.99, 15}
{104, 'Desk Chair', 'Furniture', 129.99, 8}
{105, 'Book: SQL Basics', 'Books', 29.99, 50}
Orders
{order_id, customer_id, order_date}
{1001, 1, '2023-05-01'}
{1002, 2, '2023-05-03'}
{1003, 1, '2023-05-05'}
{1004, 4, '2023-05-06'}
OrderItems
{order_item_id, order_id, product_id, quantity}
{1, 1001, 101, 1}
{2, 1001, 105, 2}
{3, 1002, 102, 1}
{4, 1003, 103, 1}
{5, 1004, 104, 1}
{6, 1004, 105, 1}
Suppliers
{supplier_id, supplier_name, contact_email}
{1, 'ElectroWorld', 'contact@electroworld.com'}
{2, 'HomeEssentials', 'hello@homeessentials.com'}
{3, 'BookBarn', 'sales@bookbarn.com'}
ProductSuppliers
{product_id, supplier_id}
{101, 1}
{102, 1}
{103, 2}
{104, 2}
{105, 3}
Practice SQL Queries
1. List all products in the 'Electronics' category.
2. Find the total number of customers.
3. Show all customers from 'Boston'.
4. List all orders with customer names and order dates.
5. Get product names and quantities ordered in order ID 1001.
6. List each customer and the number of orders they have placed.
7. Add a column `phone` to the `Customers` table.
8. Update the stock quantity of 'Desk Chair' to 5.
9. Insert a new customer named 'Frank' from 'Austin'.
10. Delete the order with order_id 1003.
11. Which product has been sold the most?
12. List the names of all suppliers and the products they supply.
13. Calculate total revenue from each product.
14. Find the average order value (sum of order total / number of orders).
15. List customers who have not placed any orders.
16. Show product names that were never ordered.
17. List the top 3 most expensive products.
18. Find customers who ordered more than one item in any single order.
University Student Course Management
Tables
Students
{student_id, name, age, gender, dept_id}
{1, 'Alice', 20, 'F', 'CS'}
{2, 'Bob', 21, 'M', 'EE'}
{3, 'Charlie', 22, 'M', 'CS'}
{4, 'Diana', 20, 'F', 'ME'}
{5, 'Eve', 23, 'F', 'EE'}
Courses
{course_id, course_name, dept_id}
{101, 'Database Systems', 'CS'}
{102, 'Digital Circuits', 'EE'}
{103, 'Thermodynamics', 'ME'}
{104, 'Operating Systems', 'CS'}
{105, 'Signals & Systems', 'EE'}
Enrollments
{student_id, course_id, grade}
{1, 101, 'A'}
{2, 102, 'B'}
{3, 101, 'A'}
{3, 104, 'B'}
{4, 103, 'A'}
{5, 102, 'C'}
{5, 105, 'B'}
{1, 104, 'B'}
Departments
{dept_id, dept_name}
{'CS', 'Computer Science'}
{'EE', 'Electrical Engineering'}
{'ME', 'Mechanical Engineering'}
Professors
{prof_id, prof_name, dept_id}
{1, 'Dr. Smith', 'CS'}
{2, 'Dr. Johnson', 'EE'}
{3, 'Dr. Lee', 'ME'}
CourseProfessors
{course_id, prof_id}
{101, 1}
{102, 2}
{103, 3}
{104, 1}
{105, 2}
Practice SQL Queries
1. List the names of all students enrolled in 'Database Systems'.
2. Find all courses taught by 'Dr. Johnson'.
3. Display the average age of students in the CS department.
4. Get the course name and department name for all courses.
5. List students and their grades in 'Operating Systems'.
6. Find students who got an 'A' in any course.
7. Count how many students are in each department.
8. List all professors and the number of courses they teach.
9. Show all courses a student named 'Eve' is enrolled in.
10. List students who are not enrolled in any course.
11. Find the course(s) with the highest number of students enrolled.
12. Get the names of students enrolled in both 'Database Systems' and 'Operating Systems'.
13. Retrieve the full names of departments along with the number of students in each.
14. List all students and their department names.
15. Find students who are enrolled in a course not offered by their own department.
