Questions on the CLASS DATABASE - Solutions

CREATING A PROJECTION

1.
select fname, sname
from student

2.
select sid, born, bmonth, numsib, sibpos
from student

3.
select distinct sport
from plays

4.
select distinct comptype
from techeads


REMOVING DUPLICATES

1.
select distinct house
from student

2.
select distinct suburb
from student

3.
select distinct subject
from studies

4.
select distinct sport
from plays

5.
select distinct hobby
from enjoys

6.
NOT POSSIBLE - only HDD's available

select distinct hdd
from techeads


SELECTING ROWS

1.
select fname, sname
from student
where sibpos = 1

2.
select fname, sname, aka
from student
where sibpos = 2
and numsib = 3

3.
select sport
from plays
where sid = 42

4.
select sid
from studies
where subject = 'EN'
and sid in
   (select sid
    from studies
    and subject = 'PH')

5.
select sid
from studies
where subject = 'MC'

6.
select distinct tutor
from student
where house = 'T'

7.
select fname, sname
from student
where computer = 'No'


USING RELATIONAL AND LOGICAL OPERATORS

1.
select fname, sname
from student
where sibpos = 1
or sibpos = 2

alternative:

select fname, sname
from student
where sibpos in (1,2)

2.
select fname, sname
from student
where sibpos <> numsib

3.
slect fname, sname
from student
where sname > 'Moncrieff'

4.
select sid
from student
where sid not in
   (select sid
    from plays
    where sport = 'RUGBY')

5.
select sid
from student
where sid not in
   (select sid
    from studies
    where subject = 'EC')

6.
select sid
from studies
where subject = 'MB'
and sid in
   (select sid
    from studies
    where subject = 'MC')

7.
UNABLE to do this due to the type of data
as  8Mb > 1Gb using ASCII to compare them 
as they are NOT numbers

8.
select fname, sname
from student
where sibpos = 1
and house = 'B'


NEGATED CONDITIONS

1.
select fname, sname
from student
where suburb not in
  ('Corinda','Indooroopilly','Oxley')

2.
select distinct sport
from plays
where not sport = 'RUGBY'


THE CONDITION IS [NOT] NULL

1.
select fname, sname
from student
where aka null

2.
select fname, sname
from student
where sid in
  (select sid
   from techeads
   where chip null)


THE BETWEEN..AND CONSTRUCT

1.
select fname, sname
from student
where house = 'W'
and tutg between 2 and 6

2.
select fname, sname
from student
where sid between 7 and 14

3.
ideally you would like to do this:

select fname, sname
from student
where sibpos between 2 and max(sibpos)

but it won't work!!!... so a 'kludge' is:

select fname, sname
from student
where sibpos between 2 and 100

which should work for all but the very 
best of catholic families :)

4.
select fname, sname
from student
where sibpos = 2


THE LIKE OPERATOR

1.
select fname, sname
from student
where sname like 'B%'

2.
select distinct sport
from plays
where sport like 'S%'

3.
select fname, sname
from student
where sname like '%w%'
or fname like '%w%'

4.
select fname, sname
from student
where fname like '_ _ _ _ _ _'
and fname not like '_ _ _ _ _'

5.
select fname, sname
from student
where sid in
   (select sid
    from techeads
    where comptype like '%IBM%')


THE IN OPERATOR

1.
select fname, sname
from student
where suburb in
  ('Corinda','Aspley')

2.
select fname, sname
from student
where numsib in (1,2,3)

3.
select distinct sid
from studies
where subject in ('EC','MH','MB')


ORDERING ON A SINGLE COLUMN

1.
select fname, sname
from student
order by sname, fname

2.
select distinct soprt
from plays
order by sport

3.
select distinct subject
from studies
oredr by subject desc

4.
select fname, sname, suburb
from student
order by suburb, sname, fname

5.
select aka
from student
where aka not null
order by aka desc

6.
select fname, sname, house, tutg
from student
order by house, tutg, sname, fname


BANDING (OR ORDERING)

1.
select sid, sport
from plays
order by sport

2.
select sname, born
from student
order by born


DERIVING SIMPLE STATISTICS

1.
select count(*)
from studies
where subject = 'AC'

2.
select sum(numsib)
from student

3.
select count(sid)
from student
where sid not in
  (select sid
   from studies
   where subject = 'MH')

4.
select count(*)
from studies
where subject = 'IP'



PERFORMING SIMPLE CALCULATIONS

1.
select fname, sname
from student
where sid in
  (select max(sid)
   from student)

2.
select count(*)
from tabname <--- substitute each table name

3.
select sum(sibpos)/count(sibpos)
from student

4.
select fname, sname
from student
where numsib in
  (select max(numsib)
   from student) 


USE FOR COLUMN NUMBERS

1.
select fname, sname, suburb
from student
order by 3 desc

2.
select fname, sname, numsib-sibpos
from student
order by 3 desc


GROUPING

1.
select sid, sport
from plays
order by sport

2.
select fname, sname, suburb
from student
order by suburb

3.
select subject, count(*)
from studies
group by subject

4.
select hobby, count(*0
from enjoys
group by hobby

5.
select yrlev, count(*)
from student
group by yrlev


SOME EXTENSION

1.
select fname, sname
from student
where sid in
  (select sid
   from enjoys
   where hobby in
      (select hobby
       from enjoys
       where sid in
           (select sid
            from student
            where sname='MONCRIEFF'
            and fname = 'GLADYS')))
and not(sname='MONCRIEFF'and fname = 'GLADYS')

2.
select fname, sname
from student
where not sid in
   (select sid
    from plays)

3.
create table temp(
house char(1),
tally dec(4))

insert into temp
select house, count(*)
from student
where yrlev = 12

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

drop table temp

4.
un-dropping the previously created table...

we have..

select house
from temp
where tally =
   (select max(tally)
    from temp)      
and house not in
   (select house
    from temp
    where tally =
       (select max(tally)
        from temp))

the largest of the set with the largest already 
taken out is the second largest

5.
select chip
from techeads
where sid in
   (select sid
    from studies
    where subject = 'EC')
and sid in
   (select sid
    from plays
    where sport = 'RUGBY')
and sid in
   (select sid
    from student
    where house = 'W')

join alternative:

select chip
from techeads, studies, plays, student
where student sid = plays.sid
and plays.sid = studies.sid
and studies.sid = techeads.sid
and subject = 'EC'
and sport = 'RUGBY'
and house = 'W'