8G7PUEYk

1PROMPT **** 102106751 Roland
2PROMPT **** 101776784 Tymeng
3PROMPT **** 102111726 Gavin
4PROMPT **** ASSIGNMENT 1 SCRIPT // GROUP 3
5
6PROMPT **----------------------------------------------------------------------------
7PROMPT **DROP TABLES (expect errors if no table exists)
8PROMPT **----------------------------------------------------------------------------
9
10DROP TABLE work_session;
11DROP TABLE ALLOCATION;
12DROP TABLE BOOK;
13DROP TABLE AUTHOR;
14
15PROMPT **----------------------------------------------------------------------------
16PROMPT **PART 1B CREATE TABLES
17PROMPT **----------------------------------------------------------------------------
18
19PROMPT ** creating the book table
20CREATE TABLE book (
21	bid NUMBER(4),
22	title VARCHAR2(30) NOT NULL,
23	sellingprice NUMBER(6,2),
24	CHECK (sellingprice>0),
25PRIMARY KEY(bid)
26);
27
28PROMPT ** creating the author table
29CREATE TABLE author (
30	authorid NUMBER(4),
31	fname VARCHAR2(30),
32	sname VARCHAR2(30),
33UNIQUE(fname,sname),
34PRIMARY KEY(authorid)
35);
36
37PROMPT ** creating the allocation table
38CREATE TABLE allocation (
39	bid NUMBER(4),
40	authorid NUMBER(4),
41	payrate NUMBER(6,2),
42	CHECK (payrate>=1 AND payrate<=80),
43PRIMARY KEY(authorid,bid),
44FOREIGN KEY(authorid) REFERENCES author,
45FOREIGN KEY(bid) REFERENCES book
46);
47
48PROMPT **----------------------------------------------------------------------------
49PROMPT **TASK 1C
50PROMPT **----------------------------------------------------------------------------
51
52PROMPT ** inserting data into the author table
53INSERT INTO author VALUES(40,'Ziggle','Carl');
54INSERT INTO author VALUES(42,'Taylor','Tayla');
55INSERT INTO author VALUES(44,'Merdovic','Damir');
56INSERT INTO author VALUES(45,'Grossman','Paul');
57INSERT INTO author VALUES(47,'Ziggle','Annie');
58INSERT INTO author VALUES(48,'Zhao','Cheng');
59INSERT INTO author VALUES(50,'Phan','Annie');
60
61PROMPT ** inserting data into the book table
62INSERT INTO book VALUES(101,'Knitting with Dog Hair',6.99);
63INSERT INTO book VALUES(105,'Avoiding Large Ships',11);
64INSERT INTO book VALUES(107,'Dealing with stuff',6.5);
65INSERT INTO book VALUES(108,'Teach fish to sing',10.99);
66INSERT INTO book VALUES(109,'Guide to hands free texting',10.5);
67INSERT INTO book VALUES(113,'You call that a lecture?',17.5);
68
69PROMPT ** inserting data into the allocation table
70INSERT INTO allocation VALUES(101,42,25);
71INSERT INTO allocation VALUES(101,45,32);
72INSERT INTO allocation VALUES(108,47,35);
73INSERT INTO allocation VALUES(113,48,40);
74INSERT INTO allocation VALUES(109,47,42);
75INSERT INTO allocation VALUES(105,42,26);
76INSERT INTO allocation VALUES(105,47,25);
77INSERT INTO allocation VALUES(105,40,19);
78INSERT INTO allocation VALUES(107,42,35);
79INSERT INTO allocation VALUES(108,40,45);
80
81PROMPT **----------------------------------------------------------------------------
82PROMPT **DISPLAY INFORMATION
83PROMPT **----------------------------------------------------------------------------
84
85SELECT * FROM author;
86SELECT * FROM book;
87SELECT * FROM allocation;
88
89PROMPT **----------------------------------------------------------------------------
90PROMPT **PART 1D DEMONSTRATION OF CONSTRAINTS BEING VIOLATED
91PROMPT **----------------------------------------------------------------------------
92PROMPT ** 1D Statement 1
93INSERT INTO author VALUES(51,'Phan','Annie');
94
95PROMPT ** 1D Statement 2
96INSERT INTO book VALUES(101,'',3.50);
97
98PROMPT ** 1D Statement 3
99INSERT INTO book VALUES(101,'stress as a result of work',-3.50);
100
101PROMPT ** 1D Statement 4
102INSERT INTO allocation VALUES(108,40,81);
103
104PROMPT **----------------------------------------------------------------------------
105PROMPT **PART 1E SQL QUERIES
106PROMPT **----------------------------------------------------------------------------
107PROMPT ** 1E Query 1
108SELECT * FROM allocation;
109
110PROMPT ** 1E Query 2
111SELECT http://B.bid, B.title, A.authorid, A.payrate
112FROM book B
113INNER JOIN allocation A
114ON B.bid=A.bid;
115
116PROMPT ** 1E Query 3
117SELECT http://B.bid, B.title, B.sellingprice, AU.authorid, AU.sname, AL.payrate
118FROM 
119allocation AL
120INNER JOIN author AU
121ON AL.authorid=AU.authorid
122INNER JOIN book B
123ON AL.bid=B.bid;
124
125PROMPT ** 1E Query 4
126SELECT AVG(sellingprice)
127FROM book;
128
129PROMPT ** Query 5
130SELECT bid, title, sellingprice
131FROM book
132WHERE sellingprice<(SELECT AVG(sellingprice) FROM book);
133
134PROMPT ** 1E Query 6
135SELECT bid,count(*)
136FROM allocation
137GROUP BY bid
138ORDER BY 2;
139
140PROMPT ** 1E Query 7
141SELECT http://B.bid, B.title, COUNT(http://AL.bid)
142FROM book B
143INNER JOIN allocation AL
144ON B.bid=AL.bid
145GROUP BY B.bid,B.title;
146
147PROMPT ** 1E Query 8
148SELECT http://B.bid, B.title, COUNT(http://AL.bid)
149FROM book B
150INNER JOIN allocation AL
151ON B.bid=AL.bid
152GROUP BY B.bid,B.title
153HAVING COUNT(http://AL.bid)>1;
154
155PROMPT ** 1E Query 9
156SELECT A.authorid, A.fname, A.sname, http://AL.bid
157FROM allocation AL
158INNER JOIN author A
159ON AL.authorid=A.authorid;
160
161PROMPT ** 1E Query 10
162SELECT AU.authorid, AU.sname, AU.fname, http://AL.bid
163FROM author AU
164LEFT OUTER JOIN allocation AL
165ON AU.authorid = AL.authorid
166ORDER BY AU.authorid, AL.bid;
167
168PROMPT ** 1E Query 11
169SELECT AU.authorid, AU.sname, AU.fname, http://AL.bid, B.title
170FROM author AU
171LEFT OUTER JOIN allocation AL
172ON AU.authorid = AL.authorid
173LEFT OUTER JOIN book B
174ON B.bid=AL.bid
175ORDER BY AU.authorid,B.bid;
176
177
178PROMPT **----------------------------------------------------------------------------
179PROMPT **TASK 2
180PROMPT **----------------------------------------------------------------------------
181
182
183PROMPT **----------------------------------------------------------------------------
184PROMPT **TASK 2 A Create work_session Table
185PROMPT **----------------------------------------------------------------------------
186
187DROP TABLE work_session;
188CREATE TABLE work_session (
189bid NUMBER(4),
190authorid NUMBER(4),
191workyear NUMBER(4),
192workweek NUMBER(2),
193workhours NUMBER(4,2),
194
195CONSTRAINT CHK_workyear CHECK (
196workyear>=2011 AND workyear<=2013),
197
198CONSTRAINT CHK_workweek CHECK (
199workweek>=1 AND workweek<=52),
200
201CONSTRAINT CHK_workhours CHECK (
202workhours>=0.5 AND workhours<=99.99),
203
204PRIMARY KEY(workyear, workweek, bid, authorid),
205FOREIGN KEY(authorid,bid) REFERENCES allocation
206);
207
208
209PROMPT **----------------------------------------------------------------------------
210PROMPT **TASK 2 B enter data
211PROMPT **----------------------------------------------------------------------------
212
213INSERT INTO work_session VALUES(101,42,2012,5,5);
214INSERT INTO work_session VALUES(101,42,2012,6,4);
215INSERT INTO work_session VALUES(101,42,2012,7,5);
216INSERT INTO work_session VALUES(101,45,2012,5,10);
217INSERT INTO work_session VALUES(101,45,2012,7,10);
218INSERT INTO work_session VALUES(105,42,2012,5,6);
219INSERT INTO work_session VALUES(105,47,2012,4,8);
220INSERT INTO work_session VALUES(105,47,2012,6,7);
221INSERT INTO work_session VALUES(105,47,2012,8,8);
222INSERT INTO work_session VALUES(108,40,2011,52,4);
223INSERT INTO work_session VALUES(108,40,2012,4,15);
224INSERT INTO work_session VALUES(108,40,2012,6,6);
225INSERT INTO work_session VALUES(108,47,2012,8,4);
226INSERT INTO work_session VALUES(109,47,2012,5,5);
227INSERT INTO work_session VALUES(109,47,2012,6,5);
228
229PROMPT **----------------------------------------------------------------------------
230PROMPT **TASK 2 C constraint testing
231PROMPT **----------------------------------------------------------------------------
232
233PROMPT **TASK 2 C
234PROMPT **bid/authid combination does NOT exist in parent 
235INSERT INTO work_session VALUES(101,48,2012,1,1);
236	
237PROMPT **bid/authid combination does NOT exist in parent 
238INSERT INTO work_session VALUES(109,42,2012,2,2);
239
240PROMPT **out of range workyear 
241INSERT INTO work_session VALUES(101,42,2014,9,6);
242	
243PROMPT **out of range workweek
244INSERT INTO work_session VALUES(101,45,2012,55,3);
245
246PROMPT **out of range workhours		
247INSERT INTO work_session VALUES(108,40,2012,7,120);	
248
249
250PROMPT **----------------------------------------------------------------------------
251PROMPT **TASK 2D Queries (Q5 incomplete)
252PROMPT **----------------------------------------------------------------------------
253PROMPT ** 2D Query 1
254SELECT authorid,workyear,workweek,workhours
255FROM work_session
256ORDER BY 1,2,3;	
257
258PROMPT ** 2D Query 2
259SELECT authorid,workyear,sum(workhours)
260FROM work_session
261GROUP BY authorid,workyear
262ORDER BY 2 DESC;
263
264PROMPT ** 2D Query 3 ???
265SELECT bid,authorid,workyear,workweek,workhours
266FROM work_session
267ORDER BY 1,2,3,4,5;
268
269PROMPT ** 2D Query 4
270SELECT bid,authorid,workyear,sum(workhours) AS "Total Hours"
271FROM work_session
272GROUP BY bid,authorid,workyear
273ORDER BY 1 ASC;
274
275PROMPT ** 2D Query 5
276SELECT W.bid,W.authorid,W.workyear,SUM(W.workhours),SUM(W.workhours*A.payrate) As "Total pay"
277FROM work_session W
278INNER JOIN allocation A
279ON W.bid=A.bid AND w.authorid=A.authorid
280GROUP BY W.bid,W.authorid,W.workyear
281ORDER BY W.bid,W.authorid,W.workyear;
282
283PROMPT **----------------------------------------------------------------------------
284PROMPT **TASK 3 A drop all the tables
285PROMPT **----------------------------------------------------------------------------
286DROP TABLE work_session;
287DROP TABLE allocation;
288DROP TABLE book;
289DROP TABLE author;		
290
291PROMPT ** finished script...