Questions Write SQL statements that will answer the following four questions based on the following tables. 1. Find suppliers (company name, country and city) whose country is Australia or whose city is Paris. 2. Compute the extended price for each order. Extended price is obtained by multiplying unit price by quantity (from Order Details table). Show order id, product name and extended price. Sort the results by product name in ascending order. 3. List products ids and product names of discontinued confection products that are still held in stock. (Confection products are those that have "Confections" as category name.) 4. Find the total quantity of each product ordered within each category. Display the category id, category name, product name and total quantity. Hints: The MONTH(DateField) function can be used to evaluate criteria about the month portion of a date field. The FORMAT(DateField) function can be used to return the month names. The SELECT statement has several options that can be used to select a certain set of values from the resulting query. SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM Table ALL Default - selects all records DISTINCT Omits records that contain duplicate data in the selected fields. DISTINCTROW Omits data based on entire duplicate records, not just duplicate fields. TOP n [Percent] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Tables and fields: Categories Name Type Size 137 Page 2 of 3 pages CategoryID Number (Long) 4 CategoryName Text 15 Description Memo - Picture OLE Object - Products Name Type Size ProductID Number (Long) 4 ProductName Text 40 SupplierID Number (Long) 4 CategoryID Number (Long) 4 QuantityPerUnit Text 20 UnitPrice Currency 8 UnitsInStock Number (Integer) 2 UnitsOnOrder Number (Integer) 2 ReorderLevel Number (Integer) 2 Discontinued Yes/No 1 Suppliers Name Type Size SupplierID Number (Long) 4 CompanyName Text 40 ContactName Text 30 ContactTitle Text 30 Address Text 60 City Text 15 Region Text 15 PostalCode Text 10 Country Text 15 Phone Text 24 Fax Text 24 HomePage Hyperlink - Orders Name Type Size OrderID Number (Long) 4 CustomerID Text 5 EmployeeID Number (Long) 4 OrderDate Date/Time 8 RequiredDate Date/Time 8 ShippedDate Date/Time 8 ShipVia Number (Long) 4 Freight Currency 8 ShipName Text 40 ShipAdresss Text 60 ShipCity Text 15 ShipRegion Text 15 ShipPostalCode Text 10 ShipCountry Text 15 Order Details Name Type Size OrderID Number (Long) 4 ProductID Number (Long) 4 UnitPrice Currency 8 Quantity Number (Integer) 2 Discount Number (Single) 4 138 There is a one-to-many relationship between categories and products (that is, there can be many products belonging to a category). There are also one-to-many relationships between suppliers and products, orders and order details, products and order detail
Questions
Write SQL statements that will answer the following four questions based on
the following tables.
1. Find suppliers (company name, country and city) whose country is
Australia or whose city is Paris.
2. Compute the extended price for each order. Extended price is obtained by
multiplying unit price by quantity (from Order Details table). Show order id,
product name and extended price. Sort the results by product name in
ascending order.
3. List products ids and product names of discontinued confection products
that are still held in stock. (Confection products are those that have
"Confections" as category name.)
4. Find the total quantity of each product ordered within each category.
Display the category id, category name, product name and total quantity.
Hints:
The MONTH(DateField) function can be used to evaluate criteria about the month portion of a
date field.
The FORMAT(DateField) function can be used to return the month names.
The SELECT statement has several options that can be used to select a
certain set of values from the resulting query.
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM Table
ALL Default - selects all records
DISTINCT Omits records that contain duplicate data in
the selected fields.
DISTINCTROW Omits data based on entire duplicate records,
not just duplicate fields.
TOP n [Percent] Returns a certain number of records that fall
at the top or the bottom of a range specified
by an ORDER BY clause.
Tables and fields:
Categories
Name Type Size
137
Page 2 of 3 pages
CategoryID Number (Long) 4
CategoryName Text 15
Description Memo -
Picture OLE Object -
Products
Name Type Size
ProductID Number (Long) 4
ProductName Text 40
SupplierID Number (Long) 4
CategoryID Number (Long) 4
QuantityPerUnit Text 20
UnitPrice Currency 8
UnitsInStock Number (Integer) 2
UnitsOnOrder Number (Integer) 2
ReorderLevel Number (Integer) 2
Discontinued Yes/No 1
Suppliers
Name Type Size
SupplierID Number (Long) 4
CompanyName Text 40
ContactName Text 30
ContactTitle Text 30
Address Text 60
City Text 15
Region Text 15
PostalCode Text 10
Country Text 15
Phone Text 24
Fax Text 24
HomePage Hyperlink -
Orders
Name Type Size
OrderID Number (Long) 4
CustomerID Text 5
EmployeeID Number (Long) 4
OrderDate Date/Time 8
RequiredDate Date/Time 8
ShippedDate Date/Time 8
ShipVia Number (Long) 4
Freight Currency 8
ShipName Text 40
ShipAdresss Text 60
ShipCity Text 15
ShipRegion Text 15
ShipPostalCode Text 10
ShipCountry Text 15
Order Details
Name Type Size
OrderID Number (Long) 4
ProductID Number (Long) 4
UnitPrice Currency 8
Quantity Number (Integer) 2
Discount Number (Single) 4
138
There is a one-to-many relationship between categories and products (that is, there can be many
products belonging to a category). There are also one-to-many relationships between suppliers
and products, orders and order details, products and order detail
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
