SQL(Structural Query Language)

What is SQL ?
 SQL stands for structured query language
 Is a non-procedural language, i.e you can specify what information
you require, rather than how to get it.
 Is an ANSI standard computer language
 Allow you to access a database
 SQL can execute queries against a database
 Is easy to learn
 Has two major components
 DDL
 DML

2
3
What can SQL do?

 SQL can execute queries against a database

 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures,andviews
4
5
Writing SQL Commands
 SQL statement consists of :-
 Reserved words :- fixed part of SQL language and have fixed
meaning
 Must spelt exactly
 User defined words :- are made up by the user (according to
certain syntax rule)
 They represent the names of varies database objects, i.e, tables,
columns, views, indexes ---
 ;(semicolon) :- used as statement terminator to mark the end of SQL
statement
 SQL is case insensitive
 Exception :- literal character data must be typed exactly as it appear in
the database. E.g. if we store a persons name ‘ABEBE’ and
search for it using the string ‘Abebe’ the row will not found.
6
 Writing SQL Commands … Cont’d
 SQL is free format
 For readability begin with a new line for clauses
 SQL identifier
 Used to identify objects in database , such as table name, view name ,columns
 Consists of letter, digit and underscore(_)
 can be no longer than 128 character
 Can’ t contain space
 Must start with letter or underscore

7
 SQL data types
1. Character data : Consists of sequence of character
Syntax CHARACTER[length]
CHARACTERVARYING[length]
Length :- the maximum number of character the a column hold.
Abbreviation CHARACTER CHAR
CHARACTERVARYING VARCHAR
 A character string may be defined as having a fixed or varying length.
 If fixed length is defined and we enter a string fewer character than
this length , the string padded with blanks to make up the required
size,
 If varying length is defined and we enter a string fewer character than
this length, only those characters entered are stored.
E.g. name CHAR(20), address VARCHAR(20)
8
SQL data types … Cont’d
2. Numeric data:- The exact numeric data type is used to define
numbers with an exact representation.
Syntax NUMERIC[precision, [scale]]
DECIMAL[precision, [scale]]
INTEGER
SMALLINTGER
Precision:- the total number of significant decimal digit
scale:- the total number of decimal place
Abbreviations:-
INTEGER INT
DECIMAL DEC
NUMERIC NUM
SMALLINTEGR SMALLINT
9
SQL data types … Cont’d
 NUMERIC and DECIMAL store number in decimal notation
 INTEGER is used for large positive or negative value
 SMALLINT is used for small positive or negative value
E.g. age SMALLINT
salary DECIMAL(5,2)

#1. for salary column which is valid

A. 999.99
B. 999.888
C. 9999.09

10
SQL data types … Cont’d
3. Date time data:- Used to define points in time
 DATE , TIME, TIMESTAMP
 DATE:- is used to sore calendar dates using YEAR,MONTH & DAY
fields.
 TIME:-is used to store time using HOUR,MINUTE & SECOND
fields
 TIMESTAMP:- used to store date and time. E.g. birthdate DATE

4.Boolean data
 Consists of the distinct truth values TRUE or FALSE, unless
prohibited by a NOTNULL constraint
 For NULL type it return UNKNOWN result.
 E.g. status BOOLEAN

11
Integrity enhancement features
 Integrity refers constraints that we wish to impose in order to protect
the database from becoming inconsistent.
 It includes:- Required data
domain constraint
Entity integrity
Referential integrity
1. Required data:- Some column must contain a valid value.
 They are not allowed to contain NULL values
 NULL is distinct from blank or zero
 NULL is used to represent data that is either not available, missing
or not applicable.
 Set at defining stage (CREATE, ALTER)

E.g. position VARCHAR(10) NOT NULL

12
Integrity enhancement features … Cont’d
2. Domain Constraint:- Every column has a domain, i.e. a set of legal
values. To set these constraint use
 CHECK: Check clause allows a constraint to be defined on a
column or the entire table.
CHECK (searchcondition)
e.g. Sex CHAR NOT NULL CHECK(sex IN(‘M’, ‘F’))
 DOMAIN

Syntax: Create DOMAIN domainname[AS] datatype

