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 |
||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||
Customer |
Model |
||||||||||||||||||||||||||||||||
|
|
Orders |
|||||||||||||||
|
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.
1. (PART 1)
CREATE TABLE REPLACEMENT(
P_ID NUMBER(3) NOT NULL,
C_ID VARCHAR(2) NOT NULL,
CUST_NAME(30),
COST NUMBER(4),
CONSTRAINT PK_PID_CID PRIMARY KEY (P_ID,C_ID),
CONSTRAINT CT_CK CHECK (COST>= 0),
CONSTRAINT PID_FK FOREIGN KEY(P_ID) REFERENCES PART(P_ID) ON DELETE CASCADE ON UPDATE CASCADE);
(PART 2)
ALTER TABLE REPLACEMENT
ADD MODEL VARCHAR9(10),
ADD CONSTRAINT MODEL_DELETE_RULE FOREIGN KEY(MODEL) REFERENCES MODEL(MODEL) ON DELETE SET NULL;
2.(1) SELECT CUSTOMER.NAME
FROM ORDERS LEFT OUTER JOIN CUSTOMER
ON ORDERS.C_ID=CUSTOMER.C_ID,
WHERE ORDERS.MODEL ='SD70Ace' OR ORDERS.MODEL='SD70 ACs';
(2) SELECT MODEL.P_ID
FROM ORDERS JOIN MODEL
ON ORDERS.MODEL=MODEL.MODEL
WHERE ORDERS.C_ID='CN';
(3) SELECT INVENTORY.P_ID,INVENTORY.QUANTITY
FROM ORDERS, MODEL, INVENTORY
WHERE ORDERS.MODEL=MODEL.MODEL AND MODEL.P_ID=INVENTORY.P_ID AND ORDER.ORDER_ID=1 AND (ORDERS.NB_UNIT*MODEL.QUANTITY)<INVENTORY.QUANTITY;
(4) SELECT PART.COST*MODEL.QUANTITY
FROM PART, MODEL
WHERE MODEL.P_ID=PART.P_ID AND MODEL.MODEL='SD70Ace';
(5) DELETE MODEL.MODEL
FROM MODEL,INVENTORY
WHERE MODEL.P_ID=INVENTORY.P_ID AND (MODEL.QUANTITY/10)>INVENTORY.QUANTITY;
(7) SELECT PART.COST*MODEL.QUANTITY
FROM PART, MODEL, ORDERS
WHERE PART.P_ID=MODEL.P_ID AND MODEL.MOMDEL=ORDERS.MODEL AND PART.SUPPLIER='Melco' ;
(8) SELECT COUNT(C_ID)
FROM ORDERS, CUSTOMER
WHERE ORDERS.C_ID=CUSTOMER.CID AND CUSTOMER.LOCATION='USA';
3.(1) INSERT INTO ORDERS VALUES(3,'UP','SD60',25,2018);
(2) DELETE FROM ORDERS
WHERE ORDERS.YEAR<2000;
(3) UPDATE PART
SET COST=COST+(COST/10)
WHERE LOCATION='USA';
UPDATE PART
SET COST=COST+(COST*0.15)
WHERE LOCATION EXCEPT 'USA';
(4) UPDATE INVENTORY
SET QUANTITY=QUANTITY+10
WHERE QUANTITY<100 AND WAREHOUSE_ID='A1';
Instructions Try to answer all the questions using what you have learned in class. Please make...
Answer the following questions using mysql. Please show screenshots to make sure the queries work. 1. Use a sub-query and the IN operator to find the number and name of each customer that placed an order on October 23, 2007. 2. Repeat step 2, but this time use the EXISTS operator in your answer. 3. Using a sub-query, find the number and name of each customer that did not place an order on October 23, 2007. 4. Using a sub-query,...
DATABASE SYSTEMS PLEASE HELP ME FOR QUESTIONS 1 THRU 2 SHOW THE QUERY AND THE RESULTS OF THE QUERY (OUTPUT IN TABLE). ALSO, SHOW THE COMMANDS TO CREATE EACH TABLE (DDL COMMANDS) AND THE INSERTION OF THE DATA FOR EACH TABLE (DML COMMANDS). Question 2: SQL (40 points) Use the following database to answer all parts of this question: Table: EMP Primary Key: E NO E NO E NAME E DEPT E RATHE $400.00 $200.00 $150.00 $150.00 S120.00 $100.00 $100.00...
Using MySQL commands answer the questions listed below using the Premier Products Company schema. 1.Using Views a) Create a view called part_location that has the following attributes: part_num, part_description, part_quantity, warehouse_name, warehouse_address. This data comes from the part and warehouse entities. b) Write a query using the view that shows the total number of parts ordered from each warehouse. The output should look like this: 2. Using Triggers a) Execute the following SQL to create the customer_audit table in the...
Using the Premier Products database answer the following questions 1. Using a union, display all customers that have rep number 65 or live in the town of Sheldon. Display all customer information. Insert your snip of the query and resultset together here: 2. Using a subquery, list the rep information for all customers who have the same credit limit as customer number 725. To receive credit for this question, do not explicitly query for rep number 35. Let MySQL do...
Please finish all 6 parts with indexing SQL. Don't need to do the measure time things, but you can do it if you wanna do it. Thanks! Write the following SQL queries and measure their execution time both with and without using indexes 1. Count how many parts in NYC have more than 70 parts on hand 2. Count how many total parts on hand, in both NYC and SFO, are Red 3. List all the suppliers that have more...
Any help appreciated! Question 1 (5 points). Answer each of the following questions briefly. The questions are based on the following relational schema: Emp( eid: integer, ename: string, age: integer, sala1l1: real) Works( eid: integer, did: integer, pet_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) 1. Give an example of a foreign key constraint that involves the Dept relation. 2. Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign...
Questions in this part ask you to write SQL queriesthat would retrieve certain information from the tables. For each question, write the SQL query that would display the information asked by the question. Display information about parts that have their weights greater than the average weight of all parts. PNO PNAME P2 BOLT P3 SCREW Find supplier numbers who supply any screws (i.e., such that the part name is ‘screw’). The constraint is that you may not use any join or...
Please finish all 6 parts. Don't need to do the measure time things, but you can do it if you wanna do it. Thanks! Write the following SQL queries and measure their execution time both with and without using indexes 1. Count how many parts in NYC have more than 70 parts on hand 2. Count how many total parts on hand, in both NYC and SFO, are Red 3. List all the suppliers that have more total on _hand...
Program: MYSQL (please show screenshots of the solution) Ill give good rating! Define a view named TopLevelCust that consists of the number, name, address, balance and credit limit of all customers with credit limits that are greater than or equal to $5,000. Address is defined as street, city, state and zip. Concatenate these into one field named Address. A. Type in your view construct here: B. Write a query to retrieve the number and name of all customers in the TopLevelCust...
Please help me on the SQL queries, thank you so much. Write a query to display the title and publisher as well as the publisher contact for each book using JOIN...USING clause. Write a query to show the first and last names of customers who have ordered cooking books. Use the WHERE clause to join the tables. Write a query to show the title, cost and ISBN of each book in the books table. If the book has been ordered,...