SQL Practise questions from Database System Concepts, Seventh Edition
University Database Schema
student(ID, name, dept_name, tot_cred)
instructor(ID, name, dept_name, salary)
course(course_id, title, dept_name, credits)
section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
takes(ID, course_id, sec_id, semester, year, grade)
teaches(ID, course_id, sec_id, semester, year)
advisor(s_id, i_id)
department(dept_name, building, budget)
time_slot(time_slot_id, day, start_hr, start_min, end_hr, end_min)
Queries
1. Find titles of Comp. Sci. courses with 3 credits.
2. Find IDs of students taught by instructor Einstein.
3. Find highest instructor salary.
4. Find instructors earning highest salary.
5. Find enrollment of each section in Fall 2017.
6. Find maximum enrollment in Fall 2017.
7. Find sections with maximum enrollment in Fall 2017.
8. Find ID and GPA of each student (grade_points table given separately).
9. Increase salary of Comp. Sci. instructors by 10%.
10. Delete courses never offered.
11. Insert students with tot_cred > 100 as instructors.
12. Students who took at least one Comp. Sci. course.
13. Students who have not taken any course before 2017.
14. For each department find maximum instructor salary.
15. Find lowest among departmental maximum salaries.
16. Accounting students advised by Physics instructors.
17. Departments whose budget > Philosophy.
18. Students who retook a course ≥3 times.
19. Students who retook at least three different courses.
20. Instructors who teach every course in their department.
21. History students starting with D who have not taken ≥5 Music courses.
22. Instructors who never gave an A grade.
23. Comp. Sci. courses having afternoon sections.
24. Number of students per section.
Grade Conversion Table
grade_points(grade, points)
Questions:
25. Total grade points for student '12345'.
26. GPA of student '12345'.
27. GPA of each student.
28. Modify queries to handle NULL grades.
Marks Table
marks(ID, score)
Questions:
29. Display grade (A/B/C/F) for each student.
30. Count students in each grade.
Bank Database
branch(branch_name, branch_city, assets)
customer(ID, customer_name, customer_street, customer_city)
loan(loan_number, branch_name, amount)
borrower(ID, loan_number)
account(account_number, branch_name, balance)
depositor(ID, account_number)
Questions:
31. Customers with account but no loan.
32. Customers living same address as customer '12345'.
33. Branches having customers with accounts living in Harrison.
34. Customers with accounts in every Brooklyn branch.
35. Total loan amount of bank.
36. Branches with assets greater than at least one Brooklyn branch.
Employee Database
employee(ID, person_name, street, city)
works(ID, company_name, salary)
company(company_name, city)
manages(ID, manager_id)
Questions:
37. Employees working for First Bank Corporation.
38. Same as above but salary > 10000.
39. Employees not working for that company.
40. Employees earning more than all employees of Small Bank Corporation.
41. Company located in every city of Small Bank Corporation.
42. Company with most employees.
43. Companies whose average salary > First Bank Corporation.
44. Employees living in same city as company.
45. Employees living same address as manager.
46. Employees earning more than company average.
47. Company with smallest payroll.
48. Update city of employee '12345'.
49. Give managers of First Bank Corporation raise with condition.
Library Database
member(memb_no, name)
book(isbn, title, authors, publisher)
borrowed(memb_no, isbn, date)
Questions:
50. Members who borrowed at least one McGraw-Hill book.
51. Members who borrowed every McGraw-Hill book.
52. Members who borrowed >5 books per publisher.
53. Average books borrowed per member including zero-borrowers.
Conceptual Questions
54. Give two reasons NULL values may appear.
55. Show <> ALL is equivalent to NOT IN.