Spread Knowledge

CS403 - Database Management Systems - Lecture Handout 29

User Rating:  / 0
PoorBest 

Related Content: CS403 - VU Lectures, Handouts, PPT Slides, Assignments, Quizzes, Papers & Books of Database Management Systems

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 { = | < > | ! = | > | > = | ! > | < | < = | ! < }

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';

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;

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)

_ 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.