Dbms SQL Server1

[Unit 1: Introduction to Web Design] Course: NIELIT ‘O’ Level (IT)
Module: M2-R5: Web Designing & Publishing

1 Database
 Database is collection of information organized in such a way that a computer
program can quickly select desired piece of data.
 You can think of a database as an electronic filing system.
 Use of Database
2 Select Statement
 Select Statement is used to select data from a database.
 Select col1,col2…..from table_name
 If you want all columns then use the select * syntax
 Select distinct statement is used to select distinct (different) values
 Where clause is used to filter records
 Order by keyword is used to order the result set in ascending or descending
order
 And operator is used to filter records based on more than one condition
3 Select statement…
 Or operator displays a record if any of the conditions are true.
 And operator displays a record if all of the conditions are true.
 The not operator is used in combination with other operators to give the
opposite result.
 Count(),sum(),max(),min()
4 Insert Statement
 Insert into statement is used to insert new records in table
 It is possible to write insert into statement in two ways
 Specify both the column name and values to be inserted
 Insert into table_name(col1,col2,col3,…) values (value1,value2,value3,…)
 Insert into table_name values(col1,col2,col3) ..If you are adding values for
all the columns in the table, then there is no need to specify the column
name in the sql query.
5 Update Statement
 Update statement is used to modify the existing records in a table.
 Update syntax:
 Update <table_name> set col_name = value where <condition>
 Where clause specifies which records are to be updated, if where clause is

omitted all records will be updated.
6 Delete Statement
 Delete statement is used to delete existing records in a table.
7 Select Statement
 Select top clause is used to specify the number of records to return.
 Like operator is used in where clause to search for specified pattern in a
column.
 There are two wild character are used in conjunction with like operator.
 The percent sign % represents zero , one or multiple character.
 The underscore sign _ represents one , single character.
 The In operator allows you to specify multiple values in where clause.

 The In operator is shorthand for multiple or conditions.
8 Select Statement
 In ( Select) You can use In with a subquery in the where clause.
 Not in (select)
 Select between operator is used to select values between the range. The
between operator is inclusive. Begin and end values are included.
 Group by statement groups rows that have the same values into summary
rows.
 The group by statement is often used with aggregate function
( count,max,min,sum) to group the result set by one or more columns.
 Having clause was added to SQL because the where keyword can not be used
with aggregate function.
9 Select Statement
 Select into statement copies data from one table into a new table.
 Insert into select statement copies data from one table and inserts it into
another table.
 Insert into select statement requires that data types in source and target tables
match.
10
 create table studentc
(stdid int not null,age int check age>18,name varchar(100);
 alter table student add check (age>18)
 CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Chd')
);
11
 ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
 CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
 CREATE TABLE Persons (
 ID int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Age int,
 City varchar(255) DEFAULT ‘chd'
 );
12
ADD CONSTRAINT df_City
DEFAULT ‘chd' FOR City;

ALTER COLUMN City DROP DEFAULT;
 alter table employee alter column emp_code numeric(5,0) not null
 alter table employee add constraint df_basic default 20000 for basicpay
13 commands
 SELECT ROW_NUMBER() OVER (ORDER BY EMP_code1) AS
ID ,emp_name from employee
 select emp_name ,case when dept_code = 11 then 'Admin' when

dept_code=12 then 'Sales' end as deptname from employee
14 union
 The union operator is used to combine the result set of two or more select
statements.
 Every select statement within the union must have the same number of
columns.
 The columns must have similar data types
 The column in every select statement must be in same orders
 Union operator selects only distinct values , to allow duplicate values select
union all
15 Except
 The SQL server Except function compares the result set of two queries and
returns the distinct rows from the first query that are not output by the second
query. In other words, the except subtracts the result set of a query from
another.
 Query1
Except
Query2
16 Truncate
 The TRUNCATE TABLE statement in SQL Server is a DDL (Data Definition
Language) command. This statement is used to removes all rows from the
table or specified partition without removing the table structure. It is
similar to the DELETE command, but it does not allow filtering the table
records because we cannot use the WHERE clause with this command.
However, in terms of time and resource usage, TRUNCATE is much faster
than DELETE.
 This command deallocates the data pages while removing the data in the
