sql server query collection

www.rabinkoirala.com.np [email protected]

MICROSOFT

SQL SERVER

http://www.rabinkoirala.com.np/


1. Implementing Tables and Views

a. Create and alter database,tables

b. Create and alter View

d. Create and Modify Constrains

e. Emplement Datatype

f. Implement Partitioning Solution

2. Working with Query Fundamentals

a. Query data by using CREATE, INSERT,SELECT,UPDATE, DELETE statement.

b. Return data by using the OUTPUT CLAUSE

c. Modify data by using MERGED statements

d. Implement aggregate queries

e. Combine Dataset

g. Apply built-in scalar function

3. Applying Additional Query Technique

a. Implement sub-querys

b. Implement CTE ( COMMON TABLE EXPRESSION )queries

c. Applying Rank Function

d. Control Expression Plans

e. Manage International Considerations

4. Inplementing Programming Objects

a. Create and Alter STORE PROCEDURE

b. Create and Alter User Definded Function

c. Create and Alter DML triggers

d. Create and deploy CLR-based Objects

e. Implement Error Handaling

g. Manage Transaction



1.CREATING DATABASE

create table school -- ‘School’ is database Name

2.CREATING TABLE

i. Selecting database before execute create table use school ii. create table

create table student_info(student_code varchar(100),name varchar(10),age

int,address varchar(50))

iii. create table using PRIMARY KEY & FOREIGN KEY

CREATE TABLE Orders ( O_Id identity(1,1) NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Student_info(student_code) )

3.ALTER TABLE ( Changes on Table )

i. Add New Column

alter table student_info add permanent_address varchar(30)

ii. Changes Column Datatype

alter table student_info alter column permanent_address varchar(50)

alter table student_info alter column permanent_address char(40)

iii. Change Column name

sp_rename 'student_info.permanent_address','p_address'

iV. Delete Column

alter table programmer drop column p_address



4.INSERTING IN TABLE

#First Method For Inserting Data to Table

Insert into student_info values ('1','Ram',21,'ktm')

#Second Method For Inserting Data to Table

Insert into student_info(student_code, name, age, address) values

('2','Shyamam',22,'ktm')

5.SELECTING DATA FROM TABLE

# Select Data

Select * from student_info

Select stud_code,name from student_info

# Select Data With Condition and ordering selected data

select*from student_info where student_code='1'

select*from student_info where student_code='1'and age='21'

select*from student_info where student_code='1'or age='21'

select*from student_info orderby CONVERT(numeric, student_code)

select*from student_info where student_code<11

select*from student_info where CONVERT(numeric, student_code)<11

select*from student_info where address in('ktm','basantapur')

select*from student_info where address not in('ktm','basantapur')

select top 1 student_code,name from student_info order by

CONVERT(numeric,student_code)desc

Select top 6 student_code,name from student_info order by

CONVERT(numeric,student_code)desc

# Select Data from table Using LIKE

select*from student_info where name like'K%'

select*from student_info where name like'%k'

select*from student_info where name like'%K%'

select*from student_info where name like'_am_na'



select*from student_info where name like'__lina'

select*from student_info where name like'[km]%'

select*from student_info where name like'[rm]%'

select*from student_info where name like'[a-d]%'

select*from student_info where name like'%[a-d]'

select*from student_info where name not like'%[a-d]'

select*from student_info where name not like'[rm]%'

6.UPDATE DATA OF TABLE

update student_info set name='aashish'where student_code='1'

update student_info set name='aashish',address='butwal' where

student_code='1'

7.DELETE DATA FROM TABLE

Delete from student_info

Delete from student_info where student_code='1'

Delete from student_info where name not like'am_sh'

8.ALTER AND DROP TABLE

droptable student_info

alter table student_info add class_code varchar(10)

alter table student_info alter column class_code varchar(20)

EXEC sp_rename 'student_info.name', 'name1'

9.JOIN

Table: Orders



# Inner Join

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID

# Left Join

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName

# Right Join

SELECT Customers.CustomerName, Orders.OrderID FROM Customers RIGHT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName

# Full Outer Join

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName

Table: Customers



10.UNION

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

11.SUB QUERY

select student_code, name, class_code from student

select * from class

select student_code, name, class_code,(select class from class where

class_code= student.class_code)as class_name from student

12.CREATING VIEW

create view student_new as

select student_code, name, class_code,(select class from class where

class_code= student.class_code)as class_name from student

# Using isNull

select name,isnull(class_name,'No Class') class_name from student

select student_code, name, class_code,isnull((select class from class

where class_code= student.class_code),'No class')as class_name from

student



13.USING IF...Else... and CASE Statement

1. Create Table : Marks

2. Using IF...Else

DECLARE @a as int -- Declearing Variable

Set @a = 2 –- Set Value to Variable

if @a = 1

begin

select * from marks where marks < 80

end

else if @a = 2

begin


end

else

begin


end

3. Using CASE Statement in SQL

select *,

(case when marks>=0 and marks<40 then 'Fail'

when marks>=40 and marks<50 then 'Third Division'

when marks>=50 and marks<60 then 'Second Division'

when marks>=60 and marks<80 then 'First Division'

when marks>=80 and marks<100 then 'Distinction'

else 'None'

end) as 'Division'

from marks

14.USING INBUID FUCNTION

select lower(name) from student_info

select upper(name) from student_info

select len(name) from student_info

select price,round(price,1) as round from sales

select price,round(price,0) as round from sales

For More InBuild Function

http://www.cs.utexas.edu/~mitra/csFall2009/cs329/lectures/sqlFunc.html

