December 1998
DB212: DATABASE MANAGEMENT SYSTEMS

QUESTION 3

Total Marks: 20 Marks

 Click here to access other questions

SUGGESTED SOLUTIONS
Solutions and allocated marks are indicated in green.
Return to Question 3

 
The following tables represent data stored by a University department, about students, faculty, classes taught and enrolment for those classes.

 
(a) Write an SQL query to obtain a list of course numbers for all those courses which have students enrolled. [2]
SELECT DISTINCT COURSE#
       FROM ENROLMENT;

or

SELECT DISTINCT ENROLMENT.COURSE#
       FROM ENROLMENT;

1 mark per line

 

 [2]
(b) The secretary of the Maths department needs to know the names of all students who are majoring in Maths who have more than 30 credits. Write an SQL query to give her this information. [3]
SELECT STUNAME
       FROM STUDENT
       WHERE MAJOR = 'MATH' AND CREDITS > 30;

1 mark per line

 

 [3]
(c) It has been agreed that Professor Tanaka will teach all of this courses in room B220. Write an SQL statement to change the relevant tables. [4]
UPDATE CLASS
       SET ROOM = 'B220'
       WHERE FACID =
             (SELECT FACID
             FROM FACULTY
             WHERE FACNAME = 'Tanaka');

2 marks for the subquery, 2 marks for outer update

 

 [2]
(d) Write an SQL query to find out the course numbers of all courses which are taught by Byrne of the Maths department. [5]
SELECT COURSE#
       FROM CLASS
       WHERE FACID =
             (SELECT FACID
             FROM FACULTY
             WHERE FACNAME = 'Byrne'
             AND DEPT = 'Maths'

3 marks for the subquery, 2 marks for outer query

or

SELECT COURSE#
       FROM CLASS, FACULTY
       WHERE FACNAME = 'Byrne'
             AND DEPT = 'Maths'
             AND CLASS.FACID = FACULTY.FACID;

1 mark per line

 [5]
(e) Find the name and id of the student (or students) who has the largest number of credits. [2]
SELECT STUNAME STUID
       FROM STUDENT
       WHERE CREDITS =
             (SELECT MAX(CREDITS)
             FROM STUDENT);

1 marks for the outer query, 1 marks for subquery

 

 [2]
(f) If a course has fewer than three students enrolled, it will cancelled. Write an SQL query to find out the course numbers of any courses that might be affected by this. [2]
SELECT COURSE#
       FROM ENROLMENT
       GROUP BY COURSE#
       HAVING COUNT (*) < 3;

1 marks for the outer query, 1 marks for subquery

 

 [2]
(g) What do you understand by QBE? Why might QBE be easier to use than SQL [2]
QBE means 'Query by Example': it is a query language for relational databases (1 mark), where the user fills in a template rather than specifying a structured query explicity (1 mark). [2]