table. It is similar to dropping and re-creating the table again.
17 Offset and fetch
 The FETCH and OFFSET clauses in SQL Server are used in combination with the
SELECT and ORDER BY clauses to limit the range of records returned by the query.
It was first introduced with the SQL Server version 2012 for doing pagination of
the result set. It is useful when our database contains a huge amount of data.
 OFFSET: This clause is used to specify the beginning point for returning rows from
a result set. Basically, it ignores the initial set of records. SQL Server can use it only
with the ORDER BY clause. If its value is negative, an error will be returned.
Therefore, it should always be greater than or equal to zero.
 FETCH: It is an optional clause that provides the number of rows we want to return
after the OFFSET in a query. We cannot use it without OFFSET. Its value cannot be
negative similar to OFFSET. Therefore, it should always be greater than or equal to
zero; otherwise, it will throw an error.
18 Offset and fetch
 SELECT * FROM <tablename>
ORDER BY <colname>
OFFSET 5 ROWS
FETCH NEXT 4 ROWS ONLY;
19 Join
 The inner join returns all records from multiple tables that satisfy the specified join condition. It
is the simple and most popular form of the join. If we omit the inner keyword of the join, we
receive the same output.
 SELECT columns FROM table1
INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2
 SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amoun

t_paid FROM Student INNER JOIN Fee ON Student.admission_no = Fee.admission_no;
20 Self join
 A table is joined to itself using the SELF JOIN. It means that each table row
is combined with itself and with every other table row. The SELF JOIN can
be thought of as a JOIN of two copies of the same tables. We can do this with
the help of table name aliases to assign a specific name to each table's
instance. The table aliases enable us to use the table's temporary name that
we are going to use in the query. It's a useful way to extract hierarchical data
and comparing rows inside a single table.
 SELECT T1.col_name, T2.col_name... FROM table1 T1, table1 T2
WHERE join_condition;
21 Self join
 SELECT S1.first_name, S2.last_name, S2.city
FROM Student S1, Student S2
WHERE S1.id <> S2.iD AND S1.city = S2.city
ORDER BY S2.city
22 Outer Join
 OUTER JOIN in SQL Server returns all records from both tables that
satisfy the join condition. In other words, this join will not return only the
matching record but also return all unmatched rows from one or both tables.
 We can categories the OUTER JOIN further into three types:
 LEFT OUTER JOIN
 RIGHT OUTER JOIN
 FULL OUTER JOIN

23 Left Outer Join
 The LEFT OUTER JOIN retrieves all the records from the left table and
matching rows from the right table. It will return NULL when no matching
record is found in the right side table. Since OUTER is an optional keyword,
it is also known as LEFT JOIN.
 SELECT column_lists FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
24 Right outer join
 The RIGHT OUTER JOIN retrieves all the records from the right-hand table
and matched rows from the left-hand table. It will return NULL when no
matching record is found in the left-hand table. Since OUTER is an optional
keyword, it is also known as RIGHT JOIN.
 SELECT column_lists
FROM table1 RIGHT [OUTER] JOIN table2

25 Full outer join
 The FULL OUTER JOIN in SQL Server returns a result that includes all
rows from both tables. The columns of the right-hand table return NULL
when no matching records are found in the left-hand table. And if no
matching records are found in the right-hand table, the left-hand table column
returns NULL.
 SELECT column_lists FROM table1 FULL [OUTER] JOIN table2
26 Stored Procedures
 A stored procedure is a group of one or more pre-compiled SQL
statements into a logical unit. It is stored as an object inside the database
server. It is a subroutine or a subprogram in the common computing language
that has been created and stored in the database. Each procedure in SQL
Server always contains a name, parameter lists, and Transact-SQL statements.
The SQL Database Server stores the stored procedures as named objects. We
can invoke the procedures by using triggers, other procedures, and
applications such as Java, Python, PHP, etc. It can support almost all
relational database systems.
27 Benefits of Stored Procedurs
 It can be easily modified: We can easily modify the code inside the stored procedure
without the need to restart or deploying the application. For example, If the T-SQL
queries are written in the application and if we need to change the logic, we must change
the code in the application and re-deploy it. SQL Server Stored procedures eliminate
such challenges by storing the code in the database. so, when we want to change the
logic inside the procedure we can just do it by simple ALTER PROCEDURE statement.
 Reduced network traffic: When we use stored procedures instead of writing T-SQL
