SQL Server

What is SQL?
SQL (Structured Query Language) is a database computer language designed for
managing data in relational database management systems (RDBMS). SQL, is a
standardized computer language that was originally developed by IBM for querying,
altering and defining relational databases, using declarative statements. There are
various types of statements and each one of them uses certain key words like
SELECT, INSERT etc. These statements are categorized as Data definition
language, data manipulation language etc.
In this chapter, we are going to discuss the following topics:
 What is the need of SQL language?

 SQL Language basics

 Data definition language (DDL)

 Data manipulation language (DML)

 Data Control Language (DCL)

 SQL Commands

 Operators

 Procedures

 Triggers

 Built-in functions

 Scalar functions

 Aggregate functions

Microsoft SQL Server: Microsoft SQL Server is a powerful relational database

management system (RDBMS) that offers a variety of tools for database
development, manipulation, maintenance and administration. It is used to create
robust databases that can be accessed from Workstations, Internet and other
handheld devices. It also provides us with tools for data analysis and can be used to
generate reports required by Management Information System.
Why SQL

SQL is an essential part of any database system as it allows to interact with the
database system. A small set of SQL language set is defined as ANSI SQL and it is
designed to standard across all RDBMSs. If you are looking to write a database
independent system, you can use only ANSI SQL (however it severely constrains
the capability of any RDBMS). SQL is important for the following reasons:
 Allows users to access data in relational database management systems.

 Allows users to describe the data.

 Allows users to define the data in database and manipulate that data.

 Allows to embed within other languages using SQL modules, libraries &
pre -compilers.

 Allows users to create and drop databases and tables.

 Allows users to create view, stored procedure, functions in a database.

 Allows users to set permissions on tables, procedures and views

Language Basics

Data Definition Language

The Data Definition Language (DDL) manages table and index structure. The most
basic DDL statements includes CREATE, ALTER, RENAME and DROP statements:
 CREATE creates an object (a table, for example) in the database.

 DROP deletes an object in the database, usually irretrievably.

 ALTER modifies the structure an existing object in various ways—for

example, adding a column to an existing table.

 Create Table

As the name suggests, CREATE TABLE command is used to create a table in a

database, in our case, SQL server. As we know, a table is used to store data. A
table is similar in structure to an Excel sheet. Every column is identified by a unique
name and a data type.
Syntax:
Create Table tablename (
Colname1 datatype,
Colname2 datatype,
.
.
.
.
)
Example:
CREATE TABLE Customers (
DrvLicNbr nvarchar(32),
DateIssued DATE,
DateExpired date,
FullName nvarchar(50),
Address NVARCHAR(120),
City NvarChar(40),
State NVarChar(50),
PostalCode nvarchar(20),
HomePhone nvarchar(20),
OrganDonor BIT);
* varchar is stored as regular 8-bit data (1 byte per character) and nvarchar
stores data as 2 bytes per character

5.3.1.2 ALTER Table

ALTER table is used to modify a table definition. It can perform the following
functions on a table:
 It can add a column

 It can drop a column

 It can drop constraints

 It can enable or disable constraints

 It can enable or disable a trigger

Syntax:

