The Australian National University, School of Computing COMP2400/6240 (Relational Databases) Semester 2, 2022 Lab 1 Lab Environment (Option 2 or on Campus) By the end of this lab session, you should be familiar with the basics of the lab environ- ment for SQL. We have a wide range of skill levels in the class, so some of these exercises are very basic. If you have done an exercise many times before, such as using the Ubuntu system, then feel free to skip to the next exercise. On the other hand, make sure you understand these basics, because you will need these skills to succeed in the course. The ANU School of Computing has a great resource for the basics of using the Student Computing Environment. The website is available from https://cs.anu.edu.au/docs/ student-computing-environment/. Each activity will be shown in the following format. (1). Notice what the exercises look like. When you see an item like this, it means you need to do something. Sometimes further explanation of the task will follow. In most cases, you should keep some record of what you did, for example if the instruction asks you to do a database query, you should save the SQL in a file so you can show your tutor. 1 Connecting to CECS Ubuntu (Virtual) Desktop Follow the instructions under “Option 2: Connecting to CECS Ubuntu Virtual Desktop from your computer” given on the course Wattle site or simply log in to the desktop in the lab on campus. 2 Basic Usage of CECS Ubuntu (Virtual) Desktop As this is a Ubuntu system, you should become familiar with the Unix system and the Gnome utilities that let you interact with the files and directories in the file system. Directories are also referred to as folders. Figure 1: Running the ANU CECS Ubuntu (Virtual) Desktop (1) Browsing Files Start the default file manager by clicking File Browser button on the left-edge panel (refer to Figure 1). This will display your home directory. Your home directory contains a number of default directories. Follow the link in Wattle site Option 2, “(4) Downloading the SQL files“ to download the ZIP file to the Desktop. Right click on the file and click Extract Here to unzip the file to Desktop. Gedit is the default text editor on the system and double-clicking any text file will open it in gedit. There are several other editors available on the student system which you 2 can configure to be your default text editor if you prefer. (2) Open the text editor gedit and start editing a file. Click the applications button (refer to Figure 1) and type “Text Editor“ to locate gedit. Once you have opened an empty text document, write “Hello, World!” in the file and save it as u1234567.txt (use your uid) on the Desktop. Click on the File Browser button on the left-edge panel and try to find the file that you saved. If you double click on this file, the default text editor gedit will open the file and allow you to further edit this file and save it again. You can also look in the Gnome Help Centre for instructions if you would like to use your preferred editor. 3 Terminal, Shell and PostgreSQL in the CECS Ubuntu (Virtual) Desktop Now we will learn how to execute PostgreSQL through terminals. (3) Open a terminal. Launch a terminal window by clicking the Terminal button on the left-edge panel (refer to Figure 1). This gives you access to the command line interface where you can type Unix commands into this terminal window. The program that processes your commands is sometimes called a shell. You will see the following command line interface: u1234567@UbuntuCECS-0000:~$ In the following, we will use the Option 1 as examples. (4) Use the pwd command to print the current working directory on the screen. Type the command pwd (and press Enter) in a terminal window. The pwd command is used to print the current working directory on the screen. When you first log in, the current working directory is your home directory. u1234567@UbuntuCECS-0000:~$ (5) Use the ls command to list directories. Type the command ls (lower case of ’LS’ and press Enter) in a terminal window. The ls command is used to list the files and sub-directories in the current working directory. You should see a list of names of the files and directories which are accessible from your current working directory. 3 (6) Use the cd command to change the current working directory. You have already navigated around the directory structure using the File Browser. You can also move around within the terminal using the following Unix commands. Type the command cd Desktop to move into the Desktop directory. Now the Desktop directory is the current working directory. Use the ls command again to list the files and folders under the current working directory. Type the command cd .. to move to the parent directory of the current directory. Use the pwd command to find out where you have gone. Use cd ~ to reset the current working directory to your home directory as you first log in. Your working directory should always be the one containing the files you are working on at that time. You should navigate to that directory at the beginning of a session. 4 Starting PostgreSQL on the CECS Ubuntu (Vir- tual) Desktop In this course, we will use the database management system (DBMS) PostgreSQL. PostgreSQL has been installed on the CECS Ubuntu (Virtual) Desktop and you can start using PostgreSQL (or psql). (7) Open a terminal and start the PostgreSQL interface by entering psql in your ter- minal. u1234567@UbuntuCECS-0000:~$ psql You may be asked to input your ANU password after typing the above ’psql’. (8) Type q to quit psql and return to the Unix shell. u1234567=> q (9) Type exit to close a terminal. u1234567@UbuntuCECS-0000:~$ exit 4 It’s easy to mix up commands for the shell and psql in a terminal window. Try to always remember which one you are in and the prompts for shell and psql are different: The shell shows you u1234567@UbuntuCECS-0000:~$ Under shell, you can type cd, ls, pwd and other shell commands including psql (to move from shell to psql). The psql shows you u1234567=> Under psql, you can write SQL statements and commands including q (to move from psql back to shell). Note: In case you see u1234567-> instead of u1234567=> , this indicates an unfinished SQL command. Type a semicolon ‘;’ to finish the com- mand and press Enter to execute. 5 SQL Exercise In this optional exercise, we will try to execute some basic SQL statements and com- mands. (10) Open the text editor gedit and enter the following code. The following code will generate a table named friend and insert one row in this table. DROP TABLE IF EXISTS friend; CREATE TABLE friend ( name varchar(20), phone varchar(20), email varchar(50) ); INSERT INTO friend VALUES (‘Ava’, ‘01234567’, ‘u1234567@anu.edu.au’); Save the text file as friend.sql on the Desktop folder. 5 (11) Create the relation schema named friend in psql As the above file friend.sql is stored on the Desktop folder, we need to use cd in the shell to change the current working directory to be Desktop. u1234567@UbuntuCECS-0000:~$ cd ~/Desktop Now we can type ls in the shell to double check that we can find the file friend.sql in the current working directory. u1234567@UbuntuCECS-0000:~$ ls Now we can start PostgreSQL. u1234567@UbuntuCECS-0000:~$ psql You’ll see the command line as follows. u1234567=> Now we can create the table friend in the current database by running the file we just created. u1234567=> i friend.sql If you see an error message “friend.sql: No such file or directory”, it indicates that the current working directory does not contain the file friend.sql. You can either exit psql and change your current working directory or simply provide the full address of the file (e.g., i ~/Desktop/friend.sql ). If you’re successful you should see the following message as the last two lines. CREATE TABLE INSERT 0 1 Now you can query the content in the table friend using the following command (don’t forget the semicolon at the end) u1234567=> SELECT * FROM friend; Output should look like the following table. name | phone | email ——+———-+——————– Ava | 01234567 | u1234567@anu.edu.au (1 row) 6 That’s all for this lab. Please take a screenshot of the above query result and send it to the tutor of your lab session. You can power off the CECS Ubuntu (Virtual) Desktop after this lab and your modification will be stored. 7
