Homework One: Solutions

Homework Six

CS325 Database Management Systems

Spring 2009

Date Assigned: 3-5-2009

Problem One:

Consider the database shown above.

Specify the following queries on the relational schema shown above using SQL DML. Describe IN

ENGLISH, how the query works. Also show the result of each query as it would apply to the database

shown above.

a) Retrieve the names of all senior students majoring in ‘CS’ (Computer Science)

b) Retrieve the names of all courses taught by Professor King in 1998 and 1999.

c)For each section taught by Professor King, retrieve the course number, semester, year, and number of

students who took the section.

d)Retrieve the name and transcript of each senior student (Class = 5) majoring in CS. A transcript includes

course name, course number, credit hours, semester, year, and grade for each course completed by the

student.

e)Retrieve the names and major departments of all straight-A students (students who have a grade of A in all

of their courses).

f) Retrieve the names and major departments of all students who do not have a grade of A in any of their

courses.

Grading Criteria:

Each sub-problem receives a maximum of 5 points. Maximum grade for problem is 30 points. Each problem is

graded differently with the cost of an error weighted by the underlying principle of the problem.

Problem Two:

Consider the relational database shown above.

Specify the following queries on the relational schema shown above using SQL DML. Describe IN

ENGLISH, how the query works. Also show the result of each query as it would apply to the database

shown above.

a) Retrieve the department number, number of employees, combined salary, and average salary for each

department.

b) Retrieve the department number and number of employees for each department with a combined salary

over $100,000.

c)Retrieve the department number and average salary for any department with more employees than

department 4.

d)Consider the following view, DEPT_SUMMARY, defined on the COMPANY database:

CREATE VIEW DEPT_SUMMARY(DNO, COUNT, SUM_SALARY, AVERAGE_SALARY)

AS SELECT DNO, COUNT(*), SUM(SALARY), AVG(SALARY)

FROM EMPLOYEE

GROUP BY DNO;

Is the following UPDATE possible? Why or why not?

UPDATE DEPT_SUMMARY

SET DNO = 3

WHERE DNO = 4;

e)Consider the view from problem (d).

Is the following DELETE possible? Why or why not?

DELETE

FROM DEPT_SUMMARY

WHERE COUNT > 4;

Grading Criteria:

Each sub-problem receives a maximum of 5 points. Maximum grade for problem is 30 points. Each problem is

graded differently with the cost of an error weighted by the underlying principle of the problem.