Oracle SQL (note: remember all of the specifications) 1. In Oracle, create a CUS

Question

Oracle SQL (note: remember all of the specifications)

1. In Oracle, create a CUSTOMERS table with the following fields. (You may use varchar2 if you like ) SQL> describe customers Name Null? Type SSN LASTNAME FIRSTNME MIDDLE PHONE CHAR (9) CHAR (30) CHAR (30) CHAR (30) CHAR (10) 2. Write a program hw7.sql that will insert a new customer record into the table, like the following SQL> start a :hw7 -- New Customer Program -- Please enter the customer'3 SOCIAL SECURITY NUMBER: 837465538 Please enter the customer'3 LAST NAME koch Please enter the customer's FIRST NAME: george please enter the customer' 3 MIDDLE NAME: will Please enter the customer' s PHONE: 5629851111 *- The following record has been updated: Social Security Number: 837-46-5538 Name Koch, George W. Phone: (562) 985-1111 Specifications: 1. The system skips 1 line and displays the New Customer Program title 2. The system prompts for social security number, last name, first name, middle name, and phone of the new customer. Notice that the user can enter in lower case 3. The system inserts the new record into the CUSTOMERS table 4. The system displays the new record (from the CUSTOMERS table) for confirmation Note that the data should be displayed with appropriate format Spool your output to hw7.txt. Run the program until there is no error. Test your program by entering at least 2 customers (so I can see your query pulls up the correct record). Print a copy of hw7.sql and hw7.txt. Turn in both copies.

Explanation / Answer

-------This is the script, if you have doubts please mention me in comments I will reply you.

set echo off
set verify off
set feedback off
prompt ---------------------------*
prompt ----New Customer Program --*
prompt ---------------------------*

accept s1 char prompt "Please enter the customer's SOCIAL SECURITY NUMBER: "
accept l1 char prompt "Please enter the customer's LAST NAME: "
accept f1 char prompt "Please enter the customer's FIRST NAME: "
accept m1 char prompt "Please enter the customer's MIDDLE NAME: "
accept p1 char prompt "Please enter the customer's PHONE: "

insert into customers values('&s1','&l1','&f1','&m1','&p1');

prompt
prompt
prompt
prompt

prompt *------Following Record has been Updated:

declare

s1 char(20):='&s1';
l1 char(20):='&l1';
f1 char(20):='&f1';
m1 char(20):=initcap(substr('&m1',1,1));
p1 char(20):='&p1';

begin
l1:=initcap(l1);
f1:=initcap(f1);

dbms_output.put_line('Social Security Number: '|| substr(s1,1,3)||'-'||substr(s1,4,2)||'-'||substr(s1,7,4)

);
dbms_output.put_line(' ');
dbms_output.put_line('Name: '|| l1||','||f1||m1);
dbms_output.put_line(' ');
dbms_output.put_line('Phone: '|| '('||substr(p1,1,3)||')'||substr(p1,4,3)||'-'||substr(p1,7,4));

end;
/

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.