PostgreSQL workshop UAlbany ASIS&T Dima Kassab

perchmysteriousData Management

Nov 30, 2012 (4 years and 7 months ago)

337 views



PostgreSQL workshop

UAlbany ASIS&T

Dima Kassab

Installation:

Go to http://www.postgresql.org/download/ and
download

the version based on your operating
system.

The documentation explains in details how to install it.

Now let's do our work on my database
server.



Op
en PuTTY: Start
-
> Programs
-
> SSH
-
>PuTTY



Enter the Host Name: dkassab.net



Enter Port: 8999



Then click on Open



Login as: dkassabn



Password:

Connect to the database:



Type in: psql
-
U

dkassabn_
user*
-
d dkassabn_group* (* is your group number)



The
password is user*

To get some information from the database we can use



Select system_function/system_variable.



http://www.postgresql.org/docs/9.2/static/functions
-
info.html



For example, to see who the current user is
, we can issue select current_user;

To g
et help:



Type in
\
h
for help with SQL syntax and

\
?

for help with psql



F
or a specific command type in
\
h command_name. For example,
\
h create table


Now let's create tables in this database. We'll be using the
attached

ER diagram.



Work with your group on
deciding:

o

Data types for each column

o

Constraints (Primary Key, Foreign Key, Check, Not Null, Unique)



Individually, create different tables.



Notice that to create a tabl
e in a simple form
:

CREATE TABLE [ IF NOT EXISTS ]
table_name

( [{
column_name

data_type

[
column_constraint

[ ... ] ]

|
table_constraint
])

where
column_constraint

is:




[ CONSTRAINT
constraint_name

]

{ NOT NULL |


NULL |


CHECK (
expression

) [ NO INHERIT ] |


DEFAULT
default_expr

|


UNIQUE
index_parameters

|


PRIMARY KEY
index_
parameters

|


REFERENCES
reftable

[ (
refcolumn

) ] [ ON DELETE
action

] [ ON UPDATE
action

] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY
IMMEDIATE ]



and
table_constraint

is:


[ CONSTRAINT
constraint_name

]

{ CHECK (
expression

) [ NO INHERIT ] |


UNIQUE (
column_name

[, ... ] )
index_parameters

|


PRIMARY KEY (
column_name

[, ... ] )
index_parameters

|


FOREIGN KEY (
column_name

[, ... ] ) REFERENCES
reftable

[ (
refcolumn

[, ... ] ) ]

[ ON
DELETE
action

] [ ON UPDATE
action

] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY
IMMEDIATE ]




Now let's insert some data in our database



To insert a new row, we use INSERT


INSERT INTO
table_name

[ (
column_name

[, ...] ) ]{ DEFAULT VALUES | VALUES ( {
expression

| D
EFAULT } [, ...] ) [,...] |
query

}



To update some values in the data, we use UPDATE statement.


UPDATE
table_name

SET {
column_name

= {
expression

| DEFAULT } | (
column_name

[, ...]
) = ( {
expression

| DEFAULT } [, ...] ) } [, ...]

[ WHERE
condition
]




To delete data from your table, we use DELETE statement.


DELETE FROM
table


[ WHERE
condition

]




To view the data within tables we use queries: SELECT statement


SELECT [ ALL | DISTINCT] * |
expression

[ AS
output_name

] [, ...]


[ FROM
from_item

[, ...] ]


[ WHERE
condition

]


[ GROUP BY
expression

[, ...] ]


[ HAVING
condition

[, ...] ]




[ { UNION | INTERSECT | EXCEPT } [ ALL ]
select

]


[ ORDER BY
expression

[ ASC | DESC | USING
operator

] [, ...] ]


where
from_item

can be one of
:



table_name

[ [ AS ]
alias

from_item

[ NATURAL ]
join_type

from_item

[ ON
join_condition

| USING (
join_column

[, ...] ) ]













Customer
PK
Customer
_
Id

Organizatio
/
person

Organization
_
Name

First
_
Name

Middle
_
Name

Last
_
Name

Email
_
Address

Login
_
Name

Login
_
Password

Phone
_
Number

Address

City

Zip
_
Code

State

Country
Painting
PK
Painting
_
Code

Title

Artist
_
First
_
Name

Artist
_
Last
_
Name

Style

Buying
_
Price

Selling
_
Price

Description

Available
_
Quantity

URL
Shopping Cart Items
PK
,
FK
1
Painting
_
Code
PK
,
FK
2
Customer
_
Id

Date
_
Added

Quantity
_
Ordered
Order
PK
Order
_
Id
FK
1
Customer
_
Id

Order
_
Status

Order
_
Date
Order Items
PK
Order
_
Item
_
Id
FK
2
Order
_
Id
FK
1
Painting
_
Code
Shipment
PK
Shipment
_
Id
FK
1
Invoice
_
Number

Tracking
_
Number

Shipment
_
Date

Status
Invoice
PK
Invoice
_
Number
FK
1
Order
_
Id

Status

Invoice
_
Date
Shipment Items
PK
,
FK
1
Shipment
_
Id
PK
,
FK
2
Order
_
Item
_
Id

Comments
Payment
PK
Payment
_
Id
FK
1
Invoice
_
Number

Date

Amount
Customer
_
Payment
_
Method
PK
Card
_
Number

Customer
_
Id

Payment
_
Method

Card
_
Holder
_
First
_
Name

Card
_
Holder
_
Last
_
Name

Billing
_
Address

Expiration
_
Date

Identification
_
Number