INFO1103: Assignment Three
Due, Sunday 24 February 2019, submitted to D2L before midnight.
- For the following ER Diagram, generate the relational schema as well as DDL
statement. Use reasonable datatypes. - Write and execute (test) SQL queries to answer the following using the Pine
Valley Furniture database in Oracle SQL Developer.
a) List the names of the supervisors and number of employees supervised
(label this value HeadCount) for each supervisor who supervises more
than two employees.
b) Display the product description and product ID along with the total number
that was ordered for all tables.
c) Display the names of employees and the number of skills (label this
SkillsCount) for employees that have more than 2 skills.
d) Calculate the total raw material cost (label TotCost) for each product
compared to its standard product price. Display productID, product
description, standard price, and the total raw material cost.
INFO 1103, 2019 Winter
e) List the salespeople’s names, city, state and the name of their territory
who live in Florida. Use the headings Name, City, State, Territory.
f) Display the vendors and the type of material they supply, but only for
those that supply wood.
Submission Instructions (submit files to D2L dropbox): - A document with the relation schema
- A single.sql file containing all queries
- Screen capture of the query results.