problem_queries.js

var queries = {
        1 : "SELECT Title FROM Books WHERE Unit_in_Stock > 5",
        2 : "SELECT SUM(b.Unit_Price * od.Quantity) as total 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 LastName = \"Smith\" AND FirstName = \"John\"",
        3 : "SELECT c.FirstName, c.LastName, SUM(b.Unit_Price * od.Quantity) AS total_price 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 GROUP BY c.CustomerID HAVING total_price < 20",
        4 : "SELECT b.Title, s.ShipperName FROM Books AS b INNER JOIN OrderDetails AS od ON od.BookID = b.BookID INNER JOIN Orders AS o ON o.OrderID = od.OrderID INNER JOIN Shippers AS s ON s.ShipperID = o.ShipperID WHERE o.ShippedDate = '08/04/10'",
        5 : "SELECT b.Title FROM Books AS b INNER JOIN OrderDetails AS od ON od.BookID = b.BookID INNER JOIN Orders AS o ON o.OrderID = od.OrderID INNER JOIN Employees AS e ON e.EmployeeID = o.EmployeeID WHERE e.LastName = 'Steely' AND e.FirstName = 'John'",
        6 : "SELECT c.FirstName, c.LastName, SUM(b.Unit_Price * od.Quantity) AS total 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.CustomerID != '' GROUP BY c.CustomerID ORDER BY total DESC",
        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",
        8 : "SELECT b.Title, SUM(od.Quantity) as total_sold FROM Books AS b INNER JOIN OrderDetails AS od ON b.BookID = od.BookID GROUP BY b.BookID ORDER BY total_sold",
        9 : "SELECT c.FirstName, c.LastName FROM Customers AS c INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID INNER JOIN OrderDetails AS od ON od.OrderID = o.OrderID INNER JOIN Books AS b ON b.BookID = od.BookID WHERE b.Title LIKE '%Louisiana%' GROUP BY c.CustomerID",
        10 : "SELECT c.FirstName, c.LastName FROM Customers AS c INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID INNER JOIN OrderDetails AS od ON od.OrderID = o.OrderID INNER JOIN Books AS b ON b.BookID = od.BookID WHERE b.Author = 'Thomas' GROUP BY c.CustomerID",
        11 : "SELECT c.FirstName, c.LastName, b.Title FROM Customers AS c INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID INNER JOIN OrderDetails AS od ON od.OrderID = o.OrderID INNER JOIN Books AS b ON b.BookID = od.BookID INNER JOIN Subjects AS s ON s.SubjectID = b.SubjectsId WHERE s.CategoryName = 'Fiction' OR s.CategoryName = 'Travel' GROUP BY c.CustomerID",
        12 : "SELECT s.CategoryName, b1.SubjectsID, b1.Title, b1.Unit_Price FROM Books AS b1 INNER JOIN Books AS b2 LEFT JOIN Subjects AS s ON s.SubjectID = b1.SubjectsID WHERE (b1.SubjectsID NOT IN (SELECT b3.SubjectsID FROM Books AS b3 GROUP BY b3.SubjectsID HAVING COUNT(b3.SubjectsID) > 1)) OR (b1.SubjectsID = b2.SubjectsID AND b1.Unit_Price < b2.Unit_Price) GROUP BY b1.SubjectsID",
        13 : "SELECT b.Title, SUM(od.Quantity) as Quantity FROM Books AS b LEFT JOIN OrderDetails AS od ON b.BookID = od.BookID LEFT JOIN Orders AS o ON o.OrderID = od.OrderID WHERE o.ShippedDate IS NULL OR o.ShippedDate >= '2008-04-10' GROUP BY b.BookID HAVING Quantity > 0",
        14 : "SELECT c.FirstName, c.LastName, SUM(od.Quantity) as total_sold FROM Customers AS c LEFT JOIN Orders AS o ON o.CustomerID = c.CustomerID LEFT JOIN OrderDetails AS od ON od.OrderID = o.OrderID GROUP BY c.CustomerID HAVING total_sold > 1 ORDER BY total_sold DESC",
        15 : "SELECT c.FirstName, c.LastName, c.Phone FROM Customers AS c LEFT JOIN Orders AS o ON o.CustomerID = c.CustomerID LEFT JOIN OrderDetails AS od ON od.OrderID = o.OrderID GROUP BY c.CustomerID HAVING SUM(od.Quantity) > 1"
    }