CIS 373-30 SQL 1 EXERCISE

CIS 373-30 SQL 1 EXERCISE
What to do:
Problem: ConstructionCo, pp. 278 – 282, Do problems 1 to 12 from page 278-280
You are given a partially correct Oracle script file: Ch07_ConstructCo_ORA.sql (posted in the same folder).  The script is incomplete because they did not define primary keys and foreign keys when creating the tables.  As shown in class (and also in my video capture), you will create your own script file based on this one by defining the primary keys and foreign keys (refer to the text book for the ERD).
To make it easier for you, I added several SQL Plus commands to redirect the output.  At the beginning of the Ch07_ConstructCo_ORA.sql ,  I added:

spool j:scriptsSQL1.lst
set pagesize 24
set pause on
set linesize 60
and at the very end, I added:
Spool out

The Spool command will redirect the output, in my case, to the J: drive in the scripts folder and the file will be called SQL1.lst  (lst = listing, typical extension for output list).  Make sure you know the path name to redirect the output to.  I would recommend you to use the USB drive; make your path name shorter to avoid typos.  Please also noted that I set the pause ON. It means that you have to hit “Enter” to move to the next page.
How to do:
(1)    Start your Notepad, copy the necessary statements from Ch07_ConstructCo_ORA.sql  with the proper PKs and FKs, save it as  Ch07_SQL_1_Answers.sql.
(2)    Log in Oracle 11g XE
(3)    Start SQL Command Line (SQL Plus) program.
(4)    In SQL Plus, load and execute  Ch07_SQL_1_Answers.sql  (by using the start command in SQL Plus.)
(5)    To answer the questions, you can type your SQL statement directly in SQL Plus.  If it works, copy and paste the statement in your script file Ch07_SQL_1_Answers.sql.  Use comment, e.g. –, or /* …. */ to separate the questions.  The following is the skeleton of your script file:

spool j:scriptsSQL1.lst
set pagesize 24
set pause on
set linesize 60

— Name:  <Your Name>  SQL 1 Homework, pp. 278-280
—
<<<< Based on Ch07_SQL_1_Answers.sql, create the tables with proper PKs and FKs.  Copy all  INSERT statements to populate the tables.  Then you can start answering the questions.  Make sure you have created and populated  the tables correctly before moving forward.  Do it incrementally like I demonstrated in the videos. >>>>

— Problem 1:
select name from student where student_ID = 1234;

—
— Problem 2:
insert into Student Values (13567, “Doe”, “John”, “CIS”);
: : : : :

—  Problem 12:
UPDATE EMP_2
SET …….
WHERE EMP_HIREDATE ….
AND JOB_CODE >= ….. ;

Spool out

After you have finished all problems and created the script file, run the script file in its entirety one last time. When you are finished, submit both Ch07_SQL_1_Answers.sql and the output file SQL1.lst in Blackboard.