Assignment: Database Management Systems
SQL - Structured Query Language - Single table queries
Using the following table, create the SQL commands below that will produce the required results.
|
Item
|
|
|
ItemNum
|
ItemName
|
OnHand
|
Category
|
Storehouse
|
Price
|
|
AH74
|
Patience
|
9
|
GME
|
3
|
$22.99
|
|
BR23
|
Skittles
|
21
|
GME
|
2
|
$29.99
|
|
CD33
|
Wood Block Set (48 piece)
|
36
|
TOY
|
1
|
$89.49
|
|
DL51
|
Classic Railway Set
|
12
|
TOY
|
3
|
$107.95
|
|
DR67
|
Giant Star Brain Teaser
|
24
|
PZL
|
2
|
$31.95
|
|
DW23
|
Mancala
|
40
|
GME
|
3
|
$50.00
|
|
FD11
|
Rocking Horse
|
8
|
TOY
|
3
|
$124.95
|
|
FH24
|
Puzzle Gift Set
|
65
|
PZL
|
1
|
$38.95
|
|
KA12
|
Cribbage Set
|
56
|
GME
|
3
|
$75.00
|
|
KD34
|
Pentominoes Brain Teaser
|
60
|
PZL
|
2
|
$14.95
|
|
KL78
|
Pick Up Sticks
|
110
|
GME
|
1
|
$10.95
|
A. List the item number, name and price for all products in the category of GME, PZL, or TOY.
B. List the item number, name, price and a new field named NewPrice that will add $5 to the current price.
C. Give the item number, name, and value (OnHand*Price) for each item in category GME.
D. Show the complete Item table. Sort the results by category and within category by item number.
E. List the item number, name, and price for all toys that are over $100. Rename the ItemName field to be named ProductName. Sort in descending order by Price.
F. How many products are in the Item table?
G. What is the total, average, highest and lowest price of all products?
H. What is the total, average, highest and lowest price of all products by category? List the category name first. Sort by category in ascending order.