SQL BASICS-II FOR JNTU BTECH STUDENTS

Selection of TuplesUp to now we have only focused on selecting (some) attributes of all tuples from a table. If one is
interested in tuples that satisfy certain conditions, the where clause is used. In a where clause
simple conditions based on comparison operators can be combined using the logical connectives
and, or, and not to form complex conditions. Conditions may also include pattern matching
operations and even subqueries (Section 1.5).
4
Example: List the job title and the salary of those employees whose manager has the
number 7698 or 7566 and who earn more than 1500:
select JOB, SAL
from EMP
where (MGR = 7698 or MGR = 7566) and SAL > 1500;
For all data types, the comparison operators =, != or <>,<, >,<=, => are allowed in the
conditions of a where clause.
Further comparison operators are:
• Set Conditions: [not] in ()
Example: select * from DEPT where DEPTNO in (20,30);
• Null value: is [not] null,
i.e., for a tuple to be selected there must (not) exist a defined value for this column.
Example: select * from EMP where MGR is not null;
Note: the operations = null and ! = null are not defined!
• Domain conditions: [not] between and
Example: • select EMPNO, ENAME, SAL from EMP
where SAL between 1500 and 2500;
• select ENAME from EMP
where HIREDATE between ’02-APR-81’ and ’08-SEP-81’;
1.2.3 String Operations
In order to compare an attribute with a string, it is required to surround the string by apostrophes,
e.g., where LOCATION = ’DALLAS’. A powerful operator for pattern matching is the
like operator. Together with this operator, two special characters are used: the percent sign
% (also called wild card), and the underline , also called position marker. For example, if
one is interested in all tuples of the table DEPT that contain two C in the name of the department,
the condition would be where DNAME like ’%C%C%’. The percent sign means that any
(sub)string is allowed there, even the empty string. In contrast, the underline stands for exactly
one character. Thus the condition where DNAME like ’%C C%’ would require that exactly one
character appears between the two Cs. To test for inequality, the not clause is used.
Further string operations are:
• upper() takes a string and converts any letters in it to uppercase, e.g., DNAME
= upper(DNAME) (The name of a department must consist only of upper case letters.)
• lower() converts any letter to lowercase,
• initcap() converts the initial letter of every word in to uppercase.
• length() returns the length of the string.
• substr(, n [, m]) clips out a m character piece of , starting at position
n. If m is not specified, the end of the string is assumed.
substr(’DATABASE SYSTEMS’, 10, 7) returns the string ’SYSTEMS’.