SQL Lab I

Tom Kelliher, CS 318

Feb. 1, 2002

The purpose of this lab will be to create two tables and insert some sample data to get some experience with various constraints. You'll be using psql as your PostgreSQL client.

1. If you haven't already done so, create a database using your login name for the database name and connect to it.

2. Create the table Flavors with the following schema:

   

   It may take a few tries to get the syntax down, so create the SQL statement in a text file and use \i <file> in psql to execute it.

3. Create the table People with the following schema:

   

   Use \i again.

4. Remember the SQL statement to view the contents of a table:

   SELECT * FROM <table_name>;
   

   You'll need this to examine the results of your experiments.

5. Try to populate People:

   INSERT INTO People (Name, IceCream) VALUES ('Tom', 'Mint');
   

   What happened? Why?

6. Populate Flavors:

   INSERT INTO Flavors VALUES ('Mint', 'Bittersweet Mint', 'PSU',
   'The best flavor in the whole world.');
   

   What's the syntax difference between this insert and the previous insert? Why the variants?

7. Repeat the first insert. Success, yes?

8. Insert a couple more rows into each of the tables.

9. Test the other constraints:
   1. Try to enter rows with duplicate Code and Name attributes into Flavors.

   2. Try to enter a row with a SoldBy attribute of 'DQ'.

   3. Try to enter a row without a Descr.

   4. Enter a row into People with a missing Age.

10. Drop your two tables:

    DROP TABLE People;
    DROP TABLE Flavors;
    

    Use \q to exit psql.