Sunday, 5 November 2017

SQL

SQL
        - Structured Query Language
        - It is an ANSI(American National Standard Institute)
        - Used to Store & 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

1. CREATE DATABASE
Create Database Testdb

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 Order by
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'
Select * from Persons Where City Like 's%'
Select * from Persons Where City Like '%chen%'
Select * from Persons Where City Like B_ska__n
Select * from Persons Where City Like '[bsp]'

n) Between Operator
Select * from Persons Where City between 'chennai' AND 'Madurai'

o) NOTBetween Operator
Select * from Persons Where City Not between 'chennai' AND 'Madurai'

p) Alias or as
Select FrstName as Name from Persons

q) Select 2nd Highest Value from Table
Select max(cost) from Table where cost NOT IN (select max(cost) from Table)

r) Select using GroupBy
select id,sum(cost) from Table group by id

s) Select ODD Records
select * from Emp where (RowID%2)=1

t) Select EVEN Records
select * from Emp where (RowID%2)=0

u) SELECT Null Values
select * from Emp where  name is NULL

v) 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

w) Select Last Row
Select top 1 from table order by Regno DESC

x)Select Capital Values
Select * from table where  data=UPPER (data) collate SQL_Latin1_General_CP1_CS_AS

y) 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

z) Finding, Deleting Duplicate Records
Delete from Emp where empid in(select empid from emp group by empid having count(*) > 1)

aa) Select UNION Records
Select * from Person Union Select * From Order

ab) Select UNION ALL
Select * from Person Unionall Select * From Order

ac) Backup - Select INTO
Select * into Personbackup From Persons
Select * into Personbackup From Persons where City='Chennai'

ad) 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

ae) Group By and Having Clause
a.Select name,sum(Price) from person where id>10 groupby name having max(price) >= 100

af) 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)

ag).Aggregate Functions with Group By
select name,lastname,sum(price) from orders Groupby name,lastname

ah) 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

4. INSERT to Table

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
a.Select Max Date
SELECT MAX(EntryDate) as max_date FROM FileData

b.Select Date(YYYY/MM/DD)
select * from FileData where CONVERT(varchar(10), EntryDate,111) = CONVERT(varchar(10),EntryDate,111)

c.Select Date Using Between Operator
select EntryDate from FileData where EntryDate between '2016-06-30' and '2016-07-31'

d.Select Only Time (hh:mm AM/PM)
(SELECT RIGHT(CONVERT(CHAR(20), convert(nvarchar,(select getdate())), 22), 8)) 

O/p
10:52AM

e.Select based on Time(hh:mm:tt)
CONVERT(VARCHAR(8), GETDATE(), 108) //15:12:12
O/p
10:52:42

