Get premium membership and access revision papers, questions with answers as well as video lessons.

Got a question or eager to learn? Discover limitless learning on WhatsApp now - Start Now!

Database Systems Question Paper

Database Systems

Course:Computer Studies

Institution: Maseno University question papers

Exam Year:2013

SCS 217: Database Systems (Lab)

Date: Time:

Instructions:
1. Attempt Question 1 (Compulsory) and any other two questions.
2. Start a new question on a new page.

Question 1 (Compulsory, 20 Marks)
a) Define the following terms:
(i) Meta data (2 marks)
• Data about data.

(ii) Attributes (2 marks)
• Characteristics of entities

(iii) Primary Key (2 marks)
• A field that uniquely and minimally identifies a record in a table

Write sql code to:
b) Rename the column “title” to “position” in the table Employee. (2 Marks)
• SELECT NOW();

c) What’s the distinction between DDL and DML? (4 marks)
• DDL commands act on DB objects eg columns, tables and the database itself, whole DML commands act on records/ data items.

d) the remainder of 9/2 (2 marks)
• select MOD(9, 2);

e) Return the remainder of 5/2. (2 marks)
• select ROUND(3.1437, 2);

SECTION B: ATTEMPT ANY TWO QUESTIONS FROM THIS SECTION
Question 2 (20 marks)
Use the table below to answer the questions that follow.
Table: Customer
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country). Use it to write SQL statements to:
a. Display all contact names and addresses of all customers residing in Kisumu city. (3 Marks)

Select contactname,address from customer where city = “kisumu”;
b. Display contact names and city of all customers whose names contain the letters
“ILL” grouped in ascending order of their coutry. (5 Marks)
Select contactname,city from customer where customername like”%ill% group by country asc;
c. Display contact names and addresses of customers whose country contains
the letter “e” as the second letter in its name. (5 Marks)

Select contactname,address from customers where country like”_e%”;
d. display today’s day value (2 marks)
• SELECT DAYOFMONTH(CURRENT_DATE);

e. Delete the table Employee_data an d all its contents. (2 marks)
• Drop table employee_data;
f. join the strings “Maseno” and “University” as Institution, separating them
with a single space from the table COURSES. (3 marks)

Question 3 (20 marks)

Study the tables below and answer the questions that follow
Table: Titles Table: Publishers
Field name Data type
Title Text
yearPublished Number
ISBN Text
pubID Number
Field name Data Type
PubID Number
companyName Text
state Text
city Text

Assuming that the tables have been populated with data write SQL commands that display the following
a. The year and the number of titles published in the last 10 years (4 marks)
SELECT title,year published;
FROM titles;
WHERE year published BETWEEN 2003 AND 2013;
b. The title, year published and ISBN for titles published by Longhorn publisher (4marks)
SELECT title,year published,ISBN,companyName;
FROM titles,publishers;
WHERE company name=”Longhorn publisher”;

c. The ten per cent of total number of titles published more recently (4 marks)
SELECT title;*****
FROM titles;
WHERE company name=”Longhorn publisher”;
select titles * 0.1);

d. The publisher id, name, state and city of all publishers sorted by state
in descending order and at the same time in a given state sort by
ascending order (4 marks)
SELECT PubID,companyName,state,city;
FROM publishers;
ORDER by state DESC,”” ASC;

e. The title, year published, and the name of the publisher for the titles
published between 1988 and 1996 and sorted in descending order of
company name (4 marks)
SELECT title,year published,companyName;
FROM titles,publishers;
WHERE yearpublished BETWEEN 1988 AND 1996”;

Use the tables below to answer question 4 and 5. They have been extracted from the
database MIRADI.

TABLE PROJECT

Project Code Project Title Project Manager Project Budget
PC010 Pensions System M Phillips 24500
PC045 Salaries System H Martin 17400
PC064 HR Upgrade K Lewis 12250
PC011 NW Upgrade J Phillips 27560

TABLE EMPLOYEE
Project Code Employee No. Employee Name Department No. Department Name Hourly Rate
PC010 S10001 A Smith L004 IT 22.00
PC010 S10030 L Jones L023 Pensions 18.50
PC010 S21010 P Lewis L004 IT 21.00
PC045 S10010 B Jones L004 IT 21.75
PC045 S10001 A Smith L004 IT 18.00
PC045 S31002 T Gilbert L028 Database 25.50
PC045 S13210 W Richards L008 Salary 17.00
PC064 S31002 T Gilbert L028 Database 23.25
PC064 S21010 P Lewis L004 IT 17.50
PC064 S10034 B James L009 HR 16.50

Question 4 (20 marks)
a. Show, giving appropriate SQL commands, step by _step how you would go
About:
i. Creating the database and tables above in MySQL. (6 Marks)

ii. For each table, add the first record. (3 marks)
Create database miradi; 1 mark
Use miradi; 1 mark
Create table project(………>>>>>>>>>>>>>>>>>>); 2 marks
Create table employee(>>>>>>>>>>>>>>>>>); 2 Marks
Insert intyo project(>>>>>>>>>>>>>>>>); 1.5 marks
Insert into employee(>>>>>>>>>>>>>>>) 1.5 Marks

Write SQL statements to:
b. Compute the total budget for all upgrade projects. (4 marks)
SELECT fName, sName @ 1 mark
FROM guests
WHERE (fName=”*s*”); @ 2 marks

(a) Names and departments of the five least expensive employees? (4 marks)
SELECT AVG(price) @ 1 mark
FROM guests
WHERE (roomType=”s”); @ 2 marks
(b) Return employee information grouped by department. (3 marks)
SELECT fname, sname @ 1 mark
FROM guests
ORDER BY dateFrom DESC @ 2 marks
LIMIT 1; @ 1 mark

Question 5 (20 marks)
a. Introduce two new columns, “Years_of_service” and “Date_of_Birth”, in the
table employee. (4 marks)
SELECT fName, sName, dateTo – dateFrom AS NumOfDays
FROM guests; @ 1 mark @ 2 marks

b. List department name and employee name of the 3 eldest employees. (4 marks)
SELECT type, avg(price) @ 1 mark
FROM guests
GROUP BY type @ 1 mark
HAVING (avg(price)>1500); @ 2 marks

c. Average age of the ten longest serving employees. (5 marks)
ALTER TABLE guests @ 1 mark
CHANGE type roomType char(10); @ 1 mark
d. Double rates of all staff in the IT department. (4 marks)
UPDATE guests @ 1 mark
SET price=price+1500 @ 2 marks
WHERE roomType=''D''; @ 1 mark

e. List employees who were born before 1985. (3 marks)
SELECT fName,sName, dateFrom, dateTo @ 1 mark
FROM guests
WHERE ((dateFrom < 2013-01-24 AND datefrom> 2013-2-1) AND (dateTo <2013-01-24 AND dateto>2013-2-1)); @ 2 marks

Database Systems Question Paper

More Question Papers

Popular Exams