queries at the application level, only the procedure name is passed over the network
instead of the whole T-SQL code.
 Reusable: Stored procedures can be executed by multiple users or multiple client
applications without the need of writing the code again.
 Security: Stored procedures reduce the threat by eliminating direct access to the tables.
 When creating a stored procedure you can either use CREATE PROCEDURE
or CREATE PROC. After the stored procedure name you need to use the
keyword "AS" and then the rest is just the regular SQL code that you would
normally execute.
 One thing to note is that you cannot use the keyword "GO" in the stored
procedure. Once the SQL Server compiler sees "GO" it assumes it is the end
of the batch.
 Also, you can not change database context within the stored procedure such
as using "USE dbName" the reason for this is because this would be a
separate batch and a stored procedure is a collection of only one batch of
statements.
 CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
 -- Add the parameters for the stored procedure here
 <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
 <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
 AS
 BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 -- Insert statements for procedure here

 SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
 END
 GO
30 Stored procedure with parameter
 Creating a stored procedure with default parameters values
 Following is the example of a stored procedure with default parameter values.
 Create PROCEDURE [dbo].[selectemp]
 @pempcode numeric(6)=78091
 AS
 BEGIN
 SET NOCOUNT on;
 -- Insert statements for procedure here

 select * from employee where emp_code = @pempcode
 END
31 Stored Procedure
 Creating a stored procedure with an output parameter
 ALTER PROCEDURE [dbo].[ins_NewEmp_with_outputparamaters]
 (@Ename varchar(50),
 @EId int output)
 AS
 BEGIN
 SET NOCOUNT ON

 INSERT INTO Employee (Emp_Name,dept_code ,remarks1) VALUES (@Ename,11,'ok')


 SELECT @EId= SCOPE_IDENTITY()


 END
 Modifying the stored procedure
 Use the ALTER PROCEDURE statement to modify the existing stored procedure.
 ALTER PROCEDURE [dbo].[studentcount]
 @cnt as numeric(5) output
 AS
 BEGIN
 --SET NOCOUNT on;
 select @cnt=count(*) from Student

 print @cnt
 Renaming the stored procedure
 To rename a stored procedure using T-SQL, use system stored procedure
sp_rename
 sp_rename 'selectemp', 'selectempnew‘
34 Variable Types in SQL: Local, Global
 MS SQL has two types of variables:
 Local variable
 Global variable.
 However, the user can only create a local variable.

 Local variable
 A user declares the local variable.
 By default, a local variable starts with @.
 Every local variable scope has the restriction to the current batch or
procedure within any given session.

35 Global variables
 Global variable
 The system maintains the global variable. A user cannot declare them.
 The global variable starts with @@
 It stores session related information.
 The following are some frequently used global variables –

 @@SERVERNAME
 @@CONNECTIONS
 @@MAX_CONNECTIONS
 @@CPU_BUSY
 @@ERROR
 @@IDLE
 @@LANGUAGE
 @@TRANCOUNT
 @@VERSION
36 Global Variable
 @@SERVERNAME :
This is used to find the name of the machine/computer on which SQL Server
is running
 @@CONNECTIONS :
This is used to find number of logins or attempted logins since SQL Server
was last started.
 @@MAX_CONNECTIONS :
This is used to find the maximum number of simultaneous connections that
can be made with SQL Server or instance in this computer environment.
37 Global Variable
 @@CPU_BUSY :
This is used to find the amount of time, in microseconds, that the CPU has
spent doing SQL Server work since the last time SQL Server was running.
 @@ERROR :
This is used to check the error status (succeeded or failed) of the most
recently executed statement. It contains Zero (0) if the previous transaction
succeeded, else, it contains the last error number generated by the system.
 @@IDLE :
The amount of time, in microseconds, that SQL Server has been idle since it
was last started.
38 Global Variable
 @@LANGUAGE :
This is used to find the name of the language that is currently used by the
SQL Server.
 @@TRANCOUNT :
This is used to count the number of open transactions in the current session.
 @@VERSION :
This is used to find the current version of the SQL Server Software.
39 Variables
 Before using any variable in batch or procedure, you need to declare the variable.
 DECLARE command is used to DECLARE variable which acts as a placeholder for
