Nested Query and Joins Practice Solutions.docx
doc
keyboard_arrow_up
School
Liberty University *
*We aren’t endorsed by this school
Course
326
Subject
Information Systems
Date
Apr 3, 2024
Type
doc
Pages
4
Uploaded by DrFlag11340
Nested Query and Joins Practice Solutions
1. Using a Type I nested query, list the customer number, name (first and last), and city of each cus-
tomer who has a balance greater than $150 and placed an order in February 2007. SELECT CustNo
, CustFirstName
, CustLastName
, CustCity
FROM Customer
WHERE CustBal > 150 AND CustNo IN
( SELECT CustNo
FROM OrderTbl
WHERE OrdDate BETWEEN '2/1/2007' AND '2/28/2007' );
2.
Using a Type II nested query, list the customer number, name (first and last), and city of each cus-
tomer who has a balance greater than $150 and placed an order in February 2007. The nested query references the outer query in the condition involving the CustNo
columns.
SELECT CustNo
, CustFirstName
, CustLastName
, CustCity
FROM Customer
WHERE CustBal > 150 AND EXISTS
( SELECT CustNo
FROM OrderTbl
WHERE OrdDate BETWEEN '2/1/2007' AND '2/28/2007' AND Customer
.
CustNo = OrderTbl
.
CustNo );
3.
List the order number, order date, employee number, and employee name (first and last) of orders placed on January 23, 2007. List the order even if there is not an associated employee. SELECT OrdNo
, OrdDate
, Employee
.
EmpNo
, EmpFirstName
, EmpLastName
FROM OrderTbl LEFT JOIN Employee ON OrderTbl
.
EmpNo = Employee
.
EmpNo
WHERE OrdDate = '1/23/2007'
4.
List the order number, order date, employee number, employee name (first and last), customer number, and customer name (first and last) of orders placed on January 23, 2007. List the order even if there is not an associated employee. SELECT OrdNo
, OrdDate
, Employee
.
EmpNo
, EmpFirstName
, EmpLastName
,
Customer
.
CustNo
, CustFirstName
, CustLastName
FROM ( OrderTbl LEFT JOIN Employee ON OrderTbl
.
EmpNo = Employee
.
EmpNo )
INNER JOIN Customer ON Customer
.
CustNo = OrderTbl
.
CustNo
WHERE OrdDate = '1/23/2007'
3/29/2024
Nested Query and Joins Practice Problems Answers
5.
Using two Type I nested queries, list the product number, the name, and the price of products with a price greater than $150 that were ordered on January 23, 2007. Type I nested queries work for this problem because the result table contains columns from only one table.
SELECT ProdNo
, ProdName
, ProdPrice
FROM Product
WHERE ProdPrice > 150 AND ProdNo IN
( SELECT ProdNo
FROM OrdLine
WHERE OrdNo IN
( SELECT OrdNo
FROM OrderTbl
WHERE OrdDate = '1/23/2007' ) );
6.
Using a Type II nested query, list the customer number and name of Colorado customers who have not placed orders in February 2007. SELECT Customer
.
CustNo
, CustFirstName
, CustLastName
FROM Customer
WHERE CustState = 'CO' AND NOT EXISTS
( SELECT *
FROM OrderTbl WHERE OrdDate BETWEEN '2/1/2007' AND '2/28/2007'
AND OrderTbl
.
CustNo = Customer
.
CustNo )
7.
Using a Type I nested query with a NOT IN condition, list the customer number and name of Colorado customers who have not placed orders in February 2007. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. SELECT Customer
.
CustNo
, CustFirstName
, CustLastName FROM Customer WHERE CustState = 'CO' AND CustNo NOT IN
( SELECT CustNo
FROM OrderTbl WHERE OrdDate BETWEEN '2/1/2007' AND '2/28/2007' )
8.
Using a Type I nested query, list the employee number, first name, and last name of employees in the (720) area code who have not taken orders. An employee is in the (720) area code if the employee phone number contains the string (720) in the beginning of the column value. SELECT EmpNo
, EmpLastName
, EmpFirstName
FROM Employee
WHERE EmpPhone LIKE '(720)%' AND EmpNo NOT IN 2
3/29/2024
Nested Query and Joins Practice Problems Answers
( SELECT distinct EmpNo FROM OrderTbl where EmpNo is NOT NULL)
9.
Using a Type II nested query, list the employee number, first name, and last name of employees in the (720) area code who have not taken orders. An employee is in the (720) area code if the employee
phone number contains the string (720) in the beginning of the column value. SELECT EmpNo
, EmpLastName
, EmpFirstName
FROM Employee
WHERE EmpPhone LIKE '(720)%' AND NOT EXISTS ( SELECT * FROM OrderTbl WHERE Employee
.
EmpNo = OrderTbl
.
EmpNo and OrderTbl
.
EmpNo is NOT NULL)
The condition “EmpNo IS NOT NULL” is needed in the nested query to eliminate the null value in the result of the nested query. Otherwise, the result contains no rows because an employee number compared to null returns null, not false.
10.
List all the people in the database. The resulting table should have all columns of the Customer and Employee tables. Match the Customer and Employee tables on first and last names. If a customer does not match any employees, the columns pertaining to the Employee table will be blank. Similarly, for an employee who does not match any customers, the columns pertaining to the Customer table will be blank. This problem requires a full outer join. Both the Full Join technique and the two one-sided outer joins and a union technique are used in this example. Full-join syntax:
SELECT *
FROM Customer FULL JOIN Employee ON Customer
.
CustFirstName = Employee
.
EmpFirstName AND Customer
.
CustLastName = Employee
.
EmpLastName
;
Union syntax:
SELECT Customer
.*, Employee
.*
FROM Customer LEFT JOIN Employee ON Customer
.
CustFirstName = Employee
.
EmpFirstName AND Customer
.
CustLastName = Employee
.
EmpLastName UNION SELECT Customer
.*, Employee
.*
FROM Customer RIGHT JOIN Employee ON Customer
.
CustFirstName = Employee
.
EmpFirstName
AND Customer
.
CustLastName = Employee
.
EmpLastName
11.
For each Ink Jet product ordered in January 2007, list the order number, order date, customer number, customer name (first and last), employee number (if present), employee name (first and 3
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
3/29/2024
Nested Query and Joins Practice Problems Answers
last), quantity ordered, product number, and product name. Include products containing Ink Jet in the product name. Include both Internet (no employee) and phone orders (taken by an employee). SELECT OrderTbl
.
OrdNo
, OrdDate
, Employee
.
EmpNo
, EmpFirstName
, EmpLastName
, Customer
.
CustNo
, CustFirstName
, CustLastName
, OrdLine
.
Quantity
,
Product
.
ProdNo
, ProdName
FROM ( ( ( OrderTbl LEFT JOIN Employee ON OrderTbl
.
EmpNo = Employee
.
EmpNo
) INNER JOIN Customer ON Customer
.
CustNo = OrderTbl
.
CustNo )
INNER JOIN OrdLine ON OrderTbl
.
OrdNo = OrdLine
.
OrdNo )
INNER JOIN Product ON OrdLine
.
ProdNo = Product
.
ProdNo WHERE OrdDate BETWEEN '1/1/2007' AND '1/31/2007'
AND ProdName LIKE '%Ink Jet%'
12.
List the order number and order date of orders containing only one product with the words Ink Jet in the product description. select ot
.
ordno
, ot
.
orddate from ordertbl ot
, ordline ol
, product p
where ot
.
ordno = ol
.
ordno and p
.
prodno = ol
.
prodno
and p
.
prodname like '%Ink Jet%'
group by ot
.
ordno
, orddate
having count
(*) = 1
13.
List the order number and order date of orders containing every product with the words Ink Jet in the product description. This problem requires a division operation because the problem statement involves orders containing every
“Ink Jet” product, not just any “Ink Jet” product. The COUNT method is used for the division operation.
SELECT OrderTbl
.
OrdNo
, OrdDate
FROM OrderTbl
, OrdLine
, Product WHERE OrderTbl
.
OrdNo = OrdLine
.
OrdNo AND OrdLine
.
ProdNo = Product
.
ProdNo
AND ProdName LIKE '%Ink Jet%'
GROUP BY OrderTbl
.
OrdNo
, OrdDate
HAVING COUNT
(*) =
( SELECT COUNT
(*)
FROM Product
WHERE ProdName LIKE '%Ink Jet%' )
4