MS Word

outstandingmaskData Management

Nov 29, 2012 (4 years and 6 months ago)

141 views

CMPS 341 Database Systems

Homework 5
: SQL homework with PostgreSQL


This homework is to use
PostgreSQL

to run SQL statements on the
Company

database.


1.

Insert yourself as an employee.
Do NOT enter your real social security number
. You are
only required to
use your real first and last name for the
fname
and
lname

attributes, and you
may make up data for all other attributes.

2.

List all the attributes of the Employee table of yourself using your SSN.

3.

Add a new project controlled by department 4.

4.

Retrieve the n
ames
and addresses
of department managers who
do NOT
work on any project
.

5.

Choose at least two projects you like to work on and enter them into the
Works_on

table.

6.

Find those (by first name and last name and their department name) who work on
at least one
project the same

as you do, assuming you only know your SSN

and you cannot use project
numbers you work on explicitly
.

7.

Find the first and last name of those who are directly supervised by the same supervisor as you
are. Assuming you only know your own SSN.


To save your SQL statements and the results returned by PostgreSQL, you need to use the
script

command. Here is how it works. Let’s say you want to save e
verything in a file named DB
-
HW5
.txt.


First use PowerTerm

(or any telnet program)

to start a telne
t session to either server1 or server2.


After logging on successfully, enter



script DB
-
HW5
.txt


The system responds:



Scrip
t started, output file is DB
-
HW5
.txt


Now the system will log everything you enter
(including backspaces)
and everything that is

displayed
on screen. So now you are ready to connect to the Company DB.



psql

h server8

d company

U cs34105
grp2


(Assuming you are
in
team 2.)


Now you can answer all the queries
specified above.


When you are all done, exit from PostgreSQL and at t
he OS prompt, enter the following to stop
scripting
and terminate the telnet session
.



exit


Now the DB
-
HW5
.txt should contain all your input and output by the system. Remove all
extraneous
characters such as backspaces and format the file to make it read
able. You may do the editing and
formatting in MW Word. Submit a hardcopy by the due date.