ALTER TABLE table_name {

ALTER COLUMN column_name .....various options;
ADD | DROP Constraint.....

Example:

ALTER TABLE customer ALTER COLUMN customer_name NVARCHAR (100)

NOT NULL.
This statement makes the customer_name column of size 100 and a not null type.
ALTER TABLE Customer ADD customeraddr VARCHAR (20) NULL
This statement creates a new column customeraddr in the table CUSTOMER.

DROP Table
To delete a table using SQL, we use DROP TABLE command:

Syntax:

DROP TABLE TableName

RENAME a table

To rename a table using code, execute the sp_rename stored procedure using the
following syntax.
RENAME ExistingTableName, TableNewName;

Data Manipulation Language (DML)

The Data Manipulation Language includes insert, update, delete and select
statements. The Data Manipulation Language (DML) is the subset of SQL used to
add, update and delete data.

'SELECT' Statement - SQL SELECT Statement is used to fetch the data from a
database table which returns data in the form of result table. These result tables are
called result-sets.

Syntax:

The basic syntax of SELECT statement is as follows:

SELECT column1, column2 FROM table_name;

Here, column1, column2...are the fields of a table whose values you want to
fetch(select/display). If you want to fetch all the fields available in the field, then you
can use the following syntax:
SELECT * FROM table_name;
For example:
SELECT * FROM STUDENT.
This statement will select and display all the rows and all the columns of STUDENT
table.
SELECT student_name, student_age from STUDENT.
This statement will select only student name and student age columns. However it
will bring all the rows.

SQL WHERE Clause

The SQL WHERE clause is used to specify a condition while choosing the data from
single table or joining with multiple tables. If the given condition is satisfied, then only
it returns specific value from the table. You would use WHERE clause to filter the
records and fetching only necessary records.
The WHERE clause is not only used in SELECT statement, but it is also used in
UPDATE, DELETE statement.
Syntax:

SELECT column1, column2, columnN FROM table_name WHERE [condition]

Select * from table1.
For Example:
SELECT * FROM STUDENT WHERE student_age > 12.
In this case, the statement will bring all the records (and all the columns) where
students' age is more than 12 years old.
We use 'AND' and 'OR' operators to create multiple conditions in the WHERE
clause.

The AND Operator

The AND operator allows us to check multiple conditions in an SQL statement's

WHERE clause.
Example:-

SELECT ID, NAME, SALARY FROM emp WHERE SALARY > 10000 AND age <
25.

5.3.2.4 The OR Operator

The OR operator is used to combine multiple conditions in an SQL statement's

WHERE clause.
Example: -

SELECT ID, NAME, SALARY FROM emp WHERE SALARY > 10000 or age < 25.

SQL UPDATE Statement

SQL update statement is used to update selected records from the table. You have
to use WHERE clause with UPDATE query to update selected rows, otherwise all
the rows would be affected.
Syntax:-

The basic syntax of UPDATE query with WHERE clause is as follows:

UPDATE table_name SET column1 = value1, column2 = value2
WHERE [condition];
We can update any number of columns of a particular record using update
statement.

Example: -

UPDATE emp SET ADDRESS = 'Pune' WHERE ID = 6.

This statement will update a record from employee table whose ID is 6.

SQL DELETE and TRUNCATE Query

The SQL DELETE Query is used to delete the existing records from a table.
Same as update statement to use where clause with delete statement is necessary
else all records will be deleted.
Syntax:

DELETE FROM table_name WHERE [condition];

DELETE FROM emp WHERE ID = 6;
The TRUNCATE key word is used to delete all the rows from a table. It is faster than
delete statement and uses less transaction resources.
TRUNCATE TABLE tablename;

The ORDER BY Keyword

If you want the data to appear in a particular order you need to use the “order by”
keyword.
Example:
Select * from country order by countryname
Here country is my table name..it will give you result order by countryname.
You can customize this order ascending or descending order.
Select * from country order by countryname desc
Select * from country order by countryname asc

SELECT DISTINCT

In a table, some of the columns may contain duplicate(repeated/same) values.

however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
The syntax is as follows:
select distinct <column_names> from <table_names>
OPERATORS - Operators play an important role in writing more powerful
SQL statements. These operators are generally used with the WHERE
clause. Following operators are used:

Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of
the Columns.

SQL LIKE Clause - The SQL LIKE clause is used to compare a value to
similar values using wildcard operators. There are two wildcards used in
conjunction with the LIKE operator:

The percent sign (%)

The underscore (_)
The percent sign represents zero, one, or multiple characters. The underscore
represents a single number or character. The symbols can be used in combinations.

Syntax:

The basic syntax of % and _ is as follows:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
You can combine N number of conditions using AND or OR operators. Here, XXXX
could be any numeric or string value.

IN Operator

The IN operator permits you to specify multiple values in a WHERE clause. The IN
operator is used to compare a value to a list of literal values that have been
specified.
Syntax:

SELECT column_name(s) FROM table_name WHERE column_name IN

(value1,value2,...)

BETWEEN Operators

The BETWEEN operator selects a range of data between two values. The values
can be numbers, text, or dates
Syntax:

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN

value1 AND value2
Exapmple:- Select * from employee where salary BETWEEN 20000 AND 25000
This statement returns all employee details from employee table whose salary is
between 20000 to 25000.

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. Top clause scans the
whole table that’s why Returning a large number of records can impact on
performance.
Syntax:

SELECT TOP number|percent column_name(s) FROM table_name

Example:

select TOP 1 * from employee

This will return only one (first) record.
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 can be any user-defined name.
Syntax :
SELECT column_name(s) FROM table_name AS alias_name

5.4 Joins

JOINS are used to use filter the results from the SQL statements SQL server
supports four types of joins. These joins are as follows:

 INNER JOIN: Returns all rows when there is at least one match in BOTH
tables. It is the default join of SQL Server

 LEFT JOIN: Return all rows from the left table, and the matched rows from
the right table

 RIGHT JOIN: Return all rows from the right table, and the matched rows from
the left table

 FULL JOIN: Return all rows when there is a match in ONE of the tables

Examples of Joins:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
This is an example of INNER JOIN and the query will fetch all the results where
CustomerID in ORDERS table will match the CustomerID in CUSTOMERS table. If
there are no matching records, the query will not return any result.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
This is an example of LEFT JOIN and the query will fetch all the rows from
CUSTOMERS Table. If there are no matching records in the ORDERS table, it will
show blank values.
The next two queries are assignments for you:
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

MISC SQL STATEMENTS

UNION: 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.
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City.

SELECT City FROM Customers

UNION ALL
SELECT City FROM Suppliers
ORDER BY City.

EXCEPT: Except operator works like MINUS operator of Mathematics. It subtracts

set of values of second table from the first table and gives the resultant values after
the subtraction. The first table should comprise of larger set of values than the
second table.

Example :

Select dept_id from dept

Except
Select dept_id from emp;

Consider that the tables have the following set of values:-

Dept – Dept-id - 1,2,3
Emp – Dept-id – 1,3

Output:- 2.

INTERSECT: It retrieves the set of values that are common to both the tables.
It also needs two set of values from two different tables, so that it provides the
similar set of values.

Example:

Select dept_id from dept

Intersect
Select dept_id from emp.

Consider that the tables have the following set of values:-

Dept – Dept-id - 1,2,3
Emp – Dept-id – 1,3

Output: - 1,3

To Copy only the 'German' customers into the new table:

SELECT * INTO CustomersBackup2013
FROM Customers
WHERE Country='Germany';

Use the IN clause to copy the table into another database:

SELECT * INTO CustomersBackup2013 IN 'Backup.mdb’ FROM

Customers;

EXAMPLES OF AGGREGATE FUNCTIONS:

The GROUP BY statement is used in conjunction with the aggregate

functions to group the result-set by one or more columns.
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS
NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

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

SELECT Employees.LastName, COUNT(Orders.OrderID) AS

NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS

NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

Using UCASE, LOWER

SELECT UCASE(CustomerName) AS Customer, City

FROM Customers

SELECT LOWER (column_name) FROM table_name;

Using TEXT FUNCTIONS

SELECT MID(City,1,4) AS ShortCity

FROM Customers

SELECT CustomerName, LEN(Address) as LengthOfAddress

FROM Customers

SELECT ProductName, ROUND(UnitPrice,0) AS RoundedPrice FROM

Products
SELECT ProductName, Price, Now () AS PerDate FROM Products;

SELECT REVERSE (ContactName) from Customers.

SELECT 'Mr.'+' ' +ContactName+'. ' From Customers;

SELECT STUFF ('abcdef', 2, 3, 'ijklmn'); Result: aijklmnef

SELECT PATINDEX ('%en_ure%', 'please ensure the door is locked');

Result : 8

Using DATE Related FUNCTIONS

SELECT NOW (), CURDATE(),CURTIME()

Extract: SELECT EXTRACT (YEAR FROM OrderDate) AS

OrderYear, EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay FROM Orders
WHERE OrderId=1

DATE_ADD: SELECT OrderId,DATE_ADD(OrderDate,INTERVAL

45 DAY) AS OrderPayDate
FROM Orders

DATE_DIFF: SELECT DATEDIFF(Day,'2008-11-30','2008-11-29')

AS DiffDate

SQL Constraints

Constraints are the rules enforced on data columns on table. These are used to limit
the type of data that can go into a table. This ensures the accuracy and reliability of
the data in the database. Constraints could be column level or table level. Column
level constraints are applied only to one column, whereas table level constraints are
applied to the whole table.
 SQL constraints are used to specify rules for the data in a table.
 If there is any violation between the constraint and the data action, the
action is aborted by the constraint.
 Constraints can be specified when the table is created (inside the
CREATE TABLE statement) or after the table is created (inside the
ALTER TABLE statement).

SQL provides us with following constraints:

 NOT NULL - Indicates that a column cannot store NULL value
 UNIQUE - Ensures that each row for a column must have a unique
value

 PRIMARY KEY - A combination of a NOT NULL and UNIQUE.

Ensures that a column (or combination of two or more columns) have a
unique identity which helps to find a particular record in a table more
easily and quickly

 FOREIGN KEY - Ensure the referential integrity of the data in one

table to match values in another table

 CHECK - Ensures that the value in a column meets a specific condition

 DEFAULT - Specifies a default value when specified none for this

column
 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 UNIQUE constraint uniquely identifies each record in a database
table.

Example:
CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

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.
CREATE TABLE PersonsUnique
(
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:

CREATE TABLE PersonsUniqueMulti

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

ALTER TABLE Persons ADD UNIQUE (P_Id)

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE
(P_Id,LastName)

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values and cannot contain NULL values. Each
table should have a primary key, and each table can have only ONE primary key.
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar (255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY
constraint on multiple.

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
ALTER TABLE student master WITH CHECK ADD CONSTRAINT
[CheckPostalCode] CHECK ((len([postal_code])=(10)))
ALTER TABLE student_master ADD CONSTRAINT Prn_Unq UNIQUE(prn)

Cursors, Stored Procedures and Functions

A Stored Procedure is a precompiled set of SQL statements. Stored procedures

provide an excellent way of organizing database interactions without affecting the
system performance. That's because, as opposed to normal SQL statements, they
are not compiled, every time they are executed.
Functions are very similar to Stored procedures with one small difference. Functions
can return values whereas stored procedures can't return values.

Before, we start looking at procedures and functions, lets first look at the variables.

Variables

To use the user-defined variable first you should create the variable. Syntax for
creating the variable is…. DECLARE when you want to define the variables. Local
variables must have the symbol “@” as a prefix. You also need to specify a data
type for your variable (int, varchar(x), etc.).

Syntax for declaring variables:

declare @local_variable data_type

Example:

Declare @val int

When you want to assign values to the variable, you have to use either a SET or a
SELECT statement.

Example:
declare @val int
set @val=111
If you want to check the value for a variable that you assigned, you can use the
PRINT command like this
print @val
It will return 111.

Stored Procedures Examples

Example 1: -
Create PROCEDURE [dbo]. [Bindcategory]
as
BEGIN
select CategoryName,CategoryId from categorymaster ;
END

Between this Begin and End block you can write your statements.

Example 2:

Two numbers are passed in and the midpoint of the two numbers is listed:

CREATE PROCEDURE ut_MidPoint @LowerNumber int, @HigherNumber int

AS
BEGIN
DECLARE @Mid int
IF @LowerNumber > @HigherNumber
RAISERROR('You have entered your numbers the wrong way
round',16,1)

SET @Mid = ((@HigherNumber - @LowerNumber) / 2) +

@LowerNumber
SELECT @Mid
END

Cursors
Cursor is a database object used by applications to manipulate data in a set on a
row-by-row basis, it's like recordset in the ASP and visual basic. Let's look at a
cursor first and then we will explain the components and the key words used in it:

DECLARE @name VARCHAR (50) -- database name

DECLARE @path VARCHAR (256) -- path for backup files
DECLARE @fileName VARCHAR (256) -- filename for backup
DECLARE @fileDate VARCHAR (20) -- used for file name

SET @path = 'E:\Gauri\'

SELECT @fileDate = CONVERT (VARCHAR (20), GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor
Based on the example above, cursors include these components:

 DECLARE statements - Declare variables used in the code block

 SET\SELECT statements - Initialize the variables to a specific value

 DECLARE CURSOR statement - Populate the cursor with values that will
be evaluated

NOTE - There are an equal number of variables in the DECLARE

<cursor_name> CURSOR FOR statement as there are in the SELECT
statement.  This could be 1 or many variables and associated columns.

 OPEN statement - Open the cursor to begin data processing

 FETCH NEXT statements - Assign the specific values from the cursor to
the variables

 NOTE - This logic is used for the initial population before the
WHILE statement and then again during each loop in the process
as a portion of the WHILE statement

 WHILE statement - Condition to begin and continue data processing

 BEGIN...END statement - Start and end of the code block

 NOTE - Based on the data processing multiple BEGIN...END

statements can be used

 Data processing - In this example, this logic is to back up a database to a

specific path and file name, but this could be just about any DML or
administrative logic

 CLOSE statement - Releases the current data and associated locks, but
permits the cursor to be re-opened

 DEALLOCATE statement - Destroys the cursor

 OUTPUT parameter in Procedure

Triggers

A trigger is a special kind of stored procedure that automatically executes when an

event occurs in the database server. DML triggers execute when a user tries to
modify data through a data manipulation language (DML) event. DML events are
INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire
when any valid event is fired, regardless of whether or not any table rows are
affected.

Types Of Triggers

There are three action query types that you use in SQL which are INSERT,
UPDATE and DELETE. So, there are three types of triggers and hybrids that come
from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)
(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.

(ii) Instead Of Triggers: supported for views.

Example: This trigger is fired after an INSERT on the table.

Let’s see an example: -

CREATE TRIGGER trgAfterInsert ON [dbo]. [Employee_Test]

FOR INSERT
AS
declare @empid int;
declare @empname varchar (100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

Example : This trigger is fired after an update on the table. Let’s create the
trigger as:-
CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';

insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Time
stamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'

Built - in Functions

SQL has many built-in functions for performing calculations on data. We have 2
categories of functions, namely aggregate functions and scalar functions. Aggregate
functions return a single value, calculated from values in a column, while scalar
functions return a single value, based on the input value.

Aggregate functions

examples:
AVG () - Returns the average value
STDEV () - Returns the standard deviation value
COUNT () - Returns the number of rows
MAX () - Returns the largest value
MIN () - Returns the smallest value
SUM () - Returns the sum

Scalar functions

Examples:
UPPER () - Converts a field to upper case
LOWER () - Converts a field to lower case
LEN () - Returns the length of a text field
ROUND () - Rounds a numeric field to the number of decimals specified
GETDATE () - Returns the current system date and time
How to use Comments

Using comments in you SQL script is important to make the script easier to read and
understand.
In SQL, there are two types of comments available.
--Single-line comment
/* */ Multiple-line comment

Single- line comment

you can comment one line at the time using “--” before the text you want to comment
out.
Syntax:
-- text_of_comment
Example:
--this procedure is for user insert by admin

5.8.2 Multiple- line comment

You can comment several lines using “/*” in the start of the comment and “*/” in the
end of
the comment.
Syntax:
/*
text_of_comment
text_of_comment
*/
Assignments
.
 SELECT CustomerName, City FROM Customers;

 SELECT * FROM Customers;

 SELECT DISTINCT City FROM Customers;

 SELECT * FROM Customers WHERE Country='Mexico';

 SELECT * FROM Customers

WHERE Country='Germany'
AND City='Berlin';

 SELECT * FROM Customers

WHERE City='Berlin' OR City='München';

 SELECT * FROM customer ORDER BY Country,CustomerName;

 INSERT INTO Customers (CustomerName, ContactName, Address, City,

PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen
21','Stavanger','4006','Norway');

 UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg'

WHERE CustomerName='Alfreds Futterkiste';

 DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'

AND ContactName='Maria Anders';

 SELECT TOP 2 * FROM Customers;

 SELECT * FROM Persons LIMIT 5;

 SELECT * FROM Persons WHERE ROWNUM <=5;

 SELECT TOP 50 PERCENT * FROM Customers;

 SELECT * FROM Customers WHERE City LIKE 's%';

 SELECT * FROM Customers WHERE City LIKE '%s';

 SELECT * FROM Customers WHERE Country LIKE '%land%';

 SELECT * FROM Customers WHERE City LIKE '_erlin';

 SELECT * FROM Customers WHERE City LIKE '[bsp]%';

 SELECT * FROM Customers WHERE City LIKE '[a-c]%';

 SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%';

 SELECT * FROM Customers WHERE City IN ('Paris','London');

 SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

 SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;