create table
tbl (id int, col1 varchar(50),col2 varchar(50), col3 varchar(50))

insert into
tbl values(1,'colval1','colval2','colval3')

CREATE FUNCTION
fn_TestFunc

(

@param1 int

)

RETURNS varchar(max)

BEGIN

declare
@result varchar(max)

select
@result =
Col1+ ','
+ Col2 + ',' + Col3

from tbl t

where t.ID = @param1

return
@result

END

select dbo.fn_TestFunc(1)

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with
data.

Below is an example of a table called "Persons":

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).

SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement will select all the records in the "Persons" table:

SELECT * FROM Persons

In this tutorial we will teach you all about the different SQL statements.

Keep in Mind That...

SQL is not case sensitive

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to
the server.

We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

SELECT
- extracts data from a database
UPDATE
- updates data in a database
DELETE
- deletes data from a database
INSERT INTO
- inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints
between tables. The most important DDL statements in SQL are:

CREATE DATABASE
- creates a new database
ALTER DATABASE
- modifies a database
CREATE TABLE
- creates a new table
ALTER TABLE
- modifies a table
DROP TABLE
- deletes a table
- creates an index (search key)
DROP INDEX
- deletes an index

SQL AND & OR Operators

« Previous

Next Chapter »

The AND & OR operators are used to filter records based on more than one condition.

The AND & OR Operators

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson":

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName='Tove'

AND LastName='Svendson'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

OR Operator Example

Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName='Tove'

OR FirstName='Ola'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to "Tove" OR to "Ola":

We use the following SELECT statement:

SELECT * FROM Persons WHERE

LastName='Svendson'

AND (FirstName='Tove' OR FirstName='Ola')

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s) ASC|DESC

ORDER BY Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger

Now we want to select all the persons from the table above, however, we want to sort the persons by their last name.

We use the following SELECT statement:

SELECT * FROM Persons

ORDER BY LastName

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
4	Nilsen	Tom	Vingvn 23	Stavanger
3	Pettersen	Kari	Storgt 20	Stavanger
2	Svendson	Tove	Borgvn 23	Sandnes

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.

We use the following SELECT statement:

SELECT * FROM Persons

ORDER BY LastName DESC

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger
1	Hansen	Ola	Timoteivn 10	Sandnes

Try it Yourself

To see how SQL works, you can copy the SQL statements below and paste them into the textarea, or you can make your own SQL statements.

SELECT * FROM customers

SELECT CompanyName, ContactName FROM customers

SELECT * FROM customers WHERE companyname LIKE 'a%'

SELECT CompanyName, ContactName

FROM customers

WHERE CompanyName > 'a'

When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).

SELECT CompanyName, ContactName

FROM customers

WHERE CompanyName > 'g'

AND ContactName > 'g'

The TOP Clause

The TOP clause is used to specify the number of records to return.

The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note:
Not all database systems support the TOP clause.

SQL Server Syntax

SELECT TOP number|percent column_name(s)

FROM table_name

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

SELECT column_name(s)

FROM table_name

LIMIT number

Example

SELECT *

FROM Persons

LIMIT 5

Oracle Syntax

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

Example

SELECT *

FROM Persons

WHERE ROWNUM <=5

SQL TOP Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger

Now we want to select only the two first records in the table above.

We use the following SELECT statement:

SELECT TOP 2 * FROM Persons

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

SQL TOP PERCENT Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger

Now we want to select only 50% of the records in the table above.

We use the following SELECT statement:

SELECT TOP 50 PERCENT * FROM Persons

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

SQL LIKE Operator

« Previous

Next Chapter »

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

The LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern

LIKE Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons living in a city that starts with "s" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 's%'

The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%s'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "tav" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%tav%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
3	Pettersen	Kari	Storgt 20	Stavanger

It is also possible to select the persons living in a city that NOT contains the pattern "tav" from the "Persons" table, by using the NOT keyword.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City NOT LIKE '%tav%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

SQL Wildcard Examples

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character,
followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

IN Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName IN ('Hansen','Pettersen')

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

SQL Alias

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column
names.

An alias name could be anything, but usually it is short.

SQL Alias Syntax for Tables

SELECT column_name(s)

FROM table_name

AS alias_name

SQL Alias Syntax for Columns

SELECT column_name AS alias_name

FROM table_name

Alias Example

Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases of "p" and "po" respectively.

Now we want to list all the orders that "Ola Hansen" is responsible for.

We use the following SELECT statement:

SELECT po.OrderID, p.LastName, p.FirstName

FROM Persons AS p,

Product_Orders AS po

WHERE p.LastName='Hansen' AND p.FirstName='Ola'

The same SELECT statement without aliases:

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName

FROM Persons,

Product_Orders

WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'

As you'll see from the two SELECT statements above; aliases can make queries easier to both write and to read.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The
purpose is to bind data together, across tables, without repeating all of the data in every table.

Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id
distinguishes two persons even if they have the same name.

Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using
their names.

Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

JOIN
: Return rows when there is at least one match in both tables
LEFT JOIN
: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN
: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN
: Return rows when there is a match in one of the tables

SQL INNER JOIN Keyword

« Previous

Next Chapter »

SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

PS:
INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders",
those rows will NOT be listed.

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

PS:
In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

LEFT JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

PS:
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the orders with containing persons - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

RIGHT JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
		34764

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

SQL FULL JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders, and all the orders with their persons.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

FULL JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove
		34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons"
that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

3 TABLE JOINING

string fetchtbl = "select hd.Tamil_year_txt,hd.Tamil_month_txt,hd.Tamil_Day_lng,hd.Stare_txt,hd.Padham_txt," +

"hd.Rasi_txt,hd.Lagnam_txt,hd.Chevvai_Dhosam_txt,hd.Nalikai_txt,hd.Didi_txt,hd.Suya_gothram_txt," +

"hd.Madulam_txt,hd.Dasa_Name_txt,hd.Dasa_Balance_txt,hd.Date_txt,hd.Month_txt,hd.Year_txt,hr.hr1_txt,hr.hr2_txt,hr.hr3_txt,hr.hr4_txt," +

"hr.hr5_txt,hr.hr6_txt,hr.hr7_txt,hr.hr8_txt,hr.hr9_txt,hr.hr10_txt,hr.hr11_txt,hr.hr12_txt," +