Table Name: Marks


http://www.cs.utexas.edu/~mitra/csFall2009/cs329/lectures/sqlFunc.html


15.CREATING FUCNTION

#Example 1

# Creating Function

CREATE FUNCTION RETURNSITE(@SITE_ID INT)

RETURNS VARCHAR(50)

AS

BEGIN

DECLARE @SITE_NAME VARCHAR(50);

IF @SITE_ID<10

begin

SET @SITE_NAME='WWW.ABC.COM';

end

ELSE

begin

SET @SITE_NAME='WWW.XYZ.COM';

end

RETURN @SITE_NAME;

END

# Select Data From Function

SELECT DBO.RETURNSITE(1)

#Example 2

# Creating Function

CREATE FUNCTION RETURNSTUDENT(@STD_CODE INT)

RETURNS VARCHAR(50)

AS

BEGIN

DECLARE @RETURN_STUD_NAME VARCHAR(50)

SELECT @RETURN_STUD_NAME =NAME FROM STUDENT_INFO WHERE

STUD_CODE=@STD_CODE

RETURN @RETURN_STUD_NAME

END

# Using Function

SELECT DBO.RETURNSTUDENT(1)



#Example 3

i. Creating Eample Table

create table grading_table (code int, value int,grade varchar (30),fyear int)

ii. Inserting Data in Grading_ Table

create function getGrading(@value as decimal(18,2))

returns varchar(30)

as

begin

declare @grade as varchar(30)

declare @currentvalue as int

declare @currentgrade as varchar (10)

set @grade=''

Declare abc CURSOR fast_forward FOR

select value,grade from grading_table order by value asc

OPEN abc;

FETCH NEXT FROM abc INTO @currentvalue,@currentgrade

WHILE (@@FETCH_STATUS=0)

begin

if @value>=@currentvalue

begin

set @grade=@currentgrade

end

FETCH NEXT FROM abc INTO @currentvalue,@currentgrade

end

close abc;

DEALLOCATE abc;

return @grade

end

ii. Selecting Data from getGrading Function

select dbo.getGrading(30)



16.USING CAST

Create table ExamResult(name varchar(50),Subject varchar (50), marks int)

Select*,

(Select sum(marks) from ExamResult a where a.name=b.name group by name) as

Total_marks,cast(((Select sum(marks) from ExamResult a where a.name=b.name

group by name)/3) as varchar (30))+ '%' as Percentage from ExamResult b

17.USING RANK

Select name,subject,marks, Rank() over(order by marks desc) as Rank from

ExamResult order by Rank

Select name,subject,marks, dense_Rank() over(order by marks desc) as Rank

from ExamResult order by Rank

Select name,subject,marks, dense_Rank() over(order by marks desc) as

Rank,ROW_NUMBER() over(order by marks asc) from ExamResult order by Rank

18.USING STORE PROCEDURE

i. Creating Store Procedure

create procedure insert_result

@name varchar(10),

@subject_name varchar(10),

@marks int

as

begin

INSERT into ExamResult(name,Subject,marks)

values(@name,@subject_name,@marks)

end

ii. Using Store Procedure

execute insert_result 'rita','maths',70



19.USING COMMON TABLE EXPRESSION (CTE)

with Table1 (name,subject,marks) as

(

Select name,subject,marks from ExamResult

)

,Table2(student_code,name,age,address) as

(

Select student_code,name,age,address from student_info

)

,

Table3(student_code,name,subject,marks) as

(

Select student_code,Table1.name,[subject],marks From table1

INNER JOIN Table2 on Table1.name=Table2.name

)

Select *from Table3

20.ERROR HANDALING

i. Example 1

Begin try

DECLARE @X INT

SET @X=1/10

SELECT @X

PRINT 'PASS'

END TRY

BEGIN CATCH

PRINT 'FAIL'

END CATCH

ii. Example 2

Begin try

DECLARE @X INT

SET @X=10/2

SELECT @X

PRINT 'PASS'

END TRY

BEGIN CATCH

PRINT 'FAIL'

END CATCH



iii. Example 3

Begin try

DECLARE @X INT

SET @X=1/0

SELECT @X

PRINT 'PASS'

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS [ERROR NUMBER]

SELECT ERROR_MESSAGE() AS [ERROR MESSAGE]

SELECT ERROR_STATE() AS [ERROR STATE]

END CATCH

21.USING TRANSACTION

Begin try

BEGIN TRANSACTION

DECLARE @X INT

SET @X=1/0

SELECT @X

PRINT 'PASS'

COMMIT TRANSACTION

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS [ERROR NUMBER], ERROR_MESSAGE() AS

[ERROR MESSAGE], ERROR_STATE() AS [ERROR STATE]

ROLLBACK TRANSACTION

END CATCH

22.TRIGGER

i. Create ACCOUNT & ACCOUNT1 Table

CREATE TABLE ACCOUNT (ACC_NUM INT, AMOUNT DECIMAL(18,2))

CREATE TABLE ACCOUNT1 (ACC_NUM INT, AMOUNT DECIMAL(18,2))

ii. Insert value on table

INSERT INTO ACCOUNT(ACC_NUM,AMOUNT) VALUES(4,8000.76)



iii. Creating Trigger

CREATE TRIGGER TRIGGER_ACCOUNT_INSERT

ON ACCOUNT

FOR INSERT

AS

DELETE FROM ACCOUNT1

INSERT INTO ACCOUNT1(ACC_NUM,AMOUNT)(SELECT ACC_NUM,AMOUNT FROM ACCOUNT)


sql server query collection

Education