Workshop 3-4: Introducing the Postgres Database
Date: September 27th, 2007
Place:409
Time:14:30 – 18:00
Structured Query Language
SQL allows you to access a database
SQL is an ANSI standard computer language
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert new records in a database
SQL can delete records from a database
SQL can update records in a database
SQL is easy to learn
source: www.w3schools.com
Open source SQL database server originating from the
University of California at Berkeley
-data storage
psql
Creating Table
Data Types
integer: a whole number: 1,5,100
float: contains fractional part: 0.33, 9.888
numeric, decimal (p,s)
double: exponential number: 312E+7
CREATE TABLE friend (nr INTEGER PRIMARY KEY,
firstname CHAR(15), lastname CHAR(20), city CHAR(10),
age INTEGER);
nr firstname lastname city age
1 Mike Smith Helsinki 20
Selecting (to view data in the table)
SELECT * FROM friend;
Adding Data
INSERT INTO friend VALUES (1, ‘Mike’, ‘Smith’,‘Helsinki’,
20);
SELECT * FROM friend WHERE age = 23;
SELECT city, age FROM friend WHERE age >= 21;
Deleting Data
DELETE FROM friend WHERE lastname = 'Barnes';
Updating Data
UPDATE friend SET age = 20 WHERE firstname = 'Mike';
Ordering
SELECT * FROM friend ORDER BY name; (ascending by
default)
SELECT * FROM friend ORDER BY age DESC;
SELECT * FROM friend WHERE age >= 21 ORDER BY firstname;
AND
SELECT * FROM friend WHERE age >= 22 AND age <= 25;
OR
SELECT * FROM friend WHERE age = 40 OR age = 25;
DISTINCT
SELECT DISTINCT on (AGE) AGE, CITY FROM FRIENDS (NOTE:
FIRST COLUMN WITH DISTINCT!)
Retrieving Information from 2 Tables
first need to create a second table to join with table
friend:
create table pets (ownernr int, species varchar(15),
petname varchar(15), city varchar(15));
enter data into table pets!
table: friend
nr table: pets
firstname
lastname ownernr
city species
age petname
Viewing the pets of Mike:
SELECT firstname, species, petname FROM friend, pets where nr=ownernr and
firstname=’Mike’;
Functions
AVG(COLUMN)
SELECT AVG(AGE) FROM FRIEND;
SELECT AVG(AGE), CITY FROM FRIEND GROUP BY CITY;
SUM(COLUMN)
SELECT SUM(AGE) FROM FRIEND;
COUNT(COLUMN)
SELECT COUNT(AGE) FROM FRIEND;
MAX(COLUMN)
SELECT MAX(AGE) FROM FRIEND;
Deleting all information from table
TRUNCATE TABLE FRIEND;
Deleting Table
DROP TABLE friend;
List of tables
select * from pg_tables where tableowner=’userid’;