Lab #1

foreheadsobstinacyΛογισμικό & κατασκευή λογ/κού

15 Αυγ 2012 (πριν από 5 χρόνια)

252 εμφανίσεις

INFO
-
340
Lab #2

Introduction to PostgreSQL


Objectives



To review the software infrastructure
used in this class (tomcat, jsp, etc.)
so
that you can sketch a diagram of its major components and describe them



To learn how
to
submit SQL queries to the Postgr
eSQL server from the
command line and from NetBeans 4.0



To learn how to run PostgreSQL scripts to load and query databases



To learn some basic SQL commands


These

activities will
teach you all you need for Assignment #2.



1)

psql command shell
:

The psql c
ommand shell allows you to submit SQL
queries for querying and updating databases. To use the shell
,

follow these
steps:

a.

Use
Secure Shell to

log in to

your Linux account

b.

At the command line type psql <DB_NAME>, where DB_NAME is the
name of the database t
hat you will connect to.
DB_NAME will be the
user name that you used to log into Linux.

c.

It should prompt you for a password, which is
the last 5 digits of your
student ID

+ “temp” (e.g. 52634temp)
.

d.

You should now be in the psql command shell. Now, you sh
ould
change your password by typing this command:

alter user <USER_NAME> with password '<NEW_PASSWORD>';


(don’t forget the semi
-
colon)


2)

Practice with the shell
. Using the shell,
do the following:

a.

Create a table for storing the
names

and
ages

for
sailors


b.

Insert three records into the table

c.

Write an SQL query to select all sailors that are older that 30 years old


The information you need for these tasks is available here:

INFO
-
340 Course Page


Programming Examples


PostgreSQL


Quick Guide



Complete information about PosgreSQL

and SQL command syntax

is
available here:

http://w
ww.postgresql.org/docs/7.4/static/index.html


3)

Load data into tables with a psql script
. Do the following:

a.

Examine the script

INFO
-
340
HP


Programming Examples


PosgreSQL


Importing Data


b.

Now, adapt this script to load the dreamhome data tables into your
database. The data for dreamhome is found here:

http://courses.washington.edu/info3
40/data/dreamhome/



4)

Connecting to databases within NetBeans 4.0
. Do the following:

a.

Install the PostgreSQL driver into NetBeans. The driver is located here:

http://jdbc.postgresql.org/download.htm
l

Download this version:
pg74.216.jdbc3.jar


To install it,
navigate to: Runtime tab, Databases, Drivers and then right click on
Drivers. Then add the driver.


For more information, see
http://students.washington.edu/~giffordc/INFO340/piki.cgi

Under Post
greSQL

b.

Navigate to the PostgreSQL driver by clicking on the runtime tab, then
click on databases, and finally drivers


c.

Now, right click on the PostgreSQL driver and select Connect using…
and configure the database url:

jdbc:postgresql://<HOST>:<PORT>/<DB>


HOST ==
linux.ischooldev
.washington.edu

PORT ==

5432

DB == your login name


The string should look something like:


jdbc:postgresql://

linux.ischooldev.washington.edu
:
5432/
username


d.

Entry

your user name
(your login name) a
nd password

e.

Select the public s
chema and press OK

f.

Now expand the connection and the folder Tables. You should see the
Sailors table that you created earlier.

g.

Right click the folder Tables and select Execute Command and type an
SQL command such as
select * from Sailor
.

h.

You can use thi
s GUI to execute SQL commands to databases.

i.

You can navigate the folder to see tables and their attributes