Database System

CSCI 6622 | Databases | 14 Winter

 Quiz 2 will be on 2/26/14 and will cover topics from SQL DML : everything up to and including GROUP BY,
HAVING, nested HAVING, nested FROM, VIEWS.
1. (60 points)
You have to implement queries related to the Premiere Products database.
You need to turn in the following:
 You need to turn in a hard copy (paper copy) of the following:
{ For each of the queries :
 Copy the query in English from this HW sheet to the top of the page of what you turn in.
 Show the SQL to implement the query.
 If you are using views or subqueries or intermediate tables, show the SQL to create the views or
subqueries or intermediate tables, and show the tables for these views or subqueries or intermediate
tables.
 Show what is the nal output table (produced by the DBMS) on the query on the database instance.
 You also need to submit on blackboard the database HW2Q1 containing
{ The relational instance of Premier Products database
{ All the saved SQL queries, clearly labelled as querya, queryb etc.
{ If you are using views or subqueries or intermediate tables, these should also be here, clearly labelled.
Notes:
 I have put on blackboard the Premiere Products database hw2database which you need to use for this
problem.
 Please note that what is turned in on blackboard should exactly match what is being turned in on the hard
copy. So, for example, if you have some queries on the database you are submitting on blackboard, those
should be an exact match with what is being turned in on the hard copy.
 If the hard copy is submitted on time but the blackboard submission is done after the start of class on the
due date, the assignment/problem will be marked late.
 If the the blackboard submission is done on time but hard copy is submitted after the start of class on the
due date, the assignment/problem will be marked late.
 Feel free to use Access QBE (query by example) if you nd it easier to do so, though what you have to
show is the SQL: if you use QBE, you can go to the SQL view and nd the equivalent SQL query generated
by Access. It may not be possible to implement some of the queries in QBE.
 Feel free to create views or to save queries (to help answer a more complex query) or to create other tables
(eg: using the INTO command discussed in class) if that helps you. If you are using views or subqueries,
show the SQL to create the views and subqueries, and show the tables for these views or subqueries.
 You can implement your queries in either Microsoft Access or another relational DBMS of your choice. If
you are doing this in another DBMS, it is your responsibility to make sure you have the same tables, by
either entering the same data yourself or by transforming the Access data. Also, if you are using another
DBMS, please indicate clearly which DBMS you are using.
 It should be possible to implement all the queries, though if a particular SQL construct is not available in
the DBMS you are using, you may have to use a dierent SQL construct.
 If you are unable to implement a particular query, you should at least present the SQL query written by
hand and explain why you could not implement it.
 Email submissions will not be accepted.
(a) For each customer whose Rep is Rep Num 35, list the customer's number and name.
(b) For each customer whose Credit Limit is less than 7200.00, list the customer's name and the rst name of
their rep.(c) List the names of all customers who bought a part in Class \HW".
(d) Find the names of the customers who have either bought a Dryer or whose rep's last name is "Hull".
(e) What is the average cost of a part in the class \AP"?
(f) List the names of the parts which have been bought by at least two dierent customers..
(g) Find the names of the customers who have bought a Dryer but have not bought any other part.
(h) Get a count of the number of warehouses which supply at least 4 parts.
(i) From among the customers who placed at least 2 orders, nd the name of the customer with the largest
balance.
2. (30 points) This problem is based on Elmasri's company database. For each of the following, specify the query
using SQL : this is a paper and pencil problem, you do not need to implement these queries, but if
you want to implement them, you are free to do so.
Feel free to create views or to save queries (to help answer a more complex query) or to create other tables (eg:
using the INTO command discussed in class) if that helps you. If you are using views or subqueries, show the
SQL to create the views and subqueries.
(a) What are the names of the projects on which the combined hours (i.e. the total of all the hours of all the
employees who work on that project) are more than 50.
(b) Specify the following view in SQL: A view that has the department name and the number of locations
for that department for each department. Please note that here you have to use the \CREATE VIEW"
statement.
(c) List the names of the projects such that every employee works on that project.
(d) How many employees from the Administration department have at least three dependents.
(e) What is the name of the department whose employees have the lowest average salary.
3. (10 points) This problem is based on Elmasri's company database, In each of these SQL queries, there is a
mistake.
 Explain clearly and brie
y what the mistake is. Please note that I am not looking for a minor SQL syntax
error here, but rather you have to nd a signicant semantic (logic) error.
 Give the correct SQL for specifying the query.
(a) Find the names of the employees who do not work on any project.
(SELECT FNAME, LNAME FROM EMPLOYEE )
EXCEPT
(SELECT FNAME,LNAME FROM EMPLOYEE, WORKS_ON
WHERE EMPLOYEE.SSN = WORKS_ON.ESSN );
(b) Find the social security number of employee with the lowest salary.
SELECT SSN FROM EMPLOYEE
WHERE SALARY <= ANY
(SELECT SALARY
FROM EMPLOYEE);
Extra Credit 4: This problem is based on Elmasri's company database. Note that you will rst need to create the
relevant tables. Create a report which gives the name and salary of all thise employees who have at least 2 dependents
and who work on at least 2 projects. Turn in the disk , a hardcopy of the report denition, and a hardcopy of a sample
report.
Extra Credit 5: Implement any three of the Problem 2 queries in a dierent DBMS than the one you used for
Problem 1. For example, if you used Microsoft Access for Problem 1, you could do the implementation for this Extra
Credit Problem in MySQL or Oracle or SQL Server. Note that you will rst need to create the relevant tables. Turn
in a disk with the SQL, a hardcopy of the tables, a hardcopy of the SQL, a hardcopy of the results of each of the
query.
Textbook Extra Credit Problems: Elmasri problem 5.8, 5.9.
Click Here To Get More on This Topic......