Exercise SQL

1. CREATE TABLE and GRAND Rights:
   Create a new table "students" for the workshop DB.
   Grant fred all rights for your new table students,
   than revoke his right to UPDATE (write to) the table
   (you can check the rights with \z).

2. INSERT and SELECT:
   Insert some (at least 10) students into the students table.
   Select all students, so that you can display the complete table.
   Restrict your selection to first and second half of the students.

3. ALTER TABLE:
   Change the name of the students table from the workshop DB to stud.
   Add a column semester of type integer to the stud table,
   stud should have at least the columns 'id', 'name', 'gender', 'diploma' (BIO, INF, PHY, etc), and 'semester'.
   Alter the seamster columns so that the default semester is 1.
   How could you remove (drop) semester again (don't do it)?

4. UPDATE and DELETE:
   Modify the stud table in a way that all students have semesters between 1 and 3 or NULL assigned.
   Delete the student with the ID 3 and insert a replacement.

5. Operators: Boolean, IN, LIKE, IS NULL:
   Select all male students from stud who are not any more in the first semester.
   Select all students with name beginning with letters 'G' to 'M' (try also 'g' to 'm').
   Select all students with id 2, 5 or 7.
   Select all students with a name containing to same vowel.
   Select all students who's semester is not known.

6. Select Filters: DISPLAY, ORDER BY, LIMIT:
   Display the stud table with the column names in German.
   How many distinct diploma exist in your stud table?
   Display the stud table with the students ordered by semester and/or diploma.
   List the first and second half of the stud table.

7. Functions and Groups:
   List PostgreSQL's internal (build-in) functions and operators.
   Compare the average semester of male and female students.
   Count the number of male and female students.
   Determine the average semester of male students holding a diploma in Biology.
   Display all diploma-groups (BIO, PHY, etc) with an average number of semesters greater than two.

8. Constrains and Keys:
   Set ID as primary keys in the stud table (see ALTER TABLE for instructions).
   Add checks for the gender, it should be 'M' or 'F' and the semesters which should be greater than zero.

9. Joins:
   Create the following tables in the workshop DB:
   teachers with attributes: id, name;
   projects with attributes: id, name, duration (in hours).
   You may add more attributes.
   Add the necessary primary keys and at least on check for each table (this is easiest when you create the tables).

   How should the project assignment table "assign" be structured? Create the assign table and include the attributes "percentage" for the percentage of work that a student contributed to a project.

   Now, find one useful example for each join type (cross, inner, non-equi, left/right/full outer, self) and try it.

10. Combined and Sub-Queries:
    Create a table with external projects insert the first two existing projects (use SELECT INTO) and one new project (only external).

    Apply Union, INTERSECT and EXCEPT to the internal and external tables.

    Invent one example each for the sub-queries EXISTS and IN.

    Use EXISTS to find the smallest free ID in the studs table. IDs in the studs table are not continuous, because of deletions.

11. Copy and Backups:
    Copy all tables in the workshop DB into files in a backup directory.
    Reload one table into a newly created "test_table".

    Dump the workshop DB into the file "workshop.dump" in the same directory.
    Reload the workshop DB into newly created "test_db".

12. Inheritance, Views and Rules:
    Create a "juniors" table that inherits the teachers table and add the column "age".
    Insert some juniors and display the teachers together with the juniors.

    Create a view of the juniors table showing only the name and age of the older juniors.

    Create a rule that prevents the insertion of too old juniors.

13. Sequences and Cursors:
    Create a sequence for the id in the stud table and add it as default (new ids should start after the largest existing one).

    Use a cursor to select the students one by one ordered by name.

14. Server-Side Functions:
    Create an aggregate function to determine if there are more male or female students in the workshop DB.

    Write a Trigger that guarantees that to the name 'john' is always assign 'm' as gender and to 'mary' always 'f'.

15. Scripts:
    Write a script that displays all student and teacher names.

16. Transactions:
    Change the assignment of working percentages of students in one project within one transaction.
    Begin an other transaction to change to assignment but use ROLLBACK instead of COMMIT.
    Verify the results in each case.
    What happens to the transaction when you enter a command with a syntax error?