--create database and schema
create database employe
create schema emp
--create employee table
create table [Link](emp_id int constraint pk primary key,
emp_name varchar(43)constraint cn not null,
city varchar(23) constraint dn default 'chennai',
email char(23) constraint un unique,
emp_age int constraint ch check(emp_age between 20 and 40))
-- display the structure
sp_help '[Link]'
--insert into employee
insert into [Link] values(32,'nandi',default,'ytrd@[Link]',32)
--display the values
select * from [Link]
------------------------------------------------------------------------------
-- create department table
create table [Link](dept_id int identity(100,1),
dept_code int constraint fk references [Link](emp_id),
dept_name varchar(23))
--insert values
insert into [Link] values(12,'cse')
--display the values
select * from [Link]
--add one column in department table
alter table [Link] add dept_desc varchar(32)
--modify the value in dept_desc column
update [Link] set dept_desc='comp sci' where dept_code=12
--auto generation off
set identity_insert [Link] on
--manual insert
insert into [Link] (dept_id,dept_code,dept_name,dept_desc)
values (200,11,'cse','comp sci')
-- auto generation on/manual off
set identity_insert [Link] off
--insert the values while in AG ON mode
insert into [Link] values(17,'it','info tech')
---------------------------------------------------------------------------
--Aggregate function
select count(*) from [Link]
select count(dept_code) dp from [Link]
select max(dept_code) from [Link]
select * from [Link]
select * from [Link]
select avg(emp_age) EA from [Link]
select sum(emp_age) EA from [Link]
------------------------------------------------------------------------------
-- Operation
select * from [Link]
update [Link] set emp_age=null where emp_id=16
select emp_id from [Link] where emp_age is null
select * from [Link] where emp_age is null
select emp_id from [Link] where emp_age is not null
select * from [Link] where emp_age is not null
select emp_name,emp_id from [Link] where emp_name like '%i%'
select emp_name,emp_id from [Link] where emp_name not like '%i%'
-----------------------------------------------------------------------------
---
--union/intersect/except
sp_help '[Link]'
sp_help '[Link]'
select emp_id ,emp_name from [Link]
union
select dept_id,dept_name from [Link]
--intersect
select emp_id from [Link]
intersect
select dept_code from [Link]
--except
select emp_id from [Link]
except
select dept_code from [Link]
--group by/order by/having
select * from [Link]
select * from [Link]
select dept_name,count(dept_name) from [Link] group by dept_name
select dept_name,count(dept_name) from [Link]
group by dept_name order by dept_name desc
select dept_code,count(dept_code) from [Link]
group by dept_code having dept_code=17
select dept_code,count(*) from [Link] where dept_name='it'
group by dept_code
select dept_code,count(*) from [Link] where dept_name='it'
group by dept_code having dept_code=17
-----------------------------------------------------------------------
--Joins
select * from [Link]
select * from [Link]
--inner
select * from [Link] d
join [Link] e on e.emp_id=d.dept_code
--full
select * from [Link] d full
join [Link] e on e.emp_id=d.dept_code
-- left
select * from [Link] d left
join [Link] e on e.emp_id=d.dept_code
--right
select * from [Link] d right
join [Link] e on e.emp_id=d.dept_code
--cross // whenever give where condition in cross join,it behaves like inner
join
select * from [Link] d cross
join [Link] e
select * from [Link] d cross
join [Link] e where e.emp_id=d.dept_code
-----------------------------------------------------------------------------
--perform constraint operation
select * from [Link]
select * from [Link]
sp_help '[Link]'
--drop constraint
alter table [Link]
drop constraint un
--add constraint to existing column
alter table [Link]
add constraint un unique (email)
update [Link] set email='ias@[Link]' where emp_age=29 --shows some
error
--------------------------------------------------
--VIEWS
create view empdetails
as
select emp_id,emp_name,emp_age from [Link]
--display the view (empdetails) table
select * from empdetails
--insert the values into view table
insert into empdetails values(34,'indu',28)
update empdetails set emp_age=39 where emp_id=16
delete empdetails where emp_id=34
sp_help 'empdetails'
sp_helptext empdetails
--create and drop the view
create view vv
as select emp_id from [Link]
select * from vv
drop view vv
--- create view with encryption(to hide the base table details)
create view vvv with encryption
as
select emp_name,city from [Link]
select * from vvv
sp_helptext 'vvv'
-----------------------------------------------------------------------------
--create ss table
create table [Link](id int,name char(34),addr varchar(54))
insert into [Link] values(2,'indu','eg')
select * from [Link]
sp_helptext '[Link]'
--create view sss from table ss with schemabinding
create view [Link] with schemabinding
as
select id,addr from [Link]
select * from [Link]
insert into [Link] values(34,'indus')
delete from [Link] where id=14
--first delete the view then oly can delete the table wen create the view
with schemabinding
drop table [Link]
drop view [Link]
