Database Lab #0
Lab for getting started with PostgreSQL
Goal for this lab: be sure your linux user acct is working, and
also your postgresql database account, and try it out.
Sign in to this class, if you have not already done so.
In the lab, we are using PostgreSQL, an open-source, client-server
database. The server is running on linux.ubishops.ca, and accepts local connections
from any database user (all of you.)
The client can be psql,
which runs in a terminal and allows you to type SQL commands and see
results, or a program that YOU write.
Getting to linux - 2 ways of working
Click Here for software download instructions (show or hide)
If using your own computer, you will have to downloadt the software I am about to mention.
Shahn Nadeau of ITS has made a video of how to do this, and provided these links in it, for me and you.
Using SSH
Since most of your work will be done in a terminal window, SSH will get you to a terminal session on linux.ubishops.ca
- Windows: You need to use the program Putty. Put in your linux username,
and the host, linux.ubishops.ca.
Then a window opens up, and in it asks for your password, on success, you will get the
command prompt. You are now in a terminal session "on linux".
To transfer files, you can use WinSCP.
- Mac-OS (or Linux): Open a terminal. Since your linux user name differs from your Bishops one, Use it where I have written xxx
Type this command:
ssh xxx@linux.ubishops.ca
So you would normally copy queries and results you like into a plain text file,
and at the end of the lab, submit that file to me.
Using X2GO
Use this if you wish a nice remote desktop on linux to work and submit the files you produce "to me".
- Start the application X2GO from either Windows or Mac-OS, and get the "New Session"
dialog.
- Fill in these fields (others are ok)
- Session Name: Linux
- Host: linux.ubishops.ca
- Login:
your linux username (lower case, no digits)
- Change Session type to XFCE (not KDE)
- Click on OK, then click on the session that appears on the right. Then type your password in the dialog that appears, and click OK.
mousepad is the simplist plain text editor on current linux systems.
Initial Setup
- Open a terminal on linux.ubishops.ca (either in X2GO session or ssh linux.ubishops.ca - with Windows, use PuTTY)
- Optional:To change password
(New users, it is what I told you, please change.)
, type
passwd and follow instructions. (You will not see the passwords you type)
Today's work
Today, use psql. (re)open a terminal (or use ssh or putty from somewhere
else to get a terminal on
linux.ubishops.ca) and type:
psql jensen
I have made you a database user. Your database username is the
same as your linux username, and this connects you without requiring a password.
You can now type SQL statements. Here are some examples:
select * from enroll; -- see the contents of a table
select * from student, enroll; -- don't like this? try either of the following:
select * from student s, enroll e where e.linux=s.linux;
select * from student natural join enroll;
To get help on commands, type
\h or use the link to documentation on the course website
For examle, to recall the SELECT syntax, type \h select
use \d to see
tables, \d battles to
see schema of battles, etc.
Use up-arrow to recall previously typed lines, \e to edit last
query.
Schema of the class lists
STUDENT (given, family, linux, email)
ENROLL (linux, class)
COURSES (class, credits, days, time, bldg, roomno, title, prof)
- CS courses from a pre-pandemic year. class is the number part, so CS 307 owould be just 307.
FRIENDS -- One I created during a class session last year,
You may play with it. Your privileges are limited to insert.
To see schema, \d friends
Queries during the lab:
Open a plain text editor (Mousepad, Notepad++...) for pasting queries and results into, along with any comments you may have. Type your name in that file!
Do the following queries, or something similar that appeals to you:
- Insert your information in STUDENT
- ENROLL into at least one CS class (give integer number only)
- Join the two tables
- List all the students with gmail addresses (email like
'%gmail%')
- List names and email of the 307 class
(compare with my list)
- Count how many students are in each class
- List all the 216 students, order by linux
Please save the plain text file with these queries, and results of those with less than
12 rows in the result.
Submit the file:
- It is on your computer
-
- email it to me
- It is on linux (if you usr x2go) preferred
-
In a terminal (on linux) type (supposing the file name is "lab0.txt")
submit cs307 lab0.txt
To get out of a terminal session, type exit
Back to Course page
or Lin Jensen