Scribd
Upload
4 views4 pages

SQL Exercise

The document presents a model exam question for a Library Management System, detailing the structure of Members, Books, and Borrowings tables. It includes various SQL tasks such as creating tables, modifying them, updating records, and performing queries with different SQL operations like JOINs and aggregate functions. The exam covers a comprehensive range of beginner MySQL topics, totaling 100 marks.

Uploaded by

Udara J
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
4 views4 pages

SQL Exercise

The document presents a model exam question for a Library Management System, detailing the structure of Members, Books, and Borrowings tables. It includes various SQL tasks such as creating tables, modifying them, updating records, and performing queries with different SQL operations like JOINs and aggregate functions. The exam covers a comprehensive range of beginner MySQL topics, totaling 100 marks.

Uploaded by

Udara J
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

SQL Practice 1

A good model exam question should make you use all the topics in one scenario. Here's a realistic one:

Model Exam Question – Library Management System


A library stores information about its members and books.

Table Structures
Members

MemberID MemberName City


1 Kamal Colombo
2 Nimal Galle
3 Sunil Kandy
4 Amara Jaffna

Books

BookID BookTitle Category Price


101 Database Systems IT 2500
102 Java Programming IT 3000
103 English Grammar Language 1500
104 Mathematics Education 2000

Borrowings

BorrowID MemberID BookID DaysBorrowed


1 1 101 10
2 1 102 7
3 2 101 12
4 3 103 5
Question 1 (CREATE TABLE)
Write SQL statements to create the following tables:

Members
Books
Borrowings

Include:

Primary Keys
Foreign Keys where necessary

(15 Marks)

Question 2 (MODIFY TABLE)


Add a new column called Email to the Members table.

(5 Marks)

Question 3 (UPDATE)
Update the city of member Kamal to Kalutara .

Update the price of the book "Java Programming" to 3500 .

(10 Marks)

Question 4 (ORDER BY)


Write SQL queries to:

a) Display all books ordered by price in ascending order.

b) Display borrowing records ordered by DaysBorrowed in descending order.

(10 Marks)

Question 5 (Aggregate Functions)


Write SQL queries to find:

a) Total number of members.

b) Average book price.

c) Highest book price.

d) Lowest book price.

e) Total value of all books.

(15 Marks)

Question 6 (GROUP BY)


Write SQL queries to find:

a) Number of books borrowed by each member.

b) Average borrowing period for each book.

(15 Marks)

Question 7 (INNER JOIN)


Display:

Member Name
Book Title
Days Borrowed

using an INNER JOIN.

(10 Marks)

Question 8 (LEFT JOIN)


Display all members and the books they borrowed.

Ensure members who have not borrowed any books are also displayed.

(10 Marks)
Question 9 (RIGHT JOIN)
Display all books and the members who borrowed them.

Ensure books that have never been borrowed are also displayed.

(10 Marks)

Question 10 (Comprehensive Query)


Write a query to display:

Member Name
Book Title
Days Borrowed

Sort the results by DaysBorrowed from highest to lowest.

(10 Marks)

Total: 100 Marks


This single paper forces you to use:
✅ CREATE TABLE
✅ ALTER/MODIFY TABLE
✅ UPDATE
✅ ORDER BY
✅ GROUP BY
✅ COUNT, SUM, AVG, MAX, MIN
✅ INNER JOIN
✅ LEFT JOIN
✅ RIGHT JOIN
which covers the full beginner MySQL syllabus you described.

You might also like