Using Microsoft Access and the Northwind sample database, produce an SQL statement for each of the 4 exercises below. For each exercise you should submit both the SQL statement you have written along with a screen shot of the results.

#1) Number of Orders with Credit Card Payment

Write a SQL statement that will count the number of orders in the [Order Details] table that were paid by credit card.

Example:

SELECT [field], count(1)

FROM [table]

WHERE [field] = "value"

GROUP BY [field]

SELECT [PAYMENT TYPE], COUNT(ORDERS.[ORDER ID]) as Count_of_Credit_Card

FROM ORDERS

where ORDERS.[Payment Type] = "Credit Card"

Group By [PAYMENT TYPE]

#2) Top Selling Product based on Order Amount (One Join)

Write a SQL statement that will join the [Order Details ] table and [Products] table to determine the top selling product based on [Quantity]*[Unit Price].

Example:

SELECT B.[field], sum(A.[field]*A.[field]) as Orders

FROM ( [table] A

INNER JOIN [table] B

on A.[field] = B.[field] )

GROUP BY B.[field]

ORDER BY sum(A.[field] *A.[field] ) desc

SELECT [Products].[Product Name], sum([Unit Price]*Quantity) as Orders

FROM ([Products]

INNER JOIN [Order Details]

on Products.[ID] = [Order Details].[Product ID])

GROUP BY Products.[Product Name]

ORDER BY sum([Unit Price]*Quantity) desc

#3) Products with Less Than $2500 in Orders

Write a SQL statement that will join the [Order Details] table and [Products] table to determine only those products with less than $2500 in Orders based on [Quantity]*[Unit Price].

Example:

SELECT B.[field], sum(A.[field] * A.[field]) as Orders

FROM ([table] A

INNER JOIN [table] B

on A.[field] = B.[field])

GROUP BY B.[field]

HAVING sum(A.[field]*A.[field]) < value

ORDER BY sum(A.[field] *A.[field]) desc

SELECT [Product Name], sum([Unit Price]*Quantity) as Orders

FROM ([Products]

INNER JOIN [Order Details]

on Products.[ID] = [Order Details].[Product ID])

GROUP BY [Product Name]

HAVING sum([Unit Price]*Quantity)< 2500

ORDER BY sum([Unit Price]*Quantity) desc

#4) Top Producing Employee base on Order Amount (Two Joins)

Write a SQL statement that will join the [Orders] table, [Order Details] table and [Employees] table to determine the highest producing employee based on the employees first name and [Quantity]*[Unit Price].

Example:

SELECT C.[field], sum(A.[field] *A.[field] ) as Orders

FROM (([table] A

INNER JOIN [table] B

on A.[field] = B.[field])

INNER JOIN [table] C

on B.[field] = C.[field])

GROUP BY C.[field]

ORDER BY sum(A.[field] *A.[field] ) desc

SELECT [FIRST NAME], sum([Order Details].[Unit Price]*[Order Details].Quantity) as Orders

FROM (([ORDER DETAILS]

INNER JOIN [ORDERS]

ON [ORDERS].[ORDER ID] = [ORDER DETAILS].[ORDER ID])

INNER JOIN [EMPLOYEES]

ON [EMPLOYEES].[ID] = [ORDERS].[EMPLOYEE ID])

GROUP BY [FIRST NAME]

ORDER BY sum([Order Details].[Unit Price]*[Order Details].Quantity) DESC