Simple Questions on the MUSIC DATABASE - Solutions

SET A
1. The ALBUMS TABLE

1.
select albname
from albums
where media = 'cd'

2.
select distinct artist
from albums

3.
select *
from albums
where (1997-pdate) > 10

4.
select albname
from albums
where artist like '%Brian%'
and artist like '%Eno%'

5.
select artist
from albums
where pdate between 
   1980 and 1981

6.
select sernum
from albums
where recco='EMI'

7.
select distinct recco
from albums
order by recco asc

8.
select albname
from albums
where media <> 'CD'
and 2005-pdate >=3


2. THE PERFORMERS TABLE

1.
select distinct artist
from performers
where instrument = 'Bass'

2.
selece distinct instrument
from performers
where artist = 'Brian Eno'

3.
select artist
from performers
where artist not in
  (select artist
   from performers
   where instrument = 'Bass')


4. NASTY QUESTIONS

1.
intersection solution:
select song
from tracks
where sernum in
  (select sernum
   from albums
   where albname = 'Violent Femmes'
   and artist = 'Violent Femmes')

alternatively, a join solution:

select song
from albums, tracks
where albums.sernum = tracks.sernum
and albname = 'Violent Femmes'
and artist = 'Violent Femmes'

2.
select distinct artist
from performers
where sernum in
   (select sernum
    from albums
    where artist = 'U2'
    and albname = 'Achtung Baby')



SET B

1.
select *
from albums
where not media = 'CD'

2.
select count(*)
from albums
where pdate < 1985

3.
select albname, artist, recco
from albums
order by recco

4.
select distinct instrument
from performers
where artist = 'Brian Eno'

5.
select song, sernum
from tracks
where song like '%axe%'

6.
select max(1997-pdate)
from albums

7.
select albname, recco
from albums
where artist = 'Peter Gabriel'
and not recco = 'Charisma'

8.
select count(*)*20
from albums

9.
select count(distinct media)
from albums
10.
select albname, pdate
from albums
order by pdate asc

11.
messy: the simplest way to do this at the moment is

create table temp (
 id char(20), 
 tally dec(3))

insert into temp
select sernum, count(*)
from tracks
group by sernum

select id
from temp
where tally =
  (select max(tally)
   from temp)

drop table temp



SET C

SINGLE TABLE OPERATIONS

ALBUMS TABLE

1.
select albname, artist
from albums
where media in ('CA','CD')
and pdate between 1970 and 1980

2.
select distinct artist
from albums
where artisl like 'P%'

3.
select pdate, recco
from albums
where albname = artist


TRACKS TABLE

1.
select song
from tracks
where song like 'the%'

2.
select song
from albums
where song like '%axe%'



MULTI-TABLE OPERATIONS

1.
select song
from tracks
where sernum in
  (select sernum
   from albums
   where artist = 'Pink Floyd'
   and albname = 'The Dark Side Of  The Moon')

2.
select albname
from albums
where sernum in
   (select sernum
    from tracks
    where song = 'texarcana')

3.
select song
from tracks
where sernum in
  (select sernum
   from albums
   where artist = 'Pink Floyd'
   and pdate <1973)

4.
not possible with this database