"ha.ha1_txt,ha.ha2_txt,ha.ha3_txt,ha.ha4_txt,ha.ha5_txt,ha.ha6_txt,ha.ha7_txt,ha.ha8_txt,ha.ha9_txt,ha.ha10_txt," +

"ha.ha11_txt,ha.ha12_txt" +

" from horos_details_tbl hd inner join horos_rasi_tbl hr on hd.user_id=hr.user_id" +

" inner join horos_amsam_tbl ha on hr.user_id=ha.user_id where hd.user_id='" + lbluserid.Text + "'";

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns
in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

Note:
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

PS:
The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

SQL UNION Example

Look at the following tables:

"Employees_Norway"
:

E_ID	E_Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

"Employees_USA"
:

E_ID	E_Name
01	Turner, Sally
02	Kent, Clark
03	Svendson, Stephen
04	Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway

UNION

SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Scott, Stephen

Note:
This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will
be listed. The UNION command selects only distinct values.

SQL UNION ALL Example

Now we want to list all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway

UNION ALL

SELECT E_Name FROM Employees_USA

Result

E_Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Svendson, Stephen

Scott, Stephen

SQL Dates

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column
in the database.

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.

Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:

Function	Description
NOW()	Returns the current date and time
CURDATE()	Returns the current date
CURTIME()	Returns the current time
DATE()	Extracts the date part of a date or date/time expression
EXTRACT()	Returns a single part of a date/time
DATE_ADD()	Adds a specified time interval to a date
DATE_SUB()	Subtracts a specified time interval from a date
DATEDIFF()	Returns the number of days between two dates
DATE_FORMAT()	Displays date/time data in different formats

SQL Server Date Functions

The following table lists the most important built-in date functions in SQL Server:

Function	Description
GETDATE()	Returns the current date and time
DATEPART()	Returns a single part of a date/time
DATEADD()	Adds or subtracts a specified time interval from a date
DATEDIFF()	Returns the time between two dates
CONVERT()	Displays date/time data in different formats

SQL Date Data Types

MySQL
comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
YEAR - format YYYY or YY

SQL Server
comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: a unique number

Note:
The date types are chosen for a column when you create a new table in your database!

For an overview of all data types available, go to our complete Data Types reference.

SQL Working with Dates

You can compare two dates easily if there is no time component involved!

Assume we have the following "Orders" table:

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11
2	Camembert Pierrot	2008-11-09
3	Mozzarella di Giovanni	2008-11-11
4	Mascarpone Fabioli	2008-10-29

Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

The result-set will look like this:

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11
3	Mozzarella di Giovanni	2008-11-11

Now, assume that the "Orders" table looks like this (notice the time component in the "OrderDate" column):

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11 13:23:44
2	Camembert Pierrot	2008-11-09 15:45:21
3	Mozzarella di Giovanni	2008-11-11 11:12:01
4	Mascarpone Fabioli	2008-10-29 14:56:59

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

we will get no result! This is because the query is looking only for dates with no time portion.

Tip:
If you want to keep your queries simple and easy to maintain, do not allow time components in your dates!

NULL values represent missing unknown data.

By default, a table column can hold NULL values.

This chapter will explain the IS NULL and IS NOT NULL operators.

SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the
field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note:
It is not possible to compare NULL and 0; they are not equivalent.

SQL Working with NULL Values

Look at the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola		Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari		Stavanger

Suppose that the "Address" column in the "Persons" table is optional. This means that if we insert a record with no value for the "Address" column, the
"Address" column will be saved with a NULL value.

How can we test for NULL values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

SQL IS NULL

How do we select only the records with NULL values in the "Address" column?

We will have to use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons

WHERE Address IS NULL

The result-set will look like this:

LastName	FirstName	Address
Hansen	Ola
Pettersen	Kari

Tip:
Always use IS NULL to look for NULL values.

SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?

We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons

WHERE Address IS NOT NULL

The result-set will look like this:

LastName	FirstName	Address
Svendson	Tove	Borgvn 23

In the next chapter we will look at the ISNULL(), NVL(), IFNULL() and COALESCE() functions.

SQL HAVING Clause

« Previous

Next Chapter »

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value

SQL HAVING Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find if any of the customers have a total order of less than 2000.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000

The result-set will look like this:

Customer	SUM(OrderPrice)
Nilsen	1700

Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

WHERE Customer='Hansen' OR Customer='Jensen'

GROUP BY Customer

HAVING SUM(OrderPrice)>1500

The result-set will look like this:

Customer	SUM(OrderPrice)
Hansen	2000
Jensen	2000

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name

Parameter	Description
column_name	Required. The field to round.
decimals	Required. Specifies the number of decimals to be returned.

SQL ROUND() Example

We have the following "Products" table:

Prod_Id	ProductName	Unit	UnitPrice
1	Jarlsberg	1000 g	10.45
2	Mascarpone	1000 g	32.56
3	Gorgonzola	1000 g	15.67

Now we want to display the product name and the price rounded to the nearest integer.

We use the following SELECT statement:

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

The result-set will look like this:

ProductName	UnitPrice
Jarlsberg	10
Mascarpone	33
Gorgonzola	16

The AVG() Function

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

SQL AVG() Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find the average value of the "OrderPrice" fields.

We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result-set will look like this:

OrderAverage

950

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.

We use the following SQL statement:

SELECT Customer FROM Orders

WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The result-set will look like this:

Customer

Hansen

Nilsen

Jensen

SQL Views

« Previous

Next Chapter »

A view is a virtual table.

This chapter shows how to create, update, and delete a view.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Note:
A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.

The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the
following SQL:

CREATE VIEW [Current Product List] AS

SELECT ProductID,ProductName

FROM Products

WHERE Discontinued=No

We can query the view above as follows:

SELECT * FROM [Current Product List]

Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:

CREATE VIEW [Products Above Average Price] AS

SELECT ProductName,UnitPrice

FROM Products

WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the view above as follows:

SELECT * FROM [Products Above Average Price]

Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called
"Product Sales for 1997":

CREATE VIEW [Category Sales For 1997] AS

SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales

FROM [Product Sales for 1997]

GROUP BY CategoryName

