340 Lab #3
Introduction to PostgreSQL
To review the software infrastructure
used in this class (tomcat, jsp, etc.)
that you can sketch a diagram of its major components and describe them
To learn how
submit SQL queries to the Postgr
eSQL server from the
command line and from NetBeans
To learn how to run PostgreSQL scripts to load and query databases
To learn some basic SQL commands
activities will teach the basic skills needed for Assignment #2.
psql command shell
: The psql
command shell allows you to submit SQL
queries for querying and updating databases. To use the shell, follow these
Use SSH to log into the iSchool Linux server,
At the command line type
, where DB_NAME is the
name of the database that
you will connect to. For the iSchool server,
your database name is your iSchool username.
psql will prompt you for a password. The default database password is
the first 5 digits of your
+ ‘temp’) (e.g. ‘12345temp’)
You should now be in the p
sql command shell. If you didn’t change
your PostgreSQL password in Lab #1, you should do so now. To change
your passwprd, enter the following at the psql prompt:
alter user USER_NAME with password 'NEW_PASSWORD';
(don’t forget the semi
e with the psql shell
. Using the psql shell, try out the following:
Create a table for storing the
Make up some sailor names and ages, and insert three records into the
Write and execute an SQL query to select all sailors
that are older that
30 years old
The information you need for these tasks is available here:
340 Course Page
Load data into tables with a psql script
Examine the script
Now, adapt this script to load the dreamhome data tables into your
database. The data for dreamhome is found here:
Connect to the database with NetBeans
Install the PostgreSQL driver into NetBeans. The driver is located here:
Download this ver
To install it,
navigate to: Runtime tab, Databases, Drivers and then right click on
Drivers. Then add the driver.
Navigate to the PostgreSQL driver by clicking on the Runtime tab, then
Databases, then Drivers
Now, right click o
n the PostgreSQL driver you just installed and select
Configure the database connecting as follows:
DB == your login name
The string should look some
NetBeans cannot directly connect to the database from off
If you wish to connect to the database from outside the network, you must
first log into the VPN.
r user name (your login name) a
Select the “public” schema and press OK
Expand the new connection and the folder called Tables. You should
see the Sailors table that you created earlier.
Right click the folder Tables and select
some SQL commands such as
select * from Sailor
. Click the
Run button to execute your queries.
You can use this GUI to execute SQL commands to databases.
You can navigate the folder to see tables and their attributes
Spend a few minutes viewing a
nd editing your tables.
5) Exporting your database
Sometimes we might want to transfer our database from one server to another.
We can do this by exporting our database file from
utility allows us to convert an existing database in
to a sequence of SQL
commands that can be used to recreate the database.
a. Return to the linux command prompt (not the psql prompt)
b. Enter the following command:
pg_dump DATABASE_NAME > lab3db.sql
character copies the output of pg_dump into a
The file now contains the SQL needed to recreate the
c. You can now use the exported file to recreate your databases, as you
did in Step 2. For instructions on how to export different types of data,
the linux prompt.
What to turn in
directory in your current NetBeans project. Create a new
file in that directory, and paste the contents of
directory (don’t worry about formatting). Alternately, you can link
lab3db.sql from the page. In the top
, create a link to your new lab
file. Deploy the WAR file to the server.