SQL

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