| [Tut/Lab Sessions Schedule] | [Course Information] [COMP2400/6240 Home] |
Contents of my q1.sql file:
-- cae July 2006
-- COMP2400/6240 2006/2
-- q1 List full details of all artists.
set pagesize 200
ttitle "Full Details of All Artists"
select *
from carol.artist;
Starting my q1.sql:
partch:~/comp2400/lab1> sqlplus /
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jul 24 16:33:38 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL> @q1
Mon Jul 24 page 1
Full Details of All Artists
ARTIST_ID NAME
---------- ----------------------------------------
1 Beatles
2 Ringo Starr
3 Cream
4 Rolling Stones
5 John Lennon
6 Derek and the Dominos
7 Wings
8 Cold Chisel
9 Michael Jackson
10 Van Halen
11 David Bowie
12 Led Zeppelin
13 Faces
14 Who
15 U2
16 Jon Bon Jovi
17 Bruce Springsteen
18 Southside Johnny and the Asbury Jukes
19 Dire Straits
20 Bob Dylan
21 Honeydrippers
22 Jimmy Barnes
22 rows selected.
SQL>
Contents of my q2.sql file:
set echo on
-- cae July 2006
-- COMP2400/6240 2006/2
-- q2 Report on the birth countries of all musicians.
-- Each row on the list should contain the id for the musician,
-- the given and last names of the musician,
-- and the birth country (in that order).
ttitle "Birth Countries of All Musicians"
select musician_id, given_name, family_name, birth_country
from carol.musician
order by birth_country, family_name, given_name;
Starting my q2.sql:
SQL> @q2
SQL>
SQL> -- cae July 2006
SQL> -- COMP2400/6240 2006/2
SQL>
SQL> -- q2 Report on the birth countries of all musicians.
SQL> -- Each row on the list should contain the id for the musician,
SQL> -- the given and last names of the musician,
SQL> -- and the birth country (in that order).
SQL>
SQL> set pagesize 250
SQL>
SQL> ttitle "Birth Countries of All Musicians"
SQL>
SQL> select musician_id, given_name, family_name, birth_country
2 from carol.musician
3 order by birth_country, family_name, given_name;
Wed Jul 26 page 1
Birth Countries of All Musicians
MUSICIAN_ID GIVEN_NAME FAMILY_NAME BIRTH_COUNTRY
----------- -------------------- -------------------- ---------------
69 Rick DANKO Canada
70 Garth HUDSON Canada
68 Robbie ROBERTSON Canada
38 Ginger BAKER England
147 Jeff BECK England
63 Marc BOLAN England
87 John BONHAM England
88 David BOWIE England
27 Eric CLAPTON England
89 Roger DALTREY England
137 Brian ENO England
91 John ENTWISTLE England
103 Peter FRAMPTON England
4 George HARRISON England
6 Mick JAGGER England
150 Elton JOHN England
5 Brian JONES England
86 John Paul JONES England
2 John LENNON England
54 George MARTIN England
3 Paul MCCARTNEY England
176 Keith MOON England
85 Jimmy PAGE England
84 Robert PLANT England
7 Keith RICHARD England
1 Ringo STARR England
8 Ian STEWART England
112 Rod STEWART England
14 Mick TAYLOR England
90 Pete TOWNSHEND England
9 Charlie WATTS England
10 Bill WYMAN England
40 Klaus VOORMANN Germany
133 Paul (Bono Vox) HEWSON Ireland
136 Larry MULLEN Ireland
81 Alex VAN HALEN Netherlands
80 Eddie VAN HALEN Netherlands
92 Mark KNOPFLER Scotland
78 Duane ALLMAN USA
82 Mike ANTHONY USA
165 Roy BITTAN USA
145 Jon BON JOVI USA
215 Ry COODER USA
75 Bob DYLAN USA
167 Danny FEDERICI USA
58 Levon HELM USA
79 Michael JACKSON USA
219 Al KOOPER USA
192 Huey LEWIS USA
61 Linda MCCARTNEY USA
59 Harry NILSSON USA
83 David Lee ROTH USA
218 Leon RUSSELL USA
146 Bruce SPRINGSTEEN USA
141 Garry TALLENT USA
118 Luther VANDROSS USA
140 Max WEINBERG USA
160 Little Richard USA
113 Carlos ALOMAR
200 David AMATO
173 Wayne ANDRE
169 Mike APPEL
182 Ray ARNOTT
148 Kenny ARONOFF
209 Chris BAILEY
144 Bobby BANDIERA
47 John BARHAM
177 Jimmy BARNES
213 Harry BECKETT
220 Madelaine BELL
217 Byron BERLINE
183 Graham BIDSTRUP
184 David BLIGHT
33 Sugar BLUE
66 James BOOKER
171 Michael BRECKER
172 Randy BRECKER
41 Steve BRENDELL
191 Rick BREWSTER
188 Tony BROCK
212 David BROMBERG
37 Jack BRUCE
189 Jonathan CAIN
67 Jimmy CALVERT
170 Ernest CARTER
210 Howie CASEY
123 Ava CHERRY
106 Alan CLARK
125 Robin CLARK
135 Adam CLAYTON
71 Merry CLAYTON
164 Clarence CLEMONS
142 Rusty CLOUD
36 Mel COLLINS
29 Ray COOPER
72 Steve CROPPER
159 Robbin CROSBY
49 King CURTIS
114 Dennis DAVIES
93 Jesse Ed DAVIS
174 Richard DAVIS
110 Joop DE KORTE
13 Jim DICKINSON
193 Johnny DIESEL
222 Rocky Dijon
201 David Glen EILSLEY
95 Joe ESPOSITO
50 Tom EVANS
208 Jon FARRIS
17 Vanetta FIELDS
74 Chuck FINDLEY
129 Jean FINEBERG
116 Robert FRIPP
131 Michael GARSON
203 Seth GLASSMAN
157 Bob GLAUB
22 Shirley GOODMAN
48 Jim GORDON
24 Joe GREEN
97 Robert GREENIDGE
195 Walter HAWKINS
100 Duitch HELMER
73 Tom HENSLEY
197 Jim HILBURN
124 Anthony HINTON
186 John HOFFMAN
55 Milt HOLLAND
15 Nicky HOPKINS
105 John ILLSLEY
149 Randy JACKSON
98 John JARVIS
31 Charlie JOLLY
28 Kenney JONES
198 Peter KEKELL
46 Jim KELTNER
206 Chuck KENTIS
12 Bobby KEYS
18 Clydie KING
20 Jesse KIRKLAND
102 Danny KORTCHMAR
126 Emir KSASAN
162 Denny LAINE
111 Ronnie LANE
138 Daniel LANOIS
32 Ed LEACH
107 Hal LINDES
199 David LINDLEY
99 Cooker LOPRESTI
21 Tammi LYNN
44 Rod LYNTON
119 Antonia MAASS
35 Ian MACLAGAN
30 Blue MAGIC
104 Melissa MANCHESTER
109 Tommy MANDEL
205 Gregg MANGIAFICO
175 Richard MANUEL
202 Jerry MAROTTA
207 Wendy MATHEWS
154 Myrna MATTHEWS
204 John MCCURRY
25 Kathi MCDONALD
128 Ralph MCDONALD
214 Jimmy MILLER
130 Jean MILLINGTON
51 Joey MOLLAND
178 Ian MOSS
194 Shawn MURPHY
115 George MURRAY
216 Nanette NEWMAN
120 Andy NEWMARK
152 Aldo NOVA
39 Yoko ONO
94 Van Dyke PARKES
153 Phil PARLAPIANO
166 Warren PEACE
52 Al PERKINS
62 Richard PERRY
158 Lou Diamond PHILLIPS
53 Mike PINDER
26 Bill PLUMMER
65 Vini PONCIA
16 Billy PRESTON
181 Steven PRESTWICH
11 Jim PRICE
76 Carl RADLE
60 Martha REEVES
185 Billy RODGERS
163 Nile RODGERS
132 Pablo ROSARIO
121 David SANBORN
168 David SANCIOUS
190 Neal SCHON
64 Tom SCOTT
127 Earl SLICK
180 Phil SMALL
101 Paul STALLWORTH
196 Lynette STEPHENS
117 Diane SUMLER
151 Benmont TENCH
45 John TOUT
221 Doris TROY
43 Ted TURNER
57 Derek VAN EATON
56 Lon VAN EATON
143 Steve VAN ZANDT
211 Tony VISCONTI
179 Don WALKER
155 Julia WALTERS
122 Larry WASHINGTON
161 Waddy WATCHELL
156 Maxine WATERS
34 Willy WEEKS
42 Alan WHITE
77 Bobby WHITLOCK
187 Megan WILLIAMS
108 Terry WILLIAMS
19 Ron WOOD
23 Dr John
96 Sneaky Pete
139 Southside Johnny
134 The Edge
222 rows selected.
SQL>
SQL> @q3a
SQL>
SQL> -- cae July 2006
SQL> -- COMP2400/6240 2006/2
SQL>
SQL> -- q3a For all musicians who have played on a gig:
SQL> -- List their musician_id.
SQL>
SQL> set pagesize 250
SQL>
SQL> ttitle "IDs of musicians who have played on a gig"
SQL>
SQL> select distinct musician_id
2 from carol.gig;
Wed Jul 26 page 1
IDs of musicians who have played on a gig
MUSICIAN_ID
-----------
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
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
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
220
221
222
206 rows selected.
SQL>
SQL> @q3b
SQL>
SQL> -- cae July 2006
SQL> -- COMP2400/6240 2006/2
SQL>
SQL> -- q3b For all musicians who have played on a gig:
SQL> -- List their full name.
SQL>
SQL> set pagesize 250
SQL>
SQL> ttitle "Full names of musicians who have played on a gig"
SQL>
SQL> select distinct m.given_name, m.family_name
2 from carol.gig g, carol.musician m
3 where (g.musician_id = m.musician_id);
Wed Jul 26 page 1
Full names of musicians who have played on a gig
GIVEN_NAME FAMILY_NAME
-------------------- --------------------
Adam CLAYTON
Al PERKINS
Alan CLARK
Alan WHITE
Alex VAN HALEN
Andy NEWMARK
Anthony HINTON
Antonia MAASS
Ava CHERRY
Bill PLUMMER
Bill WYMAN
Billy PRESTON
Billy RODGERS
Blue MAGIC
Bob DYLAN
Bobby BANDIERA
Bobby KEYS
Bobby WHITLOCK
Brian ENO
Brian JONES
Bruce SPRINGSTEEN
Byron BERLINE
Carl RADLE
Carlos ALOMAR
Charlie JOLLY
Charlie WATTS
Chris BAILEY
Chuck FINDLEY
Chuck KENTIS
Clarence CLEMONS
Clydie KING
Cooker LOPRESTI
Daniel LANOIS
Danny FEDERICI
Danny KORTCHMAR
David AMATO
David BLIGHT
David BOWIE
David BROMBERG
David LINDLEY
David SANBORN
David SANCIOUS
David Glen EILSLEY
David Lee ROTH
Dennis DAVIES
Denny LAINE
Derek VAN EATON
Diane SUMLER
Don WALKER
Doris TROY
Dr John
Duane ALLMAN
Duitch HELMER
Earl SLICK
Ed LEACH
Eddie VAN HALEN
Emir KSASAN
Eric CLAPTON
Ernest CARTER
Garry TALLENT
Garth HUDSON
George HARRISON
George MARTIN
George MURRAY
Ginger BAKER
Graham BIDSTRUP
Gregg MANGIAFICO
Hal LINDES
Harry BECKETT
Harry NILSSON
Howie CASEY
Huey LEWIS
Ian MACLAGAN
Ian MOSS
Ian STEWART
Jack BRUCE
James BOOKER
Jean FINEBERG
Jean MILLINGTON
Jeff BECK
Jerry MAROTTA
Jesse KIRKLAND
Jesse Ed DAVIS
Jim DICKINSON
Jim GORDON
Jim HILBURN
Jim KELTNER
Jim PRICE
Jimmy BARNES
Jimmy CALVERT
Jimmy MILLER
Jimmy PAGE
Joe ESPOSITO
Joe GREEN
Joey MOLLAND
John BARHAM
John BONHAM
John ENTWISTLE
John HOFFMAN
John ILLSLEY
John JARVIS
John LENNON
John MCCURRY
John TOUT
John Paul JONES
Johnny DIESEL
Jon BON JOVI
Jon FARRIS
Jonathan CAIN
Joop DE KORTE
Kathi MCDONALD
Keith MOON
Keith RICHARD
Kenney JONES
King CURTIS
Klaus VOORMANN
Larry MULLEN
Larry WASHINGTON
Leon RUSSELL
Levon HELM
Linda MCCARTNEY
Lon VAN EATON
Luther VANDROSS
Lynette STEPHENS
Madelaine BELL
Marc BOLAN
Mark KNOPFLER
Max WEINBERG
Megan WILLIAMS
Mel COLLINS
Melissa MANCHESTER
Merry CLAYTON
Michael BRECKER
Michael GARSON
Michael JACKSON
Mick JAGGER
Mick TAYLOR
Mike ANTHONY
Mike APPEL
Mike PINDER
Milt HOLLAND
Nanette NEWMAN
Neal SCHON
Nicky HOPKINS
Nile RODGERS
Pablo ROSARIO
Paul MCCARTNEY
Paul STALLWORTH
Paul (Bono Vox) HEWSON
Pete TOWNSHEND
Peter FRAMPTON
Peter KEKELL
Phil SMALL
Ralph MCDONALD
Randy BRECKER
Ray ARNOTT
Ray COOPER
Richard DAVIS
Richard MANUEL
Richard PERRY
Rick BREWSTER
Rick DANKO
Ringo STARR
Robbie ROBERTSON
Robert FRIPP
Robert GREENIDGE
Robert PLANT
Robin CLARK
Rocky Dijon
Rod LYNTON
Rod STEWART
Roger DALTREY
Ron WOOD
Ronnie LANE
Roy BITTAN
Rusty CLOUD
Ry COODER
Seth GLASSMAN
Shawn MURPHY
Shirley GOODMAN
Sneaky Pete
Southside Johnny
Steve BRENDELL
Steve CROPPER
Steve VAN ZANDT
Steven PRESTWICH
Sugar BLUE
Tammi LYNN
Ted TURNER
Terry WILLIAMS
The Edge
Tom EVANS
Tom HENSLEY
Tom SCOTT
Tommy MANDEL
Tony BROCK
Tony VISCONTI
Van Dyke PARKES
Vanetta FIELDS
Vini PONCIA
Walter HAWKINS
Warren PEACE
Wayne ANDRE
Wendy MATHEWS
Willy WEEKS
Yoko ONO
206 rows selected.
SQL>
SQL> @q4a
SQL> -- cae July 2006
SQL> -- COMP2400/6240 2006/2
SQL>
SQL> -- q4a Retrieve the birth date of the musician whose family name is
SQL> -- 'KOOPER'.
SQL>
SQL> ttitle "Birth Date of KOOPER"
SQL>
SQL> select birth_date
2 from carol.musician
3 where family_name = 'KOOPER';
Sun Jul 30 page 1
Birth Date of KOOPER
BIRTH_DATE
-----------
05-FEB-1944
SQL>
SQL> @q4b
SQL> -- cae July 2006
SQL> -- COMP2400/6240 2006/2
SQL>
SQL> -- q4b Retrieve the birth date of the musician whose name is
SQL> -- 'Nicky Hopkins'.
SQL>
SQL> ttitle "Birth Date of Nicky Hopkins"
SQL>
SQL> select birth_date
2 from carol.musician
3 where lower(given_name) = 'nicky'
4 and upper(family_name) = 'HOPKINS';
Sun Jul 30 page 1
Birth Date of Nicky Hopkins
BIRTH_DATE
-----------
SQL>
SQL> @q5
SQL> -- cae July 2006
SQL> -- COMP2400/6240 2006/2
SQL>
SQL> -- q5 List the label and title of all albums released before 2000.
SQL>
SQL> set pagesize 100
SQL> set linesize 132
SQL>
SQL> ttitle "Albums released before 2000"
SQL>
SQL> select label, title
2 from carol.album
3 where year < 2000;
Sun Jul 30 page 1
Albums released before 2000
LABEL TITLE
--------------- ----------------------------------------
Rolling Stone Its Only Rock'n'Roll
Decca Let It Bleed
Rolling Stone Exile on Main Street
Apple White
Apple Let It Be
Apple Anthology 2
Apple Ringo
ATCO Fresh Cream
Apple Imagine
ATCO Layla and Other Assorted Love Songs
CBS Thriller
CBS Off The Wall
WB Van Halen
Swan Song Physical Graffiti
Polydor It's Hard
WB Long Player
RCA Heroes
RCA Young Americans
Island The Joshua Tree
Rolling Stone Some Girls
Vertigo Alchemy
Asylum Planet Waves
Polydor Rotogravure
EMI Better Days
Polygram Blaze of Glory
RCA Station to Station
Apple Band on the Run
Atlantic Volume One
CBS Born to Run
Mushroom Twentieth Century
Mushroom Freight Train Heart
Decca Who's Next
32 rows selected.
SQL>
|
URL: http://computer/student/comp2400.2006/tutlabs/labworksheets/SBR_Queries_SolnNotes.shtml
Last modified: Sunday, 30-Jul-2006 19:01:07 EST |