Design an Oracle/MySQL database using SQL DDL based on the above relational sche
ID: 3914502 • Letter: D
Question
Design an Oracle/MySQL database using SQL DDL based on the above relational schema. Below is a guideline:
-Create a user/schema
-Grant privileges to the user/schema
-Create tables (make appropriate assumptions. See below database state)
-Populate the tables (Insert data). Use the below database state
-Add foreign keys
Write SQL queries for the following:
-Retrieve the full details of all customers.
-Retrieve the item info of all items ordered by the customer “Tom Brady”.
-Retrieve the order numbers processed by the employee “Tony Romo”.
-Retrieve the names of customers who have ordered at least one item that costs more than $30.00.
-Retrieve the names and cities of employees who have taken orders for items costing more than $50.00.
-Retrieve the names of customers who order items only from employees who live in the same city as themselves.
-Retrieve the names of customers who have orders shipped the same day.
-Retrieve the names of customers who have not placed an order.
-Retrieve a list of item numbers, item names, and total quantity ordered for each item.
-For each employee, retrieve his/her number, name, and total sales (in dollars).
Please put up all used SQL DDL statements.
2). Relational database Schema Employees Endovee number Finste nome Last-namel City Customers Customer number fint-name last-namel City Item Item_number Item_name price quantity Order Orderanumber Customer number temploper numberItem number fumargleite frecite matenExplanation / Answer
1.
SELECT *
FROM CUSTOMERS;
2.
SELECT *
FROM Item
WHERE Item_number =( SELECT Item_number
FROM Order
WHERE Customer_number = ( SELECT Customer_number
FROM Customers
WHERE Fname = 'Tom' AND Lname = 'Brady'));
3. SELECT Order_number
FROM Order
WHERE Employee_number = ( SELECT Employee_number
FROM Employees
WHERE Fname = 'Tony' AND Lname = 'Romo');
4..
SELECT Fname, Lname
FROM Customers
WHERE Customer_number =( SELECT Customer_number
FROM Order
WHERE Item_number = ( SELECT Item_number
FROM Items
WHERE price > 30.00));
I have done first 4 please, please post the remaing other again in set of 4 questions as 4 parts per question are allowed.
Thanks and Cheers
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.