My queries

problem_queries.sql

-- Problem 1:

-- <<
SELECT B.Title
FROM Books B
WHERE B.Unit_in_Stock >5
-- >>

-- Problem 2:

-- <<
SELECT TRUNCATE( SUM( OD.Quantity * B.Unit_Price ) , 2 ) AS Total
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
INNER JOIN Books B ON OD.BookID = B.BookID
WHERE C.FirstName =  'John'
AND C.LastName =  'Smith'
-- >>

-- Problem 3:

-- <<
SELECT sum.FirstName, sum.LastName
FROM (
SELECT C.FirstName, C.LastName, TRUNCATE( SUM( OD.Quantity * B.Unit_Price ) , 2 ) AS Total
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID
INNER JOIN Books B ON B.BookID = OD.BookID
GROUP BY C.CustomerID
HAVING Total <20
ORDER BY Total DESC
) AS sum
-- >>

-- Problem 4:
--      Note: This query returns no rows so I assume that either it is supposed to be OrderDate instead of ShippedDate or something else.

-- <<
SELECT DISTINCT B.Title
FROM Books B
INNER JOIN OrderDetails OD ON B.BookID = OD.BookID
INNER JOIN Orders O ON OD.OrderID = O.OrderID
WHERE O.ShippedDate = '08/04/10'
-- >>

-- Problem 5:

-- <<
SELECT DISTINCT B.Title
FROM Books B
INNER JOIN OrderDetails OD ON B.BookID = OD.BookID
INNER JOIN Orders O ON OD.OrderID = O.OrderID
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE E.FirstName =  'John'
AND E.LastName =  'Steely'
-- >>

-- Problem 6:

-- <<
SELECT C.FirstName, C.LastName, TRUNCATE( SUM( OD.Quantity * B.Unit_Price ) , 2 ) AS Total
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID
INNER JOIN Books B ON B.BookID = OD.BookID
GROUP BY C.CustomerID
ORDER BY Total DESC
-- >>

-- Problem 7:

-- <<
SELECT B.Title
FROM Books AS B
INNER JOIN OrderDetails AS OD ON B.BookID = OD.BookID
INNER JOIN Orders AS O ON O.OrderID = OD.OrderID
INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
WHERE (C.FirstName =  "John" AND C.LastName =  "Smith")
OR (C.FirstName =  "Jackie" AND C.LastName =  "Collins")
GROUP BY B.BookID
-- >>

-- Problem 8:

-- <<
SELECT B.Title, SUM( OD.Quantity ) AS quantity
FROM Books B
INNER JOIN OrderDetails OD ON OD.BookID = B.BookID
GROUP BY B.BookID
ORDER BY quantity ASC
-- >>

-- Problem 9:


-- <<
SELECT DISTINCT C.FirstName, C.LastName
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID
INNER JOIN Books B ON B.BookID = OD.BookID
WHERE B.Title LIKE  '%Louisiana%'
-- >>

-- Problem 10:

-- <<
SELECT DISTINCT C.FirstName, C.LastName
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID
INNER JOIN Books B ON B.BookID = OD.BookID
WHERE B.Author =  'Thomas'
-- >>

-- Problem 11:

-- <<
SELECT DISTINCT C.FirstName, C.LastName, B.Title
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID
INNER JOIN Books B ON B.BookID = OD.BookID
INNER JOIN Subjects S ON S.SubjectID = B.SubjectID
WHERE S.CategoryName =  'Fiction'
OR S.CategoryName =  'Travel'
-- >>

-- Problem 12:

-- <<
SELECT S.CategoryName, MIN( B.Unit_Price ) 
FROM Books B
INNER JOIN Subjects S ON S.SubjectID = B.SubjectID
GROUP BY S.SubjectID
-- >>

-- Problem 13:
--      Note: I assumed that a NULL ship date meant that it had not been shipped

-- <<
SELECT B.Title, SUM( OD.Quantity ) AS quantity
FROM Books AS B
INNER JOIN OrderDetails OD ON B.BookID = OD.BookID
INNER JOIN Orders O ON O.OrderID = OD.OrderID
WHERE O.ShippedDate > '2008-04-10' OR O.ShippedDate IS NULL
GROUP BY B.BookID
-- >>

-- Problem 14:

-- <<
SELECT C.FirstName, C.LastName, SUM( OD.Quantity ) AS quantity
FROM Books B
INNER JOIN OrderDetails OD ON OD.BookID = B.BookID
INNER JOIN Orders O ON O.OrderID = OD.OrderID
INNER JOIN Customers C ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
HAVING quantity >1
ORDER BY quantity DESC
-- >>

-- Problem 15:

-- <<
SELECT list.FirstName, list.LastName, list.Phone
FROM (
SELECT C.FirstName, C.LastName, C.Phone, SUM( OD.Quantity ) AS quantity
FROM Books B
INNER JOIN OrderDetails OD ON OD.BookID = B.BookID
INNER JOIN Orders O ON O.OrderID = OD.OrderID
INNER JOIN Customers C ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
HAVING quantity >1
) AS list
-- >>