SQL SBA #01 and Marking Guide

 SQL SBA #01

Total Marks: 15

You are required to create and manipulate a simple database for a school library system. The database should store information about books. Answer the following questions using SQL statements only.

(a) Create a database named SchoolLibrary. [1 mark]

(b) Create a table named Books with the following fields:

• BookID (Integer, Primary Key)
• Title (Varchar, 100)
• Author (Varchar, 100)
• YearPublished (Integer)
• CopiesAvailable (Integer) [2 marks]

(c) Insert the following three records into the Books table:

1. BookID: 1, Title: 'Introduction to Programming', Author: 'John Smith', YearPublished: 2015, CopiesAvailable: 5
2. BookID: 2, Title: 'Database Systems', Author: 'Mary Brown', YearPublished: 2018, CopiesAvailable: 3
3. BookID: 3, Title: 'Networking Basics', Author: 'Peter White', YearPublished: 2017, CopiesAvailable: 4 [2 marks]

(d) Update the number of copies available for the book with BookID 2 to 7. [1 mark]

(e) Write a query to display all books published after the year 2016. [2 marks]

(f) Create a view named AvailableBooks that displays only the Title and CopiesAvailable of books where CopiesAvailable is greater than 3. [3 marks]

(g) Write a query to display all book records sorted by YearPublished in descending order. [4 marks]

 

 MARKING GUIDE – GRADE 12 SBA (SQL)

Total Marks: 15

(a) Create a database named SchoolLibrary.
[1 mark]
✅ Correct SQL statement:

CREATE DATABASE SchoolLibrary;

• 1 mark: Correct syntax and database name.
• 0 mark: Incorrect syntax or wrong/missing name.

(b) Create a table named Books with the specified fields and primary key.
[2 marks]
✅ Sample correct SQL:

CREATE TABLE Books (

    BookID INT PRIMARY KEY,

    Title VARCHAR(100),

    Author VARCHAR(100),

    YearPublished INT,

    CopiesAvailable INT

);

• 1 mark: Correct table name and field definitions.
• 1 mark: Correct use of PRIMARY KEY on BookID.

(c) Insert the 3 records into the Books table.
[2 marks]
✅ Sample correct SQL:

INSERT INTO Books VALUES

(1, 'Introduction to Programming', 'John Smith', 2015, 5),

(2, 'Database Systems', 'Mary Brown', 2018, 3),

(3, 'Networking Basics', 'Peter White', 2017, 4);

• 1 mark: Correct INSERT INTO syntax.
• 1 mark: All 3 records correctly entered.

(d) Update the number of copies available for BookID 2 to 7.
[1 mark]
✅ Sample correct SQL:

UPDATE Books SET CopiesAvailable = 7 WHERE BookID = 2;

• 1 mark: Correct UPDATE statement with WHERE clause.
• 0 mark: Missing WHERE or wrong field update.

(e) Display all books published after 2016.
[2 marks]
✅ Sample correct SQL:

SELECT * FROM Books WHERE YearPublished > 2016;

• 1 mark: Correct use of SELECT * and table name.
• 1 mark: Correct WHERE clause (YearPublished > 2016).

(f) Create a view AvailableBooks showing Title and CopiesAvailable where CopiesAvailable > 3.
[3 marks]
✅ Sample correct SQL:

CREATE VIEW AvailableBooks AS

SELECT Title, CopiesAvailable

FROM Books

WHERE CopiesAvailable > 3;

• 1 mark: Correct CREATE VIEW syntax.
• 1 mark: Correct field selection (Title, CopiesAvailable).
• 1 mark: Correct WHERE clause (CopiesAvailable > 3).

(g) Display all books sorted by YearPublished in descending order.
[4 marks]
✅ Sample correct SQL:

SELECT * FROM Books ORDER BY YearPublished DESC;

• 1 mark: Correct SELECT *.
• 1 mark: Correct table name.
• 1 mark: Correct ORDER BY clause.    1 mark: Correct use of DESC.