SQL SBA #2 and Marking Guide

You are managing a simple database system for a Library Management System. You are required to perform various SQL operations to manage the data stored in a table called Books.

(a) [3 marks]
Create a table named Books with the following fields:

• BookID – Integer, Primary Key
• Title – Varchar(100)
• Author – Varchar(50)
• PublishedYear – Integer
• Price – Decimal(6,2)
• Available – Boolean

(b) [3 marks]
Insert the following records into the Books table using one INSERT INTO statement per record:

1. (1, 'Introduction to SQL', 'John Smith', 2018, 89.99, TRUE)
2. (2, 'Advanced Database Concepts', 'Jane Doe', 2020, 120.50, TRUE)
3. (3, 'Data Structures in Practice', 'Alan Turing', 2016, 75.00, FALSE)

(c) [1 mark]
Create a form named BookEntryForm to input new records into the Books table. (Describe the fields to be included in the form.)

(d) [2 marks]
Write an SQL query to display all books that are currently available (i.e., Available = TRUE), showing only Title, Author, and Price.

(e) [2 marks]
Write an SQL query to update the price of the book titled 'Data Structures in Practice' to 85.00.

(f) [1 mark]
Write an SQL query to delete the book record where the BookID is 2.

(g) [3 marks]
Write an SQL query that displays all books published after 2017, sorted by PublishedYear in descending order.

 

 

 

✅ Marking Key and Guide (Total: 15 Marks)

(a) Create Table – [3 Marks]

Expected SQL:

CREATE TABLE Books (

    BookID INT PRIMARY KEY,

    Title VARCHAR(100),

    Author VARCHAR(50),

    PublishedYear INT,

    Price DECIMAL(6,2),

    Available BOOLEAN

);

Marking Guide:

• 1 mark – Correct SQL syntax for CREATE TABLE and all fields included
• 1 mark – Correct data types used (INT, VARCHAR, DECIMAL, BOOLEAN)
• 1 mark – Primary key correctly set on BookID

(b) Insert Records – [3 Marks]

Expected SQL:

INSERT INTO Books VALUES (1, 'Introduction to SQL', 'John Smith', 2018, 89.99, TRUE);

INSERT INTO Books VALUES (2, 'Advanced Database Concepts', 'Jane Doe', 2020, 120.50, TRUE);

INSERT INTO Books VALUES (3, 'Data Structures in Practice', 'Alan Turing', 2016, 75.00, FALSE);

Marking Guide:

• 1 mark per correct INSERT INTO statement (3 statements = 3 marks)
• Each must have the correct order and type of values

(c) Form Design – [1 Mark]

Expected Response (any reasonable format):

The form BookEntryForm should include fields: BookID, Title, Author, PublishedYear, Price, Available.

Marking Guide:

• 1 mark – Correctly names the form and includes all relevant fields from the table

(d) Query for Available Books – [2 Marks]

Expected SQL:

SELECT Title, Author, Price

FROM Books

WHERE Available = TRUE;

Marking Guide:

• 1 mark – Correct SELECT statement with the correct fields
• 1 mark – Correct WHERE condition (Available = TRUE)

(e) Update Price – [2 Marks]

Expected SQL:

UPDATE Books

SET Price = 85.00

WHERE Title = 'Data Structures in Practice';

Marking Guide:

• 1 mark – Correct UPDATE and SET clause
• 1 mark – Correct WHERE clause specifying the title

(f) Delete Record – [1 Mark]

Expected SQL:

DELETE FROM Books

WHERE BookID = 2;

Marking Guide:

• 1 mark – Correct DELETE statement with the correct WHERE condition

(g) Query Books Published After 2017 – [3 Marks]

Expected SQL:

SELECT *

FROM Books

WHERE PublishedYear > 2017

ORDER BY PublishedYear DESC;

Marking Guide:

• 1 mark – Correct SELECT statement
• 1 mark – Correct WHERE condition (PublishedYear > 2017)
• 1 mark – Correct ORDER BY PublishedYear DESC