-
Notifications
You must be signed in to change notification settings - Fork 0
/
A9B.sql
80 lines (68 loc) · 1.59 KB
/
A9B.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
------------------------
-- 1. Creates Table ---
------------------------
CREATE TABLE NONGAME
(
ITEM_NUM CHAR(4),
DESCRIPTION CHAR(30),
ON_HAND decimal(4,0),
CATEGORY CHAR(3),
PRICE DECIMAL(6,2)
);
-- Makes PK
ALTER TABLE NONGAME
ADD PRIMARY KEY (ITEM_NUM)
------------------------
-- 2. Adds Specific Rows
------------------------
INSERT INTO NONGAME
SELECT ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, PRICE
FROM ITEM
WHERE ITEM.CATEGORY <> 'GME'
------------------------
-- 3. Update Table -----
------------------------
UPDATE NONGAME
SET DESCRIPTION = 'Classic Train Set'
WHERE ITEM_NUM = 'DL51'
------------------------
-- 4. Update Table -----
------------------------
UPDATE NONGAME
SET PRICE = PRICE * 1.02
WHERE CATEGORY = 'TOY'
------------------------
-- 5. Insert Into Table
------------------------
INSERT INTO NONGAME
VALUES ('TL92', 'Dump Truck', 10, 'TOY', 59.95)
------------------------
-- 6. Delete Table Entry
------------------------
DELETE FROM NONGAME
WHERE CATEGORY = 'PZL'
------------------------
-- 7. Update Table
------------------------
UPDATE NONGAME
SET CATEGORY = NULL
WHERE ITEM_NUM = 'FD11'
------------------------
-- 8. Alter Table
------------------------
ALTER TABLE NONGAME
ADD ON_HAND_VALUE DECIMAL(7,2)
UPDATE NONGAME
SET ON_HAND_VALUE = ON_HAND * PRICE
------------------------
-- 9. Alter Table
------------------------
ALTER TABLE NONGAME
ALTER COLUMN DESCRIPTION CHAR(40)
------------------------
-- 10. Delete Table
------------------------
BEGIN TRAN
DROP TABLE NONGAME
--Rollback
--COMMIT