f.based on Date
date          -  yyyy-MM-dd
datetime   -  yyyy-MM-dd HH:mm:ss
Year         -  yyyy (or) yy
Standard Date Formats
Date FormatStandardSQL StatementSample Output
Mon DD YYYY 1
HH:MIAM (or PM)		DefaultSELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)Jun 8 2011 1:30PM 1
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]06/08/11
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]06/08/2011
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]11.06.08
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]2011.06.08
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]08/06/11
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]08/06/2011
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]08-06-11
DD-MM-YYYYItalianSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]08-06-2011
DD Mon YY 1-SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]08 Jun 11 1
DD Mon YYYY 1-SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]08 Jun 2011 1
Mon DD, YY 1-SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]Jun 08, 11 1
Mon DD, YYYY 1-SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]Jun 08, 2011 1
HH:MM:SS-SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)		13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1Default +
nanoseconds		SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)		Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]06-08-11
MM-DD-YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]06-08-2011
YY/MM/DD-SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]11/06/08
YYYY/MM/DD-SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]2011/06/08
YYMMDDISOSELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]110608
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1Europe default + nanosecondsSELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)		08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)-SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)]
SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]		13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h)ODBC CanonicalSELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)ODBC Canonical
(with nanoseconds)		SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AM-SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)06/08/11 1:30:45 PM
YYYY-MM-DD-SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)2011-06-091
HH:MI:SS (24h)-SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN-SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNNISO8601SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1KuwaitiSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAMKuwaitiSELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)08/06/2011 1:30:45.9428675PM
Extended Date Formats
Date FormatSQL StatementSample Output
YY-MM-DD
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD]
11-06-08
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), '/', '-') AS [YYYY-MM-DD]
2011-06-08
YYYY-M-DSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D]2011-6-8
YY-M-DSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D]11-6-8
M-D-YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY]6-8-2011
M-D-YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY]6-8-11
D-M-YYYYSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY]8-6-2011
D-M-YYSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY]8-6-11
YY-MMSELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM]		11-06
YYYY-MMSELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM]2011-06
YY-MSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M]11-6
YYYY-MSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M]2011-6
MM-YYSELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY]		06-11
MM-YYYYSELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY]06-2011
M-YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY]6-11
M-YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY]6-2011
MM-DDSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD]06-08
DD-MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM]08-06
M-DSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D]6-8
D-MSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M]8-6
M/D/YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY]6/8/2011
M/D/YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY]6/8/11
D/M/YYYYSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY]8/6/2011
D/M/YYSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY]8/6/11
YYYY/M/DSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D]2011/6/8
YY/M/DSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D]11/6/8
MM/YYSELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY]06/11
MM/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY]06/2011
M/YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY]6/11
M/YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY]6/2011
YY/MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM]11/06
YYYY/MMSELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM]2011/06
YY/MSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M]11/6
YYYY/MSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M]2011/6
MM/DDSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD]06/08
DD/MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM]08/06
M/DSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D]6/8
D/MSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M]8/6
MM.DD.YYYYSELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY]06.08.2011
MM.DD.YYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY]06.08.11
M.D.YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY]6.8.2011
M.D.YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY]6.8.11
DD.MM.YYYYSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD.MM.YYSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
D.M.YYYYSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY]8.6.2011
D.M.YYSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY]8.6.11
YYYY.M.DSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D]2011.6.8
YY.M.DSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D]11.6.8
MM.YYYYSELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY]06.2011
MM.YYSELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY]06.11
M.YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY]6.2011
M.YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY]6.11
YYYY.MMSELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM]2011.06
YY.MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM]11.06
YYYY.MSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M]2011.6
YY.MSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M]11.6
MM.DDSELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD]06.08
DD.MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM]08.06
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY]06082011
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY]060811
DDMMYYYYSELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY]08062011
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]080611
MMYYYYSELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY]062011
MMYYSELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY]0611
YYYYMMSELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM]201106
YYMMSELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM]1106
Month DD, YYYY 1SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY]June 08, 2011 1
Mon YYYY 1SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY]Jun 2011 1
Month YYYY 1SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY]June 2011 1
DD Month 1SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month]08 June 1
Month DD 1SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD]June 08 1
DD Month YY 1SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' ' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY]08 June 11 1
DD Month YYYY 1SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY]08 June 2011 1
Mon-YY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY]Jun-08 1
Mon-YYYY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY]Jun-2011 1
DD-Mon-YY 1SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY]08-Jun-11 1
DD-Mon-YYYY 1SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY]08-Jun-2011 1
g.Select record based on Year
select EntryDate from FileData where year(BOB)>1990
select EntryDate from FileData where month(BOB)>10
select EntryDate from FileData where day(BOB)>15

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'

21.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

22.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

23.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),
);

24.SQL JOINS
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


24.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])


) AS P

25.CURSER

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

26.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

26.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

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

27.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

28.TRIGGER
special kind of a store procedure that executes in response to certain action

Types OF Triggers
a.     AFTER INSERT Trigger.
b.     AFTER UPDATE Trigger.
c.     AFTER DELETE Trigger.

29.TRANSACTION

BEGIN TRANSACTION [Tran1]
BEGIN TRY
-- Our Code here
COMMIT TRANSACTION [Tran1]
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION [Tran1]
END CATCH

30.TEMP TABLE
create table #Temp
(
    EventID int,
    EventTitle Varchar(50)
)

OR

create @Temp TABLE
(
    EventID int,
    EventTitle Varchar(50)
)


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




JOB Scheduling in SQL
  1. 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.
c.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

Select Active table used in Stored Procedure.
SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id

Active SP
SELECT name,
       type
  FROM dbo.sysobjects
WHERE (type = 'P') order by name ASC

Check Table or Procedure Exist
table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AboutUs]') AND typein (N'U', N'PC')) 
EXEC sp_rename 'AboutUs','Obsolete_AboutUs'
GO
Procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AboutUs]') AND typein (N'P', N'PC')) 
EXEC sp_rename 'AboutUs','Obsolete_AboutUs'
GO

Rename the Table/StoredProcedure 
EXEC sp_rename '_old name,'new name’

Move One DB to another DB
select * into DB1.dbo.Table1 from DB2.dbo.Table2

Get Affected Count
INSERT in to Tabla(x) VALUES('a');
SELECT SCOPE_IDENTITY();

on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)