Sql Queries

use samples
select * from information_schema.tables
use pubs

select * from emp_details
select * from emp_address
delete emp_details where emp_id=1
insert into emp_details values(1,'raja')

joins :

inner join :

select * from emp_details inner join emp_address on
emp_details.emp_id=emp_address.emp_id

outer join :
left outer join

select * from emp_details left outer join emp_address on
emp_details.emp_id=emp_address.emp_id

Right outer join:

select * from emp_details right outer join emp_address on
emp_details.emp_id=emp_address.emp_id

Full join :
select * from emp_details full join emp_address on
emp_details.emp_id=emp_address.emp_id

Cross join:
select * from emp_details cross join emp_address


Delete and Truncate :

Delete :
Select * from emp_details
begin transaction
delete emp_details where emp_id=3
select * from emp_details
truncate table emp_details


create table stock_details(stock_id int identity(1,10),stock_name varchar(50))

select * from stock_details
insert into stock_details values('supplygoods')
insert into stock_details values('maintenance')
insert into stock_details values('purchase')
insert into stock_details values('realgoods')
insert into stock_details values('manufacturing')

insert into stock_details values('suppliermerchant')
select * from stock_details

delete stock_details where stock_id=41
delete stock_details
truncate table stock_details
rollback

DML : Insert ,update,Delete ,Select
DDL : Create ,Alter,Drop
DCL : Grant,Revoke,Deny
TCL : Commit,RollBack,Savepoint,SetTransaction

self Join;

select * from emp_details

insert into emp_details values(4,'ganesh')
insert into emp_details values(5,'ravi')
insert into emp_details values(7,'sivam')
insert into emp_details values(8,'ragam')
select * from emp_details

select t1.emp_id,t1.empname,t2.empname from emp_details t1,emp_details t2
where t1.emp_id=t2.emp_id

select t1.emp_id,t1.empname,t2.empname from emp_details t1 inner join emp_details t2

on t1.emp_id=t2.emp_id


select t1.emp_id,t1.empname,t2.empname from emp_details t1 left outer join emp_details t2

on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 right outer join emp_details t2

on t1.emp_id=t2.emp_id


select t1.emp_id,t1.empname,t2.empname from emp_details t1 full join emp_details t2

on t1.emp_id=t2.emp_id

select t1.emp_id,t1.empname,t2.empname from emp_details t1 cross join emp_details t




use master

select * from syslanguages

select @@language

set language us_english

select * from information_schema.tables

Top 10 new in sql server 2005

Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language.

Native XML support.

XCopy support for moving databases from one location to another.

Robust and reliable installation using either graphical user interface (GUI) or silent modes.

Automated servicing and patching.

Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector.

Support for databases up to 4GB.

Support for disconnected systems with SQL Server Service Broker and Replication.
SQL Server Express Manager, a new, easy-to-use database management and query analysis tool.

Easier deployment via Visual Studio ClickOnce (available in Beta 3).


Stored Procedure :

use pubs
select * from emp_details


Create Procedure for without Arguments:
***************************************
create procedure emp_procedure
as
select * from emp_details

exec emp_procedure

alter procedure emp_procedure
as select empname from emp_details

exec emp_procedure

drop procedure emp_procedure


Create Procedure for with Arguments :
************************************

create procedure emp_procedure
@empid bigint,
@empname varchar(50)
as
select * from emp_details where emp_id=@empid

exec emp_procedure 3,'raj'


drop procedure emp_procedure

create procedure emp_procedure
@empid bigint ,
@empname varchar(50)
as
insert into emp_details(emp_id,empname)values(@empid,@empname)

exec emp_procedure 5,'mani'
select * from emp_details





constraints :

Create table emps(empid bigint constraint pk_emp primary key,emp_name varchar(50),empaddress varchar(50))

insert into emps values(101,'ravi','chennai')
insert into emps values(102,'raja','trichy')

select * from emps

alter table emps drop constraint pk_emp


insert into emps values(101,'sivam','bangalore')
select * from emps

alter table emps add constraint pk_emp check(empid is not null)


use pubs
select * from emp_details


Create Procedure for without Arguments:
***************************************
create procedure emp_procedure
as
select * from emp_details

