1.
Create Database
CREATE DATABASE ECommerceDB;
USE ECommerceDB;
This creates the database that will store all tables, procedures, triggers, and views used for
testing.
2. Schema Testing (Create Tables)
Schema testing checks the structure of tables, keys, and relationships.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
CustomerName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(20)
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10,2),
Stock INT
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT,
ProductID INT,
Quantity INT,
TotalPrice DECIMAL(10,2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
These tables allow testing of:
Table structure
Data types
Primary keys
Foreign keys
Constraints
3. DB Query Testing Examples
Testing SELECT, JOIN, and Aggregate queries.
SELECT Query
SELECT * FROM Customers;
JOIN Query
SELECT [Link], [Link]
FROM Customers
JOIN Orders ON [Link] = [Link];
Aggregate Function
SELECT SUM(TotalPrice) AS TotalSales
FROM OrderDetails;
Monthly Sales Report
SELECT MONTH(OrderDate) AS Month, SUM(TotalPrice) AS TotalSales
FROM Orders
JOIN OrderDetails ON [Link] = [Link]
GROUP BY MONTH(OrderDate);
4. Stored Procedure Testing
Stored procedures contain business logic.
Example: Calculate total sales.
DELIMITER //
CREATE PROCEDURE GetTotalSales()
BEGIN
SELECT SUM(TotalPrice) AS TotalSales
FROM OrderDetails;
END //
DELIMITER ;
Run procedure:
CALL GetTotalSales();
Testing checks:
Input parameters
Correct calculations
Output results
5. Trigger Testing
Triggers automatically execute when an event occurs.
Example: Reduce product stock after a sale.
DELIMITER //
CREATE TRIGGER UpdateStock
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
UPDATE Products
SET Stock = Stock - [Link]
WHERE ProductID = [Link];
END //
DELIMITER ;
Testing ensures:
Trigger fires correctly
Stock updates automatically
6. Views Testing
Views create virtual tables for reports or security.
Example: Sales report view.
CREATE VIEW SalesReport AS
SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers
JOIN Orders ON [Link] = [Link]
JOIN OrderDetails ON [Link] = [Link]
JOIN Products ON [Link] = [Link];
Test the view:
SELECT * FROM SalesReport;
Views help:
Hide sensitive data
Simplify reporting
7. Stress Testing (Example Data Insert)
Large data can be inserted to test performance.
INSERT INTO Products(ProductName, Price, Stock)
VALUES ('Laptop', 75000, 50),
('Phone', 35000, 100),
('Printer', 20000, 40);
Running many inserts and queries helps test:
Response time
System limits
Performance under load
8. Benchmarking Example Query
Measure performance.
SELECT COUNT(*) FROM OrderDetails;
Benchmarking measures:
Query execution time
Memory usage
CPU load
Summary
The SQL statements above help test:
Testing Area SQL Objects Used
Schema Testing CREATE TABLE
Query Testing SELECT, JOIN, SUM
Stored Procedure Testing CREATE PROCEDURE
Trigger Testing CREATE TRIGGER
Views Testing CREATE VIEW
Stress Testing Large INSERT operations
Benchmarking Performance queries
SQL PRACTICAL EXAM QUESTION
A Bookstore Management System requires a database to store information about
Customers, Books, and Orders.
Using SQL statements, perform the following tasks.
Question 1: Database Creation (2 Marks)
a) Create a database called BOOKSTOREDB.
b) Select the database for use.
Question 2: Schema Creation (Schema Testing) (8 Marks)
Create the following three tables with appropriate data types, primary keys, and
constraints.
Customers Table
CustomerI CustomerName Email
D
Requirements:
CustomerID should be the Primary Key
Email must be Unique
Books Table
BookID BookTitle Price Stock
Requirements:
BookID should be the Primary Key
Price must be greater than 0
Orders Table
OrderID CustomerID BookID Quantity
Requirements:
OrderID should be the Primary Key
CustomerID should be a Foreign Key referencing Customers
BookID should be a Foreign Key referencing Books
Quantity must be greater than 0
Question 3: Insert Data (6 Marks)
Insert at least three records into each table:
Customers
Books
Orders
Example structure:
Customers
| CustomerID | CustomerName | Email |
Books
| BookID | BookTitle | Price | Stock |
Orders
| OrderID | CustomerID | BookID | Quantity |
Question 4: Integration Testing (4 Marks)
Write SQL statements to verify that the Orders table correctly references Customers and
Books by displaying:
Customer Name
Book Title
Quantity Ordered
Use a JOIN operation.
Question 5: Database Query Testing (6 Marks)
Write SQL queries to perform the following:
a) Display all books with their prices.
b) Display all customers.
c) Calculate the total number of books ordered using an aggregate function.
d) Display the total value of each order (Price × Quantity).
Question 6: Data Integrity Testing (4 Marks)
Write SQL statements to:
a) Update the price of a book to 500.
b) Delete a customer record with CustomerID = 3.
Explain what happens if the customer has existing orders.
MARKING SCHEME A BOOKSTORE MANAGEMENT SYSTEM
1. Database Creation
SQL Statement
CREATE DATABASE BOOKSTOREDB;
USE BOOKSTOREDB;
2. Customers Table Creation with Constraints
SQL Statement
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Email VARCHAR(100) UNIQUE
);
3. Books Table Creation with Constraints
SQL Statement
CREATE TABLE Books (
BookID INT PRIMARY KEY,
BookTitle VARCHAR(100),
Price DECIMAL(10,2) CHECK (Price > 0),
Stock INT
);
4. Orders Table Creation with Foreign Keys
SQL Statement
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
BookID INT,
Quantity INT CHECK (Quantity > 0),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (BookID) REFERENCES Books(BookID)
);
5. Inserting Data into Customers
INSERT INTO Customers VALUES
(1,'John','john@[Link]'),
(2,'Mary','mary@[Link]'),
(3,'James','james@[Link]');
6. Inserting Data into Books
INSERT INTO Books VALUES
(1,'Database Systems',500,10),
(2,'Web Development',700,8),
(3,'Networking Basics',450,12);
7. Inserting Data into Orders
INSERT INTO Orders VALUES
(1,1,2,3),
(2,2,1,2),
(3,3,3,1);
8. Integration Testing JOIN Query
SELECT [Link], [Link], [Link]
FROM Orders
JOIN Customers ON [Link] = [Link]
JOIN Books ON [Link] = [Link];
9. Query 1 – Display Books
SELECT * FROM Books;
10. Query 2 – Display Customers
SELECT * FROM Customers;
11. Query 3 – Aggregate Function
SELECT SUM(Quantity) AS TotalBooksOrdered
FROM Orders;
12. Query 4 – Order Value Calculation
SELECT [Link], [Link], [Link],
([Link] * [Link]) AS OrderValue
FROM Orders
JOIN Books ON [Link] = [Link];
13. Update Statement
UPDATE Books
SET Price = 500
WHERE BookID = 1;
14. Delete Statement Explanation
SQL Statement
DELETE FROM Customers
WHERE CustomerID = 3;
Expected Explanation for the last statement
If the customer has existing records in the Orders table, the deletion may fail due to the
foreign key constraint because Orders depends on Customers.
Deleting the customer would cause orphan records.