SQL
- Structured Query Language
- Access and Manipulate the Database
- It is an ANSI
- Used to retrieve the data from Database
1.DML
2.DDL
3.DCL
4.TCL
1.DML - Data Manipulation Language
a) Insert
b) Select
c) Update
d) Delete
2.DDL - Data Definition Language
a) Create Database
b) Alter Database
c) Drop Database
d) Create Table
e) Alter Table
f) Drop Table
g) Trigger Table
h) Truncate Table
3.DCL - Data control language
a) Grant- Access Permission
b) Revoke - take back Access Permission
4.TCL - Transaction control language
a) Commit
Transaction
b) Rollback
Transaction
c) Save Transaction
DB
1. CREATE DataBase
Create Database Testdb
TABLE
2. CREATE Table
Create Table Persons (id int primary key identity(1,1) not null,Name varchar(255), City Varchar(255))
3. SELECT From Table
a) Select all data
Select * from Persons
b) Select Particular column
Select Firstname,LastName from Persons
c) Distinct Select
Select Distinct(City) from Persons
d) With Where
Select * from Persons where city='chennai'
e) Select with AND
Select * from Persons where Firstname='Bas' AND LastName ='ran'
f) Select with OR
Select * from Persons where Firstname='Bas' OR LastName ='ran'
g) Select with AND & OR
Select * from Persons where Firstname='Bas' AND ( LastName ='ran' OR LastName ='ron' )
h) Select with Orderby
Select * from Persons Order by Firstname ASC
Select * from Persons Order by Firstname DESC
i) Select Based on Multiple id using IN Operator
Select * from Persons where id in (1,2,3)
j) Select Limit Records
Select * from Person LIMIT 5
k) Select using Top
Select Top 5 * from Persons
l) Select using Top with Percentage
Select Top 50 Percent * from Persons
m) Select using Wildcards and Like
Select * from Persons Where City Like '%s'
n) Select using Wildcards and Like
Select * from Persons Where City Like 's%'
o) Select using Wildcards and Like
Select * from Persons Where City Like '%chen%'
p) Select using Wildcards and Like
Select * from Persons Where City Like B_ska__n
q) Select using Wildcards and Like
Select * from Persons Where City Like '[bsp]'
r) Between Operator
Select * from Persons Where City between 'chennai' AND 'Madurai'
s) NOTBetween Operator
Select * from Persons Where City Notbetween 'chennai' AND 'Madurai'
t) Alias or as
Select FrstName as Name from Persons
u) Select 2nd Highest Value from Table
Select max(cost) from Table where cost NOT IN (select max(cost) from Table)
v) Select using GroupBy
select id,sum(cost) from Table group by id
w)Select ODD Records
select * from Emp where (RowID%2)=1
x)Select EVEN Records
select * from Emp where (RowID%2)=0
y) Descending Order
select * from Emp Order by DESC
Z) Ascending Order
select * from Emp Order by ASC
aa) SELECT Null Values
select * from Emp where name is NULL
ab)Select 1000 Character from Column
Select left(column,1000) as Body from Table
Select left(column,len(column)-50)) as Body from Table
Select SUBSTR(column,1,100) as Body from Table
ac)Select Last Row
Select top 1 from table order by Regno DESC
ad)Select Capital Values
Select * from table where data=UPPER (data) collate SQL_Latin1_General_CP1_CS_AS
ae)Select and CONVERT (or) CAST
Select avg(Convert(decimal(18,2),weight) from table Ex:18.2
Select Convert(nvarchar(10),date) from Table
Select Cast(weight as float) from Table
af)Finding, Deleting Duplicate Records
Delete from Emp where empid in(select empid from emp group by empid having count(*) > 1)
ag)Select UNION Records
Select * from Person
Union Select * From Order
ah)Select UNION ALL
Select * from Person
Unionall Select * From Order
ai)Backup - Select INTO
Select * into Personbackup From Persons
Select * into Personbackup From Persons where City='Chennai'
aj)Having Clause
a. Select Employee .Name,Count(order .orderid) as NumOForder from (order Inner Join Employee on order.Employeeid = Employee.Employeeid Groupby Lastname having count(order.orderid) > 10
ak)Group By and Having Clause
a.Select name,sum(Price) from person where id>10 groupby name having max(price) >= 100
al)Auto Increment
create table person(id int identity(1,1), name varchar(255) Not Null)
create table person(id int autoIncrement(1,1), name varchar(255) Not Null)
am).Aggregate Functions with Group By
select name,lastname,sum(price) from orders Groupby name,lastname
an)CASE Opertion for 'Static' Display Value to Column
SELECT ID,DAY,Time,
CASE
WHEN [SMB1(performance)] =1 THEN 'Good Performance'
WHEN [SMB1(performance)] =0 THEN 'Under Performance'
END as [SMB1(performance)] from LNT_CMS_GRT1_Inverter1_SMB_table
Original
ID DAY Time SMB1(performance)
11 2016-07-28 16:28:10.073 7:00AM 1
12 2016-07-28 16:28:10.073 8:00AM 1
o/p
ID DAY Time SMB1(performance)
11 2016-07-28 16:28:10.073 7:00AM Under Performance
12 2016-07-28 16:28:10.073 8:00AM Under Performance
a) Insert All Columns
Insert into Persons('4','AAA','BBB')
b) Insert Particular Columns
Insert into Person(id,lastname,firstname) values('4','AAA','BBB')
c) Multiple Columns INSERT in Sql
insert into LoginMaster (username,password,accesstype,status,EntryBy,EntryDate) values('test1','','SuperAdmin','A','test1',getdate()),('test2','','SuperAdmin','A','','')
5.UPDATE Table
a) Update based on id
Update Persons set FirstName='Baskaran', Lastname='R' where id='1'
b) Update based on AND
Update Persons set FirstName='Baskaran', Lastname='R' where id='1' AND Phoneno='9786'
c) Update based on OR
Update Persons set FirstName='Baskaran', Lastname='R' where id='1' OR Phoneno='9786'
d) Update based on AND & OR
Update Persons set FirstName='Baskaran', Lastname='R' where id='1' AND (Phoneno='9786' OR Phoneno='9785')
e) Update particular Column
Update Persons set FirstName='Baskaran' where id='1'
6.DELETE from Table
a) Delete All
Delete * from Persons
b) Delete based on id
Delete from Persons where id='1'
c) Delete based on AND
Delete from Persons where id='1' AND Phoneno='9786'
d) Delete based on OR
Delete from Persons where id='1' OR Phoneno='9786'
e) Delete based on AND & OR
Delete from Persons where id='1' AND (Phoneno='9786' OR Phoneno='9785')
e) Delete Top 10 Records From Bottom (or) Top
TOP --> delete top(10) from table
BOTTOM -->delete top(10) from table order by id DESC
Percentage wise --> DELETE TOP(25) PERCENT FROM Table
7)TRUNCATE Table- Delete data from database .not date structure
Truncate table Persons
8) DROP Table- Delete table from database
Drop table Persons
Drop Database DB_Test
9) ALTER Table
a) ADD
Alter table Persons add city varchar(255)
b) MODIFY or ALTER
Alter table Persons Alter Column city varchar(255)
c) DROP
Alter table Persons drop Column city
10)CONCATENATE or COMBINE TWO COLUMNS
select concat(Column1,',', Column2) from table
Select Column1+','+ Column2 [values] from table
11)MERGE ROWS IF ID SAME
SELECT t1.batchid, Name = REPLACE( (SELECT filename AS [data()]
FROM LogData t2
WHERE t2.batchid = t1.batchid
ORDER BY filename
FOR XML PATH('')), ' ', '~')
FROM LogData t1
GROUP BY batchid
12)CREATE and TableBackup or CopyNewtable
SELECT * INTO Backuptable FROM Usertable
13)DataBase to DataBase CREATE and TableBackup
Select * into SNIPP_WEB.dbo.tableName from SNIPP_DEV.dbo.Field_Master
14)DataBase to DataBase Copy or Insert to table
INSERT INTO SNIPP_WEB.dbo.Table1 SELECT Column1,Column2 FROM SNIPP_DEV.dbo.Table1
15)DATE
1.Select Max Date
SELECT MAX(EntryDate) as max_date FROM FileData
2.Select Date in YYYY/MM/DD
select * from FileData where CONVERT(varchar(10), EntryDate,111) = CONVERT(varchar(10),EntryDate,111)
3.Select Date Using Between Operator
select EntryDate from FileData where EntryDate between '2016-06-30' and '2016-07-31'
4.Select Only Time with AM/PM
(SELECT RIGHT(CONVERT(CHAR(20), convert(nvarchar,(select getdate())), 22), 8))
O/p
10:52AM
5.Select Only Time with AM/PM
(SELECT RIGHT(CONVERT(CHAR(20), convert(nvarchar,(select getdate())), 22), 8))
O/p
10:52AM
5.Select based on Date
CONVERT(VARCHAR(8), GETDATE(), 108) //15:12:12
O/p
10:52:42
6.based on Date
date - yyyy-MM-dd
datetime - yyyy-MM-dd HH:mm:ss
Year - yyyy (or) yy
7.Select record based on Year
select EntryDate from FileData where year(BOB)>1990
16)SPLIT STRING IN SQL
SELECT
PARSENAME(REPLACE(Column,',','.'),1) 'Value1',
PARSENAME(REPLACE(Column,',','.'),2) 'Value2'
FROM Table WITH (NOLOCK)
17)Display table Details
sp_help tablename
18)Display Query Details
print @Query
19)Excecute Query
Exec(@Query)
20)SELECT COLUMN NAME ONLY Query
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'LoginMaster'
Aggregate Functions
1. First()
Select First(Price) From Orders
2. Last()
Select Last(Price) From Orders
3. Min()
Select Min(Price) From Orders
4. Max()
Select Max(Price) From Orders
5. Count()
a. Select Count(*) from Orders
b. Select Count(Price) from Orders
c. Select Count(Price) from Orders where id='1'
d. Select Count(Price) from Orders where id='1' AND id='2'
6. Sum()
select sum(price) from orders
7. Avg()
select Avg(price) from orders
Scalar Functions()
1. Uppercase()
Select Ucase(Name) from Persons
Select UPPER (name) from persons
2. Lowercase()
Select Lcase(Name) from Persons
Select LOWER (name) from persons
3. length()
Select Len(Name) from Persons
4. Mid()
Select Mid(Name,1,4) from Persons
5. Round()
Select round(Price,0) from Persons
6. Now()
Select Name,now() from Persons
7. Now with Format
Select Name Format(now(),'yyyy-MM-DD') from Persons
SQL Constraints
1. Not Null
Create table Persons(id int
NOT NULL, Name varchar(255))
2. Unique
Create table Persons(id int
Unique, Name varchar(255))
3. Primary Key
Create table Persons(id int
Primary Key, Name varchar(255))
4. Foreign Key
Create table Orders(oid int
Primary Key, Name varchar(255),id int
Foreign Key References Persons(id))
5.Check
Create table Persons(id int check(id>0), Name varchar(255))
6.Default
Create table Persons(id int, Name varchar(255),city varchar(255) Default 'Chennai', Date DateTime Default Getdate() )
7.Multiple Primary Key (or) Composite Primary Key
CREATE TABLE userdata
(
userid INT,
userdataid INT,
info char(200),
primary key (userid, userdataid),
1. Left Joins
Select P.Lastname,P.FirstName,o.Orderno From Person P Left Join Order o on p.pid =o.oid orderby p.Lastname
2. Right Joins
Select P.Lastname,P.FirstName,o.Orderno From Person P Right Join Order o on p.pid =o.oid orderby p.Lastname
3. Inner Joins
Select P.Lastname,P.FirstName,o.Orderno From Person P Inner Join Order o on p.pid =o.oid orderby p.Lastname
4. Full Joins
Select P.Lastname,P.FirstName,o.Orderno From Person P Full Join Order o on p.pid =o.oid orderby p.Lastname
PIVOT IN SQL
CREATE TABLE [dbo].[PivotExample](
[Country] [nvarchar](50) NULL,
[Year] [smallint] NOT NULL,
[SalesAmount] [money] NULL
)
GO
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Australia', 2005, 1309047.1978)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Germany', 2006, 521230.8475)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United States', 2007, 2838512.3550)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'France', 2008, 922179.0400)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Australia', 2007, 3033784.2131)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'France', 2005, 180571.6920)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United Kingdom', 2006, 591586.8540)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Canada', 2006, 621602.3823)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United Kingdom', 2005, 291590.5194)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United States', 2005, 1100549.4498)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Canada', 2007, 535784.4624)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'France', 2007, 1026324.9692)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Germany', 2007, 1058405.7305)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Australia', 2006, 2154284.8835)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United Kingdom', 2008, 1210286.2700)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United States', 2008, 3324031.1600)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Germany', 2008, 1076890.7700)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United Kingdom', 2007, 1298248.5675)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Australia', 2008, 2563884.2900)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Canada', 2005, 146829.8074)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Germany', 2005, 237784.9902)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'Canada', 2008, 673628.2100)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'United States', 2006, 2126696.5460)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N'France', 2006, 514942.0131)
GO
SELECT * FROM [dbo].[PivotExample] ORDER BY Country
GO
SELECT [Country], [2005], [2006], [2007], [2008], [2009], [2010]
FROM [dbo].[PivotExample]
PIVOT
(
SUM(SalesAmount)
FOR [Year] IN ([2005], [2006], [2007], [2008], [2009], [2010])
Syntax
DECLARE
cursor_name CURSOR
--Declare
Curser
[LOCAL
| GLOBAL] --define
cursor scope
[FORWARD_ONLY
| SCROLL] --define
cursor movements (forward/backward)
[STATIC
| KEYSET | DYNAMIC | FAST_FORWARD] --basic
type of cursor
[READ_ONLY
| SCROLL_LOCKS | OPTIMISTIC] --define
locks
FOR
select_statement --define
SQL Select statement
FOR
UPDATE
[col1,col2,...coln] --define
columns that need to be updated
Example
DECLARE
@EmployeeDet NVARCHAR(max),@Values
varchar(MAX),@Data
varchar(max)
--
sample Values assgined
SET
@EmployeeDet='''1001'',''sandhiya'',''S'',''pallavaram'',''chennai''|''1002'',''Ram'',''S'',''Agravaram'',''Vellore''|''1003'',''Ragu'',''S'',''Nagar'',''Kovai'''
--Curser
Declaration
DECLARE
InsertSample CURSOR
--
Value Assign to Curser from Split string Function
FOR
SELECT
*
from
dbo.SplitString(@EmployeeDet,'|')
--
Open a Curser
OPEN
InsertSample
--
Fetch a Curser value
FETCH
NEXT
FROM
InsertSample into
@Values
--if
@@FETCH_STATUS=0 means Featch Status Successful
WHILE
@@FETCH_STATUS=0
BEGIN
--
Call the Storedprocedure
SET
@Data='INSERT_Emp
'+@Values+''
EXEC(@Data)
--
get next available row into variables
FETCH
NEXT
FROM
InsertSample into
@Values
END
--
Drop the Insersample Curser.
close
InsertSample
Deallocate
InsertSample
Function
Create
FUNCTION
[dbo].[SplitString]
(
@Input
NVARCHAR(MAX),
@Character
CHAR(1)
)
RETURNS
@Output TABLE
(
Id INT
IDENTITY(1,1)
NOT
NULL,
Item
NVARCHAR(1000)
)
AS
BEGIN
DECLARE
@StartIndex INT,
@EndIndex INT,
@Value NVARCHAR(1000)
SET
@StartIndex =
1
IF
SUBSTRING(@Input,
LEN(@Input)
-
1,
LEN(@Input))
<>
@Character
BEGIN
SET
@Input =
@Input +
@Character
END
WHILE
CHARINDEX(@Character,
@Input)
>
0
BEGIN
SET
@EndIndex =
CHARINDEX(@Character,
@Input)
SET
@Value =
SUBSTRING(@Input,
@StartIndex,
@EndIndex -
1)
IF
@Value <>
''
INSERT
INTO
@Output(Item)
VALUES
(@Value)
--SELECT
SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET
@Input =
SUBSTRING(@Input,
@EndIndex +
1,
LEN(@Input))
END
RETURN
END
Stored
Procedure
CREATE
PROC
INSERT_Emp
(
@HREmpId
nvarchar(10),
@FirstName
nvarchar(30),
@LastName
nvarchar(30),
@Address
nvarchar(30),
@City
nvarchar(30)
)
AS
BEGIN
INSERT
INTO
Employee(HREmpId,FirstName,LastName,Address,City)VALUES(@HREmpId,@FirstName,@LastName,@Address,@City)
END
STORED
PROCEDURE
1.Select
Create
Procedure
Sp_Select
(
@id
varchar(20)
)
As
Begin
BEGIN
TRANSACTION
[Tran1]
BEGIN
TRY
Select
* from Table1 where id=@id
COMMIT
TRANSACTION
[Tran1]
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
[Tran1]
END
CATCH
End
2.Insert
Create
Procedure SP_insert
(
@id
int,
@Name
varchar(20),
@DOB
DateTime
)
As
Begin
BEGIN
TRANSACTION
[Tran1]
BEGIN
TRY
Insert
into Table1(id,Name,DOB) values(@id,@Name,@DOB)
COMMIT
TRANSACTION
[Tran1]
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
[Tran1]
END
CATCH
End
3.Update
Create
Procedure
SP_update
(
@id
int,
@Name
varchar(20),
@DOB
DateTime
)
As
Begin
BEGIN
TRANSACTION
[Tran1]
BEGIN
TRY
Update
Table1 set Name=@Name,DOB=@DOB
where id=@id
COMMIT
TRANSACTION
[Tran1]
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
[Tran1]
END
CATCH
End
4.Delete
Create
Procedure
Sp_Delete
(
@id
int
)
As
Begin
BEGIN
TRANSACTION
[Tran1]
BEGIN
TRY
Delete
from Table1 where id=@id
COMMIT
TRANSACTION
[Tran1]
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
[Tran1]
END
CATCH
End
Execute Procedure
Execute SP_test
Exec SP_test
Stored Procedure
- Return 0 or n value
- Have I/p, O/p Parameter
- DML Statement
- Using try Catch Block
- Transaction Management
Function
- Return only one(1) value
- Have I/p Parameter only
- Select Statement only
- Not Using try Catch Block
- No Transaction Management
VIEW
- Virtual data
- view the data
Syntax
CREATE VIEW
CREATE
VIEW view_name AS
SELECT
column_name(s)
FROM
table_name
WHERE
condition
Example
CREATE
VIEW
Sample
AS
SELECT
*
FROM
Table1
WHERE
RecordStatus='A'
Order
BY
Id
SELECT VIEW
select * from view_name
DROP
VIEW
DROP
VIEW view_name
TRANSACTION
BEGIN TRANSACTION [Tran1]
BEGIN TRY
-- Our Code here
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [Tran1]
END CATCH
create table #Temp
(
EventID int,
EventTitle Varchar(50)
)
create @Temp TABLE
(
EventID int,
EventTitle Varchar(50)
)
JOB
Scheduling in SQL
SQL
Server Agent → Job → New Job
a.
General → Fill (Name and Description)
b.
Steps → New → Fill(Step Name) → Choose Database Name →
Command write or call Stored procedure like(Exec SP_Insert) →
ok.
Schedules
→ New → Fill (Name), Choose(Occurs, Recurs every, Occurs one
at) → OK
2.
Now Check the table it will work.
Example
Create
table
Tbl_Testing(Id
int,
Name nvarchar(50))
Create
Procedure
SP_InsertonTime
As
Begin
Insert
into
Tbl_Testing
(Id,
Name)
values(1,
getdate())
End
--Exec
SP_InsertonTime
SQL
Server Connection
Choose Server Type:
Database Engine
Server Name:
1).
2) .\SQLExpress
3) Pc-Name\ SQLExpress
Authentication:
Windows (or) SQL Server
Creating
SQL Server User login
After connecting Default Connection -> goto Security -> Login -> RightClick àNew Login -> Login Name, Select Sql
Server Authentication ,Password, Conform password -> Usermapping (leftside) --> Select DataBase and
roles Membership(Public, Db-owner, Db_security, admin). -> ok.--> User has been
Created.
Restoring
DataBase(.MDF, .LDF) File
-> Put the .mdf, .ldf files into c://Programfiles\MicrosoftSQLServer\MSSQL\Data
-> In
sql Server Right Click the Data Base -> Attach the .mdf Files -> Database has been added.
Restore
(.bak) File
-> Create Empty Database(Name as Example)
-> Right click Example DB -> Tasks -> Restore -> Database -> Select
DB in General Tab.
-> In Option Tab choose Overwrite -> ok -> Restore DONE.
Restore
(.SQL Script) File
In SQL Server -> file -> open the script file -> Execute file(F5) -> Restore
Done.
BackUp
DataBase
Right Click DB -> Tasks -> Backup -> Choose DB -> choose location -> ok Done
