Create a table called temp-student with the columns

Assignment Help Database Management System
Reference no: EM131321826

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

Reference no: EM131321826

Member information in the member register

To maintain member records, the library has hired an executive who maintains member information in the Member register. The details stored in this register consist of Member

Create naming conventions for each entity and attributes

Create naming conventions for each entity and attributes. Propose an efficient data structure that may hold the tour operator's data using a normalization process. Describe

Design and present a data collection table

Design and present a data collection table that you will use to collect and monitor data pertaining to the second of two simulations when it starts. The data that you will c

Creating a meaningful statistical data analysis for dataset

Creating a meaningful statistical data analysis for a dataset, Case Study: You work for a health insurance company that provides medical coverage for their members. You are t

Design and create a database for an airline

CSG1207/CSI5135 Systems and Database Design. You are required to design and create a database for an airline. The database must contain details of the airline's planes, fligh

Develop a domain model for the case study

The objective of the assignment is to apply and consolidate skills acquired in the requirement and analysis disciplines through analysis of a simple case study, and to expre

Design and implement the database-best home real estate

you are to design and implement the database of Best Home real estate that you have modelled in assignment-1 and a series of SQL queries to reflect the business logic of it

Normalize the given orders table to three-nf

For this assignment, you will normalize the below Orders table to 3NF. Make sure to identify all the entities and their attributes, especially if you make any new items (suc

Reviews

Write a Review

 
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd