-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathoutput_spool.txt
231 lines (199 loc) · 36 KB
/
output_spool.txt
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
SQL> REM Griffin Cosgrove
SQL> Set Echo On
SQL> Set Linesize 270
SQL> Set wrap off
SQL> REM SUBQUERY #1
SQL> REM Display the first name, last name, and fraternity name of every brother that held a position and paid dues for the fall 2018 semester.
SQL> select b_first_name, b_last_name, frat_name
2 from brother
3 where b_id in(select b_id from position
4 where b_id in(select b_id from dues_paid
5 where upper(dues_paid_semester) = 'FALL' AND dues_paid_year = 2018));
B_FIRST_NAME B_LAST_NAME FRAT_NAME
------------------------- ------------------------- -------------------------
Griffin Cosgrove Alpha Tau Omega
Jack Langford Delta Kappa Epsilon
Andrew Nappi Alpha Tau Omega
Robert Hoover Delta Tau Chi
SQL> REM SUBQUERY #2
SQL> REM Display Display the first name, last name, and fraternity name of every brother that was president and has paid dues.
SQL> select b_first_name, b_last_name, frat_name
2 from brother
3 where b_id in(select b_id from dues_paid
4 where b_id in(select b_id from position where UPPER(pos_title) = 'PRESIDENT'));
B_FIRST_NAME B_LAST_NAME FRAT_NAME
------------------------- ------------------------- -------------------------
Griffin Cosgrove Alpha Tau Omega
Jack Wakeham Kappa Sigma
Jack Langford Delta Kappa Epsilon
Robert Hoover Delta Tau Chi
SQL> REM CORRELATED SUBQUERY #3
SQL> REM Display the ID, gpa, major, and fraternity name of the brother with the highest gpa in each fraternity.
SQL> select b_id, b_last_name, b_gpa, b_major, frat_name from brother b1 where b_gpa =
2 (select MAX(b_gpa) from brother b2
3 where b1.frat_name = b2.frat_name);
B_ID B_LAST_NAME B_GPA B_MAJOR FRAT_NAME
---------- ------------------------- ---------- ------------------------- -------------------------
1 Cosgrove 3.98 CIS Alpha Tau Omega
6 Johnson 3.2 Chemistry Kappa Sigma
9 Bush 3.8 Political Science Delta Kappa Epsilon
10 Hoover 2.7 Political Science Delta Tau Chi
13 Anders 2.6 finance Sigma Alpha Kappa
17 Sevens 3.5 Undecided Gamma Delta Iota
14 Fred 3.2 MIS Xi Delta Alpha
7 rows selected.
SQL> REM INNER JOIN #4
SQL> REM Display the ID, position title, and year of brothers who held a position and paid dues.
SQL> select b.b_id, b.b_last_name, p.pos_title, dp.dues_paid_year as year
2 from brother b, position p, dues_paid dp
3 where b.b_id = p.b_id AND p.b_id = dp.b_id;
B_ID B_LAST_NAME POS_TITLE YEAR
---------- ------------------------- ------------------------- ----
1 Cosgrove President 2018
2 Kendzulak Grand Master 2018
3 Nappi worthy sentinel 2018
4 Wakeham president 2018
7 Langford president 2018
10 Hoover President 2018
6 rows selected.
SQL> REM INNER JOIN #5
SQL> REM Display the faculty ID for the faculty advisor, date founded, and the brother IDs for all brothers in Alpha Tau Omega.
SQL> select fac.fac_id, fac.fac_last_name, frat.frat_name, frat.frat_date_founded, b.b_id, b.b_last_name
2 from faculty_advisor fac, fraternity frat, brother b
3 where UPPER(b.frat_name) = 'ALPHA TAU OMEGA' AND fac.fac_id = frat.fac_id AND frat.frat_name = b.frat_name
4 order by b.b_id;
FAC_ID FAC_LAST_NAME FRAT_NAME FRAT_DATE_ B_ID B_LAST_NAME
---------- ------------------------- ------------------------- ---------- ---------- -------------------------
1 Smith Alpha Tau Omega 2008 1 Cosgrove
1 Smith Alpha Tau Omega 2008 2 Kendzulak
1 Smith Alpha Tau Omega 2008 3 Nappi
SQL> REM INNER JOIN #6
SQL> REM Display the faculty ID, fraternity name, and the house address for each house.
SQL> select fac.fac_id, fac.fac_last_name, frat.frat_name, h.house_address
2 from faculty_advisor fac, fraternity frat, house h
3 where fac.fac_id = frat.fac_id AND frat.frat_name = h.frat_name
4 order by fac.fac_id;
FAC_ID FAC_LAST_NAME FRAT_NAME HOUSE_ADDRESS
---------- ------------------------- ------------------------- -------------------------
1 Smith Alpha Tau Omega 164 Love and Respect Way
1 Smith Alpha Tau Omega 861 Village Lane
1 Smith Alpha Tau Omega 1165 Manor
4 Lindberg Kappa Sigma 660 Walnut Lane
4 Lindberg Kappa Sigma 543 greek row
5 Froning Delta Kappa Epsilon 540 greek row
7 Dosh Xi Delta Alpha 547 Layman Av.
8 Dingus Sigma Alpha Kappa 1160 Usman Circle
8 rows selected.
SQL> REM INNER JOIN(SELF-JOIN) #7
SQL> REM Display the faculty advisor lasst name, and the last name of their supervisor.
SQL> select f1.fac_id, f1.fac_last_name, f2.fac_id, f2.fac_last_name as supr_name
2 from faculty_advisor f1, faculty_advisor f2
3 where f1.fac_supervisor = f2.fac_id
4 ;
FAC_ID FAC_LAST_NAME FAC_ID SUPR_NAME
---------- ------------------------- ---------- -------------------------
1 Smith 3 Choo
2 Klokov 3 Choo
3 Choo 4 Lindberg
5 Froning 3 Choo
9 Diesel 6 Norris
SQL> REM THREE-WAY OUTER JOIN #8
SQL> REM Display the faculty ID for every faculty advisor even if they do not advise a fraternity, the name of the fraternity they advise, and the address of the fraternity's house.
SQL> select fac.fac_id, fac.fac_last_name, frat.frat_name, h.house_address
2 from faculty_advisor fac, fraternity frat, house h
3 where fac.fac_id = frat.fac_id(+) AND
4 frat.frat_name = h.frat_name(+)
5 order by fac.fac_id;
FAC_ID FAC_LAST_NAME FRAT_NAME HOUSE_ADDRESS
---------- ------------------------- ------------------------- -------------------------
1 Smith Alpha Tau Omega 164 Love and Respect Way
1 Smith Alpha Tau Omega 1165 Manor
1 Smith Alpha Tau Omega 861 Village Lane
2 Klokov
3 Choo
4 Lindberg Kappa Sigma 543 greek row
4 Lindberg Kappa Sigma 660 Walnut Lane
5 Froning Delta Kappa Epsilon 540 greek row
6 Norris Delta Tau Chi
7 Dosh Xi Delta Alpha 547 Layman Av.
8 Dingus Sigma Alpha Kappa 1160 Usman Circle
FAC_ID FAC_LAST_NAME FRAT_NAME HOUSE_ADDRESS
---------- ------------------------- ------------------------- -------------------------
9 Diesel Gamma Delta Iota
12 rows selected.
SQL> REM OUTER THREE-WAY JOIN #9
SQL> REM Display the brother ID for every brother even if they do not hold a position, the name of the position, and the year they paid dues.
SQL> select b.b_id, b.b_last_name, b.frat_name, p.pos_title, dp.dues_paid_year as year
2 from brother b, position p, dues_paid dp
3 where b.b_id = p.b_id(+) AND b.b_id = dp.b_id(+)
4 order by b.b_id;
B_ID B_LAST_NAME FRAT_NAME POS_TITLE YEAR
---------- ------------------------- ------------------------- ------------------------- ----
1 Cosgrove Alpha Tau Omega President 2018
2 Kendzulak Alpha Tau Omega Grand Master 2018
3 Nappi Alpha Tau Omega worthy sentinel 2018
4 Wakeham Kappa Sigma president 2018
5 Langford Kappa Sigma 2017
6 Johnson Kappa Sigma Social
7 Langford Delta Kappa Epsilon president 2018
8 Howitzer Delta Kappa Epsilon 2018
9 Bush Delta Kappa Epsilon Worthy Eminent
10 Hoover Delta Tau Chi President 2018
11 Blutarsky Delta Tau Chi
B_ID B_LAST_NAME FRAT_NAME POS_TITLE YEAR
---------- ------------------------- ------------------------- ------------------------- ----
12 Can Sigma Alpha Kappa
13 Anders Sigma Alpha Kappa
14 Fred Xi Delta Alpha
15 White Xi Delta Alpha
16 Losser Gamma Delta Iota
17 Sevens Gamma Delta Iota
17 rows selected.
SQL> REM OUTER JOIN #10
SQL> REM Display every fraternity name and their house address even if they do not occupy a house.
SQL> select f.frat_name, h.house_address
2 from fraternity f, house h
3 where f.frat_name = h.frat_name(+)
4 order by f.frat_name;
FRAT_NAME HOUSE_ADDRESS
------------------------- -------------------------
Alpha Tau Omega 164 Love and Respect Way
Alpha Tau Omega 861 Village Lane
Alpha Tau Omega 1165 Manor
Delta Kappa Epsilon 540 greek row
Delta Tau Chi
Gamma Delta Iota
Kappa Sigma 543 greek row
Kappa Sigma 660 Walnut Lane
Sigma Alpha Kappa 1160 Usman Circle
Xi Delta Alpha 547 Layman Av.
10 rows selected.
SQL> REM OUTER JOIN #11
SQL> REM Display every brother ID and last name, fraternity name, and position even if they do not hold a position.
SQL> select b.b_id, b.b_last_name, b.frat_name, p.pos_title
2 from brother b, position p
3 where b.b_id = p.b_id(+)
4 order by b.b_id;
B_ID B_LAST_NAME FRAT_NAME POS_TITLE
---------- ------------------------- ------------------------- -------------------------
1 Cosgrove Alpha Tau Omega President
2 Kendzulak Alpha Tau Omega Grand Master
3 Nappi Alpha Tau Omega worthy sentinel
4 Wakeham Kappa Sigma president
5 Langford Kappa Sigma
6 Johnson Kappa Sigma Social
7 Langford Delta Kappa Epsilon president
8 Howitzer Delta Kappa Epsilon
9 Bush Delta Kappa Epsilon Worthy Eminent
10 Hoover Delta Tau Chi President
11 Blutarsky Delta Tau Chi
B_ID B_LAST_NAME FRAT_NAME POS_TITLE
---------- ------------------------- ------------------------- -------------------------
12 Can Sigma Alpha Kappa
13 Anders Sigma Alpha Kappa
14 Fred Xi Delta Alpha
15 White Xi Delta Alpha
16 Losser Gamma Delta Iota
17 Sevens Gamma Delta Iota
17 rows selected.
SQL> spool off