DATABASE PROGRAMMING ASSIGNMENT # 1 SOLUTION

CONNECT into ORACLE via ORACLE Developer or SQL*PLUS, and perform the following:

Part a:(25 Points)
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: (25 Points)

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: (25 Points)
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

SQL> start prog1c (or  @prog1c)


/* get into sqlplus */

/* execute prog1a */

Part d: (25 Points)
Issue the SQL statements necessary to answer the following queries:
    (a) For each faculty list the faculty’s name and the names of his/her student advisees

    (b) Give the names and phone numbers of students who are not enrolled in any courses

    (c) 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

    (d) For each student name, list the course numbers(s), the student took, where the student obtained the lowest grade

    (e) Give the names of faculty who do not advise any students

Create a script file named prog1d.sql for all the above queries.

Test/run your script file as follows:
    • sqlplus

SQL> start prog1d (or  @prog1d)


/* get into sqlplus */

/* execute prog1a */


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