Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 22, 2024

Good Evening looking for some help in Oracle: 1. Create a table named COPY_customers from the customers table. 2. Use the CREATE or REPLACE option

Good Evening looking for some help in Oracle:

1. Create a table named COPY_customers from the customers table.

2. Use the CREATE or REPLACE option to create a view of all the columns in the COPY_customers table called view_ customers.

3. Use view_ customers to INSERT a row of data into the underlying COPY_customers table. Execute a SELECT * from COPY_customers to verify your DML command.

4. Show the contents of the view_ customers view.

5. Write the query to show the name of all your views in your schema. Use the USER_VIEWS.

Here's the table:

--CONNECT om/om;

--SET DEFINE OFF;

-- Begin an anonymous PL/SQL script that

-- drops all tables in the EX schema and

-- suppresses the error messages that are displayed if the tables don't exist

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE order_details';

EXECUTE IMMEDIATE 'DROP TABLE orders';

EXECUTE IMMEDIATE 'DROP TABLE items';

EXECUTE IMMEDIATE 'DROP TABLE customers';

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('');

END;

CREATE TABLE customers

(

customer_id NUMBER NOT NULL,

customer_first_name VARCHAR2(50),

customer_last_name VARCHAR2(50) NOT NULL,

customer_address VARCHAR2(255) NOT NULL,

customer_city VARCHAR2(50) NOT NULL,

customer_state CHAR(2) NOT NULL,

customer_zip VARCHAR2(20) NOT NULL,

customer_phone VARCHAR2(30) NOT NULL,

customer_fax VARCHAR2(30),

CONSTRAINT customers_pk

PRIMARY KEY (customer_id)

);

CREATE TABLE items

(

item_id NUMBER NOT NULL,

title VARCHAR2(50) NOT NULL,

artist VARCHAR2(50) NOT NULL,

unit_price NUMBER NOT NULL,

CONSTRAINT items_pk

PRIMARY KEY (item_id),

CONSTRAINT title_artist_unq

UNIQUE (title, artist)

);

CREATE TABLE orders

(

order_id NUMBER NOT NULL,

customer_id NUMBER NOT NULL,

order_date DATE NOT NULL,

shipped_date DATE,

CONSTRAINT orders_pk

PRIMARY KEY (order_id),

CONSTRAINT orders_fk_customers

FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

);

CREATE TABLE order_details

(

order_id NUMBER NOT NULL,

item_id NUMBER NOT NULL,

order_qty NUMBER NOT NULL,

CONSTRAINT order_details_pk

PRIMARY KEY (order_id, item_id),

CONSTRAINT order_details_fk_orders

FOREIGN KEY (order_id)

REFERENCES orders (order_id),

CONSTRAINT order_details_fk_items

FOREIGN KEY (item_id)

REFERENCES items (item_id)

);

-- INSERT INTO customers

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (1,'Korah','Blanca','1555 W Lane Ave','Columbus','OH','43221','6145554435','6145553928');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (2,'Yash','Randall','11 E Rancho Madera Rd','Madison','WI','53707','2095551205','2095552262');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (3,'Johnathon','Millerton','60 Madison Ave','New York','NY','10010','2125554800','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (4,'Mikayla','Damion','2021 K Street Nw','Washington','DC','20006','2025555561','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (5,'Kendall','Mayte','4775 E Miami River Rd','Cleves','OH','45002','5135553043','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (6,'Kaitlin','Hostlery','3250 Spring Grove Ave','Cincinnati','OH','45225','8005551957','8005552826');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (7,'Derek','Chaddick','9022 E Merchant Wy','Fairfield','IA','52556','5155556130','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (8,'Deborah','Damien','415 E Olive Ave','Fresno','CA','93728','5595558060','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (9,'Karina','Lacy','882 W Easton Wy','Los Angeles','CA','90084','8005557000','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (10,'Kurt','Nickalus','28210 N Avenue Stanford','Valencia','CA','91355','8055550584','8055556689');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (11,'Kelsey','Eulalia','7833 N Ridge Rd','Sacramento','CA','95887','2095557500','2095551302');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (12,'Anders','Rohansen','12345 E 67th Ave NW','Takoma Park','MD','24512','3385556772','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (13,'Thalia','Neftaly','2508 W Shaw Ave','Fresno','CA','93711','5595556245','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (14,'Gonzalo','Keeton','12 Daniel Road','Fairfield','NJ','07004','2015559742','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (15,'Ania','Irvin','1099 N Farcourt St','Orange','CA','92807','7145559000','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (16,'Dakota','Baylee','1033 N Sycamore Ave.','Los Angeles','CA','90038','2135554322','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (17,'Samuel','Jacobsen','3433 E Widget Ave','Palo Alto','CA','92711','4155553434','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (18,'Justin','Javen','828 S Broadway','Tarrytown','NY','10591','8005550037','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (19,'Kyle','Marissa','789 E Mercy Ave','Phoenix','AZ','85038','9475553900','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (20,'Erick','Kaleigh','Five Lakepointe Plaza, Ste 500','Charlotte','NC','28217','7045553500','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (21,'Marvin','Quintin','2677 Industrial Circle Dr','Columbus','OH','43260','6145558600','6145557580');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (22,'Rashad','Holbrooke','3467 W Shaw Ave #103','Fresno','CA','93711','5595558625','5595558495');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (23,'Trisha','Anum','627 Aviation Way','Manhatttan Beach','CA','90266','3105552732','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (24,'Julian','Carson','372 San Quentin','San Francisco','CA','94161','6175550700','NULL');

INSERT INTO customers

(customer_id,customer_first_name,customer_last_name,customer_address,customer_city,customer_state,customer_zip,customer_phone,customer_fax)

VALUES (25,'Kirsten','Story','2401 Wisconsin Ave NW','Washington','DC','20559','2065559115','NULL');

-- INSERT INTO items

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(1,'Umami In Concert','Umami',17.95);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(2,'Race Car Sounds','The Ubernerds',13);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(3,'No Rest For The Weary','No Rest For The Weary',16.95);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(4,'More Songs About Structures and Comestibles','No Rest For The Weary',17.95);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(5,'On The Road With Burt Ruggles','Burt Ruggles',17.5);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(6,'No Fixed Address','Sewed the Vest Pocket',16.95);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(7,'Rude Noises','Jess & Odie',13);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(8,'Burt Ruggles: An Intimate Portrait','Burt Ruggles',17.95);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(9,'Zone Out With Umami','Umami',16.95);

INSERT INTO items (item_id,title,artist,unit_price) VALUES

(10,'Etcetera','Onn & Onn',17);

-- INSERT INTO orders