ZeePedia

Data Manipulation Language

<< Select Statement, Attribute Allias
ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product >>
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
_ 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:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules