Access Millions of academic & study documents

Mydoc (11)

Content type
User Generated
Showing Page:
1/5
Surname 1
Student’s Name
Course
Instructor’s Name
Date
Relational Database
(1) For which items are there at least 10 units in quantity on hand?
SELECT * FROM Inventory WHERE QuantityOnHand >= 10
(2) List the invoice number, dates, and salesperson for sales made to Rodney Wern. SELECT
Sales.SalesInvoiceID,Sales.SaleDate,Sales.SalesPerson
From Sales
Where Sales.CustomerID =153
(3) List the dates, description, and quantality for each transaction in which Stainless-color appliances were sold
in October.
SELECT Sales.SaleDate,Inventory.Description,Inventory.color,Inventory.QuantityOnHand

Sign up to view the full document!

lock_open Sign Up
Showing Page:
2/5
Surname 2
FROM (Sales INNER JOIN Sales_Inventory on Sales.salesinvoiceid = Sales_Inventory.SalesInvoiceID)
INNER JOIN Inventory on Inventory.ItemID = Sales_Inventory.ItemID
WHERE Inventory.color = "Stainless" AND Sales.SaleDate BETWEEN "10/01/2021" AND "10/31/2021";
(4) List description and sum of quantity of inventory types that sold 2 or more units in October.
SELECT Inventory. Description, Sum(Sales_Inventory. Quantity) AS SumOfQuantity
from (Sales INNER JOIN Sales_Inventory on Sales.salesinvoiceid = Sales_Inventory.SalesInvoiceID)
inner JOIN Inventory on Sales_Inventory.ItemID = Inventory.ItemID WHERE Sales.saledate
BETWEEN "10/1/2021" AND "10/30/2021" GROUP BY Inventory. Description
HAVING Sum(Sales_Inventory.quantity)>=2;
(5) List sale date and invoice total for each invoice in October.
SELECT s.salesinvoiceid 's.salesinvoiceid', s.saledate 's.saledate', Sum([si].[quantity]*[si].[soldprice]) as
InvoiceTotal
from Sales as s INNER JOIN Sales_Inventory as si on s.salesinvoiceid = si.salesinvoiceid WHERE
s.saledate BETWEEN "10/01/2021" AND "10/31/2021"
GROUP BY s.salesinvoiceid,s.saledate;

Sign up to view the full document!

lock_open Sign Up
Showing Page:
3/5

Sign up to view the full document!

lock_open Sign Up
End of Preview - Want to read all 5 pages?
Access Now
Unformatted Attachment Preview
Surname 1 Student’s Name Course Instructor’s Name Date Relational Database (1) For which items are there at least 10 units in quantity on hand? SELECT * FROM Inventory WHERE QuantityOnHand >= 10 (2) List the invoice number, dates, and salesperson for sales made to Rodney Wern. SELECT Sales.SalesInvoiceID,Sales.SaleDate,Sales.SalesPerson From Sales Where Sales.CustomerID =153 (3) List the dates, description, and quantality for each transaction in which Stainless-color appliances were sold in October. SELECT Sales.SaleDate,Inventory.Description,Inventory.color,Inventory.QuantityOnHand Surname 2 FROM (Sales INNER JOIN Sales_Inventory on Sales.salesinvoiceid = Sales_Inventory.SalesInvoiceID) INNER JOIN Inventory on Inventory.ItemID = Sales_Inventory.ItemID WHERE Inventory.color = "Stainless" AND Sales.SaleDate BETWEEN "10/01/2021" AND "10/31/2021"; (4) List description and sum of quantity of inventory types that sold 2 or more units in October. SELECT Inventory. Description, Sum(Sales_Inventory. Quantity) AS SumOfQuantity from (Sales INNER JOIN Sales_Inventory on Sales.salesinvoiceid = Sales_Inventory.SalesInvoiceID) inner JOIN Inventory on Sales_Inventory.ItemID = Inventory.ItemID WHERE Sales.saledate BETWEEN "10/1/2021" AND "10/30/2021" GROUP BY Inventory. Description HAVING Sum(Sales_Inventory.quantity)>=2; (5) List sale date and invoice total for each invoice in October. SELECT s.salesinvoiceid 's.salesinvoiceid', s.saledate 's.saledate', Sum([si].[quantity]*[s ...
Purchase document to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Indeed
4.5
Sitejabber
4.4