Web Programming

Copyright © 2018
Yaodong Bi

This Web page is designed for students at the University of Scranton, who either are taking Database Systems course or are interested in using PostgreSQL for their projects.

PostgreSQL at the University

PostgreSQL is a open-source relational database management system and a PostgreSQL server has been installed on the department DB server. Students may have their own databases created on this server and access the databases through the interactive user interface, psql, of Postgresql.

Using psql via freebsd1 or freebsd2

Normally a user does not log in to our database server directly to interact with the PostgreSQL. The user must use a remote connection to the PostgreSQL. Currently the PostgreSQL server is set up to accept connections only from department user servers and web server.. You can connect to your database from freebsd1 or freebsd2 using the psql client application.

To use psql, first log in to either freebsd1 or freebsd2. Then enter:

psql -h lovelace.cs.scranton.edu -d your_db_name -U your_user_name -W

Here, option -h specifies the host on which PostgreSQL server is running, -d the database you want to connect to, and -U your postgresql user name. -W will show password prompt. Note that your postgresql user name is the same as your department account user name.

If the psql command is executed successfully, the following should be displayed,

Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

your_db_name=#

Assume this is a new database, then it should be empty now. The next step is to create tables.

Creating Tables

You may create tables in two different ways. The first is to create tables interactively.

Interactive
You need to type in SQL statements word by word. For example, to create a student table with three attributes, type in the following:

mydb=# create table student (
mydb=# id        char(5),
mydb=# name      varchar(20)
mydb=# address   varchar(30),
mydb=# primary key (id)
mydb=# );

For availabe data types in PostgreSQL click here.

You may type in a SQL statement on multiple lines. The statement is not executed until a simicolon(;) is entered. After a simicolon is entered, the following should be displayed.

CREATE
mydb=#

Now you can execute the \d command to list the tables in the database.

mydb=# \d

The following should be displayed. Owner will be replaced with your user ID.

    List of relations
   Name   | Type | Owner
----------+-------+-------
student   | table | toc2
(1 rows)

One disadvantage of the interactive method is that you have to type perfectly, without a single typing error, since it is very hard to correct or change what you have typed in.

Using Script Files

A script file can be created using any text editor and stored in any directory on freebsd1.cs.uofs.edu or freebsd2cs.uofs.edu. This should be done outside the psql client application.

For example, use vi (or pico or any text editor) to create a file and name it table.sql with the following statements as its contents.

create table student (
id        char(5),
name      varchar(20),
address   varchar(30),
primary key (id)
);
create table faculty (
id char(5),
name varchar(20),
office varchar(20),
phone varchar(11),
primary key (id)
);
create table course (
id char(5),
title varchar(30),
instructor char(5),
primary key (id),
foreign key (instructor) references faculty(id)
);
create table stakingc (
sid char(5),
cid char(5),
grade float4,
primary key (sid, cid),
foreign key (sid) references student(id),
foreign key (cid) references course(id)
);

After the file is created, change to the directory where the file is stored and then use psql to connect to your database. At the prompt enter the following to execute the script,

\i table.sql

The following will be displayed.

     CREATE
     CREATE
     CREATE
     CREATE
     mydb=#

You may use command \d to list tables in the database. it would list

Now your tables have been successfully created in your mydb database. Our next step is to enter data into the tables.

Insert Data into Tables

To insert data into a table, you may insert one entry at a time by entering a INSERT statement or you can put multiple INSERT statements in a script file and then use \i to execute the script. The following shows both methods.

Enter One Entry at a Time

First use psql to connect to your database (mydb):

psql -h lovelace.cs.scranton.edu -d your_db_name -U yourusername

then at the psql prompt enter:

INSERT INTO "student" VALUES ('S1001','John','100 Monroe, Scranton');

You may enter the statement on multiple lines, it would not be executed until a semicolon is entered.

Enter Data in Batch Mode

