CSC452 - DATABASE PROGRAMMING - ASSIGNMENT # 1  - Solved

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                      /* 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)    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                      /* get into sqlplus */ 

SQL> start prog1d (or @prog1d)  /* execute prog1a *