Purdue University Fort Wayne Oracle SQL Database Programming Task

Description

1 attachmentsSlide 1 of 1attachment_1attachment_1

Unformatted Attachment Preview

CSC 352/452: DATABASE PROGRAMMING
ASSIGNMENT # 1 (100 Points) Due Date 7/24 11:59pm
Review and Refresh
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)
Primary keys are all underlined, Foreign keys are in italics, and faculty_id & advisor_id are the same.
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 prog1b */
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 prog1c */
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 prog1d */
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

Purchase answer to see full
attachment

Explanation & Answer:
2 pages

User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.

Reviews, comments, and love from our customers and community:

Article Writing

Keep doing what you do, I am really impressed by the work done.

Alexender

Researcher

PowerPoint Presentation

I am speechless…WoW! Thank you so much!

Stacy V.

Part-time student

Dissertation & Thesis

This was a very well-written paper. Great work fast.

M.H.H. Tony

Student

Annotated Bibliography

I love working with this company. You always go above and beyond and exceed my expectations every time.

Francisca N.

Student

Book Report / Review

I received my order wayyyyyyy sooner than I expected. Couldn’t ask for more.

Mary J.

Student

Essay (Any Type)

On time, perfect paper

Prof. Kate (Ph.D)

Student

Case Study

Awesome! Great papers, and early!

Kaylin Green

Student

Proofreading & Editing

Thank you Dr. Rebecca for editing my essays! She completed my task literally in 3 hours. For sure will work with her again, she is great and follows all instructions

Rebecca L.

Researcher

Critical Thinking / Review

Extremely thorough summary, understanding and examples found for social science readings, with edits made as needed and on time. Transparent

Arnold W.

Customer

Coursework

Perfect!

Joshua W.

Student

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>