Question

In mySQL Northwind data base.

How many of Northwind's customers, that don't work in sales, are explicitly identified as "Manager" in their job title? Please use a subquery.

What is the total order price for each order listed in Northwind's database?

Add to the previous query. Include the Customer ID and Shipping Name for each order that is totaled in the previous query. Sort the output by Customer ID.

Add to the previous query. Include the Title of Courtesy, First Name, and Last Name of the Northwind employee responsible for each sale. Sort the output by the responsible employee's last name.
Edit the query so the responsible employee's information appears in one single column in the output. Results must still be sorted by the employee's last name.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question 2:

SQL Query :

select Orders.orderID, sum(UnitPrice*Quantity) 'Total Order Price'
from orders,[order Details]
where
orders.orderid=[order Details].orderID
group by Orders.orderID;

Screen in SQL Server :

select Orders.orderID, sum(UnitPrice Quantity) 'Total Order Price' from orders, [order Details] where orders.orderid=[order Details].orderID group by Orders.orderID:|| 100% - Results Messages orderlD Total Order Price 10248 440.00 102491863.40 102501813.00 10251 670.80 10252 3730.00 10253 1444.80 10254 625.20 10255 2490.50 10256 517.80 10257 1119.90 10258 2018.60 12 10259 100.80 13 10260 1746.20 14 10261 448.00 15 10262 624.80 Query executed successfu... (local) (12.0 RTM) NAGESH-PC\Nagesh (61) Northwind 00:00:00 830 rows

*****************************************

Question 3:

SQL Query :

select Orders.orderID,CustomerID,ShipName, sum(UnitPrice*Quantity)
'Total Order Price'
from orders,[order Details]
where
orders.orderid=[order Details].orderID
group by Orders.orderID,CustomerID,ShipName
order by CustomerID;

Screen in SQL Server :

