SQL QUERIES

writing queries in sql server  (2005/2008/R2 Edition)

/*creating Database Syntax*/

Create Database Sample

/*Create Table Syntax*/

create table Student(Sno numeric,Sname varchar(25),Sage numeric,Sdob datetime,Splace varchar(25))

/*Inserting Values in Table*/

insert into student values(103,'dhas',25,11/26/1990,'Tambaram')

/*select Table*/

select * from student

/*Drop Table Syntax */

drop table student

/*Truncate table Syntax*/

truncate table student

/*Delete table syntax */

delete from student

/*update table*/

update student set sname='amal' where sno=101

/*Primary Key without constraint name*/

create table Empdetails(Empno numeric primary key,Ename varchar(30),Edob datetime,Emobile numeric,Eemail varchar(50),Eplace varchar(30))

insert into Empdetails values(102,'Peter','02/23/1984',9233333,'reeed@rediffmail.com','India')

select * from Empdetails

/*foreign key without constraint name*/

create table Empsaldetails(Empno numeric foreign key references empdetails(empno),Ebpay numeric,Ehra numeric,Eda numeric,Esal numeric )

insert into empsaldetails values(103,25000,5000,500,30500)

/*primary key with constraint name*/

create table Persons(p_id numeric constraint pk_Persons primary key,Pname varchar(25),Pcity varchar(25))

insert into persons values(2,'ramky','trichy')

select * from persons

/*Drop Constraint Syntax*/

ALTER TABLE Persons

DROP CONSTRAINT pk_Persons

/*foreign key with constraint name */

create table orders(O_id numeric constraint fk_orders foreign key references persons(p_id),O_number numeric)

insert into orders values(3,875)

drop table orders

select * from orders

/*Drop Constraint Syntax*/

ALTER TABLE orders

DROP CONSTRAINT fk_orders

delete from orders where o_id=2

/*Joins*/

create table customers(c_id numeric primary key,c_fname varchar(25),c_Lname varchar(50),a_addr varchar(50),c_city varchar(25))

create table supplier(S_id numeric,S_num numeric,c_id numeric foreign key references customers(c_id))

insert into customers values(2,'asas','ghjj','kambar c8','trichy')

drop table supplier

select * from supplier

select * from customers

insert into supplier values(3,8967,3)

/*Inner Joins*/

SELECT customers.c_fname, customers.c_Lname, supplier.S_num

FROM customers

INNER JOIN supplier

ON customers.c_id=supplier.c_id

ORDER BY customers.c_Lname

/*join concept */

create table employee(ID int,name nvarchar(25),salary int)

create table job(ID int,title nvarchar(25),averageSalary int)

select * from employee;

select * from job;

/*Inner join */

 SELECT e.ID, e.Name, j.title FROM Employee e INNER JOIN Job j ON e.Id = j.ID

  /*leftOuter Join*/

SELECT e.id,e.name,j.title FROM Employee e LEFT OUTER JOIN job j ON e.id = j.id

/*Right Outer Join*/

SELECT e.id, e.name, j.title FROM Employee e RIGHT OUTER JOIN job j ON e.id = j.id

          

/*cross Join*/

 SELECT e.ID,e.Name,j.title FROM Employee e CROSS JOIN job j ORDER BY j.ID

/*full join */

select * from employee full join job on employee.id=job.id

select * from employee

select * from job

/*Union Concept */

create table one(names varchar(30),age int)

insert into two values('five',5)

select * from one

select * from two

/*union*/

select * from one

union

select * from two

/*union all*/

select * from one

union all

select * from two