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