the memory location.
 Only once the declaration is made, a variable can be used in the subsequent part of
batch or procedure.
 Rules:
 Initialization is an optional thing while declaring.
 By default, DECLARE initializes variable to NULL.
 Using the keyword ‘AS’ is optional.
 To declare more than one local variable, use a comma after the first local variable
definition, and then define the next local variable name and data type.
40 Variables
 Assigning a value to SQL Variable
 You can assign a value to a variable in the following three ways:
 During variable declaration using DECLARE keyword.
 Using SET
 Using SELECT
41 Conditional Statement
 Conditional statements in the SQL server help you to define different logics and
actions for different conditions. It allows you to perform different actions based on
conditions defined within the statement.
 IF… Else statement in SQL Server
 In MS SQL, IF…ELSE is a type of Conditional statement.
 Any T-SQL statement can be executed conditionally using IF… ELSE.
 If <condition>
 Statement/Block of statements
 Else
 Statements Block of statement
42 Rules
 The condition should be Boolean Expression, i.e., condition results in Boolean

value when evaluated.
 IF ELSE statement in SQL can conditionally handle a single T-SQL statement or
block of T-SQL statements.
 Block of statement should start with keyword BEGIN and close with keyword
END.
 Using BEGIN and END helps SQL server to identify statement block that needs
to be executed and separate it from rest of the T-SQL statements which are not
part of IF…ELSE T-SQL block.
 ELSE is optional.
43 Case statement
 Nested CASE: CASE in IF ELSE
 DECLARE @Ticket int;
 SET @Ticket = 50;
 IF @Ticket > 400
 PRINT 'Visit DELHI';

 ELSE
 BEGIN
 SELECT
 CASE
 WHEN @Ticket BETWEEN 0 AND 100 THEN 'Visit CHD'
 WHEN @Ticket BETWEEN 101 AND 200 THEN 'Visit PKL'
 WHEN @Ticket BETWEEN 201 AND 400 THEN 'Visit MOHALI'
 END AS Location
 END
44 SQL Cursor
 SQL cursor is one of the most popular database objects. It is used to retrieve
data from the result set of an SQL query one row at a time. Even if the cursor
is not recommended from a performance perspective, they are still widely
used especially when handling a small amount of data.
1 DECLARE <Cursor_name> CURSOR FOR <Source_SQL_Query>
45 cursor
 DECLARE @var1 NVARCHAR(50)
 DECLARE @var2 NVARCHAR(50)

 DECLARE csr CURSOR FOR SELECT col1,col2 from <tablename>

 OPEN csr

 FETCH NEXT FROM csr INTO @col1, @col2


 WHILE @@FETCH_STATUS = 0
 BEGIN
 FETCH NEXT FROM csr INTO @col1, @col2
 END

 CLOSE csr
 Deallocate csr
46 functions
 String functions
 Upper
 Lower
 Left
 Right
 Reverse
 Concat
 Replicate
 Substring(expression,start,length)
 Ltrim
 RTrim
 Len
47 String function
 Stuff function
 The STUFF() function deletes a part of a string and then inserts another
part into the string, starting at a specified position.

 STUFF(string, start, length, new_string)
48 Date function
 Date functions
 Getdate()
 Day
 Month
 Year
 dateadd - DATEADD (datepart , number , date )
 Datepart – year,month,week,day
 DATEDIFF ( datepart , startdate , enddate )

49 Isnull function
 Isnull
 It allows the user to replace the NULL values with a given replacement
value.
 ISNULL ( Expression, Replacement )
50 Row_number
 WITH dup_cte AS (
 SELECT
 studID,
 rollNo,
 Name,

 ROW_NUMBER() OVER(

 PARTITION BY
 studID,
 rollNo,
 Name

 ORDER BY
 studID,
 rollNo,
 Name
 ) row_num
 FROM students
 ) select * from dup_cte
51
 SELECT
 studID,
 rollNo,
 Name,
 COUNT(*) occurrences
 FROM students
 GROUP BY
 studID,
 rollNo,
 Name
 HAVING COUNT(*) > 1
 )

 SELECT students.studID, students.rollNo, students.Name FROM students INNER JOIN dup_cte ON dup_cte.studID= students.studID
 AND
 dup_cte.rollNo = students.rollNo
 AND
 dup_cte.Name = students.Name

 ORDER BY
 students.studID,
 students.rollNo,
 students.Name;
