3. Using Operators
Using Operators
Operators are used inside an expression to articulate specified conditions to retrieve data.
Comparison Operators
Comparison operators compare expressions and return one of three values: TRUE, FALSE, or Unknown.
Ques. List the name and salary of the employees whose salary is more than 1000.
SELECT ename, Sal
FROM Emp
WHERE Sal > 1000;
Practice: Can you sort the above results in descending order of Salary.
Ques. Display the all the Employees where Salary greater than equal to 3000 and less than equal to 4000.
SELECT empno, ename, Sal
FROM Emp
WHERE Sal >= 3000 and Sal <=4000;
Ques. List the names of the clerks working in the department 20;
SELECT ename
FROM emp
WHERE job = 'CLERK' and DeptNo = 20;
Ques. Display the names and jobs of analysts and salesman;
SELECT ename, job
FROM emp
WHERE job = 'ANALYST' OR job = 'SALESMAN';
Ques. List thedetails ofthe employees who have joined before the end of 30 September 81
SELECT *
FROM emp
WHERE hiredate <= '30-SEP-81';
Ques. List the names and jobs of the employees who are NOT managers.
SELECT ename,job
FROM emp
WHERE job <> 'MANAGER';
IN and NOT IN Operators
The IN and Not In operators determine whether a value matches any values in a list or a subquery.
Ques. List the name of the employees whose employee numbers are 7369,7521,7839
SELECT empno, ename,job
FROM emp
WHERE empno IN(7369,7521,7839)
Ques. Select all the Employees who work in Departments 10,20 and 30.
SELECT empno, ename, DeptNo
FROM Emp
WHERE DeptNo IN (10,20,30);
Ques. Select all the Employees who work ‘Not In’ Departments 10,20 and 30.
SELECT empno, ename, DeptNo
FROM Emp
WHERE DeptNo NOT IN (10,20,30);
Ques. Display Employee Number, Employee names and Job of employees having the Job as Clerk, SalesMan, Analyst
SELECT empno, ename, Job
FROM Emp
WHERE Job IN ('CLERK','SALESMAN', 'ANALYST');
The BETWEEN Operator is used to specify a range of values
Ques. Display ename, Job and Salary where Salary is between 3000 and 4000.
SELECT ename, job, Sal
FROM Emp
WHERE SAL BETWEEN 3000 and 4000;
Practice: Try the above query with NOT BETWEEN
Ques. List employee names, hiredate who have joined between 30th June 81 and after 31st December 81
SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN '30-Jun-81' and '31-DEC-81';
Selecting Data based on pattern match – using operator Like/Not Like with % and _
The pattern match condition is useful when you need to find values that are similar to a given pattern string or when you have only a partial piece of information to use as a search criterion. % and _ are also called wild character.
e.g. String like P% is used when the word is of any length but starts with letter P.
String like %a is used when the word is of any length but ends with a letter a.
String like %n% is used when the word is of any length but ends has ‘n’ in between
String like A_ _ _ is used when the word is of four-character word, starting with a letter A
Ques. Display the all the Employees where Name starts with a letter A.
SELECT ename
FROM emp
WHERE ename like 'A%';
Ques. Display the all the Employees where name does NOT start with a letter A.
SELECT ename
FROM emp
WHERE ename Not like 'A%';
Ques. Display the all the Employees whose names ends with an S.
SELECT ename
FROM emp
WHERE ename like 'S%';
Practice: Write above query where name starts with a letter S.
Ques. Findemployees whose names have exactly 5 characters
SELECT ename
FROM emp
WHERE ename like '_____';
Ques. List the employee names have ‘I’ as the second character
SELECT ename
FROM emp
WHERE ename like '_I%';
Ques. Display Employee Name where letters A appears exactly one time.
SELECT ename
FROM emp
WHERE ename Like '%A%' AND ename Not Like '%A%A%';
Ques. Display Employee Name where Name contains A but does not starts with AL.
SELECT ename
FROM Employees
WHERE ename Like '%A%' AND ename NOT Like '%Al%';
Using Concatenation Operator – ||
The Concatenation operator || is used to combine two columns.
Ques. Display empno concatenated with Ename and job.
SELECT empno || ename
FROM Emp;
Practice: Display empno and ename together with a hyphen between the two.
Null Values – IS Null and IS NOT NULL
Null is special. It is denoting a missing information. Null is not zero, a space or an empty string.
You cannot compare it with any other values like zero (0) or an empty string (”). NULL is even not equal to NULL.
Ques. Display employee names with no commission.
SELECT ename
FROM emp
WHERE COMM IS NULL;
Ques. List thenamesof the employee/s and their job, who does not report to anybody
SELECT ename, job
FROM emp
WHERE mgr IS NULL;
Using Arithmetic Operators
- Arithmetic operators can be done on numeric columns
- Alias names can be given to columns with expressions
- SQL Supports the basic arithmetic operators, +, -, *, and /
Ques. List the name, salary and PF amount of all the employees. (PF is calculated as 10% of Salary)
SELECT ename, sal, sal * .1 as PF
FROM emp;
Ques. Display ename, Salary as Monthly Salary and Salary as annual salary of employees in department 30.
SELECT ename, sal as "Monthly Salary”, Sal * 12 as "Annual Salary"
FROM emp
WHERE deptno = 30;