[Tut/Lab Sessions Schedule] [Course Information]   [COMP2400/6240 Home]
Coloured Line

Queries against the SQL, Bugs, and Rock 'n' Roll Database

Solution Notes

Coloured Line

  1. 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>

  2. 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>
    

    1. 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>
      
    2. 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>
      

    1. 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>
      
    2. 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> 
      

  3. 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> 
    

[Top]

Coloured Line

Carol Edmondson   <carol@cs.anu.edu.au>
URL: http://computer/student/comp2400.2006/tutlabs/labworksheets/SBR_Queries_SolnNotes.shtml
Last modified: Sunday, 30-Jul-2006 19:01:07 EST