We can query the view above as follows:

SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":

SELECT * FROM [Category Sales For 1997]

WHERE CategoryName='Beverages'

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:

CREATE VIEW [Current Product List] AS

SELECT ProductID,ProductName,Category

FROM Products

WHERE Discontinued=No

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

SQL Constraints

« Previous

Next Chapter »

SQL Constraints

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

We will focus on the following constraints:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

The next chapters will describe each constraint in details.

SQL NOT NULL Constraint

« Previous

Next Chapter »

By default, a table column can hold NULL values.

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a
value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

SQL UNIQUE Constraint

« Previous

Next Chapter »

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

UNIQUE (P_Id)

)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(

P_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

)

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD UNIQUE (P_Id)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons

DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

SQL CREATE INDEX Statement

« Previous

Next Chapter »

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.

Indexes

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note:
Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on
columns (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

Note:
The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex

ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex

ON Persons (LastName, FirstName)

SQL Wildcards

« Previous

Next Chapter »

SQL wildcards can be used when searching for data in a database.

SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

SQL Wildcard Examples

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character,
followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

SQL IN Operator

« Previous

Next Chapter »

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

IN Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName IN ('Hansen','Pettersen')

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Introducing to Stored Procedure

Stored procedure by definition is a segment of code which contains declarative or procedural SQL statements. A stored procedure is resided in the catalog
of the database server so we can call it from a trigger, another stored procedure or even from client appliations.

As the definition above, the stored procedure can contains any SQL statement like INSERT, UPDATE and DELETE or any SQL data definition like CREATE TABLE,
ALTER TABLE and etc. In addition, a stored procedure also supports procedure statements such as IF ELSE, WHILE... to make it as powerful as another
programming languages such as C/C++, C# and Java.

Using stored procedure has several advantages :

§ It is used to increases the performance of application because when we create stored procedures, they are compiled and stored in database catalog. Later
when client applications call them, they are generally executed faster than uncompiled SQL statements which are sent from the client applications.

§ The network traffic between application server and database server is also signification reduced because the applications don't have to send such long
and uncompiled SQL statements to the server to get the data back.

§ Stored procedures can be used for database security purpose because each store procedure can have its own database privileges.

§ One of the most advantage of stored procedure is code reusability. Once created, a stored procedure can be reused over and over again by multiple
applications.

It is the best to illustrate the ability of stored procedure by showing examples. You can follow this tutorial to understand more about stored procedure.
We will use Microsoft SQL Server to demonstrate stored procedure, you can also use MySQL with a change a little bit because of specification of
each database server is different. Start learning how to write a stored procedure by following the tutoria

Writing the first stored procedure

Here is the first stored procedure source code:

1.CREATE
PROCEDURE
Delete_Employee

2.(@EmployeeId
INT)

3.AS

4.

5.BEGIN

6.DELETE
FROM
Employees

7.WHERE
EmployeeId = @EmployeeId;

8.END

A stored procedure must contains at least three parts: stored procedure name, parameter list and its body.

The CREATE PROCEDURE is similar to CREATE TABLE or INDEX statement. It is actually a SQL statement. The CREATE PROCEDURE will force the database server add
the stored procedure to the its catalog. The name of stored procedure is followed after the CREATE PROCEDURE statement, in this case it is Delete_Employee. It would be the best that the name is meaningful and follows by the naming convention of the database server specification, for
example each stored procedure should begin with "sp". In almost relation database product, the name of stored procedure must be unique.

The second part of the stored procedure is parameter list. In this case the list contains only one parameter @EmployeeId (the employee identity).
Microsoft SQL Server requires prefix @ for every parameters and variables of stored procedure. Followed each parameter is its type, in this case, its type
is integer (INT).

The main part of a stored procedure is the stored procedure body. It starts with keywords BEGIN and ends with keyword END. In this example the body is very
simple; It deletes employee by employee identity.

When all syntax statements inside body are correct, the database server will store the stored procedure name and code in its catalog for reusing later by
another stored procedure or programs.

Calling a stored procedure

We can call a stored procedure from the console window, from another stored procedure or from a program which can access database server. The syntax of
calling a stored procedure is simple as follows:

1.EXEC
spName(parameter_value_list)

The EXEC statement is used to invoke a stored procedure. After the EXEC statement is the stored procedure name followed by parameter list. This is an
example to delete an employees with identity is 8 by calling the sample procedure above Delete_Employee:

1.EXEC
Delete_Employee(8)

If a stored procedure has more than one parameters, the values of them can be passed to it and separated by a comma.

As you see writing and calling a stored procedure is very simple and easy. In the following tutorials, we will show you the feature and syntax of a stored
procedure along with statement which can be used inside the body so you can empower its power. Next you will learn how to use parameter list to pass and
get data in and out of stored procedures parameter list in stored procedure.

stored procedure can have zero, one or more than one parameters. If a stored procedure has more than one parameter, each one must be separated by a comma.

A parameter can be described by three parts : name, data type and its type which can by IN, OUT or INOUT. The first two pars are mandatory and the third
part is optional.

The name of parameter in Microsoft SQL Server must has @ sign as the prefix Otherwise the database server will notify the error and of course the stored
procedure cannot be saved in the database catalog.

Following the name is the parameter data type. The data type of the stored procedure can be any valid data type which are predefined in the database
server. If you specify the size of the data type you can do it as follows:

1.@parameter VARCHAR(255)

The third part of a parameter is its types. This part is optional, by default it is the IN. There are three parameter types: IN, OUT and INOUT. As you
guess IN is abbreviation of input, OUT is abbreviation of output and INOUT is combined of both. With input parameter you can pass the value to the stored
procedure. As in the previous tutorial example, we pass the employee identity to the storprocedure and delete the employee based on it. Output parameter
allows you get the value back from the stored procedure, for example you may write a stored procedure to insert an employee and get its id back to the
application to use in another part of the program. The INOUT parameter type as its name suggested can act as both types of parameter.

In some SQL database sever product, it does not require parameter has @ sign as prefix so you should be careful that the name of the parameter must not be
equal to the name of column otherwise you may face up to the disaster without any warning or error message from the database server.

In Microsoft SQL Server 2005/20008, it allows you to specify the default value of the parameter. this is a big plus because you can call a stored procedure
without passing parameters, It will use default values. Here is syntax of default value of parameter.

1.@parameter DataType(Size) = Default_Value

In our example we can modify to use default value for stored procedure as follows:

1.CREATE PROCEDURE Delete_Employee

2.(@EmployeeId INT = 0)

3.AS

5.BEGIN

6.DELETE FROM Employees

7.WHERE EmployeeId = @EmployeeId;

8.END

When you call it without passing parameter value, it will delete the employee with the identity is zero.

SQL stored procedure parameter list is simple and easy to grasp? let's move to

The body part of a stored procedure is where you can put your business logic codesinside to execute it as you want it to do. A stored procedure body always
begin with BEGIN and END keywords. Inside the body, you can put the declarative SQL statements or procedure call like calling other stored procedures.
Beside that, you can also use procedure statement like IF, WHILE; you can declare local variables and use them inside the stored procedure. Here is the
general stored procedure syntax :

01.<create procedure statement> ::=

02.CREATE PROCEDURE <procedure name> ( [

03.<parameter list> ] ) AS

04.<procedure body>

05.

06.<procedure body> ::= <begin-end block>

07.

08.<begin-end block> ::=

09.[ <label> : ] BEGIN <statement list> END [

10.<label> ]

11.

12.<statement list> ::= { <body statement> ; }...

13.

14.<statement in body::=

15.<declarative statement> |

16.<procedural statement>

17.

18.<declarative statement> ::=

19.<EXEC statement> |

20.<CLOSE statement> |

21.<COMMIT statement> |

22.<DELETE statement> |

23.<EXECUTE immediate statement> |

24.<FETCH statement> |

25.<INSERT statement> |

26.<LOCK TABLE statement> |

27.<OPEN statement> |

28.<ROLLBACK statement> |

29.<savepoint statement> |

30.<SELECT statement> |

31.<SELECT INTO statement> |

32.<SET statement> |

33.<SET transaction statement> |

34.<start -transaction statement> |

35.<UPDATE statement>

36.

37.<procedural statement> ::=

38.<BEGIN-END block> |

39.<EXEC statement> |

40.<CLOSE statement> |

41.<DELCARE condition statement> |

42.<DELCARE cursor statement> |

43.<DELCARE handler statement> |

44.<DELCARE variable statement> |

45.<FETCH cursor statement> |

46.<flow control statement> |

47.<OPEN cursor statement> |

48.<SET statement>

With the BEGIN and END keword you can label the block of code inside the body. You can have one or more blocks, each block can be nested each other.
Labeling the block has its own advantages. For example, it makes your code more clean when you have mutilple blocks. Let's get your hand with some source
code to demonstrate the stored procedure body.

Imagine we have employee table, in one day the table may have many records and it is very costly to get all the data from it to display them in
our application. It would be nice if we can provide pagination feature for application to select needed records it needs to reduce the traffic between the
database server and application server. Here is stored procedure to make it possible:

01.CREATE PROCEDURE GetEmployeePaged

02.@PageSize int = 10,-- pagesize

03.@CurrentPage int = 1,-- current page no

04.@ItemCount int output -- total employee found

05.AS

06.BEGIN

07.-- declare local variables for pagination

08.DECLARE @UpperBand INT,

09.@LowerBand INT

10.SET @LowerBand = (@CurrentPage - 1)* @PageSize

11.SET @UpperBand = @CurrentPage* @PageSize + 1

12.

13.-- assign itemcount output parameter

14.SET @ItemCount = (

15.SELECT COUNT(employeeId)

16.FROM employees

17.)

18.

19.-- create temporary table to store paged data

20.CREATE TABLE #ALLROW(

21.RowID INT PRIMAY KEY IDENTITY(1,1),

22.EmployeeId INT,

23.Name VARCHAR(255),

24.salary DECIMAL(7,2)

25.)

