Page 54 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 3: Structured Query Language

The use of ‘select *’ is useful for those queries which are interactive but it is a poor style as it does Notes
not clearly mention the schema of the resulting relation. To determine the schema one has to
refer the relation mentioned in FROM clause.
Result of “Select *”

Eid Ename DNo Esal Age Phone
101 John 2 35000 50 24578912
100 Henry 7 22000 25 55809192
97 David 5 30000 41 23535135
108 Sam 1 25000 32 24532121
102 Henry 2 22000 35 24578290
120 Smith 4 20000 20 56408489
When strings are sorted alphabetically then we can apply comparison operators.

Collation

It is a mechanism that is used to compare the string characters to determine which characters are
smaller (ASCII code) than the other characters in a particular string.
In addition to this, SQL provides another operator LIKE operator to perform pattern matching.
It is of the form,
Scalar expression LIKE literal [Escape character] where,
Scalar expression = string value
Literal = ‘-’ single character

= ‘%’ zero or more character sequence!
The string ‘% ar %’ results the set of strings which contains the characters ‘ar’ in them. The length
of the resulting string must be atleast four characters.

To understand this, consider the following query
Query (d): List the names of the employees whose name start with ‘H’ and has ‘r’ as the third
character.

Solution:
SELECT E.ename AS name, E.sal as salary
FROM Employee E

WHERE E.ename LIKE ‘H-r%’.
This will result in a relation consisting of names of all the employees whose name start with H
and third character is ‘r’. The answer in this case is

Name Salary
Harry 18000

LOVELY PROFESSIONAL UNIVERSITY 47