Question

There are 7 problems that require using joins.

Each problem has 10 points.
1. Write an SQL command that will find any customers who have not placed orders and sort them out by CustomerID in ascending order.

2. List the employees and supervisors names for each supervisor who supervises more than two employees.

3. List the name of each employee, his or her birth date, the name of his or her manager, and the manager’s birth date for those employees who were born before their manager was born; label the manager’s data Manager and ManagerBirth.

4. Write an SQL command to display the order number, customer number, order date, and items ordered sorted by order date from low to high for some particular customer.

5. Write an SQL command to display each item ordered for order number 1, its standard price, and the total price for each item ordered sorted from low to high by total price.

6. Write an SQL command to total the cost of order number 1.

7. Calculate the total raw material cost (label TotCost) for each product compared to its standard product price. Display product ID, product description, standard price, and the total cost in the result. Order by ProductId ascending.

The questions in about quaries using sql

PVFC database

Answer 1

SQL Commands:

(1) SELECT CustomersID

FROM Customers

WHERE CustomersID NOT IN(SELECT OrderID

FROM Customers)

ORDER BY CustomersID ASC;

(2) SELECT EmployeeName,SupervisorName,SupervisorID AS SID

FROM Employee

WHERE COUNT(DISTINCT EmployeeID) >2 AND SupervisorID =SID;

(3) SELECT EmployeeName,EmpBirthDate,ManagerName AS Manager,ManagerBirthDate AS ManagerBirth

FROM Employee
WHERE EmpBirthDate < ManagerBirthDate

(4) SELECT OrderNumber,CustomerNumber,OrderDate,ItemOrdered

FROM SalesItem

WHERE CustomerID =ANY (SELECT CustomerID  

FROM SalesItem

WHERE OrderDate IS NOT NULL)

ORDER BY OrderDate ASC;

(5) SELECT ItemOrder,Prize,SUM(Prize)

FROM SalesItem

WHERE OrderNumber=1

ORDER BY Prize ASC;

(6) SELECT SUM(Cost)

FROM SalesItem

WHERE OrderNumber=1;

(7) SELECT ProductID,productDescription,standprize,SUM(Cost) AS TotCost

FROM SalesItem
WHERE Cost<StandardPrize

GROUP BY ProductID,productDescription,standprize

ORDER BY ProductID ASC;