Instructions Try to answer all the questions using what you have learned in class. Please make your query general not data related

This schema is used for inventory management for an OEM

Part

Inventory

p_id	Name	Cost	Supplier	Location
1	Traction motor	200	Melco	Japan
2	Alternator	400	kato	USA
3	HVAC	300	Melco	Japan

p_id	Warehouse_id	quantity
1	A1	100
2	A2	250
3	B1	300

Customer

Model

c_id	Name	Location
CN	Canada National	Canada
UP	Union Pacific	USA
BT	Bombardier Transport	Sweden

Model	p_id	Quantity
SD70Ace	1	60
SD70M	2	1
SD60	3	4

Orders

order_id	C_id	Model	nbUnit	Year
1	CN	SD70Ace	50	2018
2	UP	SD70Ace	60	2018

Hints: part p_id is the primary key for part. Part relation includes the part information and their suppliers.

Inventory tables list the parts, their quantities and warehouse_id they are stored in. Both p_id and warehouse_id form the primary key.

The customer table stores the customer information and uses c_id as primary key.

The model table includes the Model name and the part it constitutes. It uses Model and p_id as primary key.

Order includes all the orders information, what Model customer orders, how many units and which year they place the order in.

• I. SQL DDL

1. Write an SQL statement that create a new table replacement that stores the customers name and the p_id, c_id and sale price. The primary key being the combination of part and customer id. The sale price has to be positive. No part id can be left blank or null. When a part is deleted from the part table it has to be removed from replacement table.
2. Write an SQL statement to add an attribute Model to relation replacement, and sets it with combination of p_id to be a foreign key in relation model. In case of model changes, this will reflect on the model when a model is no longer supported and removed this will be set to null.

• II. SQL Queries

1. Write an SQL query that returns the names of all the customers that bought either SD70Ace or SD70 ACs models.
2. Write a query that selects all p_id needed for building models bought by CN
3. Write a query that returns the parts and their quantities that are not in stock for building the order 1.
4. Write a query that returns the cost of parts used for building an SD70Ace model
5. Write a query that selects the models to be removed if parts in stock are less than 10% of it over all parts
6. Select the average order size (average number of units) per customer for each model
7. Return the gross sale of supplier Melco
8. Return number of orders for customer outside the USA

• III. SQL updates

1. Write an SQL to add a new order for 25 units of SD60 bought by UP
2. Write an SQL operation that deletes all order made prior to 2000.
3. Write an SQL operation to increase parts price by 10% for US supplier and 15% for international suppliers
4. Update inventory increasing all part less than 100 by 20 parts in warehouse A1.