COSC 2307 Assignment 2 Due date: Oct 27th For each of the following, you need to provide both the SQL used to accomplish the required task and a screen shot showing it was successfully executed. As an example, each of you will have a script in your Oracle workspace which shows you how the Employees, Departments, Certifications, and EmployeeCertifications tables were created and populated. Part A) CREATE THE TABLES (10 marks) Create the database schema for the tables attached using the CREATE TABLE command. Part B) ADD THE DATA (20 marks) Populate the table using the INSERT statement. As an example, the first insert statement for the Supplier table would be as follows: INSERT INTO Supplier ( Supplier_ID, Supplier_Name, City, Status) VALUES (100, Northern, Sudbury, 20); Part C) SQL QUERIES (70 marks) Provide the SQL queries for each of the following: (Note: provide only the requested information – no more, no less). a) List all the supplier names and their locations. (5 marks) b) List all the Part names and colours (5 marks) c) List all the Supplies with a status of at least 30. (5 marks) d) List the supplier name and part number for each part in the inventory (5 marks) e) List all the parts (name and id) supplied by ‘Quality’ (without using Supplier_ID=102) (10 marks) f) What is total weight for all the parts? (10 marks) g) List the supplier names who supply the parts located in Sault Ste Marie (10 marks) h) How many parts are there with quantity more than 300? (10 marks) i) Provide a parts list for each part in stock, ordered by part number and providing the following detailis: Part_ID, Part_Name, Part_Colour, Part_Weight, QtyOnHand, Supplier_Name (10 marks) Supplier: Supplier_ID Supplier_Name City Status 100 Northern Sudbury 20 101 Superior Sault Ste Marie 10 102 Ontario Sault Ste Marie 30 103 Quality Sudbury 20 104 ProHardware Thunderbay 30 Inventory: Supplier_ID Part_ID Quantity 100 401 300 100 402 200 100 403 400 100 404 200 100 405 100 100 406 100 101 401 300 101 402 400 102 402 200 103 402 200 103 403 300 103 404 400 Parts: Part_ID Part_Name Part_Colour Part_Weight Location 401 Sink Blue 10 Sudbury 402 Cabinet Black 5 Sault Ste Marie 403 Counter Black 2 Sudbury 404 Faucet Red 6 Thunderbay 405 Dishwasher Red 8 Sault Ste Marie 406 Handle Blue 5 Sudbury



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *