Modify the following sql command so that the rep_id

ECET 450: Laboratory 5

Purpose:

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

Procedure:

Using Oracle SQL*Plus. Collect your dialog. 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 set line size to 100 and the page size to 50. Generate a 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.sql script file. This view named customer_report should 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. Develop the customer_report_format.sql script file that removes old headings and creates the column headings in the customer_report view 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_report view. 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.sql script 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_report view. 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.

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)