1

Unit- 6 SQL

Structure Query Language (SQL)

SQL was developed in 1970’s in an IBM laboratory “San Jose Research Laboratory” (now the Amaden
Research center). SQL is derived from the SEQUEL one of the database language popular during 1970’s.
SQL established itself as the standard relational database language. Two standard organization (ANSI)
and International standards organization (ISO) currently promote SQL standards to industry. In 1986
ANSI & ISO published an SQL standard called SQL-86. In 1987, IBM published its own corporate SQL
standard, the system application Architecture Database Interface (SAA-SQL). In 1989, ANSI published
extended standard for SQL called, SQL-89. The next version was SQL-92, and the recent version is SQL:
1999 and so on.

Basic Term and Terminology

Query: is a statement requesting the retrieval of information.

Query language: language through which user request information from database.

These languages are generally higher level language than programming language.

The two types of query language are:

(i) Procedural language

• User instructs the system to perform sequence of operation on the database to compete the desired
result. Example : relational algebra

ii) Non- procedural language • User describes the desired information without giving a specific
procedure for obtaining that desired information.

• Examples: tuple relational calculus and domain relational calculus.

SQL General Data Types

Each column in a database table is required to have a name and a data type. SQL developers have to
decide what types of data will be stored inside each and every table column when creating a SQL
table. The data type is a label and a guideline for SQL to understand what type of data is expected
inside of each column, and it also identifies how SQL will interact with the stored data.
The following table lists the general data types in SQL:
2

Integers

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1

(9223372036854775807).

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint

Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint

Integer data from 0 through 255.

bit

bit

Integer data with either a 1 or 0 value.

decimal and numeric

decimal

Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.

numeric

Functionally equivalent to decimal.

money and smallmoney

money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1

(+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-
thousandth of a monetary unit.
3

Approximate Numerics

float

Floating precision number data from -1.79E + 308 through 1.79E + 308.

real

Floating precision number data from -3.40E + 38 through 3.40E + 38.

datetime and smalldatetime

datetime

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-
hundredths of a second, or 3.33 milliseconds.

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

Character Strings

char

Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

varchar

Variable-length non-Unicode data with a maximum of 8,000 characters.

text

Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647)

characters.

Unicode Character Strings

nchar

Fixed-length Unicode data with a maximum length of 4,000 characters.

nvarchar
4

Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-
supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to
reference database object names.

ntext

Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.

Binary Strings

binary

Fixed-length binary data with a maximum length of 8,000 bytes.

varbinary

Variable-length binary data with a maximum length of 8,000 bytes.

image

Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax
CREATE DATABASE dbname;

SQL CREATE DATABASE Example

The following SQL statement creates a database called "student_db":
CREATE DATABASE student_db;
Database tables can be added with the CREATE TABLE statement.

 The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

Tables are organized into rows and columns; and each table must have a name.
SQL CREATE TABLE Syntax

CREATE TABLE table_name

(
column_name1 data_type(size),
column_name2 data_type(size),
5

column_name3 data_type(size),
....
)

The column_name parameters specify the names of the columns of the table. The data_type parameter
specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.). The size
parameter specifies the maximum length of the column of the table.

SQL CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: PersonID, LastName,
FirstName, Address, and City. We use the following CREATE TABLE statement:

CREATE TABLE Persons

(
PersonID int,
LastName varchar(25),
FirstName varchar(25),
Address varchar(25),
City varchar(25)
)

 SQL Constraints

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 CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

In SQL, we have the following constraints:

 NOT NULL - Indicates that a column cannot store NULL value

 UNIQUE - Ensures that each rows for a column must have a unique value
6

 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or
combination of two or more columns) have an 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

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(25) NOT NULL,
FirstName varchar(25),
Address varchar(25),
City varchar(25)
)

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:
MSSQL:

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Address varchar(25),
7

City varchar(25),
UNIQUE (P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
P_Idint NOT NULL UNIQUE,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Address varchar(25),
City varchar(25)
)

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(25) NOT NULL,
FirstName varchar(25),
Address varchar(25),
City varchar(25),
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)
8

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MSSQL:

ALTER TABLE Persons

DROP uc_PersonID

Or

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values. A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is
SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
P_Idint NOT NULL PRIMARY KEY,
LastNamevarchar(255) NOT NULL,
FirstNamevarchar(255),
Address varchar(255),
City varchar(255)
)

Composite key

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint
on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
P_Id int NOT NULL,
9

LastName varchar(25) NOT NULL,

FirstName varchar(25),
Address varchar(25),
City varchar(25),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the
value of the pk_PersonID is made up of two columns (P_Id and LastName).

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY 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 PRIMARY KEY (P_Id)

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint
on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s)
must already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID
10

