Untitled

12/02/2024
=============================================================================================
Union and union all:
--------------------

Union:
the union operator could be used to find the resultset or combination of two or more tables.
union operator return unique records for two or more tables.

rules for union and union:
1.each table must have the same numbers of columns.
2.the columns must have same datatypes 
3. order of columns of esch table must be same.

syntax:
select columns from table1
union
select columns from table2

use second;
-- create table manager(mng_id int ,m_name varchar(222),salary int,age int ,city varchar(100));
select * from employee;
-- insert into employee values(4, "nita",30000,34,"pune"),(5, "anita",50000,34,"nagpur");
select * from manager;
-- insert into manager values(1,	"pradip",	80000,	45	,"nagpur"),
-- (2,"ratna",	40000	,20,	"nashik"),
-- (3	,"rohit"	,60000	,22,	"nagpur"),
-- (4,	"nita",	30000,	34	,"pune");

select * from employee
union
select * from manager;

ex: select emp_id as id,name ,salary from employee
union
select mng_id as id,m_name,salary as name from manager;

ex:-
select emp_id as id,name ,salary from employee where salary >20000 
union
select mng_id as id,m_name,salary as name from manager where salary >20000 order by name desc;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

union all:
the union all operator could be used to find the resultset or combination of two or more tables.
union all operator return all records for two or more tables.(duplicate also)

syntax:
select columns from table1
union
select columns from table2

exa:
select emp_id as id,name ,salary from employee 
union all
select mng_id as id,m_name,salary as name from manager;

========================================================================================================================================

View :-
view is a virtual table.
a view is also contain row and columns.
for create views we can select the fields from one or more table.

syntax:
create view view_name as
select columns from table name
where condition.

exa:
create view empdetails as
select * from employee;

select * from empdetails;
create   view empinfo as 
select emp_id,name from employee;

select * from empinfo;

create or replace view empinfo as 
select emp_id,name,city from employee where salary>20000;

create or replace view empinfo as
select emp_id as id, city from employee;

_____________________________________________________________________
views created for multiple tables

create  or replace view empmnginfo as 
select distinct employee.name,manager.m_name,manager.salary
from employee,manager;

select * from empmnginfo;

drop view empinfo;

---------------------------------------------------------------------------------------------------------------------------------
Advantages:
1. security
2.complexity
3.storage capacity(memmory efficient)
4.Consistency


Disadvantages:

1. you cannot insert if the base table has any not null column yhat do not appear in view.
2.you cannot execute insert ,update and delete statement on view if read only option is enable.
3. you cannot create view on temporary table.

-_______________________________________________________________________________________________________________________________________