LEARN MORE...

INFS 3770 Intermediate Queries & Script Files Using Oracle Project

Content type

User Generated

Subject

Information Technology

Course

INFS 3770

Type

Other

Uploaded By

zhevfu_zw

Pages

Showing Page:

1/17

INFS 3770 – Project #7

Intermediate Queries & Script Files – Using Oracle

45 Points Possible

For this project, you will need to write the SQL to solve 18 query problems defined in the Project 7 – Case Questions with

Screen Shots.pdf file. You will build a single script file named yourLastName_P7Script.txt that contains all your SQL and

SQL/PL statement necessary to solve and format the 18 queries. Output should be directed to a single .txt file named

yourLastName_P7Spool.txt. You will need to submit both files for grading.

** DO NOT INCLUDE THESE SQL CREATE TABLE AND INSERT INTO STATEMENTS IN YOUR SOLUTION. ** Your solution

file should ONLY contain the SQL statements needed to solve the 18 query problems of the assignment and the PL

statements to format the result sets.

Step 3) Write the SQL code for the queries needed to generate a result set that meets the assignment problem

specification -- as defined in the accompanying document. Refer back to Hands-On Exercise #8 – and following the same

prescribed template that was demonstrated in Hands-On Exercise #8.

Step 4) Formatted output is required!

• Write PL statements to create Titles that identify the top and bottom your result sets.

• Define the page size and line size of your output.

• Format the display of the columns in your result sets. All numbers should be formatted: o currency to display as

currency,

o other numbers to display with a fixed number of decimal places. All numeric data should be aligned to the

decimal place with a fixed number of digits after the decimal point.

• The output of each result set should fit each row to one line of output without it wrapping to multiple lines.

• Reduce the width of text columns by truncating the text output. Remember that COLUMN statements persist –

and thus only need to be defined once for each column – not once for each query.

• Place all COLUMN statements together near the top of your script file make it easy to grade your SQL solutions.

(Be sure to place a CLEAR COLUMNS statement at the top of your code).

Test your queries individually and then test your script file to ensure that it executes error free. Add the Spool statements

to generate and capture the spooled output and post both the script file and spool file (appropriately named) to the

Blackboard course link.

Project Deliverables:

1. (36 points) Correct solutions for 18 queries. (2 points each, partial credit for any query with a reasonable attempt.)

2. (3 points) Properly named script file that is well organized (as prescribed above) to make it easy to find each query

problem attempted. The script file should only contain SQL code, comments and PL statements.

3. (3 points) Properly named file with captured spooled output generated by executing script file.

4. (3 points) Well formatted data in generated result sets.

Showing Page:

2/17

P7myFamilyVideo.sql

script file to create oracle tables for P7 myFamilyVideo database that will be used to answer the questions

/*remove comment on the drop table statements to reload the tables*/

--Drop table DetailRental;

--Drop table Rental;

--Drop table Membership;

--Drop table Video;

--Drop table Movie;

--Drop table Price;

--Commit;

CREATE TABLE PRICE (

PRICE_CODE NUMBER(2,0) PRIMARY KEY,

PRICE_DESCRIPTION VARCHAR2(20) NOT NULL ,

PRICE_RENTFEE NUMBER(5,2),

PRICE_DAILYLATEFEE NUMBER(5,2)

);

commit;

CREATE TABLE MOVIE (

MOVIE_NUM NUMBER(8,0) PRIMARY KEY,

MOVIE_TITLE VARCHAR2(75) NOT NULL,

MOVIE_YEAR NUMBER(4,0),

MOVIE_COST NUMBER(5,2),

MOVIE_GENRE VARCHAR2(50),

PRICE_CODE NUMBER(2,0) REFERENCES PRICE

);

commit;

CREATE TABLE VIDEO (

VID_NUM NUMBER(8,0) PRIMARY KEY,

VID_INDATE DATE,

MOVIE_NUM NUMBER(8,0) REFERENCES MOVIE

);

commit;

CREATE TABLE MEMBERSHIP (

MEM_NUM NUMBER(8,0) PRIMARY KEY,

MEM_FNAME VARCHAR2(30) NOT NULL,

MEM_LNAME VARCHAR2(30) NOT NULL,

MEM_STREET VARCHAR2(120),

MEM_CITY VARCHAR2(50),

MEM_STATE CHAR(2),

MEM_ZIP CHAR(5),

MEM_BALANCE NUMBER(10,2)

);

commit;

CREATE TABLE RENTAL (

RENT_NUM NUMBER(8,0) PRIMARY KEY,

RENT_DATE DATE DEFAULT SYSDATE,

MEM_NUM NUMBER(8,0) REFERENCES MEMBERSHIP

);

commit;

Showing Page:

3/17

End of Preview - Want to read all 17 pages?

Access Now

Unformatted Attachment Preview

INFS 3770 – Project #7 Intermediate Queries & Script Files – Using Oracle 45 Points Possible For this project, you will need to write the SQL to solve 18 query problems defined in the Project 7 – Case Questions with Screen Shots.pdf file. You will build a single script file named yourLastName_P7Script.txt that contains all your SQL and SQL/PL statement necessary to solve and format the 18 queries. Output should be directed to a single .txt file named yourLastName_P7Spool.txt. You will need to submit both files for grading. ** DO NOT INCLUDE THESE SQL CREATE TABLE AND INSERT INTO STATEMENTS IN YOUR SOLUTION. ** Your solution file should ONLY contain the SQL statements needed to solve the 18 query problems of the assignment and the PL statements to format the result sets. Step 3) Write the SQL code for the queries needed to generate a result set that meets the assignment problem specification -- as defined in the accompanying document. Refer back to Hands-On Exercise #8 – and following the same prescribed template that was demonstrated in Hands-On Exercise #8. Step 4) Formatted output is required! • Write PL statements to create Titles that identify the top and bottom your result sets. • Define the page size and line size of your output. • Format the display of the columns in your result sets. All numbers should be formatted: o currency to display as currency, o other numbers to display with a fixed number of decimal places. All numeric data should be aligned to t ...
Purchase document to see full attachment

Tags: oracle Intermediate Queries Script Files SQL programming