Practice questions DB

1. List the customer number, name (first and last), and balance of customers.

select custno, custfirstname, custlastname, custbal

from customer

2. List the customer number, name (first and last), and balance of customers who reside in Colorado
(CustState is 'CO').

select custno, custfirstname, custlastname, custbal

from customer
where custstate='CO'

3. List all columns of the Product table for products costing more than $50. Order the result by product
manufacturer (ProdMfg) and product name.

select *
from product
where prodprice > 50
order by prodmfg, prodname

4. List the order number, order date, and shipping name (OrdName) of orders sent to addresses in Denver or
Englewood.

select ordno, orddate, ordname

from ordertbl
where ordcity='Denver' OR ordcity='Englewood'

5. List the customer number, name (first and last), city, and balance of customers who reside in Denver with
a balance greater than $150 or who reside in Seattle with a balance greater than $300.

select custno, custfirstname, custlastname, custcity, custbal

from customer
where custcity='Denver' and custbal >150
OR custcity='Seattle' and custbal > 300

6. List the cities and states where orders have been placed. Remove duplicates from the result.

select distinct ordcity, ordstate

from ordertbl

7. List all columns of the OrderTbl table for Internet orders placed in January 2004. An Internet order does
not have an associated employee.

select *
from ordertbl
where orddate between '01-Jan-04' and '31-Jan-04'
and empno is null

8. List all columns of the OrderTbl table for phone orders placed in February 2004. A phone order has an
associated employee.
select *
from ordertbl
where orddate between '01-Feb-04' and '28-Feb-04'
and empno is not null

9. List all columns of the Product table that contain the words 'Ink Jet' in the product name.

select *
from product
where prodname like '%Ink Jet%'

10. List the order number, order date, and customer number of orders placed after January 23, 2004, that are
shipped to Washington recipients.

select ordno, orddate, custno

from ordertbl
where orddate > '23-Jan-04' and ordstate ='WA'

11. List the average balance of customers by city. Only include customers residing in Washington state ('WA').

select avg(custbal), custcity

from customer
where custstate='WA'
group by customer.custcity

12. List the average balance of customers by city and short zip code (the first five digits of the zip code). Only
include customers residing in Washington state ('WA'). In PostgreSQL and Oracle, the expression substr
(CustZip, 1, 5) returns the first five digits.

select avg(custbal), custcity, substr (custzip,1,5)

from customer
where custstate='WA'
group by custcity, substr (custzip,1,5)

13. List the average balance and number of customers by city. Only include customers residing in Washington
state ('WA'). Eliminate cities in the result with less than two customers.

select avg(custbal), custcity, count(custno)

from customer
where custstate='WA'
group by customer.custcity
HAVING count(custno) >= 2

14. List the number of unique short zip codes and average customer balance by city. Only include customers
residing in Washington state ('WA'). Eliminate cities in the result in which the average balance is less than
$100. In PostfeqSQL and Oracle, the expression substr (CustZip, 1, 5) returns the first five digits.

SELECT CustCity, COUNT(DISTINCT substr(CustZip, 1, 5)) AS NumShortZips, AVG(CustBal) AS AvgBal FROM

Customer
WHERE CustState = 'WA'
GROUP BY CustCity
HAVING AVG(CustBal) > 100
 15. List the product number, product name, sum of the quantity of products ordered, and total order amount
(sum of the product price times the quantity) for orders placed in January 2004. Only include products
that have more than five products ordered in January 2004. Sort the result by descending total amount.

SELECT Product.ProdNo, ProdName, SUM(Qty) AS ProdQty, SUM(Qty*ProdPrice) AS TotOrdAmt

FROM OrderTbl, OrdLine, Product
WHERE OrdDate BETWEEN '1-Jan-2004' AND '31-Jan-2004'
AND OrderTbl.OrdNo = OrdLine.OrdNo
AND OrdLine.ProdNo = Product.ProdNo
GROUP BY Product.ProdNo, ProdName
HAVING SUM(Qty) > 5
ORDER BY SUM(Qty*ProdPrice) DESC;

16. List the order number, order date, customer number, and customer name (first and last) of orders placed
in January 2004 sent to Colorado recipients.

select ordno, orddate, customer.custno, custfirstname, custlastname

from ordertbl, customer
where ordstate='CO'
and orddate between '01-jan-04' and '31-jan-04'
and ordertbl.custno = customer.custno

17. List the order number, order date, customer number, and customer name (first and last) of orders placed
in January 2004 placed by Colorado customers (CustState) but sent to Washington recipients (OrdState).

select ordno, orddate, customer.custno, custfirstname, custlastname

from ordertbl, customer
where ordstate='WA'
and orddate between '01-jan-04' and '31-jan-04'
and custstate='CO'
and ordertbl.custno = customer.custno

