Lab02 Assignment Solution

This is an introductory lab to help you familiarize yourself with the tools that will be used throughout the course.







Initialize PSQL Environment



Following, we describe the steps associated with initializing our execution environment.




Execute the following command to initialize the PSQL environment s o u r c e . / s t a r t P o s t g r e S Q L . sh






Note: Examine the script you just executed. Look at every command and try to gure out its functionality. Pay special attention to the P GP ORT variable. After executing the above script type pg ctl status to view the status of the server, it should indicate that it is running correctly.







Execute the following command to create your database s o u r c e . / c r e a t e P o s t g r e D B . sh






Note: Examine the script you just executed. Look at every command and try to gure out its functionality. What is the name of the database you just created?




Once you nished with the whole assignment, DO NOT FORGET! to call the following command to stop the server and shutdown the database.






s o u r c e . / stopPostgreDB . sh




Note: Examine the script you just executed. Look at every command and try to gure out its functionality.




1















Execute SQL Statements



After initializing your environment you should execute a series of SQL state-ments. DO NOT OPEN! a new terminal window, the scripts you just executed rely on system variables initialized through the previous scripts. Opening a new window will require initializing the values from scratch, hence running each script again after stopping the database correctly.




First, you will use the interactive environment PSQL environment to execute some SQL statements.




Type the following command to launch the PSQL interactive environ-ment






p s q l h l o c a l h o s t p $PGPORT $USER" DB"







We use $ to specify the value of the system variable with the corre-sponding name. In this case, $USER is your username. Alternatively, you can type the values directly if you know them. For example, if your username is vzois001 and the port number is set to 8192 then the command should look like:







p s q l h l o c a l h o s t p 8192 " vzois001 DB "







In the terminal type the following statement to create a table with name students






CREATE TABLE S t u d e n t s ( SID numeric ( 9 , 0 ) , Name ,! t e x t , Grade f l o a t ) ;




Type ndt to view a list of all tables in the database. You should be able to see the table you just created.



Insert a single row in the table using the following statement






INSERT INTO S t u d e n t s VALUES ( 8 6 0 5 0 7 0 4 1 , ’ John ,! Anderson ’ , 3 . 6 7 ) ;







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




5. Insert a single row in the table using the following statement







INSERT INTO S t u d e n t s VALUES ( 8 6 0 3 0 9 0 6 7 , ’Tom Kamber ,! ’ , 3.12) ;













2















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 a query using the following command



SELECT SID , Name , Grade FROM S t u d e n t s WHERE SID = ,! 860507041;




This statement will retrieve all records from the table Students which satisfy the condition that the column SID has 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 table which have GPA = 3.67.



Exit from the PSQL terminal (type nq).



Using the text editor (gedit), create a .sql le containing all of the previous statements (except ndt). At the top of the le include the following statement






DROP TABLE IF EXISTS S t u d e n t s ;




This statement is often used at the beginning of each script to avoid errors when re-initializing the tables.




Use the following command to execute all statements in the .sql le you just created






p s q l h l o c a l h o s t ,! s q l




p $PGPORT $USER "DB" < s c r i p t .



Replace "script" with the name of your script.




Shutdown the database using the appropriate script (look at previous section).



Submit the script you created in iLearn.

































3