Answers: Library Management System
1. ALTER TABLE Members ADD email VARCHAR(100);
2. UPDATE Members SET city = 'San Francisco' WHERE name = 'Charlie';
3. SELECT SUM(available_copies) FROM BookStocks WHERE branch = 'Central';
4. SELECT * FROM Borrowings WHERE return_date IS NULL;
5. SELECT m.name, b.title FROM Members m JOIN Borrowings br ON m.member_id =
br.member_id JOIN Books b ON br.book_id = b.book_id;
6. SELECT b.title, bi.quantity FROM OrderItems bi JOIN Products b ON bi.product_id =
b.product_id WHERE bi.order_id = 1001;
7. SELECT m.name, COUNT(*) AS order_count FROM Orders o JOIN Members m ON
o.member_id = m.member_id GROUP BY m.name;
8. SELECT * FROM Members WHERE YEAR(join_date) = 2022;
9. SELECT * FROM Borrowings WHERE return_date IS NOT NULL;
10. SELECT m.name FROM Members m LEFT JOIN Borrowings b ON m.member_id =
b.member_id WHERE b.member_id IS NULL;
11. SELECT * FROM Books WHERE year_published < 1950;
12. SELECT genre, COUNT(*) FROM Books GROUP BY genre;
13. UPDATE BookStocks SET available_copies = 1 WHERE book_id = 2 AND branch =
'Central';
14. SELECT m.name, b.title FROM Borrowings br JOIN Members m ON br.member_id =
m.member_id JOIN Books b ON br.book_id = b.book_id;
15. SELECT * FROM Librarians WHERE hire_date < '2021-01-01';
Answers: Online Shop
1. SELECT * FROM Products WHERE category = 'Electronics';
2. SELECT COUNT(*) FROM Customers;
3. SELECT * FROM Customers WHERE city = 'Boston';
4. SELECT o.order_id, c.name, o.order_date FROM Orders o JOIN Customers c ON
o.customer_id = c.customer_id;
5. SELECT p.product_name, oi.quantity FROM OrderItems oi JOIN Products p ON
oi.product_id = p.product_id WHERE oi.order_id = 1001;
6. SELECT c.name, COUNT(*) AS order_count FROM Customers c JOIN Orders o ON
c.customer_id = o.customer_id GROUP BY c.name;
7. ALTER TABLE Customers ADD phone VARCHAR(20);
8. UPDATE Products SET stock_quantity = 5 WHERE product_name = 'Desk Chair';
9. INSERT INTO Customers (customer_id, name, city) VALUES (6, 'Frank', 'Austin');
10. DELETE FROM Orders WHERE order_id = 1003;
11. SELECT p.product_name, SUM(oi.quantity) AS total_sold FROM OrderItems oi JOIN
Products p ON oi.product_id = p.product_id GROUP BY p.product_name ORDER BY total_sold
DESC LIMIT 1;
12. SELECT s.supplier_name, p.product_name FROM Products p JOIN ProductSuppliers ps
ON p.product_id = ps.product_id JOIN Suppliers s ON ps.supplier_id = s.supplier_id;
13. SELECT p.product_name, SUM(oi.quantity * p.price) AS revenue FROM OrderItems oi
JOIN Products p ON oi.product_id = p.product_id GROUP BY p.product_name;
14. SELECT AVG(order_total) FROM (SELECT o.order_id, SUM(p.price * oi.quantity) AS
order_total FROM Orders o JOIN OrderItems oi ON o.order_id = oi.order_id JOIN Products p
ON oi.product_id = p.product_id GROUP BY o.order_id) AS totals;
15. SELECT c.name FROM Customers c LEFT JOIN Orders o ON c.customer_id =
o.customer_id WHERE o.customer_id IS NULL;
16. SELECT p.product_name FROM Products p LEFT JOIN OrderItems oi ON p.product_id =
oi.product_id WHERE oi.product_id IS NULL;
17. SELECT * FROM Products ORDER BY price DESC LIMIT 3;
18. SELECT o.order_id, COUNT(*) FROM OrderItems oi JOIN Orders o ON oi.order_id =
o.order_id GROUP BY o.order_id HAVING COUNT(*) > 1;
