Chapter 4
docx
keyboard_arrow_up
School
University of Florida *
*We aren’t endorsed by this school
Course
PLSQL
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
7
Uploaded by EarlSciencePartridge23
Chapter 4
1. Write a SELECT statement that joins the Categories table to the Products table
and
returns these columns: CategoryName, ProductName, ListPrice.
Sort the result set by CategoryName and then by ProductName in ascending.
SELECT
C
.
CategoryName
,
P
.
ProductName
,
P
.
ListPrice
FROM
Categories C
INNER JOIN
Products P
ON
C
.
CategoryID
=
P
.
CategoryID
ORDER BY
C
.
CategoryName
ASC
,
P
.
ProductName
2. Write a SELECT statement that joins the Customers table to the Addresses table
and
returns these columns: FirstName, LastName, Line1, City, State, ZipCode.
Return one row for each address for the customer with an email address of
allan.sherwood@yahoo.com
.
SELECT C.FirstName, C.LastName, A.Line1, A.City, A.State, A.ZipCode
FROM
Customers C
INNER JOIN
Addresses A ON C.CustomerID = A.CustomerID
WHERE Emailaddress = 'allan.sherwood@yahoo.com';
3. Write a SELECT statement that joins the Customers table to the Addresses table
and
returns these columns: FirstName, LastName, Line1, City, State, ZipCode.
Code the join so only addresses that are the shipping address for a customer are
returned.
SELECT
C
.
FirstName
,
C
.
LastName
,
A
.
Line1
,
A
.
City
,
A
.
State
,
A
.
ZipCode
FROM
Customers C
INNER JOIN
Addresses A
ON
C
.
ShippingAddressID
=
A
.
AddressID
;
4. Write a SELECT statement that joins the Customers, Orders, OrderItems, and
Products tables. This statement should return these columns: LastName, FirstName,
OrderDate, ProductName, ItemPrice, DiscountAmount, and Quantity.
Use aliases for the tables.
Sort the final result set by LastName, OrderDate, and ProductName.
SELECT
C
.
LastName
,
C
.
FirstName
,
O
.
OrderDate
,
P
.
ProductName
,
OI
.
ItemPrice
,
OI
.
DiscountAmount
,
OI
.
Quantity
FROM
Customers
AS
C
JOIN
Orders
AS
O
ON
C
.
CustomerID
=
O
.
CustomerID
JOIN
OrderItems
AS
OI
ON
O
.
OrderID
=
OI
.
OrderID
JOIN
Products
AS
P
ON
OI
.
ProductID
=
P
.
ProductID
ORDER BY
C
.
LastName
,
O
.
OrderDate
,
P
.
ProductName
;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
5. Write a SELECT statement that returns the ProductName and ListPrice columns
from
the Products table.
Return one row for each product that has the same list price as another product.
(Hint: Use a self-join to check that the ProductID columns aren’t equal but the
ListPrice column is equal.)
Sort the result set by ProductName.
SELECT
p1
.
ProductName
,
p1
.
ListPrice
FROM
Products p1
INNER JOIN
Products p2
ON
p1
.
ListPrice
=
p2
.
ListPrice
AND
p1
.
ProductID
<>
p2
.
ProductID
ORDER BY
p1
.
ProductName
;
6. Write a SELECT statement that returns these two columns:
CategoryName The CategoryName column from the Categories table
ProductID The ProductID column from the Products table
Return one row for each category that has never been used.
(Hint: Use an outer join
and only return rows where the ProductID column contains a null value.)
SELECT
C
.
CategoryName
,
P
.
ProductID
FROM
Categories C
LEFT JOIN
Products P
ON
C
.
CategoryID
=
P
.
CategoryID
WHERE
P
.
ProductID
IS NULL;
7. Use the UNION operator to generate a result set consisting of three columns from
the
Orders table:
ShipStatus A calculated column that contains a value of
SHIPPED or NOT SHIPPED
OrderID The OrderID column
OrderDate The OrderDate column
If the order has a value in the ShipDate column, the ShipStatus column should
contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED.
Sort the final result set by OrderDate.
SELECT
'SHIPPED'
AS
ShipStatus
,
OrderID
,
OrderDate
FROM
Orders
WHERE
ShipDate
IS NOT NULL
UNION
SELECT
'NOT SHIPPED'
AS
ShipStatus
,
OrderID
,
OrderDate
FROM
Orders
WHERE
ShipDate
IS NULL
ORDER BY
OrderDate
;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help