Question 8) (Points: 1)

Write a Query(ies) to update the new field (from Question 7) to DLR where the Channel is Dealer, or NR where the Channel is National Retail.

Note: You may do this in 2 queries, but it can be done in a single query.

Answer:

USE SQL

7. Syntax: ALTER TABLE table_name

ADD column_name datatype;

Answer:

ALTER TABLE Dealers

ADD Channel_Mod varchar(3); // you can use varchar(3) or char(3)

Table Name: Dealers

Dealer_ID	Dealer_Name	Channel	Dealer_Type
0028013	Target Corporation	National Retail	Premiere
0035445	MGM Sales, Inc.	Dealer	Premiere
0041514	Walmart	National Retail	Premiere
0065161	One to One, Inc.	Dealer	Exclusive
0076671	Alert Cellular, L.C.	Dealer	Exclusive
0094042	Midtown Cellular, Inc.	Dealer	Premiere

Table Name: Activations

Act_Transaction_ID	Subscriber_ID	Dealer_ID	Act_Date	Product_Type	Service_Number	Monthly_Charge
4502	9897399	0035445	10/31/2007	PostPaid	323-989-7399	39.99
6928	3189556	0035445	10/1/2007	PostPaid	562-318-9556	49.99
289526	2598547	0035445	10/27/2007	PostPaid	510-259-8547	35
20748	8490151	0065161	10/2/2007	PostPaid	630-849-0151	50
28259	8881403	0065161	10/3/2007	PostPaid	630-888-1403	35
326054	7901025	0065161	10/27/2007	PostPaid	832-790-1025	35
1136841	3491687	0065161	10/27/2007	PrePaid	773-349-1687	0
1136904	5279733	0065161	10/27/2007	PrePaid	708-527-9733	0
1182394	8760643	0076671	10/27/2007	PrePaid	206-876-0643	0
181	3346255	0076671	10/31/2007	PostPaid	323-334-6255	35
388	3346246	0076671	10/31/2007	PostPaid	323-334-6246	35
241963	3818887	0076671	10/28/2007	PostPaid	323-381-8887	40
242038	8610284	0076671	10/28/2007	PostPaid	323-861-0284	40
219029	4992981	0094042	10/25/2007	PostPaid	908-499-2981	49.99
219303	2773143	0094042	10/25/2007	PostPaid	732-277-3143	39.99
298199	2789544	0028013	10/27/2007	PostPaid	720-278-9544	69.99
161670	7499768	0028013	10/19/2007	PostPaid	347-749-9768	49.99
118348	2534201	0028013	10/13/2007	PostPaid	774-253-4201	39.99
264144	2868457	0028013	10/30/2007	PostPaid	401-286-8457	39.99
177286	5186125	0028013	10/20/2007	PostPaid	860-518-6125	49.99
1092160	6409398	0028013	10/18/2007	PrePaid	724-640-9398	0
1006257	6913136	0028013	10/2/2007	PrePaid	773-691-3136	0
971034	3759382	0041514	10/4/2007	PrePaid	559-375-9382	0
937982	2541562	0041514	10/3/2007	PrePaid	909-254-1562	0
880490	5502694	0041514	10/16/2007	PrePaid	901-550-2694	0
880491	5503420	0041514	10/16/2007	PrePaid	901-550-3420	0
69560	6671034	0041514	10/8/2007	PostPaid	303-667-1034	49.99
877600	4280349	0035445	10/12/2007	PrePaid	818-428-0349	0
1179921	3578600	0035445	10/27/2007	PrePaid	818-357-8600	0
1178707	6356622	0035445	10/27/2007	PrePaid	702-635-6622	0

Table Name: Commissions

Commission_ID	Act_Transaction_ID	Commission_Type	Amount
1	181	Commission	255
2	388	Commission	255
3	4502	Bonus	50
4	4502	Commission	195
5	4502	Coop	15
6	6928	Bonus	50
7	6928	Commission	215
8	6928	Coop	15
9	20748	Bonus	50
10	20748	Commission	225
11	20748	Coop	25
12	28259	Bonus	50
13	28259	Commission	170
14	28259	Coop	25
15	69560	Commission	275
16	118348	Commission	265
17	161670	Commission	265
18	177286	Commission	265
19	219029	Bonus	50
20	219029	Commission	230
21	219029	Coop	25
22	219303	Bonus	50
23	219303	Commission	210
24	219303	Coop	25
25	241963	Commission	290
26	242038	Commission	290
27	264144	Commission	265
28	289526	Commission	160
29	289526	Coop	15
30	326054	Bonus	50
31	326054	Commission	170
32	326054	Coop	25
33	877600	Spiff	30
34	880490	Spiff	30
35	880491	Spiff	30
36	937982	Spiff	30
37	971034	Spiff	30
38	1006257	Spiff	30
39	1092160	Spiff	30
40	1136841	Spiff	30
41	1136904	Spiff	30
42	1178707	Spiff	30
43	1179921	Spiff	30
44	1182394	Spiff	30