26.

27.-- insert data into the temporary table

28.INSERT INTO #ALLROW

29.SELECT EmployeeId, Name, salary

30.FROM employees

31.

32.-- get paged data

33.SELECT *

34.FROM #ALLROW

35.WHERE RowID > @LowerBand AND RowID < @UpperBand

36.END

First in parameter list we have three parameters and their meanings are exactly what they are. @pagesize specifies number of record per page, @currentpage specifies the current page number and @itemcount specifies total record found. So we can get the employee record in the page
1 with 10 record per page by calling:

1.EXEC GetEmployeePaged(10,1,@itemcount)

The next we declare two local variables. These variables are used inside the stored procedure for determining the start row and end row we will retrive the
records. These variables' values are calculated based on the @pagesize and @currentpage.

Then we use SET statement to assign output parameter @itemcount to the total records of the employee.

Finally we create a temporary table to store the data, insert the data into the temporary table and retrieve the needed records.

As you can see the stored procedure is very flexible, you can leverage it to deal with tough situation in database developement.

In the next tutorial we will show you how to use local variable and use SET statement as shown in the example abo

Local variables are used in stored procedure to keep temporary intermediate results. In order to use a local variable we have to declare it explicitly as
follows:

1.<declare variable statement> ::=

2.DECLARE <variable list> <data type> [

3.DEFAULT <expression> ]

5.<variable list> ::=

6.<variable> [ { , <variable> }... ]

DECLARE keywords is used to declare local variables. This source code snippet shows you how to declare a numeric and an alphanumeric variables:

1.DECLARE @Found INT

2.DECLARE @Firstname VARCHAR(255)

Some database server support initial value for local variables and some not. If supported, we can specify the default value for local variable. The
expression for the default value of local variables are not limited to literals (like 'name' or 1) but may consist of compound expressions, including
scalar subqueries.

Local variable has its own scope. It does not exist when the stored procedure finish. Inside each code block local variable only visible it the block it
declared.

The SET statement can be used to assign value to local variable. Here is the syntax :

1.<set statement> ::=

2.SET <local variable definition>

3.[ {, <local variable definition> }... ]

5.<local variable definition> ::=

6.<local variable> { = | := } <scalar expression>

We can use SET statement to assign value or expression to local variable. You can refer to the example in stored procedure body tutorial.

Local variable is intermedia storage to store temporary result in stored procedure.In th

