Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

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 maten

Explanation / 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

Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote