Reference no: EM13771279

Purpose:

This laboratory provides practice in the use of SQL commands to create simple reports in an Oracle database.

Procedure:

Using your assigned user name, password, and host string, log in to Oracle SQL*Plus. Collect yourdialog. Be sure to include each of the following SQL scripts in your report. Follow the instructor's instruction concerning any additional required items, any needed sign-offs, and the due date of this report.

Use SQL to create the following reports.

1. In order to fit the output on the page setline size to 100 and the page size to 50. Generatea script file named customer_report.sql that displays the name, street, city, state, and zip code for each customer. Concatenate the city, state and zip code for each customer into a single expression. Insert a comma between the city and state data. Execute this script file.

2. Copy the customer_report.sql file to the customer_report2.sql file. Create a view within this customer_report2.sqlscript file. This view named customer_reportshould display the attributes displayed in item #1 above. The columns names should be CUST_NAME, CUST_STREET_ADDRESS, and CUST_CITY_STATE_ZIP. This script should display the contents of this view. Execute this script file to create and display this view.

3. Developthecustomer_report_format.sqlscript file that removes old headings and creates the column headings in the customer_reportview to CUSTOMER NAME, CUSTOMER ADDRESS, and CUSTOMER CITY/STATE/ZIP. Be sure to place the word "CUSTOMER" on the first line and the remaining words are displayed on the second line. Be sure that the script file displays all the data in the customer_reportview. Execute this script file.

4. Copy the customer_report_format.sql file to the customer_report_format2.sql file. Change the customer_report_format2.sql script file so that the format of the CUSTOMER_NAME column is 35 characters wide. Execute the script file.

5. Generate a representative_report.sqlscript file. Be sure that this script file contains the representative_report view that displays the sales representative full name as first name, space, and last name (named REP_NAME), his/her total commission (named REP_COMM), and his/her commission rate (named REP_RATE). The sales representative name should be displayed as last name, comma, space, and first name. Include a statement to display the view. Execute this script file to create and display the view.

6. Develop a script file named representative_report_format.sql that formats the data from the representative_reportview. The total commission should appear as currency and the commission rate should appear with two decimal places and a leading zero in front of the decimal point. Modify the column headings so that "Sales Rep." and "Name" are on separate lines, "Total" and "Commission" are on separate lines, and "Commission" and "Rate" are on separate lines. The final command should display the complete view. Execute this script file.

Databse Schema

Name                 Null?    Type

----------------------------------------------------- -------- ------------------------------------

CUST_ID              NOT NULL CHAR(4)

CUST_NAME          VARCHAR2(30)

CUST_STREET       VARCHAR2(20)

CUST_CITY          VARCHAR2(20)

CUST_STATE        CHAR(2)

CUST_ZIP           CHAR(5)

CUST_BALANCE   NUMBER(8,2)

CUST_LIMIT      NUMBER(8,2)

REP_ID           CHAR(3)

 

SQL> DESCRIBE REP;

 Name        Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 REP_ID        NOT NULL CHAR(3)

 REP_LNAME   VARCHAR2(20)

 REP_FNAME   VARCHAR2(15)

 REP_STREET   VARCHAR2(20)

 REP_CITY       VARCHAR2(15)

 REP_STATE      CHAR(2)

 REP_ZIP           CHAR(5)

 REP_COMM      NUMBER(7,2)

 REP_RATE       NUMBER(3,2)