Database SQL. In the SQL Server Management I am suppose to create five different
ID: 3770766 • Letter: D
Question
Database SQL. In the SQL Server Management I am suppose to create five different sqls that do the following:
1. The date, number of orders taken on that date for each date in August, 1996. Placing the listing in order by date.
2. The product ID, product name, the number of products sold and the total price for those products for every product sold in August, 1996.
3. The employee ID, employee first and last names, and the total number of items and the total price of those items for all the items in the meat/poultry category that were sold by that employee. Placing the listing in descending order by the total price.
4. The category ID, category name, and the total sales amount in that category for each category. Placing the listing in alphabetical order by category name.
5. The supplier ID, supplier name, and the largest quantity of any item from that supplier that has been sold. Placing the listing in descending order by the largest quantity.
If someone would be able to check to see if my code is correct it would be greatly appreciated. I think I have the first three correct but the last two I am pretty lost on. Below is my code so far.
1. SELECT Orders.OrderDate, [Order Details].Quantity
FROM Orders
FULL OUTER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE (Orders.OrderDate >= '1996-08-01' AND Orders.OrderDate <= '1996-08-31');
2. SELECT Products.ProductID, Products.ProductName, Orders.OrderDate, Products.UnitsOnOrder, UnitPrice, (Products.UnitsOnOrder * Products.UnitPrice) AS TotalCost
FROM Products, Orders
WHERE (Orders.OrderDate >= '1996-08-01' AND Orders.OrderDate <= '1996-08-31');
3. SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, [Order Details].Quantity,
Categories.CategoryID, Categories.CategoryName, Orders.OrderDate, ([Order Details].UnitPrice * [Order Details].Quantity) AS TotalPrice
FROM Employees, [Order Details], Categories, Orders
WHERE (Categories.CategoryID = 6) AND (OrderDate >= '1996-08-01' AND OrderDate <= '1996-08-31');
4. not sure how to do this one or number 5
5. SELECT MAX( UnitsOnOrder )
FROM Products
WHERE UnitsOnOrder < ( SELECT MAX( UnitsOnOrder )
FROM Products )
Explanation / Answer
1)
SELECT Orders.OrderDate, [Order Details].Quantity
FROM Orders
FULL OUTER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE (Orders.OrderDate >= '1996-08-01' AND Orders.OrderDate <= '1996-08-31');
2)
SELECT Products.ProductID, Products.ProductName, Orders.OrderDate, Products.UnitsOnOrder, UnitPrice, (Products.UnitsOnOrder * Products.UnitPrice) AS TotalCost
FROM Products, Orders
WHERE (Orders.OrderDate >= '1996-08-01' AND Orders.OrderDate <= '1996-08-31');
3)
SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, [Order Details].Quantity,
Categories.CategoryID, Categories.CategoryName, Orders.OrderDate, ([Order Details].UnitPrice * [Order Details].Quantity) AS TotalPrice
FROM Employees, [Order Details], Categories, Orders
WHERE (Categories.CategoryID = 6) AND (OrderDate >= '1996-08-01' AND OrderDate <= '1996-08-31');
*************** YES, first three are valid ********************************
--------------------------------------------------------------------------------
4)
SELECT Category.CategoryID, Category.CategoryName, SUM(Category.Sales) FROM Category ORDER BY Category.CategoryName;
-----------------------------------------------------------------------------------------------------
5) *********** Half of your sql is correct .. Here is the remaining **********************
SELECT Supplier.supplierID, Supplier.supplierName From Supplier
FULL OUTER JOIN [Products] MAX( Products.UnitsOnOrder )
WHERE Products.UnitsOnOrder < ( SELECT MAX(Products.UnitsOnOrder )) ORDER BY Products.UnitsOnOrder DESC
------------- Here I am selecting supplierID, supplierName from Supplier and then joining Products table, so that
we are getting max of units order and selecting which proucts are sold and ordering by descending order ---------------
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.