Introduction to Database Systems

lab notes

Exercise 1  Execute the following steps:

• Follow the instructions in Postures Configuration Manual on the  course web page and try to create the directory structure in the folder specified by the TA, initialize the database template and start the DBMS. ( You need to execute points 1, 2, 3 and 4 from the manual )

• Create mydb Database ( Look at point 6 in the manual )

• Launch the psql terminal. This is the place where we can start creating the tables, performing sql commands and other things.

Execute this statement in the terminal

.

CREATE TABLE Students ( SID numeric(9,0), Name text, Grade float);

 

This statement will create a table in your database called Students . The table will have three columns ID, Name and Grade.

• Execute this statement in the terminal

  .

  INSERT INTO Students VALUES ( 860507041, 'John Anderson', 3.67 );

   

  This statement will create a record in the table Students  for a new student with name John Anderson, SID 860507041 and GPA 3.67

• Execute this statement in the terminal

  .

  INSERT INTO Students VALUES ( 860309067, 'Tom Kamber', 3.12 );

   

  This statement will create a record in the table Students  for a new student with name Tom Kamber, SID 860309041 and GPA 3.12

• Execute this statement in the terminal

  .

  SELECT SID, Name, Grade FROM Students   WHERE SID = 860507041;

   

  This statement will retrieve all records from the table Students which satisfy the condition that the column SID have value  860507041

• Try to insert a new student in the table with name George Haggerty SID = 860704039 and GPA = 3.67

• Try to retrieve all records from the the table which have GPA = 3.67

• Show the result to the TA

• Exit from the psql terminal ( type \q )

• Shutdown the database ( Look at point 5 in the manual )

Exercise 2  ( Optional ) Read the whole manual and try to execute the statements in points from 1 to 11.