| [Lab Session 6] [Exercise 6] | [Course Information] [COMP2400/6240 Home] |
SQL> @qnvl
SQL> set feedback 1
SQL> set linesize 132
SQL> set pagesize 250
SQL>
SQL> -- cae Oct 2006
SQL>
SQL> -- COMP2400
SQL>
SQL> -- (using nvl)
SQL>
SQL> -- List the Musician_Id, Family_Name, Given_Name of each musician
SQL> -- and where Family_Name or Given_Name is null, print "#none#"
SQL>
SQL> ttitle "Musician Names"
SQL>
SQL> select Musician_Id as "MUSICIAN ID",
2 nvl(Family_Name, '#none#') as "FAMILY NAME",
3 nvl(Given_Name, '#none#') as "GIVEN NAME"
4 from musician;
Thu Oct 12 page 1
Musician Names
MUSICIAN ID FAMILY NAME GIVEN NAME
----------- -------------------- --------------------
1 STARR Ringo
2 LENNON John
3 MCCARTNEY Paul
4 HARRISON George
5 JONES Brian
6 JAGGER Mick
7 RICHARD Keith
8 STEWART Ian
9 WATTS Charlie
10 WYMAN Bill
11 PRICE Jim
12 KEYS Bobby
13 DICKINSON Jim
14 TAYLOR Mick
15 HOPKINS Nicky
16 PRESTON Billy
17 FIELDS Vanetta
18 KING Clydie
19 WOOD Ron
20 KIRKLAND Jesse
21 LYNN Tammi
22 GOODMAN Shirley
23 #none# Dr John
24 GREEN Joe
25 MCDONALD Kathi
26 PLUMMER Bill
27 CLAPTON Eric
28 JONES Kenney
29 COOPER Ray
30 MAGIC Blue
31 JOLLY Charlie
32 LEACH Ed
33 BLUE Sugar
34 WEEKS Willy
35 MACLAGAN Ian
36 COLLINS Mel
37 BRUCE Jack
38 BAKER Ginger
39 ONO Yoko
40 VOORMANN Klaus
41 BRENDELL Steve
42 WHITE Alan
43 TURNER Ted
44 LYNTON Rod
45 TOUT John
46 KELTNER Jim
47 BARHAM John
48 GORDON Jim
49 CURTIS King
50 EVANS Tom
51 MOLLAND Joey
52 PERKINS Al
53 PINDER Mike
54 MARTIN George
55 HOLLAND Milt
56 VAN EATON Lon
57 VAN EATON Derek
58 HELM Levon
59 NILSSON Harry
60 REEVES Martha
61 MCCARTNEY Linda
62 PERRY Richard
63 BOLAN Marc
64 SCOTT Tom
65 PONCIA Vini
66 BOOKER James
67 CALVERT Jimmy
68 ROBERTSON Robbie
69 DANKO Rick
70 HUDSON Garth
71 CLAYTON Merry
72 CROPPER Steve
73 HENSLEY Tom
74 FINDLEY Chuck
75 DYLAN Bob
76 RADLE Carl
77 WHITLOCK Bobby
78 ALLMAN Duane
79 JACKSON Michael
80 VAN HALEN Eddie
81 VAN HALEN Alex
82 ANTHONY Mike
83 ROTH David Lee
84 PLANT Robert
85 PAGE Jimmy
86 JONES John Paul
87 BONHAM John
88 BOWIE David
89 DALTREY Roger
90 TOWNSHEND Pete
91 ENTWISTLE John
92 KNOPFLER Mark
93 DAVIS Jesse Ed
94 PARKES Van Dyke
95 ESPOSITO Joe
96 #none# Sneaky Pete
97 GREENIDGE Robert
98 JARVIS John
99 LOPRESTI Cooker
100 HELMER Duitch
101 STALLWORTH Paul
102 KORTCHMAR Danny
103 FRAMPTON Peter
104 MANCHESTER Melissa
105 ILLSLEY John
106 CLARK Alan
107 LINDES Hal
108 WILLIAMS Terry
109 MANDEL Tommy
110 DE KORTE Joop
111 LANE Ronnie
112 STEWART Rod
113 ALOMAR Carlos
114 DAVIES Dennis
115 MURRAY George
116 FRIPP Robert
117 SUMLER Diane
118 VANDROSS Luther
119 MAASS Antonia
120 NEWMARK Andy
121 SANBORN David
122 WASHINGTON Larry
123 CHERRY Ava
124 HINTON Anthony
125 CLARK Robin
126 KSASAN Emir
127 SLICK Earl
128 MCDONALD Ralph
129 FINEBERG Jean
130 MILLINGTON Jean
131 GARSON Michael
132 ROSARIO Pablo
133 HEWSON Paul (Bono Vox)
134 #none# The Edge
135 CLAYTON Adam
136 MULLEN Larry
137 ENO Brian
138 LANOIS Daniel
139 #none# Southside Johnny
140 WEINBERG Max
141 TALLENT Garry
142 CLOUD Rusty
143 VAN ZANDT Steve
144 BANDIERA Bobby
145 BON JOVI Jon
146 SPRINGSTEEN Bruce
147 BECK Jeff
148 ARONOFF Kenny
149 JACKSON Randy
150 JOHN Elton
151 TENCH Benmont
152 NOVA Aldo
153 PARLAPIANO Phil
154 MATTHEWS Myrna
155 WALTERS Julia
156 WATERS Maxine
157 GLAUB Bob
158 PHILLIPS Lou Diamond
159 CROSBY Robbin
160 #none# Little Richard
161 WATCHELL Waddy
162 LAINE Denny
163 RODGERS Nile
164 CLEMONS Clarence
165 BITTAN Roy
166 PEACE Warren
167 FEDERICI Danny
168 SANCIOUS David
169 APPEL Mike
170 CARTER Ernest
171 BRECKER Michael
172 BRECKER Randy
173 ANDRE Wayne
174 DAVIS Richard
175 MANUEL Richard
176 MOON Keith
177 BARNES Jimmy
178 MOSS Ian
179 WALKER Don
180 SMALL Phil
181 PRESTWICH Steven
182 ARNOTT Ray
183 BIDSTRUP Graham
184 BLIGHT David
185 RODGERS Billy
186 HOFFMAN John
187 WILLIAMS Megan
188 BROCK Tony
189 CAIN Jonathan
190 SCHON Neal
191 BREWSTER Rick
192 LEWIS Huey
193 DIESEL Johnny
194 MURPHY Shawn
195 HAWKINS Walter
196 STEPHENS Lynette
197 HILBURN Jim
198 KEKELL Peter
199 LINDLEY David
200 AMATO David
201 EILSLEY David Glen
202 MAROTTA Jerry
203 GLASSMAN Seth
204 MCCURRY John
205 MANGIAFICO Gregg
206 KENTIS Chuck
207 MATHEWS Wendy
208 FARRIS Jon
209 BAILEY Chris
210 CASEY Howie
211 VISCONTI Tony
212 BROMBERG David
213 BECKETT Harry
214 MILLER Jimmy
215 COODER Ry
216 NEWMAN Nanette
217 BERLINE Byron
218 RUSSELL Leon
219 KOOPER Al
220 BELL Madelaine
221 TROY Doris
222 Dijon Rocky
222 rows selected.
SQL>
SQL> ttitle off
SQL> -- cae Oct 2006
SQL>
SQL> -- Look for nulls in names in Musician
SQL>
SQL> select Musician_Id, Family_Name, Given_Name
2 from musician
3 where Family_Name is null
4 or Given_Name is null;
MUSICIAN_ID FAMILY_NAME GIVEN_NAME
----------- -------------------- --------------------
23 Dr John
96 Sneaky Pete
134 The Edge
139 Southside Johnny
160 Little Richard
5 rows selected.
|
URL: http://computer/student/comp2400.2006/assessment/ca/exercises/ex6/SolnNotes.shtml
Last modified: Monday, 23-Oct-2006 10:54:32 EST |