#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]
#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
#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
#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