52
 SELECT
 studID,
 rollNo,
 Name,

 ROW_NUMBER() OVER(

 PARTITION BY
 studID,
 rollNo,
 Name

 ORDER BY
 studID,
 rollNo,
 Name
 ) row_num
 FROM students
 ) delete from dup_cte where row_num > 1
 select * from students
53 Rank
 SELECT first_name, last_name, city,
 RANK () OVER (ORDER BY city) AS Rank_No
 FROM rankdemo;
 DENSE_RANK() Function
 This function assigns a unique rank for each row within a partition as per the
specified column value without any gaps. It always specifies ranking in

consecutive order. If we get a duplicate value, this function will assign it with
the same rank, and the next rank being the next sequential number. This
characteristic differs DENSE_RANK() function from the RANK() function.
54 Rank function
 RANK() Function
 This function is used to determine the rank for each row in the result set.
The following syntax illustrates the use of a RANK function in SQL

Server:
 SELECT column_name
 RANK() OVER (
 PARTITION BY expression
 ORDER BY expression [ASC|DESC])
 AS 'my_rank' FROM table_name;
55
 select * from employee
 select emp_code,ROW_NUMBER() over( partition by empname order by

emp_code) as rownumber from employee
56 SQL Server functions
 Functions in SQL Server are the database objects that contains a set of SQL
statements to perform a specific task. A function accepts input parameters,
perform actions, and then return the result. We should note that functions
always return either a single value or a table. The main purpose of functions
is to replicate the common task easily. We can build functions one time and
can use them in multiple locations based on our needs. SQL Server does not
allow to use of the functions for inserting, deleting, or updating records in the
database tables.
57 Rules
 A function must have a name, and the name cannot begin with a special
character such as @, $, #, or other similar characters.
 SELECT statements are the only ones that operate with functions.
 We can use a function anywhere such as AVG, COUNT, SUM, MIN, DATE,
and other functions with the SELECT query in SQL.
 Whenever a function is called, it compiles.
 Functions must return a value or result.
 Functions use only input parameters.
 We cannot use TRY and CATCH statements in functions.
58 functions
 SQL Server categorizes the functions into two types:
 System Functions
 User-Defined Functions
 Functions that are defined by the system are known as system functions. In
