SQL Programming (Simply Answer all the question)

This schema is used for inventory management for an OEM Part Inventorv Name Cos Suppli Locatio p id Warehouse i quantit t er Traction Alternator 400 kato HVAC 1 A1 100 250 300 200 Melco Ja USA 300 Melco Ja 3 BI Customer Model id Name CN UP Union Pacific BT Bombardier Transport Sweden Location Model pi Quantity National USA SD70A 1 60 SD60 3 Orders order i C id Mode nbUnit Ye CN SD70Ace 50 UP SD70Ace 60 2018 2018 Total point 67 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 order's information, what Model customer orders, how many units and which year they place the order in. I. SQL DDL (total 10 points) 1. (6 points) Write an SQL statement that create a new table replacement that stores the customer's 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. (4 points) 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 (total 39 points) 1. (3 points) Write an SQL query that returns the names of all the customers that bought either SD70Ace' or SD70 ACs' models. 2. (4 points) Write a query that selects all p_id needed for building models bought by CN 3. (7 points) Write a query that returns the parts and their quantities that are not in stock for building the order 4. (6 points) Write a query that returns the cost of parts used for building an SD70Ace model 5. (6 points) Write a query that selects the models to be removed if parts in stock are less than 10% of it over all parts 6. (5 points) Select the average order size (average number of units) per customer for each model 7. (5 points) Return the gross sale of supplier 'Melco' 8. (3 points) Return number of orders for customer outside the USA IIL SQL updates (total 21 points) 1. (3 points) Write an SQL to add a new order for 25 units of SD60 bought by UP 2. (5 points) Write an SQL operation that deletes all order made prior to 2000 3.(7 points) Write an SQL operation to increase parts price by 10% for US supplier and 15% for international suppliers 4. (6 points) Update inventory increasing all part less than 100 by 20 parts in warehouse A