Insert record into table using command prompt in oracle: Oracle Assignment Help

Command prompt should look like this:

*-----------------------------------------------*

* --New Customer Program-- *

*-----------------------------------------------*

Please enter the customer's SOCIAL SECURITY NUMBER: 837465538 (Input)

Please enter the customer's LAST NAME: koch

Please enter the customer's FIRST NAME: george

Please enter the customer's MIDDLE NAME: will

Please enter the customer's PHONE NUMBER: 5629851111

*---The following record has been updated:

Social Security Number: 837-46553-5538

Name: Koch,George w.

Phone: (562)985-1111

Screenshot:

Solution: hw7.sql

set feedback off

set echo off

set verify off

set heading off

spool c:/app/hw7.txt

drop table customers;

create table customers (

	SSN varchar2(9),

	LASTNAME varchar2(30),

	FIRSTNAME varchar2(30),

	MIDDLE varchar2(30),

	PHONE varchar2(10));

prompt

prompt *-----------------------------------------------*

prompt *            --New Customer Program--           *

prompt *-----------------------------------------------*

prompt

accept vSSN prompt 'Please enter the customer''s SOCIAL SECURITY NUMBER:';

select SSN

from customers

where SSN = '&vSSN';

accept vLASTNAME prompt 'Please enter the customer''s LAST NAME:';

select LASTNAME

from customers

where LASTNAME = '&vLASTNAME';

accept vFIRSTNAME prompt 'Please enter the customer''s FIRST NAME:';

select FIRSTNAME

from customers

where FIRSTNAME = '&vFIRSTNAME';

accept vMIDDLE prompt 'Please enter the customer''s MIDDLE NAME:';

select MIDDLE

from customers

where MIDDLE = '&vMIDDLE';

accept vPHONE prompt 'Please enter the customer''s PHONE NUMBER:';

select PHONE

from customers

where PHONE = '&vPHONE';

insert into customers (SSN,LASTNAME,FIRSTNAME,MIDDLE,PHONE) values('&vSSN','&vLASTNAME','&vFIRSTNAME','&vMIDDLE','&vPHONE');

commit;

prompt

prompt

prompt *---The following record has been updated:

prompt

select 'Social Security Number:  '|| substr(SSN,1,3)||'-'||substr(SSN,4,5)||'-'||substr(SSN,6,8) from customers;

prompt

select 'Name:  '|| initcap(LASTNAME)||','|| initcap(FIRSTNAME) ||' '|| substr(MIDDLE,1,1)||'.' from customers;

prompt

select 'Phone:  ('||substr(PHONE,1,3)||')'||substr(PHONE,4,3)||'-'||substr(PHONE,7,10) from customers;

spool off

0 comments