SQL FOREIGN KEY Constraint on CREATE TABLE

CREATE TABLE Persons

(
P_Idint NOT NULL PRIMARY KEY,
LastNamevarchar(255) NOT NULL,
FirstNamevarchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Orders

(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons"
table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The
"P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table. The FOREIGN
KEY constraint is used to prevent actions that would destroy links between tables. The
FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key
column, because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already
created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

SQL Server / Oracle / MS Access:

11

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table
is created. The CHECK constraint specifies that the column "P_Id" must only include integers
greater than 0.

SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
P_Idint NOT NULL CHECK (P_Id>0),
LastNamevarchar(255) NOT NULL,
FirstNamevarchar(255),
Address varchar(255),
City varchar(255)
)

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

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons"
table is created:
My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Address varchar(25),
City varchar(25) DEFAULT 'Chitwan'
)

The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.
DROP TABLE table_name
12

The DROP DATABASE Statement

The DROP DATABASE statement is used to delete a database.

 The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

For example

INSERT INTO Example

Assume we wish to insert a new row in the "Customers" table.
We can use the following SQL statement (without specifying column names):
INSERT INTO Customers
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

or this SQL statement (including column names):

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

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

 The SQL UPDATE Statement

The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax
13

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

For example

SQL UPDATE Example

Assume we wish to update the customer "AlfredsFutterkiste" with a new contact person and city.
We use the following SQL statement:
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='AlfredsFutterkiste';

 The SQL DELETE Statement

The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
DELETE FROM table_name
WHERE some_column=some_value;

For example

SQL DELETE Example

Assume we wish to delete the customer "AlfredsFutterkiste" from the "Customers" table.
We use the following SQL statement:
DELETE FROM Customers
WHERE CustomerName='AlfredsFutterkiste' AND ContactName='Maria Anders';

Delete All Data

It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact:
DELETE FROM table_name;

or

DELETE * FROM table_name;

The SQL SELECT Statement

The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
SQL SELECT Syntax
SELECT column_name,column_name
FROM table_name; Or SELECT * FROM table_name;
14

For example

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers"
table:
Example
SELECT CustomerName,City FROM Customers;

SELECT * Example
The following SQL statement selects all the columns from the "Customers" table:
Example
SELECT * FROM Customers;

The SQL SELECT DISTINCT Statement

In a table, a column may contain many duplicate values; and sometimes you only want to list the
different (distinct) values.
The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name,column_name

FROM table_name;

SELECT DISTINCT Example

The following SQL statement selects only the distinct values from the "City" columns from the
"Customers" table:
Example
SELECT DISTINCT City FROM Customers;

The WHERE clause is used to filter records.

The SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

WHERE Clause Example

15

The following SQL statement selects all the customers from the country "Mexico", in the "Customers"
table:
Example
SELECT * FROM Customers
WHERE Country='Mexico';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM Customers
WHERE CustomerID=1;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:
Operator Description

= Equal

<> Not equal. Note: In some versions of SQL this operator may be written as !=

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

IN To specify multiple possible values for a column

1. SQL LIKE Operator

16

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;

i.e. SELECT * FROM Customers

SQL statement selects all customers with a City starting with the letter "s":
WHERE City LIKE 's%';

SQL statement selects all customers with a City ending with the letter "s":
SELECT * FROM Customers
WHERE City LIKE '%s';

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

i.e.
SELECT * FROM Customers
WHERE City IN ('Paris','London');

The SQL BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

2. SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;
17

[Link] * FROM Products

WHERE (Price BETWEEN 10 AND 20)

SELECT * FROM Orders

WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY
keyword sorts the records in ascending order by default. To sort the records in a descending order,
you can use the DESC keyword.
SQL ORDER BY Syntax

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" column:
Example
SELECT * FROM Customers
ORDER BY Country;

ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING
by the "Country" column:
Example
SELECT * FROM Customers
ORDER BY Country DESC;

12 SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

 AVG() - Returns the average value

 COUNT() - Returns the number of rows
 MAX() - Returns the largest value
 MIN() - Returns the smallest value
 SUM() - Returns the sum
18

 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

The following SQL statement gets the average value of the "Price" column from the "Products" table:

Example
SELECT AVG(Price) AS PriceAverage FROM Products;

The following SQL statement selects the "ProductName" and "Price" records that have an above
average price:

Example
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);

 SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of
the specified column:

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified
column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

SQL COUNT(*) Example

The following SQL statement counts the total number of orders in the "Orders" table:
19

Example
SELECT COUNT(*) AS NumberOfOrders FROM Orders;

SQL SUM() AND AVG FUNTIONS

Select sum(salary) as ‘total salary’ , avg(salary) as ‘average salary’ from