[DEFAULT defaultoption]
[CHECK (searchcondition)]
E.g. Create DOMAIN sextype as CHAR
DEFAULT ‘M’
CHECK(VALUE IN(‘M’,’F’));
 This creates a domain sextype.
13
Integrity enhancement features … Cont’d
 when defining a column sex,we use a domain name sextype in place
of the data type CHAR
I.e. sex sextype NOT NULL
 To remove domain use DROP DOMAIN constraint
Syntax: DROP DOMAIN domainname [RESTRICT|CASCADE]
 The drop behavior, RESTRICT|CASCADE specifies the action to be
taken if the domain is currently being used.
 If RESTRICT is specified and the domain is used in an existing table or
view the drop will fail.
 If CASCADE is specified any column that is based on domain is
automatically changed to use the domains underlying datatype
(constraint/default for domain is replaced by constraint/default of column,
if appropriate)

14
Integrity enhancement features … Cont’d
3. Entity integrity:-A primary key of a table must contain a unique, non-
null value for each row
PRIMARY KEY (sid)
 To define a composite primary key

PRIMARY KEY (sid,cid)

 Or we can use UNIQUE clause

Sid VARCHAR(5) NOT NULL,

cid VARCHAR(9) NOTNULL,
UNIQUE(sid,cid)

15
 Integrity enhancement features … Cont’d
4. Referential Integrity:-
Syntax: FOREIGN KEY(columnname) REFERENCES relationname
 Referential action specified using ON UPDATE and ON DELETE subclause of
Fk clause.
 When a user attempt to delete a row from a parent table, SQL support four
options regarding the actions to be taken :-
 CASCADE:- delete the row from the parent table and child table.
 SET NULL:- delete the parent and set null for child row , i.e. occur if the Fk
column don’t have NOT NULL qualifier specified.
 SET DEFAULT:- delete the row from the parent and set default value for
child, i.e. valid if the Fk column have a DEFAULT value specified
 NO ACTION:-reject the delete operation from the parent table. It is the
default setting if the ON DELETE rule is omitted.
E.g.1. FOREIGN KEY(cid) REFERENCES course ON DELETE SET NULL
2. FOREIGN KEY(cid) REFERENCES course ON UPDATE CASCADE
16
Data Definition Language (DDL)
 The SQL data definition language(DDL) allow database objects such
as schemas, domains, tables, views and indexes to be created and
destroyed.
 The main SQL data definition language statements are:-
CREATE DATABASE DELETE DATABASE
CRATE TABLE ALTER TABLE DROP TABLE
CRATE VIEW DROP VIEW
CREATE INDEX DROP INDEX

17
SQL Data Definition Commands
Data Definition Language (DDL) … Cont’d
1. Creating a database
Syntax: CREATE DATABASE database-name
e.g. CREATE DATABASE dillauni
2. Creating a table

Syntax:

CREATE TABLE table_name

(column-name datatype[NOT NULL][UNIQUE],

PRIMARY KEY(listofcolumn), FOREIGN KEY(listofcolumn)

REFERENCES parenttablename(listofcandidatekeycolumn))

