IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
exercise source
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 
Database Languages eXercises #12

Correlation and Existence - Some Solutions


A.

With Correlation

1.
select *
from directory d
where size =
   (select max(size)
    from directory
    where filename = d.filename)

2.
select filename, extension
from directory
where size >
   (select avg(size)
    from directory
    where extension = d.extension)

3.
select filename
from directory d
where extension = 'com'
and size <
   (select size
    from directory
    where extension = 'doc'
    and filename = d.filename)

alternative:

select filename
from directory d
where extension = 'com'
and filename =
   (select filename
    from directory
    where extension = 'doc'
    and size > d.size)


Without Correlation

1.
create table part1
(name char(8),
maxsize dec(6))

insert into part1
select filename, max(size)
from directory
group by filename

select *
from directory, part1
where filename = name
and size = maxsize

drop table part1


2.
create table part2
(type char(8),
avgsize dec(6))

insert into part1
select extension, avg(size)
from directory
group by extension

select filename, extension
from directory, part2
where extension = type
and size >  avgsize

drop table part2


3.
select L.filename
from directory L, directory R
where L.extension = 'com'
and R.extension = 'doc'
and L.size < R.size
and L.filename = R.filename

B. delete from duplicates D where exists (select name, iq from duplicates where name = D.name and task = D.task group by name, task having count(*) > 1)

 

wonko@wonko.info
©Copyright t 1992..2018+. Edition 26.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.