Homework 6 in WORD

arizonahoopleΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 11 μήνες)

384 εμφανίσεις

CMPS 341 Database Systems

Spring 2002

Homework 6: SQL homework with PostgreSQL

Due Date: 05/01/2002


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.

Display your department man
ager’s SSN, first name, last name, and date of birth.

4.

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

table.

5.

Find those (by first name and last name and their department name) who work on the
same projects as you do.

6.

Find

the first and last name of those who are directly supervised by the same
supervisor as you are.


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 every
thing in a file named DB
-
HW6.txt.


First use PowerTerm to start a telnet session to either server1 or server2. Actually you can start a telnet
using whatever telnet program you have or prefer.


After logging on successfully, enter



script DB
-
HW6.txt


Th
e system responds:



Script started, output file is DB
-
HW6.txt


Now the system will log everything you enter and everything that is displayed on screen. So now you
are ready to connect to the Company DB.



psql

h server8

d company

U cs34102grp2


(Assumi
ng you are team 2.)


Now you can follow the steps specified above.


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



exit


Telnet to server1 or server2 and print D
B
-
HW6.txt using


lpr DB
-
HW6.txt