The purpose of the following assignment is to practice the Structured Query Language - SQL statements.
Please create a single script le (.sql), containing all your SQL queries.
Consider the following schema:
S u p p l i e r s ( s i d NUMERIC( 9 , 0 ) PRIMARY KEY,
sname CHAR( 3 0 ) ,
a d d r e s s CHAR( 4 0 ) ) ;
P a r t s ( p i d NUMERIC( 9 , 0 ) PRIMARY KEY, pname CHAR( 4 0 ) ,
c o l o r CHAR( 1 5 ) ) ;
C a t a l o g ( s i d NUMERIC( 9 , 0 ) ,
p i d NUMERIC( 9 , 0 ) ,
c o s t NUMERIC( 1 0 , 2 ) ,
PRIMARY KEY( s i d , p i d ) ,
FOREIGN KEY( s i d ) REFERENCES S u p p l i e r s ,
FOREIGN KEY( p i d ) REFERENCES P a r t s ) ;
In order to create tables and load initial data please download the le lab5.zip from iLearn to you home directory. In your home directory unpack the .zip le:
u n z i p l a b 5 . z i p
Open le startPostgreSQL.sh and change the port number to a value in the range [1024; 32768]. Execute source ./startPostgreSQL.sh and make sure the server is running by typing pg ctl status. If it has not started change the port to a di erent value and try again. Make sure to check
1
/tmp/$USERNAME/log le for any error messages. Once the server is up and running execute:
s o u r c e . / c r e a t e P o s t g r e D B . sh
cp . t x t
/tmp/$USERNAME/myDB/ data /
p s q l h
l o c a l h o s t p $PORT $USERNAME
DB < c h a p t e r 5 . s q l
The second command from above will copy the .txt les in lab5.zip inside the data folder of your database. These les need to exist in that location to be used by the next command that executes chapter5.sql. The .sql script will initialize the tables and insert data into them so you can run your queries on top of a toy dataset.
Create a script le with the commands in SQL to answer the following queries:
Find the pid of parts with cost lower than 10 Find the name of parts with cost lower than 10
Find the address of the suppliers who supply \Fire Hydrant Cap" Find the name of the suppliers who supply green parts
For each supplier, list the supplier’s name along with all parts’ name that it supply
Create queries.sql and submit the le at the end of the lab. DO NOT forget to execute source ./stopPostgreDB.sh to stop the server once you are done.
2
