DATABASE AND SQL - TUTORIAL 2
Duration: 2 hours 30 minutes
Total Marks: 70 marks
SECTION A: MULTIPLE CHOICE QUESTIONS (20 marks)
Instructions: Answer all questions. Each question carries 1 mark.
1. Which of the following is an example of a DBMS?
• Microsoft Word
• MySQL
• Adobe Photoshop
• Google Chrome
2. What is a relation in database terminology?
• A row
• A column
• A table
• A database
3. Which SQL command is used to add a new column to an existing table?
• ADD COLUMN
• ALTER TABLE
• MODIFY TABLE
• UPDATE TABLE
4. What does DML stand for?
• Data Management Language
• Data Manipulation Language
• Database Manipulation Language
• Database Management Language
5. Which constraint ensures that a column cannot have duplicate values?
• PRIMARY KEY
• UNIQUE
• CHECK
• Both A and B
6. What is the purpose of the SUM() function?
• To count rows
• To add numeric values
• To find average
• To find maximum
7. Which clause is used to group rows with the same values?
• ORDER BY
• WHERE
• GROUP BY
• HAVING
8. What does the AVG() function calculate?
• Total sum
• Average value
• Maximum value
• Minimum value
9. Which command is used to remove all records from a table without removing the
table structure?
• DELETE
• TRUNCATE
• DROP
• REMOVE
10. What is an attribute in database terminology?
• A row
• A column
• A table
• A record
11. Which operator is used for pattern matching in SQL?
•=
• LIKE
• IN
• BETWEEN
12. What does the DISTINCT keyword do in SQL?
• Sorts records
• Removes duplicates
• Counts records
• Filters records
13. Which SQL command is used to create a new database?
• NEW DATABASE
• CREATE DATABASE
• MAKE DATABASE
• BUILD DATABASE
14. What is the purpose of an index in a database?
• To store data
• To speed up queries
• To enforce constraints
• To create backups
15. Which data type is used for storing dates?
• INT
• VARCHAR
• DATE
• CHAR
16. What does DCL stand for?
• Data Control Language
• Data Create Language
• Database Control Language
• Database Create Language
17. Which command is used to save changes in a transaction?
• SAVE
• COMMIT
• APPLY
• CONFIRM
18. What is the MIN() function used for?
• To find the smallest value
• To find the largest value
• To count records
• To calculate average
19. Which clause filters grouped data in SQL?
• WHERE
• HAVING
• FILTER
• IF
20. What does the AS keyword do in SQL?
• Creates aliases
• Adds columns
• Sorts data
• Filters data
SECTION B: STRUCTURAL QUESTIONS (20 marks)
Instructions: Differentiate, explain, and give examples where necessary.
1. Give the difference between: (8 marks)
a) INNER JOIN and LEFT JOIN
b) DROP and TRUNCATE
c) Relation and Tuple
d) UNIQUE and PRIMARY KEY constraints
2. What is Domain Integrity? Give an example. (3 marks)
3. Explain the GRANT and REVOKE commands in SQL. (4 marks)
4. Can two tables in the same database have the same name? Explain. (2 marks)
5. List three types of SQL commands (DDL, DML, DCL) and give one example of each. (3
marks)
SECTION C: PROBLEM SOLVING (30 marks)
Instructions: Answer all questions in this section. Each question carries 15 marks.
Question 1: Library Management System (15 marks)
You are required to create a database for a library management system.
a) Write the SQL command to create a database called "LibraryDB". (2 marks)
b) Write the SQL command to create a table called "Books" with the following columns:
• BookID (integer)
• Title (varchar, max 100 characters)
• Author (varchar, max 50 characters)
• Year (integer)
• Price (decimal)
(3 marks)
c) Write SQL commands to insert the following three records into the Books table:
• BookID: 201, Title: "Database Systems", Author: "John Smith", Year: 2018, Price: 5000
• BookID: 202, Title: "Web Programming", Author: "Mary Jones", Year: 2020, Price: 4500
• BookID: 203, Title: "Data Structures", Author: "Peter White", Year: 2019, Price: 5500
(3 marks)
d) Write an SQL command to display all records from the Books table. (1 mark)
e) Write an SQL command to display only the Title and Author of all books. (2 marks)
f) Write an SQL command to update the Price of BookID 202 to 5000. (2 marks)
g) Write an SQL command to delete the book with BookID 203. (2 marks)
Question 2: Hospital Patient Database (15 marks)
Consider a table called "Patients" with the following structure and data:
Patients Table:
PatientID Name Age Disease AdmissionDat
e
P001 Sarah Johnson 45 Diabetes 2023-01-15
P002 Michael Brown 32 Malaria 2023-02-20
P003 Emily Davis 28 Diabetes 2023-03-10
P004 David Wilson 55 Hypertension 2023-04-05
Write SQL commands for the following:
a) Display all patients ordered by Age in ascending order. (2 marks)
b) Display the Name and Disease of patients who have Diabetes. (2 marks)
c) Display all patients whose Age is between 30 and 50. (2 marks)
d) Update the Disease of PatientID P002 to "Typhoid". (2 marks)
e) Display the Name of patients whose names end with "son". (2 marks)
f) Delete all patients admitted before February 2023. (2 marks)
g) Count the number of patients for each Disease. (3 marks)