Using the schema below, how would you create the queries for the problems below

Question

Using the schema below, how would you create the queries for the problems below the schema? What would you need select in access?

1.List the company name, city, region and postal code for all Customers. Put the list in order of region, then city.
2. List the company name, city, region and postal code for all Customers who are located in Oregon. Put the list in order of region, then city, then company name. (Hints: OR is the Region value when a company is located in Oregon.)
3. List the company name, city, region and postal code for all Customers who are located in Oregon or Washington. Put the list in descending order of region, then city, then company name.
4. List the order ID, customer ID, and date for all orders placed by customers with the ID 'QUICK' , 'FRANK' or 'RANCH'. Put the list in order of customer ID, then date.
5. Show the customer ID and number of orders placed by customers with the ID 'QUICK' , 'FRANK' or 'RANCH'. Put the list in order of customer ID. (Hint: The results will have two columns and three rows.)
6. Repeat the query for Problem 5, but label the number of orders placed column with the heading "Order Count."
7. Write a query that answers the following question. How many different customers have placed orders?
8. Write a query that answers the following question. How many orders were placed in May of 1995? Use the heading "Orders placed in May 1995" as your column heading.
9. Write a query that answers the following question. How many different customer placed orders in May of 1995?
10. List the product ID, supplier ID, number of units in stock and discontinued status (1 = yes, 0 = no) of all products that are supplied by suppliers 2 or 12 that have not been discontinued and that have less than 60 units in stock. Put the list in order of supplier ID, then in descending order of the quantity of units in stock.

Suppliers Y Supplierld CompanyName ContactName ContactTitle Address Products ProductID Customers Y CustomerID ProductName Order Details Y Orderld Y Y ProductID UnitPrice Quantity Orders tal SupplierD CategoryID QuantityPerUnit Unit Price CompanyName ContacName FY OrderID Discount Region PostalCode Country Phone Fax HomePage UnitsInStock Units on order ContactTitle Address City Region PostalCode ReorderLevel Employees YEmployeeiD N1 LastName FirstName Title TitleOrCourtesy BirthDate OO Discontinued Categories Y CategoryD CategoryName Description Picture CustomerID EmployeelD OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry Country Phone Fax HireDate Address Region Shippers Shipperld CompanyName Phone PostalCode Country HomePhone Extension Photo Notes ReportsTo

Explanation / Answer

1.List the company name, city, region and postal code for all Customers. Put the list in order of region, then city

Select CompanyName, City, Region, PostalCode
From Customers
Order by Region,City

2. List the company name, city, region and postal code for all Customers who are located in Oregon. Put the list in order of region, then city, then company name. (Hints: OR is the Region value when a company is located in Oregon.)

Select CompanyName, City, Region, PostalCode
From Customers
where Region IS ;OR'
Order by Region,City,CompanyName

3. List the company name, city, region and postal code for all Customers who are located in Oregon or Washington. Put the list in descending order of region, then city, then company name.

Select CompanyName, City, Region, PostalCode
From Customers
where Region IS 'OR' OR Region IS 'WA'
Order by Region,City,CompanyName DESC

4. List the order ID, customer ID, and date for all orders placed by customers with the ID 'QUICK' , 'FRANK' or 'RANCH'. Put the list in order of customer ID, then date.

Select OrderId, customerID, OrderDate
From Orders
INNER JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
Where CustomerId IS 'QUICK' OR CustomerId IS 'FRANK' OR CustomerId IS 'RANCH'

5. Show the customer ID and number of orders placed by customers with the ID 'QUICK' , 'FRANK' or 'RANCH'. Put the list in order of customer ID. (Hint: The results will have two columns and three rows.)

Select Count(OrderId) AS COUNT, customerID
From Orders
INNER JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
Where CustomerId IS 'QUICK' OR CustomerId IS 'FRANK' OR CustomerId IS 'RANCH'
GROUPBY CustomerId

6. Repeat the query for Problem 5, but label the number of orders placed column with the heading "Order Count."

Select Count(OrderId) AS Order Count, customerID
From Orders
INNER JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
Where CustomerId IS 'QUICK' OR CustomerId IS 'FRANK' OR CustomerId IS 'RANCH'
GROUPBY CustomerId

7. Write a query that answers the following question. How many different customers have placed orders?
Select DISTINCT Count(CustomerID) AS COUNT
From Orders
INNER JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
GROUPBY CustomerId

8. Write a query that answers the following question. How many orders were placed in May of 1995? Use the heading "Orders placed in May 1995" as your column heading.

Select count(OrderId)
From Orders
INNER JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
WHERE CONTAINS(OrderDate,'*/05/1995*')

9. Write a query that answers the following question. How many different customer placed orders in May of 1995?

Select count(CustomerID)
From Orders
INNER JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
WHERE CONTAINS(OrderDate,'*/05/1995*')

10. List the product ID, supplier ID, number of units in stock and discontinued status (1 = yes, 0 = no) of all products that are supplied by suppliers 2 or 12
that have not been discontinued and that have less than 60 units in stock. Put the list in order of supplier ID, then in descending order of the quantity of
units in stock.

Select productID,supplierID,UnitsInStock, Discontinued, Count(UnitsInStock) AS Counting
from Products INNER JOIN Suppliers ON Products.supplierID=Suppliers.supplierID
WHERE supplierID IS '2' OR supplierID IS '12' AND Discontinued is '1' and Count(UnitsInStock)>60
OrderBy supplierID , Counting

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.