Tuesday, 3 May 2016

SQL

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

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

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


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

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

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

TEMP TABLE

create table #Temp
(
    EventID int, 
    EventTitle Varchar(50)
)
OR




create @Temp TABLE
(
    EventID int, 
    EventTitle Varchar(50)
)
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.
    1. 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



Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)