Lab1 - Introduction to SQL

Objectives: 

• Create a db in psql
• Create tables and insert data
• Test constraints

Try the following activities answering all the questions.  Turn in the files with your table creations as well as your written answers.

1. Here's a shell session captured using the Unix script command. It demonstrates basic usage of psql, which is a PostgreSQL command line client:

   # Initially creating a database, connecting to it within psql, and
   # creating a table within the database.  You always connect to the
   # username database the first time you connect.  The syntax of psql is:
   #
   #    psql <database name> <database username>
   #
   # See the man page for psql for more information.
   
   phoenix:~
   * psql 
   Password:
   Welcome to psql, the PostgreSQL interactive terminal.
   
   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help on internal slash commands
          \g or terminate with semicolon to execute query
          \q to quit
   
   template1=> -- This is an SQL comment.
   template1=> --
   template1=> -- Notice the nice help meta-commands above.
   template1=> -- 
   template1=> create database jillz;
   CREATE DATABASE
   template1=> -- Note that SQL statements are terminated with
   template1=> -- a semicolon.  If you forget, use \g.
   template1=> --
   template1=> -- Connect to the database I just created.
   template1=> --
   template1=> \c jillz
   You are now connected to database jillz.
   jillz=> create table example (
   jillz(>    id     integer,
   jillz(>    name   char(50) );
   CREATE
   jillz=> --
   jillz=> -- About to exit
   jillz=> \q
   

    
2. On phoenix, run psql, create a database using your login name for the database name and connect to it.
    
3. Some examples of SQL syntax for the creation of tables:
   
   Note the use of PRIMARY KEY and UNIQUE in the following example.  A table may have one  PRIMARY KEY.  Other candidate keys may be specified as UNIQUE.
   
   CREATE TABLE Course (
      CrsCode        CHAR(6),
      DeptId         CHAR(4),
      CrsName        CHAR(20),
      Descr          CHAR(100),
      PRIMARY KEY (CrsCode),
      UNIQUE (DeptId, CrsName) );
   
   Note the use of specifying domain and semantic constraints.
   
   CREATE TABLE Course (
      CrsCode        CHAR(6) NOT NULL,
      Limit          INTEGER DEFAULT '10',
      Division       CHAR(5),
      DeptId         CHAR(4),
      CrsName        CHAR(20),
      Descr          CHAR(100),
      CHECK (Limit > 0 AND Limit <= (SELECT MAX(Seats) from Room)),
      CHECK (Division IN ('Upper', 'Lower')) );
   
   In PostgreSQL the referenced foreign key bust be a primary key.
   
   CREATE TABLE Teaching (
      ProfId         INTEGER,
      CrsCode        CHAR(6),
      Semester       CHAR(6),
      PRIMARY KEY (CrsCode, Semester),
      FOREIGN KEY (CrsCode) REFERENCES Course,
      FOREIGN KEY (ProfID) REFERENCES Professor (Id) );
    
4. Create the table Employee with the following schema:

   Attribute Name	Domain	Comment
   Id	INTEGER	Primary key
   Name	CHAR(20)	Not null
   Salary	INTEGER	Greater than zero
   Department	CHAR(4)

   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.
    

5. Create the table Department with the following schema:
   
    

   Attribute Name	Domain	Comment
   DeptId	CHAR(4)	Primary key
   Budget	INTEGER	Default is $100
   MngrID	INTEGER	References an Employee Id

   Use \i again.
    

6. 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.
    
7. Try to populate Employee:

   INSERT INTO Employee (Id, Salary) VALUES (12345, 100000);

   What happened? Why?
    
8. Populate Department:

   INSERT INTO Department VALUES ('CS', 5000,34567);

   What happened? Why?
    
9. Insert an Employee with Id 34567 and then repeat the previous insert. Success, yes? Insert a couple more rows into each of the tables.
    
10. It would be desirable to have the Department attribute in Employee reference DeptId in Department.  Why would we want this constraint?  What would be the problem with initially creating the Employee table with this constraint?
    
     
11. Use ALTER TABLE as described in the text to add this constraint and test it out by adding an employee into a non-existent department.
     
12. Test the other constraints:
    1. Try to enter rows with duplicate DeptID into Department.
    2. Try to enter an employee with a negative salary.
    3. Try to enter a department without a budget.
        
13. Try to delete the Employee table with the command:

    DROP TABLE Employee ;
    
    What happened?  Why?

14. Drop your two tables:
    
    DROP TABLE Employee CASCADE;
    DROP TABLE Department;

15. Answer question 3.9a on p67

16. Use \q to exit psql.