In this section, we will cover all basic flow-control statements which can be used in stored procedures. There are two most common usage of flow-control
which are conditional execution and loop.

Conditional execution with IF-THEN-ELSE and CASE statement

The IF-THEN-ELSE statement is used to evaluate the value of boolean expression; if the value is True it execute the block code that follows it otherwise it
will execute the block code that follows by ELSE.The ELSE part is optional in thestatement.

Here is an stored procedure which finds the maximum value between two integers using IF-THEN-ELSE statement. The example is easy just for demonstration.

01.CREATE
PROCEDURE
FindMax

02.@v1
INT,

03.@v2
INT,

04.@m
INT
OUTPUT

05.AS

06.BEGIN

07.IF @v1 > @v2

08.SET
@m = @v1

09.ELSE

10.SET
@m = @v2

11.END

In complex cases, we can use CASE statement instead of IF-THEN-ELSE statement. CASE statement evaluates a list of boolean expression and returns one of
multiple possible result expressions. CASE statement is similar to the swith-case statement in other programming languages such as C/C++, C# or Java. Here
is an example of using CASE statement to display salary level of employee. We have employee table data as follows:

1.employee_id
name
salary

2.----------- -------- -------

3.1 jack 3000.00

4.2 mary 2500.00

5.3 newcomer 2000.00

6.4 anna 2800.00

7.5 Tom 2700.00

8.6 foo 4700.00

And here is the stored procedure example:

01.CREATE
PROCEDURE
DisplaySalaryLevel

02.AS

03.BEGIN

04.SELECT
employeeId,
name,salary, salary_level =

05.CASE
salary

06.WHEN
salary < 1000

07.THEN
'very low'

08.WHEN
salary > 1000
AND
salary < 2000

09.THEN
'low'

10.WHEN
salary > 2000
AND
salary < 4000

11.THEN
'average'

12.WHEN
salary > 4000
AND
salary < 10000

13.THEN
'high'

14.WHEN
salary > 10000

15.THEN
'very high'

16.END

17.FROM
Production.Product

18.END

Looping with WHILE statement

Since T-SQL is fourth generation language and designed to operate with sets of data therefore it is also possible to write the code to loop through the
record set and perform operations on a single record. Using loop will cause the performance of the server but in some cases it is necessary to use this
feature. Here we will you an example of using WHILE loop statement to calculate the factorial of an integer number.

view source

print
?

01.CREATE
PROCEDURE
Cal_Factorial

02.@inyN
INT,

03.@intFactorial
BIGINT
OUTPUT

04.AS

05.BEGIN

06.SET
@intFactorial = 1

07.

08.WHILE @inyN > 1

09.BEGIN

10.SET
@intFactorial = @intFactorial * @inyN

11.SET
@inyN = @inyN - 1

12.END

13.END

In the real world database programming you will need to use the conditional statements to write own your stored procedures. Next we will show you how to modify stored procedure using ALTER and DROP statement

Once a stored procedure is resided in the database server catalog, we can modify it by using the ALTER PROCEDURE statement as follows:

1.ALTER PROCEDURE spName(parameter_list)

2.AS

3.BEGIN

4.-- stored procedure body here

5.END

To remove a stored procedure from database catalog, we can use DROP PROCEDURE.

1.DROP PROCEDURE spName

When you remove stored procedure, be noted that some database products have a feature that allows you to remove the dependency database objects like table,
view, index or other stored procedures also.

Stored procedure is compiled before it executes. Each database server has its own compiling strategy. You can specify the compiling option when you with
WITH RECOMPILE statement as follows:

1.CREATE PROCEDURE spName

2.(parameter_list) AS

3.WITH RECOMPILE

4.BEGIN

5.-- stored procedure body here

6.END

The WITH RECOMPILE statement guarantees that each time the stored procedure is invoked, the compiler is called and compile the stored procedure again. With
WITH RECOMPILER the stored procedure is recompiled and adjusted to the current situation of the database which brings some advantages to the processing
strategy. But be noted that the compilation process takes time and also decreases the performance of the database server. Therefore for each stored
procedure ,we can specify which is the best to use WITH RECOMPILE.

image_load

-------------

Create proc Image_Load

@Name varchar (50),

@Image_Name varchar (50),

@Image_File image ,

@Image_type varchar (50),

@Id int out

Insert into Image_tbl(Name_txt,Image_Name_txt,Image_File_img,Imag_Type_txt)

values(@Name,@Image_Name,@Image_File,@Image_type)

set @Id=@@Identity

Tables

-----------------

