sample abe sql question

[gregdamona]

Here's the sample abe question for 2003. The deadline is December 20th, 2005. Good luck.

PART A
1. Create two databases then list them. Remove one database and keep the second one.
2. Create a table — referred to as Table1 — preferably with a meaning in an application
domain that you are accustomed with, with at least one column of each of the following
types: character/string, integer and date.
3. Insert at least 15 rows in Table1, some of them having NULL values (not all the NULLs in the
table should be in the same column).
4. Delete all the rows form Table1 having at least one NULL.
5. Alter Table1 by renaming and adding new columns. Insert 5 rows in the table.
6. Retrieve values from Table1 via 3 queries with the following specifications:
a. a restriction using a condition on a single field
b. a restriction using a condition on two fields
c. a projection and a restriction using a condition on three fields.
Use each of the logical operators NOT, AND, OR in at least one query.
7. Create a table named Table2 with the same specifications as Table1, but with each field
constrained in a way of your choice. All the constraints should differ from one another.
8. Create a table named Table3 with the following specifications:
a. it has fewer fields than Table2
b. each field in Table3 has one and only one corresponding field in Table2;
“corresponding”, here, means “of the same data type, but possibly of a different
name”.
c. Insert values in Table3 from Table2 via a SELECT statement.
9. Modify rows in Table3 via at least 5 different UPDATE statements.
10. Remove rows from Table3 via at least 3 different DELETE statements.
11. Create two tables —Table4 and Table5 — exactly following the structure of Table3 and
Table2 respectively. Move all the data from Table3 and Table2 into Table4 and Table5
respectively. Then, remove Table2 and Table3.
12. Create a table – Table6 — with the same specification as Table5, but adding the constraint
according to which Table6 cannot have more than 6 rows. What happens if one tries to insert
7 rows, one by one, into this table?