Homework answers / question archive / CS 166: Lab5 Assignment: Structured Query Language - SQL The purpose of the following assignment is to practice the Structured Query Language - SQL statements

CS 166: Lab5 Assignment: Structured Query Language - SQL The purpose of the following assignment is to practice the Structured Query Language - SQL statements

Computer Science

Share With

CS 166: Lab5 Assignment: Structured Query Language - SQL The purpose of the following assignment is to practice the Structured Query Language - SQL statements. Please create a single script file (.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 ( pi d NUMERIC( 9 , 0 ) PRIMARY KEY, pname CHAR( 4 0 ) , c o l o r CHAR( 1 5 ) ) ; Ca talog ( s i d NUMERIC( 9 , 0 ) , p id NUMERIC( 9 , 0 ) , c o s t NUMERIC( 1 0 , 2 ) , PRIMARY KEY( s i d , pi d ) , FOREIGN KEY( s i d ) REFERENCES S u p p l i e r s , FOREIGN KEY( pi d ) REFERENCES P a r t s ) ; In order to create tables and load initial data please download the file lab5.zip from iLearn to you computer. Unpack the .zip file, you will find three bash files (in .sh format), three text files and a sql script (in .sql format). Create the corresponding files into your assigned lab machine for all of these files. Please be careful about the file extensions. After creating these files, do the following steps: Open file 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 1 to a different value and try again. Make sure to check /tmp/$USER/logfile for any error messages. Once the server is up and running, execute: s o u r c e . / createPostgreDB . sh cp ∗ . t x t /tmp/$USER/myDB/ data / p s q l −h l o c a l h o s t −p $PGPORT $USER” DB” < c h a p t e r 5 . s q l The second command from above will copy the .txt files in lab5.zip inside the data folder of your database. These files 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 file 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 execute the script: p s q l −h l o c a l h o s t −p $PGPORT $USER” DB” < q u e r i e s . s q l Take the screenshot of the output you get after executing the script, submit the queries.sql file and the screenshot at the end of the lab. *** DO NOT forget to execute source ./stopPostgreDB.sh to stop the server once you are done. 2