Part a:
Create the following tables by using a script file named prog1a.sql:
STUDENT(student_id, std_name, home_phone, total_credits, gpa, advisor_id)
COURSE(course#, credit_hours, time, location, faculty_id)
ENROLLMENT(student_id, course#, grade)
FACULTY(faculty_id, fac_name, office, salary)
where:
student_id, std_name, advisor_id(faculty_id), office, location and fac_name are strings of maximum length of 25 characters (VARCHAR)
home_phone, and course# are strings of size 12 characters (CHAR)
total_credits, time and credit_hours are integers (NUMBER)
grade, gpa and salary are real numbers, with a maximum decimal of 2 (NUMBER)
Test/run your script file in Oracle Developer or SQL*Plus command line(please make sure you downloaded the client version based on the Oracle Instructions pdf file posted on D2L) as follows:
% sqlplus /* get into sqlplus */ SQL> start prog1a (or @prog1a) /* execute prog1a */
Part b:
Populate each table with at least 15 records of your choice by using a script file named prog1b.sql. The ENROLLMENT table, however, should be populated with at least 30 records of your choice.
Note: You may want to ensure that the queries in Part (4) of this assignment are met, when you populate these tables.
Test/run your script file as follows:
% sqlplus /* get into sqlplus */ SQL> start prog1b (or @prog1b) /* execute prog1a */
Part c:
Display the structure and the contents of each of the above tables by using a script file named prog1c.sql.
Test/run your script file as follows:
% sqlplus /* get into sqlplus */ SQL> start prog1c (or @prog1c) /* execute prog1a */
Part d: (25 Points)
Issue the SQL statements necessary to answer the following queries:
(a) (b) (c)
(d) (e)
Create a
For each faculty list the faculty’s name and the names of his/her student advisees Givethenamesandphonenumbersofstudentswhoarenotenrolledinanycourses
Give the student name and the gpa for the student with the highest gpa than all colleagues with a similar (exact) total number of credit hours Foreachstudentname,listthecoursenumbers(s),thestudenttook,wherethestudentobtainedthelowestgrade
Give the names of faculty who do not advise any students
script file named prog1d.sql for all the above queries.
Test/run
% sqlplus /* get into sqlplus */ SQL> start prog1d (or @prog1d) /* execute prog1a */
your script file as follows:
1
Note:
There are four parts to this assignment; each part may requires you to submit a file. So
please create a folder for this assignment and submit an electronic copy of your solution files of
every question/part, all in one folder zipped and named “LastName HW1” and must be
submitted to your D2L/Assignment 1 Submission page. I will give you one submission locations
on the course web site.
Again: For example, for assignment #1, you need to create a folder named your LastName HW1 under your c: home directory and save the script files prog1a.sql, prog1b.sql, prog1c.sql, and prog1d.sql under this folder. Then zip the folder and then submit the zipped file to your D2L/Assignment 1 Submission page
SUBMIT YOUR HW1 FOLDER AS ZIP FILE TO YOUR D2L ASSIGNMENT 1 SUBMISSION LINK
FOR GRADING. Make sure only one copy submitted.
2