CREATE TABLE [dbo].[Image_tbl] (

[Id_lng] [int] IDENTITY (1, 1) NOT NULL ,

[Name_txt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Image_Name_txt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Image_File_img] [image] NOT NULL ,

[Imag_Type_txt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALL SP

CREATE proc sp_comm

@Mode varchar(10),

@ID int,

@Name varchar(20),

@Place varchar(20)

Begin

if @Mode='Add'

begin

insert into det_tbl values(@Id,@Name,@Place)

End

if @Mode='Update'

begin

update det_tbl set d_Name=@Name,d_Place=@Place where d_id=@id

end

if @Mode='Delete'

begin

delete from det_tbl where d_id=@Id

end

if @Mode='Select'

begin

select * from det_tbl

end

End

Introducing to Database Trigger

Triggers are a kind of stored procedure which are invoked automatically by database server when predefined events occurred. Events here can be altering the
tables, drop the tables, adding or removing rows on a table. Trigger is a kind of stored procedure means it can contains declarative or procedural SQL
statements except one thing it cannot be invoked explicitly by user or other stored procedures. It only can be invoked when a predefined event occurred.
Because of this feature, triggers are usually consider as events in the database.

Triggers can be applied in some contexts :

§ Triggers can helps database administrator gather statistic data on table access.

§ Triggers can be used to provide transparent logging service to log data changes and then send notification to the database administrator.

§ Triggers can be used to generate and maintain derived data in derived column such as computed column.

§ Triggers can be used to enforce data integrity when the business rule of data is too complex and prevent invalid transaction.

§ And triggers can be used to enforce complex security authorization context..

Triggers are useful for use in the database but it is suggested that it should be used only when necessary. The triggers can put the burden of
interdependency on the database and also burden of maintenance.

Triggers was added to SQL3 standard but different database servers implement trigger in different ways. So it is easier to demonstrate trigger in a
specified database server. We will use Microsoft SQL Server 2005 for demonstration We surely also try to provide you resources where you can find how other
database servers implements trigger.

SQL was designed to work with a set of data and SELECT statement is the most important statement. SQL statement allows us to select a record or a set of
records based on some criteria and it solves almost problem in database management. But in some database management contexts, we need to traverse through a
set or records, process each record, move to next record and loop until the final record is reached. This is the reason why database cursors has invented.

There are many types of cursors are implemented in various RDBMS products using different syntax. Even though, the way we work with cursors are following
this fashion:

1. We have to define a cursor with and set its features

2. Then we must populate a set of records to use with the cursor

3. We must set the position for cursor to a record or set of records that we need to traverse through

4. Data from one or more current records is fetched, then we can make some modification on it.

5. Loop the step 3 and 4 until the cursor reaches the final record in the set of record

6. Finally we have to close and release the cursor resources.

In this tutorial, we will use Transact-SQL cursors for demonstration. We also provide you resources if you work with other RDBMS such as MySQL, Oracle...
Transact-SQL cursor can be used from stored procedure, functions or triggers. Let's move to the section which we will show you how to use Transact-SQL
cursors.

Implementing Triggers in SQL Server 2000

By : Navneeth Diwaker Naik

Aug 31, 2004

Page 2 / 6

AFTER Triggers

The type of trigger that gets executed automatically after the statement that triggered it completes is called an AFTER trigger. An AFTER trigger is a
trigger that gets executed automatically before the transaction is committed or rolled back.

Using the below script, first we shall create a trigger on the table USER_MASTER for the INSERT event of the table.

USE KDMNN

CREATE TRIGGER trgInsert

ON User_Master

FOR INSERT

Print ('AFTER Trigger [trgInsert] – Trigger executed !!')

BEGIN TRANSACTION

DECLARE @ERR INT

INSERT INTO USER_MASTER(USERNAME, PASSWORD)

VALUES('Damerla','Damerla')

SET @ERR = @@Error

IF @ERR = 0

BEGIN

ROLLBACK TRANSACTION

PRINT 'ROLLBACK TRANSACTION'

END

ELSE

BEGIN

COMMIT TRANSACTION

PRINT 'COMMIT TRANSACTION'

END

Output

AFTER Trigger [trgInsert] – Trigger executed !!

(1 row(s) affected)

ROLLBACK TRANSACTION

By looking at the output, we can conclude that before the transaction is rolled back or committed, the AFTER trigger gets executed automatically. A table
can have several AFTER triggers for each of the three triggering actions i.e., INSERT, DELETE and UPDATE. Using the below script, we shall create two
triggers on the table User_Master for the INSERT triggering action.

CREATE TRIGGER trgInsert2

ON User_Master

FOR INSERT

BEGIN

Print ('AFTER Trigger [trgInsert2] – Trigger executed !!')

END

CREATE TRIGGER trgInsert3

ON User_Master

FOR INSERT

BEGIN

Print ('AFTER Trigger [trgInsert3] – Trigger executed !!')

END

BEGIN TRANSACTION

DECLARE @ERR INT

INSERT INTO USER_MASTER(USERNAME, PASSWORD)

VALUES('Damerla','Damerla')

SET @ERR = @@Error

IF @ERR = 0

BEGIN

ROLLBACK TRANSACTION

PRINT 'ROLLBACK TRANSACTION'

END

ELSE

BEGIN

COMMIT TRANSACTION

PRINT 'COMMIT TRANSACTION'

END

Output

AFTER Trigger [trgInsert] – Trigger executed !!

AFTER Trigger [trgInsert2] – Trigger executed !!

AFTER Trigger [trgInsert3] – Trigger executed !!

(1 row(s) affected)

ROLLBACK TRANSACTION

From the output we can conclude that when the user tries to insert data in the table USER_MASTER, three triggers are executed automatically. That is, you
can write several AFTER triggers on one table for each of the three triggering actions.

Similarly, we can write several AFTER triggers on DELETE and UPDATE triggering actions.

Note: If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using
the stored procedure sp_settriggerorder. All the other triggers are in an undefined order which you cannot control.

An AFTER trigger can be created only on tables, not on views.

Using the script below, first we shall create a simple view [vwUserMaster] which will fetch the Username and Password from the table USER_MASTER.

Create View vwUserMaster

SELECT USERNAME, PASSWORD FROM USER_MASTER

CREATE TRIGGER trgOnView

ON vwUserMaster

FOR INSERT

BEGIN

Print ('AFTER Trigger [trgOnView] – vwUserMaster !!')

END

Output

Server: Msg 208, Level 16, State 4, Procedure trgOnView, Line 2

Invalid object name 'vwUserMaster'.

From the Output we can conclude that we cannot create an AFTER trigger on views.

Like stored procedures and views, triggers can also be encrypted. The trigger definition is then stored in an unreadable form. Once encrypted, the
definition of the trigger cannot be decrypted and cannot be viewed by anyone, including the owner of the trigger or the system administrator.

CREATE TRIGGER trgEncrypted

ON User_Master WITH ENCRYPTION

FOR INSERT

BEGIN

Print ('AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!')

END

SELECT

sysobjects.name AS [Trigger Name],

SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],

OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],

syscomments.encrypted AS [IsEncrpted]

FROM

sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id

WHERE

(sysobjects.xtype = 'TR')

Output

Trigger Name Trigger Definition Table Name IsEncrpted

-----------------------------------------------------------------

trgInsert CREATE TRIGGER trgInsert User_Master 0

trgInsert1 CREATE TRIGGER trgInsert1 User_Master 0

trgInsert2 CREATE TRIGGER trgInsert2 User_Master 0

trgEncrypted ??????????????? User_Master 1

Since the trigger trgEncrypted is created with the option WITH ENCRYPTION, the trigger definition is hidden and there is no way that one can easily decrypt
the trigger code.

We all know that the DML statements change or modify data. Sometimes it becomes necessary for the triggers to have the access to the changes being caused
by the DML statements. SQL Server 2000 provides four different ways to determine the affects of the DML statements. The INSERTED and DELETED tables,
popularly known as MAGIC TABLES, and update () and columns_updated() functions can be used to determine the changes being caused by the DML statements.

