Sql query

--To create a database
Create database DB
--To select database
Use DB

--To drop(delete) database
Drop database DB

--To create table
Create table Student
(
EnrollmentNo int,
Name varchar(30),
Programme varchar(15),
Address varchar(50),
Mobile varchar(10)
)


--To drop(delete) table
Drop table Student

--To truncate table
Truncate table Student

--To insert record into table
Insert into Student(EnrollmentNo,Name,Programme,Address,Mobile)values(073550123,'Praveen','MCA','WZ-16-C Possangipur Janakpuri New Delhi -58','9540530654')
Insert into Student(EnrollmentNo,Name,Programme,Address,Mobile)values(073550116,'Rakesh','MCA','WZ-16-C Possangipur Janakpuri New Delhi -58','921234567')
Insert into Student(EnrollmentNo,Name,Programme,Address,Mobile)values(073550123,'Pankaj','BCA','A2 A/82 Janakpuri New Delhi -58','9540530654')
Insert into Student(EnrollmentNo,Name,Programme,Address,Mobile)values(059550123,'Jatindar','BCA','Uttam Nagar New Delhi -45','9540530654')



--To display record
Select * from Student

--To display specified field
Select EnrollmentNo,Name,Programme,Address,Mobile from Student

--Where Clause
-------------
Select EnrollmentNo,Name,Programme,Address,Mobile from Student where EnrollmentNo='73550123'

Logical Operator
---------------
AND
Or
Not

--AND
Select EnrollmentNo,Name,Programme,Address,Mobile from Student where Name='Praveen' and Programme='MCA'
The above statement display records whose name is praveen and programme is MCA

--OR
Select EnrollmentNo,Name,Programme,Address,Mobile from Student where Name='Praveen' or Programme='MCA'
The above statement display records whose name is praveen or either programme is MCA

--Not
Select EnrollmentNo,Name,Programme,Address,Mobile from Student where Name !='Praveen'
The above statement display records whose name is not praveen

--To Create table

Create table StudentDetails
(
EnrollmentNo int,
CourseFee decimal(18,2),
Duration varchar(10)
)
-- To insert record
Insert into StudentDetails(EnrollmentNo,CourseFee,Duration) values(073550123,'50000.00','3 Years')
Insert into StudentDetails(EnrollmentNo,CourseFee,Duration) values(073550116,'50000.00','3 Years')
Insert into StudentDetails(EnrollmentNo,CourseFee,Duration) values(073550123,'30000.00','3 Years')
Insert into StudentDetails(EnrollmentNo,CourseFee,Duration) values(073551111,'50000.00','2 Years')
Insert into StudentDetails(EnrollmentNo,CourseFee,Duration) values(073550222,'100000.00','2 Years')
--To update record
Update Student set EnrollmentNo='603550123',Mobile='9811574400' where Name='Pankaj'
Update StudentDetails Set EnrollmentNo='603550123' where CourseFee='30000.00'

--Join
--Inner Join

Select S.EnrollmentNo,S.Name,S.Address,S.Programme,S.Mobile,SD.CourseFee,SD.Duration
from Student S
Inner Join StudentDetails SD on S.EnrollmentNo=SD.EnrollmentNo

-- Left Join
Select S.EnrollmentNo,S.Name,S.Address,S.Programme,S.Mobile,SD.CourseFee,SD.Duration
from Student S
Left Join StudentDetails SD on S.EnrollmentNo=SD.EnrollmentNo

--Right Join
Select S.EnrollmentNo,S.Name,S.Address,S.Programme,S.Mobile,SD.CourseFee,SD.Duration
from Student S
Right Join StudentDetails SD on S.EnrollmentNo=SD.EnrollmentNo

--Full Join
Select S.EnrollmentNo,S.Name,S.Address,S.Programme,S.Mobile,SD.CourseFee,SD.Duration
from Student S
Full Join StudentDetails SD on S.EnrollmentNo=SD.EnrollmentNo

--Aggregate Function
--Sum()
Select Sum(CourseFee)CourseFee from StudentDetails
--Avg()
Select Avg(CourseFee)CourseFee from StudentDetails
--Count()
Select Count(CourseFee)CourseFee from StudentDetails
To delete record from table

Select * from Student
Select * from StudentDetails

-- To insert record
--To Create table
Create Table SqlTutorial
(
Id int identity,
CommandName varchar(50),
Query Varchar(2000),
Description varchar(3000)
)