employee

 The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set
by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

For example
Select departNO,count(employeeid) as ‘total employees’

from employee

Group by departNO

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;

For example

Select departNO,count(employeeid) as ‘total employees’

from employee

Group by departNO
20

Having count(empoyeeid)> 2

Using Subqueries as Lists

Subqueries begin to shine when used as lists. A single value, commonly a column, in the outer query is
compared with the subquery’s list by means of the in operators. The subquery must return only a single
column; multiple columns will fail.

The in operator returns a value of true if the column value is found anywhere in the list supplied by the
subquery, in the same way that where ... in returns a value of true when used with a hard-coded list:

For example

SELECT ProductName, ProductID

FROM [Link]

WHERE ProductID IN

(SELECT ProductID

FROM sales

ORDER BY ProductID)

Using Joins
In relational algebra, a join is the multiplication of two data sets followed by a restriction ofthe
result so that only the intersection of the two data sets is returned. The whole purpose of the join
is to horizontally merge two data sets (usually tables, but it could be a subquery, view, common
table expression, or user-defined function) and produce a new result set from the combination by
matching rows in one data source to rows in the other data source.

Inner Join
The inner join is by far the most common join. In fact, it’s also referred to as a common join, and was
originally called a natural join by E. F. Codd. The inner join returns only those rows that represent a
match between the two data sets. An inner join is well named because it extracts only data from the
inner portion of the intersection of the two overlapping data sets.

Creating Inner Joins within SQL Code

Within SQL code, joins are specified within the from portion of the select statement. The
keyword join identifies the second table, and the on clause defines the common ground
between the two tables. The default type of join is an inner join, so the keyword inner is
21

optional:

SELECT *

FROM Table1

[INNER] JOIN Table2

ON [Link] = [Link]

Because joins pull together data from two data sets, it makes sense that SQL needs to know how to
match up rows from those sets. SQL Server merges the rows by matching a value common to both
tables. Typically, a primary key value from one table is being matched with a foreign key value from the
secondary table. Whenever a row from the first table matches a row from the second table, the two
rows are merged into a new row containing data from both tables.

FOR EXAMPLE.

USE pubs
SELECT [Link], publishers.pub_name
FROM titles JOIN publishers
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name

Multiple Table Joins

a select statement isn’t limited to one or two data sources; a SQL Server select statement may
refer to up to 256 data sources. That’s a lot of joins. Because SQL is a declarative language, the
order of the data sources is not important. Multiple joins may be combined in multiple paths, or
even circular patterns (A joins B joins C joins A). An interesting thing happens when joins across
multiple tables are combined with a where clause restriction (that is, when the joins carry with
them the where-clause restriction). A restriction in any one table means that only those rows
that meet the restriction condition

The following SQL select statement begins with the “who” portion of the question and specifies the join
tables and conditions as it works through the required tables. The query that is shown graphically in
Management Studio (refer to Figure 9-5) is listed as raw SQL in the following code sample. Notice how
the where clause restricts the ProductCategory table rows and yet affects the contacts selected:

SELECT LastName, FirstName, ProductName

FROM Contact
22

JOIN Order

ON [Link] = Order].ContactID

JOIN [Link]

ON [Link] = [Link]

JOIN [Link]

ON [Link] = [Link]

JOIN [Link]

ON [Link] = [Link]

WHERE ProductCategoryName = ‘Kite’

ORDER BY LastName, FirstName

Implementing Views
A view is simply a SELECT statement that has a name and is stored in Microsoft SQL Server. Views act as
virtual tables to provide several benefits. A view gives developers a standardized way to execute
queries, enabling them to write certain common queries once as views and then include the views in
application code so that all applications use the same version of a query. A view can also provide a level
of security by giving users access to just a subset of data contained in the base tables that the view is
built over and can give users a more friendly, logical view of data in a database. In addition, a view with
indexes created on it can provide dramatic performance improvements, especially for certain types of
complex queries. Most views allow only read operations on underlying data, but you can also create
updateable views that let users modify data via the view.

CREATE VIEW

Creates a virtual table that represents the data in one or more tables in an alternative way.
CREATE VIEW must be the first statement in a query batch.

Syntax
Create view view_name
(columns)

with ….
23

As

Select statement

CREATE VIEW titles_view

AS
SELECT title, type, price, pubdate
FROM titles

Use WITH ENCRYPTION

CREATE VIEW accounts (title, advance, amt_due)

WITH ENCRYPTION
AS
SELECT title, advance, price * royalty * ytd_sales
FROM titles
WHERE price > $5

Use built-in functions within a view

This example shows a view definition that includes a built-in function. When you use functions,
the derived column must include a column name in the CREATE VIEW statement
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type