Scribd
Upload
20 views3 pages

Library and Online Shop SQL Queries

The document contains SQL queries related to three different systems: Library Management, Online Shop, and University Student Course Management. Each section includes various operations such as selecting, updating, and joining data from different tables to manage members, products, orders, students, and courses. The queries aim to retrieve specific information or perform updates based on certain conditions.

Uploaded by

arpitachow1999
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views3 pages

Library and Online Shop SQL Queries

The document contains SQL queries related to three different systems: Library Management, Online Shop, and University Student Course Management. Each section includes various operations such as selecting, updating, and joining data from different tables to manage members, products, orders, students, and courses. The queries aim to retrieve specific information or perform updates based on certain conditions.

Uploaded by

arpitachow1999
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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 [Link], [Link] 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 [Link], [Link] FROM OrderItems bi JOIN Products b ON bi.product_id =


b.product_id WHERE bi.order_id = 1001;

7. SELECT [Link], COUNT(*) AS order_count FROM Orders o JOIN Members m ON


o.member_id = m.member_id GROUP BY [Link];

8. SELECT * FROM Members WHERE YEAR(join_date) = 2022;

9. SELECT * FROM Borrowings WHERE return_date IS NOT NULL;

10. SELECT [Link] 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 [Link], [Link] 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, [Link], o.order_date FROM Orders o JOIN Customers c ON


o.customer_id = c.customer_id;

5. SELECT p.product_name, [Link] FROM OrderItems oi JOIN Products p ON


oi.product_id = p.product_id WHERE oi.order_id = 1001;

6. SELECT [Link], COUNT(*) AS order_count FROM Customers c JOIN Orders o ON


c.customer_id = o.customer_id GROUP BY [Link];

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([Link]) 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([Link] * [Link]) 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([Link] * [Link]) 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 [Link] 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;
Answers: University Student Course Management
1. SELECT [Link] FROM Students s JOIN Enrollments e ON s.student_id = e.student_id JOIN
Courses c ON e.course_id = c.course_id WHERE c.course_name = 'Database Systems';

2. SELECT c.course_name FROM Courses c JOIN CourseProfessors cp ON c.course_id =


cp.course_id JOIN Professors p ON cp.prof_id = p.prof_id WHERE p.prof_name = 'Dr.
Johnson';

3. SELECT AVG(age) FROM Students WHERE dept_id = 'CS';

4. SELECT c.course_name, d.dept_name FROM Courses c JOIN Departments d ON c.dept_id =


d.dept_id;

5. SELECT [Link], [Link] FROM Students s JOIN Enrollments e ON s.student_id =


e.student_id JOIN Courses c ON e.course_id = c.course_id WHERE c.course_name =
'Operating Systems';

6. SELECT DISTINCT [Link] FROM Students s JOIN Enrollments e ON s.student_id =


e.student_id WHERE [Link] = 'A';

7. SELECT dept_id, COUNT(*) AS student_count FROM Students GROUP BY dept_id;

8. SELECT p.prof_name, COUNT(*) AS course_count FROM Professors p JOIN


CourseProfessors cp ON p.prof_id = cp.prof_id GROUP BY p.prof_name;

9. SELECT c.course_name FROM Courses c JOIN Enrollments e ON c.course_id = e.course_id


JOIN Students s ON s.student_id = e.student_id WHERE [Link] = 'Eve';

10. SELECT [Link] FROM Students s LEFT JOIN Enrollments e ON s.student_id =


e.student_id WHERE e.student_id IS NULL;

11. SELECT c.course_name FROM Courses c JOIN Enrollments e ON c.course_id = e.course_id


GROUP BY c.course_id, c.course_name ORDER BY COUNT(*) DESC LIMIT 1;

12. SELECT [Link] FROM Students s JOIN Enrollments e1 ON s.student_id = e1.student_id


JOIN Courses c1 ON e1.course_id = c1.course_id JOIN Enrollments e2 ON s.student_id =
e2.student_id JOIN Courses c2 ON e2.course_id = c2.course_id WHERE c1.course_name =
'Database Systems' AND c2.course_name = 'Operating Systems';

13. SELECT d.dept_name, COUNT(*) FROM Students s JOIN Departments d ON s.dept_id =


d.dept_id GROUP BY d.dept_name;

14. SELECT [Link], d.dept_name FROM Students s JOIN Departments d ON s.dept_id =


d.dept_id;

15. SELECT DISTINCT [Link] FROM Students s JOIN Enrollments e ON s.student_id =


e.student_id JOIN Courses c ON e.course_id = c.course_id WHERE s.dept_id != c.dept_id;

Common questions

Powered by AI

To efficiently add a new column for storing phone numbers in the Customers table, use the ALTER TABLE statement: ALTER TABLE Customers ADD phone VARCHAR(20); This alters the table schema by adding a new column for phone numbers .

To list courses taught by 'Dr. Johnson', the query would be: SELECT c.course_name FROM Courses c JOIN CourseProfessors cp ON c.course_id = cp.course_id JOIN Professors p ON cp.prof_id = p.prof_id WHERE p.prof_name = 'Dr. Johnson'; This retrieves all course names associated with the professor .

To retrieve hires before a specific date, such as '2021-01-01', use the query: SELECT * FROM Librarians WHERE hire_date < '2021-01-01'; This filters the dataset to show only librarians hired before January 1, 2021 .

To find the names of all members who have never borrowed a book, you would use a LEFT JOIN to locate members without any corresponding borrowings. The SQL query would be: SELECT m.name FROM Members m LEFT JOIN Borrowings b ON m.member_id = b.member_id WHERE b.member_id IS NULL; .

To determine the presence and count of book genres, employ the query: SELECT genre, COUNT(*) FROM Books GROUP BY genre; This groups books by their genre and counts them .

You can determine which product generated the highest revenue by calculating the total revenue for each product and then comparing them. Use the query: 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 ORDER BY revenue DESC LIMIT 1; This gives you the product with the highest total revenue .

To find the most popular course based on enrollment, use the query: SELECT c.course_name FROM Courses c JOIN Enrollments e ON c.course_id = e.course_id GROUP BY c.course_id, c.course_name ORDER BY COUNT(*) DESC LIMIT 1; This ranks courses by student count and retrieves the top one .

To retrieve the average age of Computer Science students, use the SQL query: SELECT AVG(age) FROM Students WHERE dept_id = 'CS'; This calculates the average age of all students in the Computer Science department .

To update book availability information in specific branches, the SQL query would be: UPDATE BookStocks SET available_copies = 1 WHERE book_id = 2 AND branch = 'Central'; This specifically updates the available copies of a book at the Central branch .

To find all customers who have not placed any orders, utilize a LEFT JOIN to identify customers without order records: SELECT c.name FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL; This ensures a list of customers without any orders .

You might also like