19
Data Definition Language (DDL) … Cont’d
1. Create table department
(did varchar(9) primary key, deptname varchar(12) NOT NULL
UNIQUE CHECK( deptname IN(‘compsc’,’maths’,’stat’),school
varchar(20))

2. create table student

(sid varchar(9) primary key, fname varchar(9) NOT NULL,lname
varchar(20),did varchar(9), constraint fk_did FOREIGN KEY(did)
REFERENCES department(did))

20
Create Table
CREATE TABLE customer (
customerID INTEGER CHECK (customerID > 0)
PRIMARY KEY,
name VARCHAR(30) NOT NULL,
Sex VARCHAR(1) NOT NULL,
Mobile VARCHAR(12) NOT NULL,
street VARCHAR(30) NOT NULL,
city VARCHAR(20)
);

21
Create Table
CREATE TABLE Product(
productID VARCHAR(12) NOT NULL PRIMARY KEY,
ProdName VARCHAR(10) NOT NULL,
ProType VARCHAR(15) NOT NULL,
Price FLOAT NOT NULL,
Exp_Date DATETIME NOT NULL
);

22
CREATE TABLE Orders(
OrderId INTEGER CHECK(OrderId>0) NOT NULL PRIMARY KEY,
productID VARCHAR(12),
customerID INTEGER,
Amount VARCHAR(12) NOT NULL,
Date DATETIME NOT NULL,
Status VARCHAR(20) NOT NULL DEFAULT 'open',
UNIQUE (customerID, productID, Date),
FOREIGN KEY (customerID) REFERENCES Customer(customerID)
ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY (productID) REFERENCES Product (productID)
ON UPDATE CASCADE
);
23
Data Definition Language (DDL) … Cont’d
3. Deleting database and Dropping tables
DROP :- is used to delete the whole database
Syntax: DATABASE database-name E.g. DELETE DATABASE dillaui
DROP:- used to remove a table from a database
Syntax: DROP TABLE table-name. E.g. DROP TABLE Orders;

4. Altering a table: Used to modify a table after it is created

Syntax: ALTER TABLE table-name
DROP COLUMN column-name
ADD column-name data-type
ALTER COLUMN column-name data-type
E.g. ALTER TABLE orders
ADD Address Varchar(15),
ALTER COLUMN status Varchar(27);
24
Data Mefinition Language (DML)
 Is used to insert, retrieve and modify database information
 The following are commonly used DML clauses:-
 INSERT:- to insert data in a table
 SELECT:- to query data in the database
 UPDATE:- to update data in a table
 DELETE:- to delete data from a table

25
Data Manipulation Commands
1. INSERT
 After a table has been created, we can use the INSERT
command to add tuples
 Unspecified attribute values are set to the default value or
NULL
 Attribute order can be changed via optional column names
 "bulk loader" utilities to insert large amounts of tuples

INSERT INTO table_name

VALUES (value1, value2, value3,...)
27
 INSERT INTO table_name (column1, column2,
column3,...) VALUES (value1, value2, value3,...)
 Recommended
 INSERT INTO Customer values (1,'Debebe
Yilma','M','+2519234556','BombTera','Addis Ababa');
 insert into customer (customerID, name,
Sex,Mobile,street,city) values(2,'Zekeriya
Shemsu','M','+2519254556','SidamoTera','Addis
Ababa');

28
Retrieval Queries in SQL/SELECT
 SQL has one basic statement for retrieving information from a
database; the SELECT statement
 This is not the same as the SELECT operation of the relational
algebra
 Important distinction between SQL and the formal relational
model:
 SQL allows a table (relation) to have two or more tuples that are
identical in all their attribute values
 Hence, an SQL relation (table) is a multi-set (sometimes called a
bag) of tuples; it is not a set of tuples
 SQL relations can be constrained to be sets by specifying
PRIMARY KEY or UNIQUE attributes, or by using the
DISTINCT option in a query
Retrieval Queries in SQL (contd.)
 A bag or multi-set is like a set, but an element may
appear more than once.
 Example: {A, B, C, A} is a bag. {A, B, C} is also a bag that
also is a set.
 Bags also resemble lists, but the order is irrelevant in a bag.
 Example:
 {A, B, A} = {B, A, A} as bags
 However, [A, B, A] is not equal to [B, A, A] as lists
Retrieval Queries in SQL (contd.)
 Basic form of the SQL SELECT statement is a SELECT-
FROM-WHERE block

SELECT <attribute list>

FROM <table list>
WHERE <condition>
 <attribute list> is a list of attribute names whose values are to be
retrieved by the query
 <table list> is a list of the relation names required to process the
query
 <condition> is a conditional (Boolean) expression that identifies
the tuples to be retrieved by the query
 A basic SQL query consists of a SELECT, a FROM and a WHERE
clause
SELECT
 - specifies the columns to appear in the result (projection in relational
algebra)
FROM
 - specifies the relations to be used (cartesian product in relational
algebra)
WHERE
 - filters the tuples (selection in relational algebra)
 - join conditions are explicitly specified in the WHERE clause

GROUP BY
 - groups rows with the same column values
 - the HAVING construct can be used to further filter the groups

ORDER BY
 - defines the order of the resulting tuples
32
In general, the SELECT FROM WHERE parts are evaluated as
follows
1. generate a cartesian product of the relations listed in the
FROM
clause
2. apply the predicates specified in the WHERE clause on the
result
of the first step
3. for each tuple in the result of the second step output the
attributes (or results of expressions) specified in the SELECT
clause
The evaluation is normally optimised by a query optimiser
33
 In the WHERE clause we can use five basic predicates (search
conditions)
comparison
 - compare two expressions

range
 - check whether the value is within a specified range of values
(BETWEEN)
set membership
 - check whether the value is equal to a value of a given set (IN)

pattern matching
 - test whether the expression matches a specifies string pattern (LIKE)

check for NULL values

 - check whether the expression is a NULL value (IS NULL)

34
Select * from Customer
Where city IN(Addis Ababa,Dessie);

SELECT protype, price * 1.5 AS newPrice

FROM Product;

35
Pattern Matching
 Strings are enclosed in single quotes
 use a double single quote for escaping
 The LIKE operator is used for pattern matching
 the underscore (_) is a placeholder for a single character
 the percent sign (%) is a placeholder for any substring

e.g. LIKE '_e%'

select name from Customer
where name like '%T%';

36
Pattern match search condition(LIKE/NOT LIKE)
 (%) percent character represent any sequence of zero or more character.
 ( _ ) underscore character represent any single character.
 LIKE ‘H%’ :- The first character must be H, the rest can be any thing
 LIKE ‘H_ _ _’ :- There must be exactly 4 characterstics ,the first must
be H
 LIKE ‘%e’ :- The last character is e
 LIKE ‘%Abebe%’:- A sequence of character of any length containing
‘Abebe’
 NOT LIKE ‘H%’:- the first character can’t be an H.

E.g. select * from student

where fname LIKE ‘A%’;

37
 Data Definition Language (DML) … Cont’d
1. Retrieve all rows and columns:- list all columns in the table
 Where clause is unnecessary

e.g. SELECT customerID, name, Sex,Mobile,street,city FROM

student;
 Use (*) for quick way of expressing all columns

e.g. SELECT * FROM Customert;

2. Use of DISTINCT clause: DISTINCT is used to eliminate the
duplicates
E.g. SELECT DISTINCT name FROM Customer

38
Q. Retrieve the Street and Name of the Customer whose
Mobile number is '+2519254556'.
Select name, street
From Customer
Where Mobile=+251925455’;
Q2: Retrieve the name start by T and Street, City of all
Customrs who Orders product type ‘Juice' .
Select name , street, city
from Customer,Product,Orders
Where prodType= ‘Juice’ ;

39
Select name , street, city
from Customer,Product,Orders
Where proType= 'Juice' and
Customer.customerID=Orders.customerID and
Product.productID=Orders.productID ;

40
FROM Clause

 The FROM clause creates a cartesian product of multipl

 relations and can be used to specify join operations
 In a previous lecture we have seen the following
relational algebra expression
"list the name and street of customers whose order is still
open“
SELECT name, street
FROM Customer, Orders
WHERE Orders.customerID = Customer.customerID AND
status = 'open';

41
Inner and Outer Joins

 Note that there exist SQL extensions to perform join

 operations between two relations R and S in the FROM

clause
Inner Joins
SELECT * FROM R NATURAL JOIN S;
SELECT * FROM R CROSS JOIN S;
SELECT * FROM R JOIN S ON R.A > S.B;

42
 Outer Joins
SELECT * FROM R LEFT OUTER JOIN S ON R.A =
S.B;
SELECT * FROM R RIGHT OUTER JOIN S ON R.A =
S.B;
SELECT * FROM R FULL OUTER JOIN S ON R.A =
S.B;
Q.Write the SQL that display Customer name and order
orderID?
name orderID

43
Select Customer.name, Orders.orderID
From Customer
Inner join Orders on
Customer.customerID=Orders.customerId;

44
Sorting

 The ORDER BY clause can be used to arrange the result

tuples in ascending (ASC) or descending (DESC) order
 multiple sort keys can be specified; highest priority first
 tuples with NULL values are either before or after non-
NULL tuples
SELECT name, street, city
FROM Customer
ORDER BY city ASC, name DESC;

45
Aggregate Functions and Grouping

 In SQL there are five aggregate functions (MIN, MAX,

AVG,
 SUM and COUNT) that take a set or multiset of values as
 input and return a single value

Example: "Find the number of customers in each city“

SELECT city, COUNT(customerID) AS number
FROM Customer
GROUP BY city;

46
The HAVING Clause
 The HAVING clause was added to SQL because the
WHERE key word could not be used with aggregate
functions
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

47
Select AVG(Price),ProType from Product
group by ProType having AVG(Price)>5;

48
DELETE
 Removes tuples from a relation
 Includes a WHERE-clause to select the tuples to be deleted
 Referential integrity is enforced
 Tuples are deleted from only one table at a time (unless
CASCADE is specified on a referential integrity constraint)
 A missing WHERE-clause specifies that all tuples in the
relation are to be deleted; the table then becomes an empty
table
 The number of tuples deleted depends on the number of
tuples in the relation that satisfy the WHERE-clause

Example???
 The DELETED table stores copies of the affected rows
during DELETE and UPDATE statements.
 During the execution of a DELETE or UPDATE
statement, rows are deleted from the trigger table and
transferred to the DELETED table.
Q. Delete the all customer whose city is Adama

DELETE FROM table_name

WHERE some_column=some_value
Delete from Customer
Where city=‘Adma”;
50
Update
The UPDATE statement is used to update existing records in
a table.
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Update Customer set street=‘Abageda Hall ‘ where

customerID=5;

51
Data Definition Language (DML) … Cont’d
I. Comparison search condition:-
in SQL the following simple comparison operator are available:

= equals

<> Is not equal to(ISO standard)

< Less than

> Grater than

<= Less than or equal to

>= Greater than or equal to

!= Is not equal to (allowed in some

dialects)
52
Data Definition Language (DML) … Cont’d
 SQL also supports logical support like:-
AND,OR,NOT
 The rules for evaluating a conditional expression are:-
 an expression is evaluated from left to right
 Sub expression in the bracket are evaluated first
 NOTs are evaluated before ANDs and Ors
 ANDs are evaluated before Ors
 Compound comparison

E.g. select * from student

where fname=‘Alemu’ OR lname=‘Kebede’;

53
Data Definition Language (DML) … Cont’d
II. Range search condition(BETWEEN/NOT BETWEEN):- between
includes the end points of the range.
E.g. select * from student
Where age BETWEEN 15 AND 25;
 Use a negated version (NOT BETWEEN) that check for values
outside the range.
III. Set membership search condition(IN/NOT IN):-
 IN checks/tests whether a data value matches one of a list of values.
 NOT IN is the negated version

E.g. select * from student

Where fname IN(‘Abebe’,’Kebede’);

54
Data Definition Language (DML) … Cont’d
V. Null search condition(IS NULL/ISNOT NULL)
 A NULL comment is considered to have unknown value.

e.g. select * from student

where did=‘d001’ AND comment IS NULL
 The negated version (IS NOT NULL) can be used to test values that
are not null.

55
 Modifying data in the database
 Update statement allows the contents of existing data to be modified.
Syntax
UPDATE <table name>
SET column name1 =data value1,column name2 =data value2,………
where search condition]
 rows in a named table to be changed

 For UPDATE all rows (where close is omitted)

If you want to double age of student
Eg . UPDATE student
Set age=age*2;

56
Modifying data in the database … Cont’d
 UPDATE specific rows
use where to specify a condition
E.g. UPDATE student
Set age=25
Where sid=’s001’;
 Update mulitiple columns

Eg. UPDATE student

Set age=45,did=’d080’
Where fname=’Abebe’

57
 Modifying data in the database … Cont’d
 Deleting data from the data base
Use the DELETE statement allow rows to be delete from a named table
This statement does not touch table definition
Syntax DELETE FROM <tablename>
WHERE [search condition]
I. DELETE all rows : delete/remove all rows
where is omitted
Eg. DELETE from student or DELETE * from student
II. Delete specific rows: where clause is used for specifying a condition.
Eg. Delete from student
Where sid=’s001’

58
SQL Aggregate Functions
 COUNT :- return the number of values in a specified column.
 SUM :- return the sum of values in a specified column
 AVG :- return the average values in a specified column.
 MIN :- return the smallest values in a specified column.
 MAX :- return the largest values in a specified column.

59
SQL Aggregate Functions … Cont’d
 Use of count(*): To count all values in a column
Eg. Select count (*) As newage
From student
Where age>25
 This statement count all columns that have age above >25
 As used for labeling column name
 Use of count(DISTINCT <column name>): Used to count distinct
value(remove repetition)
Eg. Select count(distinct age) as newage
From student
Where age>15

60
SQL Aggregate Functions … Cont’d
 Use of count and sum: We can combine functions together
E.g. Find the total no. student and sum of their age whose age is above
25
Select count(sid) as sno,sum(age) as newage
From student
Where age ≥ 25
 Use of MIN,MAX,AVG: find the min,max and average of student
age.
E.g. Select MIN(age) as minage, MAX(age) as maxage, AVG(age) as
average from staff.
 use group by used to display a grouped query.

E.g. Select did count(fname) as no ,sum(avg)as no age

From student Group by did Order by did;
61
Use Group by Clause
 Used to group aggregate functions that applied to records based on
column values.
 used to display a grouped query.

Eg. Select did count(fname) as no ,sum(avg)as noage

From student
Group by did;
Order by did;
 Sorting results(ORDER BY clause):-

Use Ascending (ASC)

Descending (DESC)
E.g. select fname,lname,age from student
ORDER by age DESC;

62
Use of Having Clause
 The HAVING clause is used to filter grouped data
 Is used to perform aggregate function on grouped data

E.g. Select did, count(fname) as noofstud, sum(age) as newage

from student
group by did
having count(fname)>1

63
Using a sub query using equality
 Sub queries consits of queries within queries that is used to retrive
data from related tables
Eg. Select fname,lname
From student where did=(select did from department where
depthead=‘Jemal’);
 using sub query with an aggregate function

Eg. Select fname,lname,age(select avg(age) from student) as agediff

From student
Where age>(select avg(age)from student);

64
Use ANY/SOME and ALL
 ALL :- this clause return value if it is satisfied by all values
produced by the sub query.
 ANY /some :- the condition will be true if it is satisfied by any(one
or more)values Produced by sub query.
e.g1. Select fname,lname,age from student
Where age >some(select age from head
where did=’d001’)
e.g2.select fname,lname,age from student
where age>ALL(select age from head
where did=’d001’)

65
 Join
J oin:- used to combine columns from several tables in to a single table
I. Simple join
 Used to join columns from two or more tables based on equality
 Is a type of equi join
 Use alias for short hand representation of tables
 Table Aliases: Using full table names as prefixes can make SQL queries
unnecessarily wordy. Table aliases can make the code a little more concise.

E.g. Select s.sid,fname, lname from student s, department d where s.sid=d.did

Order by lname desc
The SQL standared provides the f/f alternative
 From Student Join ON departement h.did=s.did
 From student Join department USING did
 From Student NATURAL Join department
66
 Join … Cont’d
 Three table Join: Selecting values from three tables
E.g. select h.hid,d.dname,s.fname, s.lname
from head h,department d, student s
where h.hid=d.hid AND d.did=s.did
order by h.hid

E.g. Multiple grouping columns

Find the no of student in compSc
Select s.sid,s.fname, count(*) as noos stud from student s,department p,
Where s.did=p.did group by s.sid, s.did order by s.sid,s.did

67
Join … Cont’d
 Computing a Join
SELECT {DISTINCT /ALL{}*/Column list}
From tablename1 CROSS JOIN table name2
 The inner join of two table

Eg. Select b*,p*

from student b, department p where b.did=p.did
 Left outer join

Eg. Select b.*,p.*

From student b LEFT JOIN Dept p ON b.bdid=p.pdid
 <Right join>
 <full join>

68
 Views
 A view is a virtual table based on the result-set of a SELECT 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 a single table.
 Note: The database design and structure will NOT be affected by the
functions, where, or join statements in a view.

Syntax: CREATE VIEW view_name AS

SELECT column_name(s)
FROM table_name
WHERE condition
 Note: The database does not store the view data. The database engine
recreates the data, using the view's SELECT statement, every time a user
queries a view.
69
 Using Views
 A view could be used from inside a query, a stored procedure, or
from inside another view. By adding functions, joins, etc., to a view,
it allows you to present exactly the data you want to the user.
 The sample database Northwind has some 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

70
 Using Views … Cont’d
 We can query the view above as follows:
SELECT * FROM [Current Product List]
 Another view from the Northwind sample database selects every
product in the Products table that has a unit price that is 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]

71
 Using Views … Cont’d
 Another example view from 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'
72