exec emp_procedure

alter procedure emp_procedure
as select empname from emp_details

exec emp_procedure

drop procedure emp_procedure


Create Procedure for with Arguments :
************************************

create procedure emp_procedure
@empid bigint,
@empname varchar(50)
as
select * from emp_details where emp_id=@empid

exec emp_procedure 3,'raj'


drop procedure emp_procedure

create procedure emp_procedure
@empid bigint ,
@empname varchar(50)
as
insert into emp_details(emp_id,empname)values(@empid,@empname)

exec emp_procedure 5,'mani'
select * from emp_details

constraints :

Create table emps(empid bigint constraint pk_emp primary key,emp_name varchar(50),empaddress varchar(50))

insert into emps values(101,'ravi','chennai')
insert into emps values(102,'raja','trichy')

select * from emps

alter table emps drop constraint pk_emp


insert into emps values(101,'sivam','bangalore')
select * from emps

alter table emps add constraint pk_emp check(empid is not null)

select * from emps
select * from emp_details
select * from emp_address



select emp_details.emp_id,empname from emp_details,emp_address
where emp_details.emp_id=emp_address.emp_id

select emp_details.emp_id,empname ,empaddress from emp_address,emp_details
select * from emp_details
begin transaction
delete emp_details where emp_id=7
commit
select * from emp_details
begin transaction
rollback

select * from emp_details
select * from emp_address

select * from emp_details where emp_id between 1 and 4


views :

select * from authors

create view view_authors as select * from authors where au_fname='Johnson'

select * from view_authors

drop view view_authors
select * from emp_details


create view empview as select * from emp_details
select * from empview

insert into empview values(5,'sivam')
select * from empview

select * from emp_details

delete empview where emp_id=5

select * from empview

alter view empview as select empname from emp_details

select * from empview

alter view empview as select * from emp_details
select * from empview

update empview set emp_id=5 where empname='ganesh'

select * from empview

drop view empview

select * from emp_details


create view empview with encryption as select * from emp_details where emp_id=5

select * from empview
select * from information_schema.views

drop view empview

create view empview with schemabinding as select emp_id,empname from dbo.emp_details

select * from empview

select * from information_schema.views

alter view empview as select emp_id from emp_details

select * from empview

select * from emp_details

------------------------------------------------------------------------------------------------------------------------------------------------
Copy one table to another Table :

select * into empdetails from emp_details

------------------------------------------------------------------------------------------------------------------------------------------------
Difference between Drop and Truncate Table

To Find duplicate Records:

Select emp_id from emp_details group by emp_id having count(*)>1

Self join :
select a.emp_id ,b.empname from emp_details a,emp_details b
where a.emp_id=b.emp_id

To find duplicate Records:
select * from emp_details where emp_id in(select emp_id from emp_details group by emp_id having count(*)>1)



To Rename DataBase

Exec sp_renamedb ‘OldDataBasename’,’NewDataBasename’

use pubs
select * from emp_details

create table empdetails INTO select * from emp_details where 1=2
drop table empdetails
select * into empdetails from emp_details

select * from empdetails
select * from emp_address

select * into empaddress from emp_address
select * from empaddress

select * from emp_details
insert into emp_details values(2,'ravimani')

select emp_id from emp_details group by emp_id having count(*)>1


Stored Procedure:
drop procedure emp_procedure
create procedure emp_procedure
as
select * from emp_details

exec emp_procedure


drop procedure emp_procedure

Self join:

select * from emp_details

select a.emp_id ,b.empname from emp_details a,emp_details b
where a.emp_id=b.emp_id


exec sp_tables


exec sp_columns pubs

exec sp_who2

To find Duplicate Records

select * from emp_details


insert into emp_details values(59,'ganesh')

select * from emp_details where emp_id in(select emp_id from emp_details group by emp_id having count(*)>1)

select * from emp_details

select top 1 emp_id from(select distinct TOP emp_id from emp_details order by emp_id desc) a order by emp_id

select emp_details.emp_id ,empname from emp_details,emp_address

use shariff
exec sp_renamedb 'pasha','shariff'

exec sp_renamedb 'shariff','pasha'