Database Languages
eXercise #12
Correlation and
Existence
A. Consider the
following Table (called directory)
Table: directory
| Filename |
extension |
size |
|
KEY |
ma |
| 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:
- For files of
each filename, list the largest file (filename and extension) along
with it's size
- 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.
- 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 |
