Question

1. What are three uses for a view?

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

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

4. 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.

5. Show the contents of the view_ customers view.

6. Write the statement to create a view including customer_id, customer_last_name, customer_city, and customer_state for all customers who live in OH, NY, and DC.

7. Using your view, write the statement to show the number of customers who live in NY.

8. Write the query to show the name of all your views in your schema. Use the USER_VIEWS. Note that I only need the name of the views

Answer 1

Table Create

drop table customers;
drop table COPY_customers;

Create table customers(customer_id int Primary key,customer_last_name varchar(40),customer_city varchar(30),customer_state varchar(30));
desc customers;
insert into customers values(101,'Rajgor','OH','Gujrat');
insert into customers values(102,'Viral','NY','Gujrat');
insert into customers values(103,'Navinbhai','DC','Gujrat');
Select * from customers;

/*1 Uses of View */

/* It Provide encapsulation */
/* It Virtual table, not stored permanently */
/* It is used to implement the security mechanism */

/*2 Uses of Old table make new table */
Create table COPY_customers as Select * from customers;
desc COPY_customers;

/*3 Make View */
Create or REPLACE VIEW view_customers as Select * from customers;
Select * from view_customers;

/*4 Insert Row in View */
insert into view_customers values(104,'Paresh','NY','Maharastra');
Select * from view_customers;

/*5 Content of View */
desc view_customers;

/*6 Content of View */
Create View View_Customer_v as Select customer_id,customer_last_name,customer_city,customer_state from customers where customer_city in('OH','NY','DC');
desc View_Customer_v;

/*7 Content of View */
Create View View_Customer_v1 as Select count(*)"Total " from customers where customer_city in('NY');
desc View_Customer_v1;

/*8 All View */

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'VIEW'

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........