other words, all the built-in functions supported by the server are referred to
as System functions. The built-in functions save us time while performing the
specific task. These types of functions usually work with the SQL SELECT
statement to calculate values and manipulate data.
59 System functions
 String Functions (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
 Date and Time Functions (datetime, datetime2, smalldatetime)
 Aggregate Functions (COUNT, MAX, MIN, SUM, AVG)
 Mathematical Functions (ABS, POWER, PI, EXP, LOG)
 Ranking Functions (RANK, DENSE_RANK, ROW_NUMBER)
60 Functions
 Functions that are created by the user in the system database or a user-
defined database are known as user-defined functions. The UDF functions
accept parameters, perform actions, and returns the result. These functions
help us to simplify our development by encapsulating complex business logic
and making it available for reuse anywhere based on the needs. The user-
defined functions make the code needed to query data a lot easier to write.
They also improve query readability and functionality, as well as allow other
users to replicate the same procedures.
 SQL Server categorizes the user-defined functions mainly into two types:
 Scalar Functions
 Table-Valued Functions
61 functions
 Scalar function in SQL Server always accepts parameters, either single or multiple
and returns a single value. The scalar functions are useful in the simplification of
our code. Suppose we might have a complex computation that appears in a number
of queries. In such a case, we can build a scalar function that encapsulates the
formula and uses it in each query instead of in each query.
 CREATE FUNCTION schema_name.function_name (parameter_list)
 RETURNS data_type AS
 BEGIN
 statements
 RETURN value
 END
62 Table valued function
 Table-valued functions in SQL Server are the user-defined function that returns
data of a table type. Since this function's return type is a table, we can use it the
same way as we use a table.
 Inline Table-Values Functions
 This UDF function returns a table variable based on the action performed by the
function. A single SELECT statement should be used to determine the value of

the table variable.
 CREATE FUNCTION fudfGetEmployee()
 RETURNS TABLE
 AS
 RETURN (SELECT * FROM Employee)
63 functions
 SQL Server also allows us to modify the table-valued functions using the
ALTER keyword instead of the CREATE keyword.
 Multi-statement table-valued functions (MSTVF)
 This UDF function returns a table variable based on the action performed
by the function. It can contain single or multiple statements to produce the
result, and it is also a function that returns the result of multiple statements in
a tabular form. It is useful because we can execute multiple statements in this
function and get aggregated results into the returned table. We can define this
function by using a table variable as the return value. Inside the function, we
execute multiple queries and insert data into this table variable.
64 Exception handling
 SQL Server also has an exception model to handle exceptions and errors that
occurs in T-SQL statements. To handle exception in Sql Server we have
TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle
exception we write code in CATCH block. If there is an error in code within
TRY block then the control will automatically jump to the corresponding
CATCH blocks. In Sql Server, against a Try block, we can have only one
CATCH block.
 BEGIN TRY { sql_statement | statement_block } END TRY
 BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH
65 Exception handling
 In the scope of a CATCH block, the following system functions can be used to
obtain information about the error that caused the CATCH block to be executed:
 ERROR_NUMBER() returns the number of the error.
 ERROR_SEVERITY() returns the severity.
 ERROR_STATE() returns the error state number.
 ERROR_PROCEDURE() returns the name of the stored procedure or trigger
where the error occurred.
 ERROR_LINE() returns the line number inside the routine that caused the error.
 ERROR_MESSAGE() returns the complete text of the error message.
66 Sequence
 insert into itemmaster values(next value for dbo.testseq,'keyboard',2)
 ALTER SEQUENCE dbo.testseq
 RESTART WITH 1 ;

Dbms SQL Server1

Uploaded by

Copyright:

Available Formats

Dbms SQL Server1

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Dbms SQL Server1

Uploaded by

Copyright:

Available Formats

[Unit 1: Introduction to Web Design] Course: NIELIT ‘O’ Level (IT)

Module: M2-R5: Web Designing & Publishing

 Insert into table_name(col1,col2,col3,…) values (value1,value2,value3,…)

 Where clause specifies which records are to be updated, if where clause is

 The percent sign % represents zero , one or multiple character.

 The underscore sign _ represents one , single character.

 The In operator allows you to specify multiple values in where clause.

 ALTER TABLE Persons

 select emp_name ,case when dept_code = 11 then 'Admin' when

 The column in every select statement must be in same orders

 SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amoun

 RIGHT OUTER JOIN

 FULL OUTER JOIN

FROM table1 RIGHT [OUTER] JOIN table2

 -- Insert statements for procedure here

 -- Insert statements for procedure here

 INSERT INTO Employee (Emp_Name,dept_code ,remarks1) VALUES (@Ename,11,'ok')

 SELECT @EId= SCOPE_IDENTITY()

 select @cnt=count(*) from Student

 However, the user can only create a local variable.

 By default, a local variable starts with @.

procedure within any given session.

 The global variable starts with @@

 It stores session related information.

 The following are some frequently used global variables –

 By default, DECLARE initializes variable to NULL.

 Using the keyword ‘AS’ is optional.

 During variable declaration using DECLARE keyword.

 Any T-SQL statement can be executed conditionally using IF… ELSE.

 The condition should be Boolean Expression, i.e., condition results in Boolean

 SET @Ticket = 50;

 IF @Ticket > 400

 PRINT 'Visit DELHI';

 WHEN @Ticket BETWEEN 0 AND 100 THEN 'Visit CHD'

 WHEN @Ticket BETWEEN 101 AND 200 THEN 'Visit PKL'

 WHEN @Ticket BETWEEN 201 AND 400 THEN 'Visit MOHALI'

 DECLARE csr CURSOR FOR SELECT col1,col2 from <tablename>

 FETCH NEXT FROM csr INTO @col1, @col2

part into the string, starting at a specified position.

 dateadd - DATEADD (datepart , number , date )

 DATEDIFF ( datepart , startdate , enddate )

specified column value without any gaps. It always specifies ranking in

The following syntax illustrates the use of a RANK function in SQL

 select emp_code,ROW_NUMBER() over( partition by empname order by

function. A single SELECT statement should be used to determine the value of

You might also like