select Orders.orderID, CustomerID, ShipName, sum(UnitPrice"Quantity) 'Total Order Price from orders, [order Details] where orders.orderid=[order Details].orderID group by Orders.orderID, CustomerID, ShipName order by CustomerID:|| 100 % < Results Messages orderlD CustomerlD Ship Name Total Order Price 10643 ALFKI Alfreds Futterkiste 1086.00 10692 ALFKI Alfred's Futterkiste 878.00 10702 ALFKI Alfred's Futterkiste 330.00 10835 ALFKI Alfred's Futterkiste 851.00 10952 ALFKI Alfred's Futterkiste 491.20 11011 ALFKI Alfred's Futterkiste 960.00 10926 ANATR Ana Trujillo Emparedados y helados 514.40 8 10759 ANATR Ana Trujillo Emparedados y helados 320.00 9 10625 ANATR Ana Trujillo Emparedados y helados 479.75 10308 ANATR Ana Trujillo Emparedados y helados 88.80 10365 ANTON Antonio Moreno Taqueria 403.20 1210507 ANTON Antonio Moreno Taqueria 881.25 13 10535 ANTON Antonio Moreno Taqueria 2156.50 14 10677 ANTON Antonio Moreno Taqueria 956.90 10573 ANTON Antonio Moreno Taqueria 2082.00 16 10682 ANTON Antonio Moreno Taqueria 375.50 17 10856 ANTON Antonio Moreno Taqueria 660.00 18 10864 AROUT Around the Hom 282.00

*****************************************

Question 4:

SQL Query :

select Orders.orderID,CustomerID,ShipName,TitleOfCourtesy,FirstName,LastName,
sum(UnitPrice*Quantity) 'Total Order Price'
from Employees,orders,[order Details]
where
Employees.EmployeeID=Orders.EmployeeID and
orders.orderid=[order Details].orderID
group by Orders.orderID,CustomerID,ShipName,TitleOfCourtesy,FirstName,LastName
order by LastName;

Screen in SQL Server :

select Orders.orderID, CustomerID, ShipName, TitleOfCourtesy, FirstName, LastName, sum (UnitPrice Quantity) 'Total Order Price from Employees, orders, [order Details] where Employees. EmployeeID=Orders. EmployeeID and orders.orderid=[order Details].order ID group by Orders.orderID, CustomerID, ShipName, TitleOfCourtesy, FirstName, LastName order by LastName;|| 100 % - First Name Steven Steven Steven Mr. Mr. Steven er Steven 8 Results orderld 10248 10254 10269 10297 10320 10333 10358 10359 10372 10378 10397 10463 10474 10477 10529 10549 10569 Messages CustomerlD VINET CHOPS WHITC BLONP WARTH WARTH LAMAI SEVES QUEEN FOLKO PRINI SUPRD PERIC PRINI MAISD QUICK RATTC Steven Ship Name TitleOfCourtesy Vins et alcools Chevalier Mr. Chop-suey Chinese White Clover Markets Blondel père et fils Wartian Herkku Wartian Herkku La maison d'Asie Seven Seas Imports Queen Cozinha Folk och fä HB Princesa Isabel Vinhos Suprêmes délices Pericles Comidas clási... Princesa Isabel Vinhos Maison Dewey QUICK-Stop Rattlesnake Canyon G... Mr. Last Name Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Buchanan Total Order Price 440.00 625.20 676.00 1420.00 516.00 954.00 452.00 3654.40 12281.20 103.20 843.20 713.30 1249.10 672.00 946.00 4181.50 977.50 er Steven Steven 12 13 Steven Steven Steven Steven Steven 15 16 17 Steven NADAL Query executed successfully.

*****************************************

Question 5:

SQL Query :

select Orders.orderID,CustomerID,ShipName,TitleOfCourtesy+'.'+FirstName+''+LastName as 'Employee',
sum(UnitPrice*Quantity) 'Total Order Price'
from Employees,orders,[order Details]
where
Employees.EmployeeID=Orders.EmployeeID and
orders.orderid=[order Details].orderID
group by Orders.orderID,CustomerID,ShipName,TitleOfCourtesy,FirstName,LastName
order by LastName;

Screen in SQL Server :

select Orders.orderID, CustomerID, ShipName, TitleOfCourtesy+'.'+FirstName+''+LastName as 'Employee', sum (UnitPrice Quantity) 'Total Order Price from Employees, orders, [order Details] where Employees. EmployeeID=Orders. EmployeeID and orders.orderid=[order Details].orderID group by Orders.orderID, CustomerID, ShipName, TitleOfCourtesy, FirstName, LastNamel order by LastName; 100 % - Results Messages orderld CustomerlD ShipName 10248 VINET Vins et alcools Chevalier 10254 CHOPS Chop suey Chinese 10269 WHITC White Clover Markets 10297 BLONP Blondel père et fils 10320 WARTH Wartian Herkku 6 10333 WARTH Wartian Herkku 7 10358 LAMAI La maison d'Asie 10359 SEVES Seven Seas Imports 9 10372 QUEEN Queen Cozinha 10 10378 FOLKO Folk och fä HB 11 10397 PRINI Princesa Isabel Vinhos 10463 SUPRD Suprêmes délices 10474 PERIC Pericles Comidas clásicas 14 10477 PRINI Princesa Isabel Vinhos 15 10529 MAISD Maison Dewey 16 10549 QUICK QUICK-Stop 17 10569 RATTC Rattlesnake Canyon Grocery Query executed successfully. Employee Mr.. Steven Buchanan Mr. Steven Buchanan Mr. Steven Buchanan Mr. Steven Buchanan Mr. StevenBuchanan Mr.Steven Buchanan Mr. Steven Buchanan Mr. Steven Buchanan Mr. Steven Buchanan Mr. StevenBuchanan Mr. Steven Buchanan Mr. Steven Buchanan Mr. Steven Buchanan Mr.. Steven Buchanan Mr.Steven Buchanan Mr. StevenBuchanan Mr. Steven Buchanan Total Order Price 440.00 625.20 676.00 1420.00 516.00 954.00 452.00 3654.40 12281.20 103.20 843.20 713.30 1249.10 672.00 946.00 4181.50 977.50

*****************************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.