18. List the customer number, name (first and last), and balance of Washington customers who have placed
one or more orders in February 2004. Remove duplicate rows from the result.

select distinct customer.custno, custfirstname, custlastname, custbal

from ordertbl, customer
where custstate='WA'
and orddate between '1-feb-04' and '28-feb-04'
and ordertbl.custno = customer.custno

19. List the order number, order date, customer number, customer name (first and last), employee number,
and employee name (first and last) of January 2004 orders placed by Colorado customers.

select ordno, orddate, customer.custno, custfirstname, custlastname, employee.empno, empfirstname,

emplastname
from employee, customer, ordertbl
where orddate between '01-jan-04' and '31-jan-04'
and custstate='CO'
and ordertbl.custno = customer.custno
and ordertbl.empno = employee.empno
 20. List the employee number, name (first and last), and phone of employees who have taken orders in
January 2004 from customers with balances greater than $300. Remove duplicate rows in the result.

select distinct employee.empno, empfirstname, emplastname, empphone

from employee, ordertbl, customer
where orddate between '01-jan-04' and '31-jan-04'
and custbal >300
and ordertbl.empno = employee.empno
and ordertbl.custno = customer.custno

21. List the product number, name, and price of products ordered by customer number C0954327 in January
2004. Remove duplicate products in the result. (????)

select distinct product.prodno, prodname, prodprice

from ordertbl natural join product natural join customer natural join ordline
where custno = 'C0954327'
and orddate between '01-jan-04' and '31-jan-04'
and ordertbl.ordno = ordline.ordno
and ordline.prodno=product.prodno
and

22. List the customer number, name (first and last), order number, order date, employee number, employee
name (first and last), product number, product name, and order cost (OrdLine.Qty * ProdPrice) for
products ordered on January 23, 2004, in which the order cost exceeds $150.

SELECT Customer.CustNo, CustFirstName, CustLastName, OrderTbl.OrdNo, OrdDate, Employee.EmpNo,

EmpFirstName, EmpLastName,Product.ProdNo, ProdName, ProdPrice*Qty AS OrderCost
FROM OrderTbl, OrdLine, Product, Customer, Employee
WHERE OrdDate = '23-Jan-2004'
AND ProdPrice*Qty > 150
AND OrderTbl.OrdNo = OrdLine.OrdNo
AND OrdLine.ProdNo = Product.ProdNo
AND OrderTbl.CustNo = Customer.CustNo
AND Employee.EmpNo = OrderTbl.EmpNo;

23. List the order number and the total amount of the order for orders on January 23, 2004. The total amount
of an order is the sum of the quantity times the product price for each product on the order.

select ordno, sum(qty*prodprice) as totordamt

from ordertbl natural join ordline natural join product
where orddate = '23-jan-04'
group by ordno

24. List the order number, order date, customer name (first and last), and total amount of the order for
orders on January 23, 2004. The total amount of an order is the sum of the quantity times the product
price for each product on the order.

select ordno, orddate, custfirstname, custlastname, sum(qty*prodprice)

from ordertbl natural join ordline natural join customer natural join product
where orddate = '23-jan-04'
group by ordertbl.orddate, ordertbl.ordno, customer.custfirstname, customer.custlastname
 25. List the customer number, customer name (first and last), the sum of the quantity of products ordered,
and the total amount of products ordered in February 2004. Only include products in which the product
name contains the string 'Ink Jet' or 'Laser'. Include only customers who have ordered more than two 'Ink
Jet' or 'Laser' products in February 2004.

select customer.custno, custfirstname, custlastname, sum(qty) as prodqty, sum(qty*product.prodprice) as

totordamt
from customer natural join ordertbl natural join product natural join ordline
where orddate between '01-feb-04' and '28-feb-04'
and prodname like '%Ink Jet%' or prodname like '%Laser%'
group by customer.custno, customer.custfirstname, customer.custlastname
having sum(qty) > 2
26. List the order number, order date, customer number, customer name (first and last), customer state, and
shipping state (OrdState) in which the customer state differs from the shipping state.

select ordno, orddate, customer.custno, custfirstname, custlastname, custstate, ordstate

from ordertbl natural join customer
where ordstate <> custstate
group by ordertbl.ordno, ordertbl.orddate, customer.custno, customer.custfirstname, customer.custlastname,
customer.custstate, ordertbl.ordstate

27. List the employee number, employee name (first and last), and total amount of commissions on orders
taken in January 2004. The amount of a commission is the sum of the dollar amount of products ordered
times the commission rate of the employee.

select employee.empno, empfirstname, emplastname, sum(qty*prodprice*empcommrate)

from employee natural join ordertbl natural join product natural join ordline
where orddate between '01-jan-04' and '31-jan-04'
group by employee.empno, employee.empfirstname, employee.emplastname

28. Using the join operator style, list the product name and the price of all products ordered by Beth Taylor in
January 2004. Remove duplicate rows from the result.