The below table depicts the contents of the INSERTED and DELETED tables for three different table Events

EVENT	INSERTED	DELETED
Insert	Contains the inserted rows	Empty
Delete	Empty	Contains the rows to be deleted
Update	Contains the rows after update	Contains the rows before update

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns
will cause an error.

The update() function is used to find whether a particular column has been updated or not. This function is generally used for data checks.

CREATE TRIGGER trgUddate

ON User_Details

FOR UPDATE

If UPDATE(FName)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - First Name has been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(LName)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Last Name has been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(MName)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - MName Name has been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(Email)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Email has been updated')

ROLLBACK TRANSACTION

END

UPDATE User_Details

SET MName = 'Diwaker'

WHERE UserID = 1

Output

AFTER Trigger [trgUddate] - Executed - MName Name has been updated

Depending upon the column updated, a message will be displayed. With this feature we can determine which column in the table has been updated, and then
proceed with the business rules to be implemented further.

Columns_Update() function returns a varbinary data type representation of the columns updated. This function return a hexadecimal values from which we can
determine which columns in the table have been updated.

INSTEAD OF Triggers

A trigger which gets executed automatically in place of triggering actions i.e., INSERT, DELETE and UPDATE is called an INSTEAD OF trigger.

INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER
triggers gets executed automatically after these constraints are checked.

CREATE TRIGGER trgAfterInsert

On User_Details

FOR INSERT

BEGIN

Print ('AFTER Trigger [trgAfterInsert] – Trigger executed !!')

END

INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)

VALUES(100, 'FName','LName','MName','test@test.com')

Since the trigger trgEncrypted is created with the option WITH ENCRYPTION, the trigger definition is hidden and there is no way that one can easily decrypt
the trigger code.

The below table depicts the contents of the INSERTED and DELETED tables for three different table Events

EVENT	INSERTED	DELETED
Insert	Contains the inserted rows	Empty
Delete	Empty	Contains the rows to be deleted
Update	Contains the rows after update	Contains the rows before update

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns
will cause an error.

The update() function is used to find whether a particular column has been updated or not. This function is generally used for data checks.

CREATE TRIGGER trgUddate

ON User_Details

FOR UPDATE

If UPDATE(FName)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - First Name has been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(LName)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Last Name has been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(MName)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - MName Name has been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(Email)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Email has been updated')

ROLLBACK TRANSACTION

END

UPDATE User_Details

SET MName = 'Diwaker'

WHERE UserID = 1

Output

AFTER Trigger [trgUddate] - Executed - MName Name has been updated

INSTEAD OF Triggers

A trigger which gets executed automatically in place of triggering actions i.e., INSERT, DELETE and UPDATE is called an INSTEAD OF trigger.

CREATE TRIGGER trgAfterInsert

On User_Details

FOR INSERT

BEGIN

Print ('AFTER Trigger [trgAfterInsert] – Trigger executed !!')

END

INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)

VALUES(100, 'FName','LName','MName','test@test.com')

We have seen that you can create any number of AFTER triggers on the table for the same event, whereas you cannot do that with INSTEAD OF triggers.

CREATE TRIGGER trgInsteadOfTrigger1

ON vwUserMaster

INSTEAD OF UPDATE

BEGIN

Print ('INSTEAD OF Trigger [trgInsteadOfTrigger1] – Trigger executed !!')

END

CREATE TRIGGER trgInsteadOfTrigger2

ON vwUserMaster

INSTEAD OF UPDATE

BEGIN

Print ('INSTEAD OF Trigger [trgInsteadOfTrigger2] – Trigger executed !!')

END

Output

Server: Msg 2111, Level 16, State 1, Procedure trgInsteadOfTrigger2, Line 6

Cannot CREATE trigger 'trgInsteadOfTrigger2' for view 'vwUserMaster' because an INSTEAD OF UPDATE trigger already exists.

From the output, it is clear that you cannot create two INSTEAD OF triggers on the view/ table for the same event.

Note: An important point to be noted is that INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE
or ON UPDATE cascading referential integrity defined.

At last, how would you know what are the triggers associated with the table and what type of the trigger it is? Whether AFTER or INSTEAD OF?

The solution for this question is sp_helptrigger. This stored procedure gives all the information about the triggers such as Event on which the trigger
gets executed, the type of the trigger, etc.

Sp_helptrigger User_Master

Output

trigger_name trigger_owner isupdate isdelete isinsert isafter isinsteadof

trgInsert dbo 0 0 1 1 0

trgInsert2 dbo 0 0 1 1 0

trgInsert3 dbo 0 0 1 1 0

trgEncrypted dbo 0 0 1 1 0

Triggers can be used in the following scenarios, such as if the database is de-normalized and requires an automated way to update redundant data contained
in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical
value in another table.

Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain
the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce
the data integrity using CONSTRAINTS, RULES and DEFAULTS. Triggers cannot be created on the temporary tables.

CURSOR

Sometimes, it is handy to be able to perform some calculation on one or more columns of a record, and then take the result of that calculation and then
add it to similar calculations performed on other related records to find a grand total.

For example, let's say you want to find the total dollar cost of an invoice. An invoice will generally involve a header record and one or more detail
records. Each detail record will represent a line item on the invoice. In order to calculate the total dollar cost of an invoice, based on two or more line
items, you would need to multiply the quantity of each item sold times the price of each item. Then, you would need to add the total price of each line
item together in order to get the total dollar cost of the entire invoice. To keep this example simple, let's ignore things like discounts, taxes,
shipping, etc.

One way to accomplish this task would be to use a cursor like the one we see below (we are using the Northwind database for this example code):

DECLARE @LineTotal money --Declare variables

DECLARE @InvoiceTotal money

SET @LineTotal = 0 --Set variables to 0

SET @InvoiceTotal = 0

DECLARE Line_Item_Cursor CURSOR FOR --Declare the cursor

SELECT UnitPrice*Quantity --Multiply unit price times quantity ordered

FROM [order details]

WHERE orderid = 10248 --We are only concerned with invoice 10248

OPEN Line_Item_Cursor --Open the cursor