To enter data in a batch mode, put INSERT statements (the following statements) into a file (let's name it data.sql),
connect to your database (mydb):

psql -h lovelace.cs.scranton.edu -d your_db_name -U yourusername

INSERT INTO "student" VALUES ('S1002','Tony','200 Adams, Scranton');
INSERT INTO "student" VALUES ('S1003','Alex','209 WiLlow, Dalton');
INSERT INTO "student" VALUES ('S1010','Some Name','AMH 118');
INSERT INTO "student" VALUES ('S1020','From the WWWW','WWW@Home');
INSERT INTO "student" VALUES ('F1040','G.W. Bush','The Whitel House');
INSERT INTO "student" VALUES ('S1050','Info System','amh 118-2001');
INSERT INTO "student" VALUES ('S2000','Mike Beckish','STT 414');

INSERT INTO "faculty" VALUES ('F3001','Yaodong Bi','STT 478','6108');
INSERT INTO "faculty" VALUES ('F3002','Dennis Martin','STT 477','6107');
INSERT INTO "faculty" VALUES ('F3003','Paul Jackowitz','STT 479','6109');
INSERT INTO "faculty" VALUES ('F3040','Dick Cheney','The White House','xxx-xxx-xxx');
INSERT INTO "faculty" VALUES ('F9999','TBA','Registrar','941-6108');
INSERT INTO "faculty" VALUES ('F0000','TBA','Registrar','941-6108');

INSERT INTO "course" VALUES ('C2001','Programming in Java','F3001');
INSERT INTO "course" VALUES ('C2002','Operating Systems','F3002');
INSERT INTO "course" VALUES ('C2003','Computer Archiecture','F3003');
INSERT INTO "course" VALUES ('C2004','Software Engineering','F3001');
INSERT INTO "course" VALUES ('C2020','Prog the WWW','F3001');
INSERT INTO "course" VALUES ('C2030','Prog the Web','F3001');

INSERT INTO "stakingc" VALUES ('S1001','C2001',3.5);
INSERT INTO "stakingc" VALUES ('S1001','C2002',3.9);
INSERT INTO "stakingc" VALUES ('S1002','C2003',3.2);
INSERT INTO "stakingc" VALUES ('S1003','C2002',3.9);
INSERT INTO "stakingc" VALUES ('S1003','C2003',4);
INSERT INTO "stakingc" VALUES ('S1001','C2004',NULL);
INSERT INTO "stakingc" VALUES ('S1002','C2001',3.99);

then execute the script using \i at the psql prompt:

\i data.sql

The above data will be inserted into the database in a batch.

Next section is to delete data from your database.

Delete Data from Tables

The SQL DELETE statement may be used to delete rows of data from a table. The syntax of DELETE is:

DELETE FROM table [WHERE condition];

For example, suppose table stakingc has the following tuples,

mydb=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1002 | C2003 |   3.2
S1003 | C2002 |   3.9
S1003 | C2003 |     4
S1001 | C2004 |
S1002 | C2001 | 3.99
(7 rows)

and the following DELETE is executed,

mydb=# delete from stakingc where sid='S1002';

then, the table would have the following tuples left.

uofsis=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1003 | C2002 |   3.9
S1003 | C2003 |     4
S1001 | C2004 |
(5 rows)

Of course, you may put multiple DELETE statements in a script file and use \i command to execute all the DELETE in a batch.

Update Data in Tables

You can use the UPDATE statement to replace values of columns (attributes) in a table. The syntax of UPDATE is as follows:

UPDATE table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

For example, if the following UPDATE is executed on the above table stakingc

uofsis=# UPDATE stakingc SET grade=3.99 where sid='S1001' and cid='C2004';

after the statement, the stakingc table would look like:

uofsis=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1001 | C2004 | 3.99
S1003 | C2002 |   3.9
S1003 | C2003 |     4
(5 rows)

Query Data from Database

Basic SQL Queries

The syntax of basic SQL queries:

    SELECT <attribute list>
    FROM   <table list>
    FROM   <condition>

Example: retrieve the name, office, and phone of faculty whose id is 'F3001'. The SELECT statement for this query can be as follows:

    SELECT name, office, phone
    FROM   faculty
    FROM   id = 'F3001';

Enter the SELECT statement in psql:

uofsis=# SELECT name, office, phone
uofsis-# FROM faculty
uofsis-# WHERE id = 'F3001';

The following result is displayed:

name | office | phone
------------+---------+-------
Yaodong Bi | STT 478 | 6108
(1 row)

SQL Queries with Multiple Tables

When an SQL statement need information from more than one table, the WHERE clause of the SELECT statement may be used to specify how the tables are to be JOINed together.

Example: retrieve for all student the student name, course title and the grade the student received for the course.

    SELECT s.name, c.title, stc.grade
    FROM   student s, stakingc stc, course c
    WHERE s.id = stc.sid AND stc.cid = c.id;

Enter the SELECT statement in psql:

uofsis=# SELECT s.name, c.title, stc.grade
uofsis-# FROM student s, stakingc stc, course c
uofsis-# WHERE s.id = stc.sid AND stc.cid = c.id;

The following result would be displayed:

PostgreSQL On-Line Resources

Home page of PostgreSQL

PostgreSQL: Introduction and Concepts by Bruce Momjian. You may read the book online. It is the most authoritive book for PostgreSQL.