select distinct prodname, prodprice

from ordline natural join product natural join ordertbl natural join customer
where orddate between '01-jan-04' and '31-jan-04'
and custfirstname='Beth' and custlastname='Taylor'
group by product.prodname, product.prodprice

29. For Colorado customers, compute the number of orders placed in January 2004. The result should include
the customer number, the last name, and the number of orders placed in January 2004.

select customer.custno, custlastname, count(ordno) as numorders

from customer natural join ordertbl
where orddate between '01-jan-04' and '31-jan-04'
and custstate='CO'
group by customer.custno, customer.custlastname

30. For Colorado customers, compute the number of orders placed in January 2004 in which the orders
contain products made by 'Connex'.

select customer.custno, custlastname, count(ordno) as numorders

from customer natural join ordertbl natural join product natural join ordline
where orddate between '01-jan-04' and '31-jan-04'
and custstate='CO'
and prodmfg='Connex'
group by customer.custno, customer.custlastname

31. For each employee with a commission rate of less than 0.04, compute the number of orders taken in
January 2004. The result should include the employee number, the employee last name, and the number
of orders taken.

select employee.empno, emplastname, count(ordno) as numorders

from employee natural join ordertbl
where orddate between '01-jan-04' and '31-jan-04'
and empcommrate < 0.04
group by employee.empno, employee.emplastname

32. For each employee with a commission rate of greater than 0.03, compute the total commission earned
from orders taken in January 2004. The total commission earned is the total order amount times the
commission rate. The result should include the employee number, the employee last name, and the total
commission earned.

select employee.empno, emplastname, sum(qty*prodprice*empcommrate) as totcommearned

from employee natural join ordertbl natural join ordline natural join product
where orddate between '01-jan-04' and '31-jan-04'
and empcommrate > 0.03
group by employee.empno, employee.emplastname

33. List the order number, the order date, the customer address and the shipping address of orders in which
any part of the shipping address (street, city, state, and zip) differs from the customers address.

select ordno, orddate, custstreet, custcity, custstate, custzip, ordstreet, ordcity, ordstate, ordzip
from ordertbl natural join customer
where ordstreet <> custstreet or ordcity <> custcity or custstate <> ordstate or ordzip <> custzip

34. For Colorado customers, compute the number of unique products ordered. If a product is purchased on
multiple orders, it should be counted only one time. The result should include the customer number, the
customer last name, and the number of unique products ordered.

select distinct custno, custlastname, count(prodno) as numproducts

from product natural join customer natural join ordertbl natural join ordline
where custstate ='CO'
group by customer.custno, customer.custlastname

35. For each 'Connex' product, compute the number of unique customers who ordered the product in January
2004. The result should include the product number, the product name, and the number of unique
customers.

select prodno, prodname, count(custno)

from product natural join customer natural join ordertbl natural join ordline
where orddate between '01-jan-04' and '31-jan-04'
and prodmfg ='Connex'
group by product.prodno, product.prodname
 36. Using a Type I nested query, list the customer number, the name (first and last), and the city of each
customer who has a balance greater than $150 and placed an order in February 2004.

select custno, custfirstname, custlastname, custcity

from customer
where custbal >150
and custno in (select custno
from ordertbl
where orddate between '01-feb-04' and '28-feb-04')

37. 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, 2004.

select prodno, prodname, prodprice

from product
where prodprice > 150
and prodno in (select prodno
from ordertbl
where orddate = '23-jan-04')

38. Using two Type I nested queries and another join style, list the product number, the name, and the price
of products with a price greater than $150 that were ordered in January 2004 by customers with balances
greater than $400.

SELECT ProdNo, ProdName, ProdPrice

FROM Product
WHERE ProdPrice > 150
AND ProdNo IN (SELECT ProdNo
FROM OrdLine
WHERE OrdNo IN (SELECT OrderTbl.OrdNo
FROM OrderTbl, Customer
WHERE OrdDate BETWEEN '01-jan-04' AND '31-jan-04'
AND OrderTbl.CustNo = Customer.CustNo
AND CustBal > 400))

39. Using a Type I nested query, list the customer number and the customer name of Colorado customers
who have not placed orders in February 2004.

SELECT Customer.CustNo, CustFirstName, CustLastName

FROM Customer
WHERE CustState = 'CO'
AND NOT EXISTS (SELECT *
FROM OrderTbl
WHERE OrderTbl.CustNo = Customer.CustNo
AND OrdDate BETWEEN '01-feb-04' and '28-jan-04')

40. List the product number and the name of products contained on every order placed on January 7, 2004,
through January 9, 2004.

select prodno, prodname

from product natural join ordertbl natural join ordline
where orddate >= '07-jan-04' and orddate<='09-jan-04'
 41. List the customer number and the name (first and last) of customers who have ordered every product
