Assignment: Advanced Relational Database

PROJECT- Advance SQL

Overview:

Using the knowledge garnered thus far, please solve all of the below problems. Please run the attached SQL script file at the SQL*PLUS prompt. This script will create all the tables and other objects needed to solve the questions in Part I, and populate the tables with sample data.

Deliverables:

Combine all your queries into a single SQL script file called XXX_PROJ1.SQL where XXX are your initials, making sure that the script run without errors before submitting it through WebTycho by the due date. Also, provide the results of running your script with the SQL*Plus spool command. Please adhere to the naming convention for naming your file. Include comments or remarks at the start of each question to show the problem #.

Using the attached Student Database Schema, create a view call BUSY_STUDENT that stores the concatenated name (first name and last name), student id and count of classes enrolled in, for all students enrolled in more than 2 classes. Name the columns, FULL_NAME, STUDENT_ID and ENROLL_NUM respectively. (20 points)

Create a table called TEMP_STUDENT with the following columns and constraints: a column STUD_ID for the student ID and is the primary key, a column FIRST_NAME for student first name, a column LAST_NAME for student last name, a column ZIP that is a foreign key to the ZIP column in the ZIPCODE table of the Student Database Schema, and a column REGISTRATION_DATE that is NOT NULL and has a CHECK constraint to restrict the registration date to dates after August 26, 2005. ALL CONSTRAINTS MUST BE NAMED. Both the first_name and last_name columns are required.

B) For the TEMP_STUDENT table created above, write insert statements that violate each of the constraints. Write 3 insert statements that succeed when executed.

Show all the different companies for which students work. Display only companies where more than four students are employed. (10 points)

Determine the highest grade achieved for the midterm for each section.

Display all the sections where classes start at 10:30 A.M.

Write the query to accomplish the following result. The output shows you all the days of the week where sections 83, 86, 107 starts. Note the order of the days.

DAY SECTION_ID
------- ------------------
Mon 107
Tue 86
Wed 83

Select the distinct course costs of all the courses. If the course cost is unknown, substitute a zero. Format the output with a leading $ sign and separate the thousands with a comma. Display two digits after the decimal point. The cost should be in ascending order. The output should look like the following:

COST
-------------
$0.00
$1,000.00