PDF Archive

Easily share your PDF documents with your contacts, on the Web and Social Networks.

ch4TAL QA .pdf

Original filename: ch4TAL_QA.pdf
Title: Microsoft Word - Answers to TAL Distributors Exercises SQL Chapter 4(1)
Author: Eric

This PDF 1.7 document has been generated by / Microsoft: Print To PDF, and has been sent on pdf-archive.com on 04/10/2016 at 18:37, from IP address 129.81.x.x. The current document download page has been viewed 5478 times.
File size: 752 KB (7 pages).
Privacy: public file

Document preview

1. List item number, description, and price for all items
SELECT ITEMNUM, DESCRIPTION, PRICE
FROM ITEM;

2. List all rows and columns for the complete ORDERS table
SELECT *
FROM ORDERS;

3.List names of customers with credit limits of 10k or more
SELECT CUSTOMERNAME
FROM CUSTOMER
WHERE CREDITLIMIT &gt;= 10000;

4.List order number for each order placed by customer number 126 on 10/15/2015

SELECT ORDERNUM
FROM ORDERS
WHERE CUSTOMERNUM = '126'
AND ORDERDATE = #10/15/2015#;
5.List the number and name of each customer represented by sales rep 30 or 45
SELECT CUSTOMERNUM, CUSTOMERNAME
FROM CUSTOMER
WHERE REPNUM = '30'
OR REPNUM = '45';

6.List the item number and description of each item that is not in category PZL
SELECT ITEMNUM, DESCRIPTION
FROM ITEM
WHERE NOT (CATEGORY = 'PZL');
or
SELECT ITEMNUM, DESCRIPTION
FROM ITEM
WHERE CATEGORY &lt;&gt; 'PZL';

7.List the item number, description, and number of units on hand for each item that has between 20 and 40 units on
hand including 20 and 40
SELECT ITEMNUM, DESCRIPTION, ONHAND
FROM ITEM
WHERE ONHAND &gt;= 20
AND ONHAND &lt;= 40;
or
SELECT ITEMNUM, DESCRIPTION, ONHAND
FROM ITEM
WHERE ONHAND BETWEEN 20 AND 40;

8.List the item number, descrciption, and on hand value of each item in TOY and assign ON_HAND_VALUE to
computed column
SELECT ITEMNUM, DESCRIPTION, ONHAND * PRICE AS ONHAND_VALUE
FROM ITEM
WHERE CATEGORY = 'TOY';

9.List the item number, description, and on hand value for each item whose on hand value is at least \$1500
SELECT ITEMNUM, DESCRIPTION, ONHAND * PRICE AS ON_HAND_VALUE
FROM ITEM
WHERE ONHAND * PRICE &gt;= 1500;

10.Use the In operator to list the item number and description of each item in category GME or PZL
SELECT ITEMNUM, DESCRIPTION
FROM ITEM
WHERE CATEGORY IN ('GME', 'PZL');

11.Find the number and name of each customer whose name begins with the letter c
In Access:
SELECT CUSTOMERNUM, CUSTOMERNAME
FROM CUSTOMER
WHERE CUSTOMERNAME LIKE ‘C*’;

12. List all the details about all items order by description
SELECT *
FROM ITEM
ORDER BY DESCRIPTION;

13. List all the details about all items order by List all the details about all items order by storehouse then item number
SELECT *
FROM ITEM
ORDER BY STOREHOUSE, ITEMNUM;

14. How many customers have balances that are more than their credit limits
SELECT COUNT(*)
FROM CUSTOMER
WHERE BALANCE &gt; CREDITLIMIT;

15.Find the total number of b laances for all customers represented by sales rep 15 with balances less than credit limits
SELECT SUM(BALANCE)
FROM CUSTOMER
WHERE REPNUM = '15'
AND BALANCE &lt; CREDITLIMIT;

16.List the number, description, and on hand value each item whose number of units on hand is more than the average
number of units on hand for all items
SELECT ITEMNUM, DESCRIPTION, (PRICE*ONHAND) AS On_Hand_Value
FROM ITEM
WHERE ONHAND &gt;
(SELECT AVG(ONHAND)
FROM ITEM);

17. What is the price of the least expensive item in the database
SELECT MIN(PRICE)
FROM ITEM;

18. What is the item number, description, and price of the least expensive item in the database
SELECT ITEMNUM, DESCRIPTION, PRICE
FROM ITEM
WHERE PRICE =
(SELECT MIN(PRICE)
FROM ITEM);

19.List the sum of the balances of all customers for each sales rep. Order and group the results by sales rep number.
SELECT REPNUM, SUM(BALANCE)
FROM CUSTOMER
GROUP BY REPNUM
ORDER BY REPNUM;

20.List the sum of the balances of all customers for each sales rep, but restrict the output to those sales reps for which
the sum is more than 5k. Order the results by sales rep number
SELECT REPNUM, SUM(BALANCE)
FROM CUSTOMER
GROUP BY REPNUM
HAVING SUM(BALANCE) &gt; 5000
ORDER BY REPNUM;

23. [Critical Thinking]TAL distributors is considering discounting the price of all items by 10 percent. List the item
number description, and discounted price for all items. Use DISCOUNTED_PRICE as the name for computed column.
SELECT ITEMNUM, DESCRIPTION, .9*PRICE AS DISCOUNTED_PRICE
FROM ITEM;