Data Manipulation Language

<< Select Statement, Attribute Allias

ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product >>

Database Management System (CS403)

Lecture No. 29

Reading Material

"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,

McGraw-Hill

"Teach Yourself SQL in 21 Days", Second Edition Que Series.

Overview of Lecture

Data Manipulation Language

In the previous lecture we have studied the SELECT statement, which is the most

widely used SQL statement. In this lecture we will study the WHERE clause. This is

used to select certain specific rows.

The WHERE clause allows you to filter the results from an SQL statement - select,

insert, update, or delete statement. The rows which satisfy the condition in the where

clause are selected. The format of WHERE clause is as under:

SELECT [ALL|DISTINCT]

{*|culumn_list [alias][,.....n]} FROM table_name

[WHERE <search_condition>]

Here WHERE is given in square brackets, which means it is optional. We will see the

search condition as under:

Search Condition

{

[ NOT ] < predicate > | ( < search_condition > ) }

[ { AND | OR } [ NOT ] { < predicate > |

( < search_condition > ) } ]

}

[ ,...n ]

< predicate > ::=

{

expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }

215

Database Management System (CS403)

expression

| string_expression [ NOT ] LIKE string_expression

| expression [ NOT ] BETWEEN expression AND

expression

| expression IS [ NOT ] NULL

| expression [ NOT ] IN ( subquery | expression [ ,...n ] )

| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }

{ ALL | SOME | ANY} ( subquery )

| EXISTS ( subquery )

}

In this format where clause is used in expressions using different comparison

operators. Those rows, which fulfill the condition, are selected in the output.

SELECT *

FROM supplier

WHERE supplier_name = 'IBM';

In this first example, we have used the WHERE clause to filter our results from the

supplier table. The SQL statement above would return all rows from the supplier

table where the supplier_name is IBM. Because the * is used in the select, all fields

from the supplier table would appear in the result set. We will now see another

example of where clause.

SELECT supplier_id

FROM supplier

WHERE supplier_name = 'IBM'

or supplier_city = 'Karachi';

We can define a WHERE clause with multiple conditions. This SQL statement would

return all supplier_id values where the supplier_name is IBM or the supplier_city is

Karachi..

SELECT supplier.suppler_name, orders.order_id

FROM supplier, orders

WHERE supplier.supplier_id = orders.supplier_id

and supplier.supplier_city = 'Karachi';

216

Database Management System (CS403)

We can also use the WHERE clause to join multiple tables together in a single SQL

statement. This SQL statement would return all supplier names and order_ids where

there is a matching record in the supplier and orders tables based on supplier_id, and

where the supplier_city is Karachi.

We will now see a query in which those courses, which are part of MCS, are to be

displayed

Q: Display all courses of the MCS program

Select crCode, crName, prName from course

where prName = `MCS

Now in this query whole table would be checked row by row and where program

name would be MCS would be selected and displayed.'

Q List the course names offered to programs other than MCS

SELECT crCode, crName, prName

FROM course

WHERE not (prName = `MCS')

Now in this query again all the rows would be checked and those courses would be

selected and displayed which are not for MCS. So it reverses the output.

The BETWEEN condition allows you to retrieve values within a specific range.

The syntax for the BETWEEN condition is:

SELECT columns

FROM tables

WHERE column1 between value1 and value2;

This SQL statement will return the records where column1 is within the range of

value1 and value2 (inclusive). The BETWEEN function can be used in any valid

SQL statement - select, insert, update, or delete. We will now see few examples of

this operator.

SELECT *

FROM suppliers

WHERE supplier_id between 10 AND 50;

217

Database Management System (CS403)

This would return all rows where the supplier_id is between 10 and 50.

The BETWEEN function can also be combined with the NOT operator.

For example,

SELECT *

FROM suppliers

WHERE supplier_id not between 10 and 50;

The IN function helps reduce the need to use multiple OR conditions. It is sued to

check in a list of values. The syntax for the IN function is:

SELECT columns

FROM tables

WHERE column1 in (value1, value2,.... value_n);

This SQL statement will return the records where column1 is value1, value2... or

value_n. The IN function can be used in any valid SQL statement - select, insert,

update, or delete. We will now see an example of IN operator.

SELECT crName, prName

From course

Where prName in (`MCS', `BCS')

It is equal to the following SQL statement

SELECT crName, prName

From course

Where (prName = `MCS') OR (prName = `BCS')

Now in these two queries all the rows will be checked for MCS and BCS one by one

so OR can be replaced by IN operator.

The LIKE operator allows you to use wildcards in the where clause of an SQL

statement. This allows you to perform pattern matching. The LIKE condition can be

used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are:

% Allows you to match any string of any length (including zero length)

218

Database Management System (CS403)

_ Allows you to match on a single character

We will now see an example of LIKE operator

Q: Display the names and credits of CS programs

SELECT crName, crCrdts, prName FROM course

WHERE prName like '%CS'

The ORDER BY clause allows you to sort the records in your result set. The ORDER

BY clause can only be used in SELECT statements.

The syntax for the ORDER BY clause is:

SELECT columns

FROM tables

WHERE predicates

ORDER BY column ASC/DESC;

The ORDER BY clause sorts the result set based on the columns specified. If the

ASC or DESC value is omitted, the system assumed ascending order.

ASC indicates ascending order. (Default)

DESC indicates descending order.

We will see the example of ORDER BY clause in our next lecture.

In today's lecture we have discussed different operators and use of WHERE clause

which is the most widely used in SQL Commands. These different operators are used

according to requirements of users. We will study rest of the SQL Commands in our

coming lectures.

219

Table of Contents: