IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
IIS home
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

eXercise #12

Correlation and Existence

A. Consider the following Table (called directory)

Table: directory
Filename extension size
prog exe 10501
prog bak 7011
prog old 6766
prog pas 7035
game bak 3022
game bas 4318
game com 12355
game doc 1428
... ... ...

Using correlated sub-queries, answer the following:

  1. For files of each filename, list the largest file (filename and extension) along with it's size
  2. List all files which are above average size for their type (that is the average for that file type, as indicated by the file extension). List the file name and the extension.
  3. List files (filename only) whose COM versions are smaller than their DOC versions.

Suggest alternative solutions to the above questions that do NOT have correlation in them.

B. The rows of any relationally acceptable table should be unique. Referring to the following table, write an SQL statement that will successfully delete all DUPLICATE rows from the table, leaving ONE COPY of each row. note:all but one of these rows should be deleted.

Table: duplicates
name iq
Bob 100
Carol 120
Ted 65
Carol 120
Alice 115
Ted 65
Murgatroid 200


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