Assignment
The Jonson Brothers is a retail company with department stores in many major US cities. The company has a large number of employees and sells a varied line of products. To manage all information about the company structure and products, a database system is used. The company consists of a number of stores that contain a number of departments. The company has a number of employees, who (among other things) sell items at the different stores. Sales are registered in the sale and debit tables. The sale and debit tables may be a bit tricky to understand. You can view a row in the debit table as representing the receipt you get when you pay for your items, while a row in the sale table represents a row on such a receipt.
The company has contracts with various suppliers, who supply items for sale and also parts for the company's computer equipment. Deliveries of computer parts are registered in the supply table.
The current state of the company database can be seen in the ER diagram given in Appendix A and the table definitions and contents in the appendixes B and C.
The lab Use SQL to find the answers to the questions below towards your Jonson Brothers company database. Whenever a question requests information about entities that have both a number and a name, select both the number and the name to make your results more useful.
A. List all employees, i.e. all tuples in the EMPLOYEE relation.
B. List the name of all departments, i.e. the NAME attribute for all tuples in the DEPT relation.
C. What parts are not in store, i.e. QOH=0? (QOH = Quantity On Hand).
D. Which employees have a salary between 10000 and 12000 (inclusive)?
E. Retrieve all items sold in the department 49 with their name, price, and price increased by10%.
F. Which employees have a family name starting with "S"? Retrieve their names, numbers and salaries.
G. What are the names and weights of all parts delivered by a supplier called "DEC"? Formulate this query using a subquery in the where-clause.
H. Formulate the same query as above, but without a subquery.
I. Retrieve the name and the color of all parts that are heavier than a black tape drive. Formulate this query using a subquery in the where-clause. (The SQL query should not contain the weight as a constant.)
J. Formulate the same query as above, but without a subquery. (The query should not contain the weight as a constant.)
K. What is the average salary of all the employees whose manager is the employee with number 199?
L. For each supplier retrieve its name and the number of different items it supplies.
M. For each supplier in Massachusetts ("Mass") retrieve the total weight of all the parts