Access over 40 Millions of academic & study documents

INFS 3770 Intermediate Queries & Script Files Using Oracle Project

Content type
User Generated
Subject
Information Technology
Type
Other
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.

Sign up to view the full document!

lock_open Sign Up
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;

Sign up to view the full document!

lock_open Sign Up
Showing Page:
3/17

Sign up to view the full document!

lock_open Sign Up
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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Indeed
4.5
Sitejabber
4.4