manufactured by 'ColorMeg,Inc.' in January 2004.

SELECT Customer.CustNo, CustFirstName, CustLastName

FROM Customer, OrderTbl, OrdLine, Product
WHERE ProdMfg = 'ColorMeg,Inc.'
AND OrdDate BETWEEN '1-Jan-2004' AND '31-Jan-2004'
AND Customer.CustNo = OrderTbl.CustNo
AND OrderTbl.OrdNo = OrdLine.OrdNo
AND Product.ProdNo = OrdLine.ProdNo
GROUP BY Customer.CustNo, CustFirstName, CustLastName
HAVING COUNT(DISTINCT Product.ProdNo) = ( SELECT COUNT(*)
FROM Product WHERE ProdMfg = 'ColorMeg,Inc.' );

42. List the employee number and the employee name (first and last) of employees who have taken orders in
January 2004 from every Denver customer.

select employee.empno, empfirstname, emplastname

from employee natural join ordertbl natural join customer
where orddate between '01-jan-04' and '31-jan-04'
and custcity='Denver'
group by employee.empno, employee.empfirstname, employee.emplastname

43. For Colorado customers, compute the average amount of their orders.

select custno, custlastname, sum(qty*prodprice) as avgordamt

from customer natural join ordertbl natural join product natural join ordline
where custstate='CO'
group by customer.custno, customer.custlastname

44. For Colorado customers, compute the average amount of their orders and the number of orders placed.
The result should include the customer number, the customer last name, the average order amount, and
the number of orders placed.

SELECT CustNo, CustLastName, AVG(OrdAmt) AS AvgOrdAmt, COUNT(DISTINCT OrdNo) AS NumOrders FROM

( SELECT Customer.CustNo, CustLastName, OrderTbl.OrdNo, SUM(Qty*ProdPrice) AS OrdAmt FROM OrderTbl,
Customer, OrdLine, Product
WHERE OrderTbl.CustNo = Customer.CustNo
AND OrderTbl.OrdNo = OrdLine.OrdNo
AND OrdLine.ProdNo = Product.ProdNo
AND CustState = 'CO'
GROUP BY Customer.CustNo, CustLastName, OrderTbl.OrdNo ) as temp_table
GROUP BY CustNo, CustLastName

45. List the employee number, employee name (first and last), commission rate, supervising employee name
(first and last), and commission rate of the supervisor.

select e.empno, e.empfirstname, e.emplastname, e.empcommrate, s.empfirstname, s.emplastname,

s.empcommrate
from employee e, employee s
where e.supempno = s.empno
 46. List the employee number and name (first and last) of second-level subordinates (subordinates of
subordinates) of the employee named Thomas Johnson.
SELECT Subr2.EmpNo, Subr2.EmpFirstName, Subr2.EmpLastName
FROM Employee E, Employee Subr1, Employee Subr2
WHERE Subr1.EmpNo = Subr2.SupEmpNo
AND Subr1.SupEmpNo = E.EmpNo
AND E.EmpFirstName = 'Thomas'
AND E.EmpLastName = 'Johnson';

47. Using a Type II nested query, list the customer number, the name (first and last), and the city of each
customer who has a balance greater than $150 and placed an order in February 2004.

SELECT CustNo, CustFirstName, CustLastName, CustCity

FROM Customer
WHERE CustBal > 150
AND EXISTS (SELECT CustNo
FROM OrderTbl
WHERE OrdDate BETWEEN '01-feb-04' and '28-feb-04'
AND Customer.CustNo = OrderTbl.CustNo);

48. Using a Type II nested query, list the customer number and the customer name of Colorado customers
who have not placed orders in February 2004.

SELECT Customer.CustNo, CustFirstName, CustLastName

FROM Customer
WHERE CustState = 'CO'
AND NOT EXISTS (SELECT *
FROM OrderTbl
WHERE OrderTbl.CustNo = Customer.CustNo
AND OrdDate BETWEEN '01-feb-04' and '28-feb-04' )

49. List the order number and the order date of orders containing only one 'Ink Jet' product. An ink jet
product contains the words 'Ink Jet' in the product description.

SELECT OrderTbl.OrdNo, Orddate

FROM OrderTbl, OrdLine, Product
WHERE OrderTbl.OrdNo = OrdLine.OrdNo
AND OrdLine.ProdNo = Product.ProdNo
AND ProdName LIKE '%Ink Jet%'
AND NOT EXISTS ( SELECT *
FROM OrdLine OL1, Product P1
WHERE OL1.ProdNo = P1.ProdNo
AND ProdName LIKE '%Ink Jet%'
AND OL1.OrdNo = OrderTbl.OrdNo
AND OL1.ProdNo <> Product.ProdNo )

50. List the order number and the order date of orders containing every 'Ink Jet' product.

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%' )