FETCH NEXT FROM Line_Item_Cursor INTO @LineTotal --Fetch next record

WHILE @@FETCH_STATUS = 0

BEGIN

SET @InvoiceTotal = @InvoiceTotal + @LineTotal --Summarize line items

FETCH NEXT FROM Line_Item_Cursor INTO @LineTotal

END

CLOSE Line_Item_Cursor --Close cursor

DEALLOCATE Line_Item_Cursor --Deallocate cursor

SELECT @InvoiceTotal InvoiceTotal --Display total value of invoice

The result for invoice number 10248 is $440.00.

What the cursor does is to select all of the line items for invoice number 10248, then multiply the quantity ordered times the price to get a line item
total, and then it takes each of the line item totals for each record and then adds them all up in order to calculate the total dollar amount for the
invoice.

This all works well, but the code is long and hard to read, and performance is not great because a cursor is used. Ideally, for best performance, we need
to find another way to accomplish the same goal as above, but without using a cursor.

Instead of using a cursor, let's rewrite the above code using set-based Transact-SQL instead of a cursor. Here's what the code looks like:

DECLARE @InvoiceTotal money

SELECT @InvoiceTotal = sum(UnitPrice*Quantity)

FROM [order details]

WHERE orderid = 10248

SELECT @InvoiceTotal InvoiceTotal

The result for invoice number 10248 is $440.00.

Right away, it is obvious that this is a lot less code and that is it more readable. What may not be obvious is that it uses less server resources and
performs faster. In our example — with few rows — the time difference is very small, but if many rows are involved, the time difference between the
techniques can be substantial.

The secret here is to use the Transact-SQL "sum" function to summarize the line item totals for you, instead of relying on a cursor. You can use this same
technique to help reduce your dependency on using resource-hogging cursors in much of your Transact-SQL code. [6.5, 7.0, 2000, 2005] Updated 9-4-2006

Database Normalization Basics

If you've been working with databases for a while, chances are you've heard the term normalization. Perhaps someone's asked you "Is thatdatabase normalized?" or "Is that in BCNF?" All too often, the reply is "Uh, yeah." Normalization is often brushed
aside as a luxury that only academics have time for. However, knowing the principles of normalization and applying them to your daily database design tasks
really isn't all that complicated and it could drastically improve the performance of your DBMS.

In this article, we'll introduce the concept of normalization and take a brief look at the most common normal forms. Future articles will provide in-depth
explorations of the normalization process.

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data
(for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are
numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical
applications, you'll often see 1NF,2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't
be discussed in this article.

Before we begin our discussion of the normal forms, it's important to point out that they are guidelines and guidelines only. Occasionally, it becomes
necessary to stray from them to meet practical business requirements. However, when variations take place, it's extremely important to evaluate any
possible ramifications they could have on your system and account for possible inconsistencies. That said, let's explore the normal forms.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

Eliminate duplicative columns from the same table.

· Create separate tables for each group of related data and identify each row with
a unique column or set of columns (the primary key).

For more details, read Putting your Database in First Normal Form

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

Meet all the requirements of the first normal form.

· Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

· Create relationships between these new tables and their predecessors through the use of foreign keys.

For more details, read Putting your Database in Second Normal Form

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

Meet all the requirements of the second normal form.
Remove columns that are not dependent upon the primary key.

For more details, read Putting your Database in Third Normal Form

Boyce-Codd Normal Form (BCNF or 3.5NF)

The Boyce-Codd Normal Form, also referred to as the "third and half (3.5) normal form", adds one more requirement:

Meet all the requirements of the third normal form.
Every determinant must be a candidate key.

For more details, read
Putting your Database in Boyce Codd Normal Form

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

Meet all the requirements of the third normal form.
A relation is in 4NF if it has no multi-valued dependencies.

Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

Should I Normalize?

While database normalization is often a good idea, it's not an absolute requirement. In fact, there are some cases where deliberately violating the rules
of normalization is a good practice. For more on this topic, read Should I Normalize My Database?.

If you'd like to ensure your database is normalized, explore our other articles in this series:

SQL Basic part2

SQL AND & OR Operators

The AND & OR Operators

AND Operator Example

OR Operator Example

Combining AND & OR

The ORDER BY Keyword

SQL ORDER BY Syntax

ORDER BY Example

ORDER BY DESC Example

The TOP Clause

SQL Server Syntax

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

Example

Oracle Syntax

Example

SQL TOP Example

SQL TOP PERCENT Example

SQL LIKE Operator

The LIKE Operator

SQL LIKE Syntax

LIKE Operator Example

The IN Operator

SQL IN Syntax

IN Operator Example

SQL Alias

SQL Alias Syntax for Tables

SQL Alias Syntax for Columns

Alias Example

SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

SQL INNER JOIN Syntax

SQL INNER JOIN Example

SQL LEFT JOIN Keyword

SQL LEFT JOIN Syntax

SQL LEFT JOIN Example

SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Syntax

SQL RIGHT JOIN Example

SQL FULL JOIN Keyword

SQL FULL JOIN Syntax

SQL FULL JOIN Example

3 TABLE JOINING

The SQL UNION Operator

SQL UNION Syntax

SQL UNION ALL Syntax

SQL UNION Example

SQL UNION ALL Example

SQL HAVING Clause

The HAVING Clause

SQL HAVING Syntax

SQL HAVING Example

The ROUND() Function

SQL ROUND() Syntax

SQL ROUND() Example

The AVG() Function

SQL AVG() Syntax

SQL AVG() Example

SQL Views

SQL CREATE VIEW Statement

SQL CREATE VIEW Syntax

SQL CREATE VIEW Examples

SQL Updating a View

SQL CREATE OR REPLACE VIEW Syntax

SQL Dropping a View

SQL DROP VIEW Syntax

SQL Constraints

SQL Constraints

SQL NOT NULL Constraint

SQL NOT NULL Constraint

SQL UNIQUE Constraint

SQL UNIQUE Constraint

SQL UNIQUE Constraint on CREATE TABLE

SQL UNIQUE Constraint on ALTER TABLE

To DROP a UNIQUE Constraint

SQL CREATE INDEX Statement

Indexes

SQL CREATE INDEX Syntax

SQL CREATE UNIQUE INDEX Syntax