Please I really need some help on this because I am behind and having trouble un
ID: 3566283 • Letter: P
Question
Please I really need some help on this because I am behind and having trouble understanding how to further my code.This is all in Northwind Database. I need your help on making my code better. Please correct my answers in SQL Server formatt. Thank you for your time. I will post my code for each one below the questions.
Write the following queries using the database.
The employee ID, first and last names, the the total dollar amout for all products sold in the Beverages category.
The product ID, product name, and quantity sold for the product for which the largest quantity was sold on a single order. In the event of a tie, list all products that are tied.
The date, the day of the week, and number of orders taken on that date for all dates for which an order was taken. Place the listing in descending order by the number of orders taken.
The category ID, category name, and the total sales amount in that category for each category.
For supplier 10, list the product ID, product name, category name, and unit price for all items supplied by that supplier.
1) SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Categories.CategoryName, SUM([Order Details].UNITPRICE*Quantity) AS [ORder TOTAL]
FROM Employees, Orders, Categories, Products, [Order Details]
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName, Categories.CategoryName
HAVING SUM([Order Details].UNITPRICE*Quantity) > 0
2)
SELECT Products.ProductID, Products.ProductName, Products.QuantityPerUnit
FROM Products;
3)
SELECT Orders.OrderDate, Orders.RequiredDate
FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID;
4)
SELECT Categories.CategoryID, Categories.CategoryName
FROM Categories;
5)
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName, Products.UnitPrice
FROM Categories,Suppliers,Products
WHERE Suppliers.SupplierID = Products.SupplierID
AND Categories.CategoryID = Products.CategoryID;
Explanation / Answer
-- The employee ID, first and last names, the the total dollar amout for all products sold in
-- the Beverages category.
SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Categories.CategoryName,
SUM([Order Details].UNITPRICE*Quantity) AS [ORder TOTAL]
FROM Employees, Orders, Categories, Products, [Order Details]
WHERE UPPER(Categories.categoryName) = 'BEVERAGES'
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName, Categories.CategoryName
HAVING SUM([Order Details].UNITPRICE*Quantity) > 0;
-- The product ID, product name, and quantity sold for the product for which the largest
-- quantity was sold on a single order. In the event of a tie, list all products that are tied.
SELECT Products.ProductID, Products.ProductName, Products.QuantityPerUnit
FROM Products
WHERE ProductId IN (
SELECT productId
FROM [Order Details]
WHERE quantity = (SELECT MAX(quantity) FROM [Order Details]));
-- The date, the day of the week, and number of orders taken on that date for all dates for
-- which an order was taken. Place the listing in descending order by the number of orders taken.
SELECT Orders.OrderDate, datename(dw,Orders.OrderDate), count([Order Details].OrderID) #OfOrders
FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderDate, datename(dw,Orders.OrderDate);
-- The category ID, category name, and the total sales amount in that category for each category.
SELECT Categories.CategoryID, Categories.CategoryName, SUM([Order Details].unitPrice * [Order Details].quantity)
FROM Categories, products, [Order Details]
WHERE Categories.CategoryID = products.CategoryID
AND products.productId = [Order Details].productId
GROUP BY Categories.CategoryID, Categories.CategoryName
-- For supplier 10, list the product ID, product name, category name, and unit price
-- for all items supplied by that supplier.
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName, Products.UnitPrice
FROM Categories,Suppliers,Products
WHERE Suppliers.SupplierID = Products.SupplierID
AND Categories.CategoryID = Products.CategoryID
AND Suppliers.SupplierID = 10;