Oracle SQl-2

SQL Queries, SELECT Statement

Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views
For example to retrieve all rows from emp table.

SQL> select  empno, ename, sal  from emp;

          Or (if you want to see all the columns values
You can also give * which means all columns)

SQL> select * from emp;

If you want to see only employee names and their salaries then you can type the following statement

SQL> select name, sal from emp;

Filtering Information using Where Conditions

 You can filter information using where conditions like suppose you want to see only those employees whose salary is above 5000 then you can type the following query with where condition

SQL>select * from emp where sal > 5000;

To see those employees whose salary is less than 5000 then the query will be

SQL> select * from emp where sal < 5000;

Logical Conditions

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table below lists logical conditions.

Condition	Operation	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM emp WHERE NOT (sal IS NULL); SELECT * FROM emp WHERE NOT (salary BETWEEN  1000 AND 2000);
AND	Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.	SELECT * FROM employees WHERE ename ='SAMI' AND sal=3000;
OR	Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.	SELECT * FROM emp WHERE ename = 'SAMI' OR sal >= 1000;

 Membership Conditions

A membership condition tests for membership in a list or subquery
The following table lists the membership conditions.

Condition	Operation	Example
IN	"Equal to any member of" test. Equivalent to "= ANY".	SELECT * FROM emp WHERE deptno IN (10,20); SELECT * FROM emp   WHERE deptno IN (SELECT deptno FROM dept WHERE city=’HYD’)
NOT IN	Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM emp  WHERE ename NOT IN  ('SCOTT', 'SMITH');

Null Conditions

A NULL condition tests for nulls.
What is null?
If a column is empty or no value has been inserted in it then it is called null. Remember 0 is not null and blank string ‘ ’ is also not null.
The following example lists the null conditions.

Condition	Operation	Example
IS [NOT] NULL	Tests for nulls. This is the only condition that you should use to test for nulls.	SELECT ename  FROM emp  WHERE deptno  IS NULL;  SELECT * FROM emp WHERE ename IS NOT NULL;

EXISTS Conditions

An EXISTS condition tests for existence of rows in a subquery.
The following example shows the EXISTS condition.

Condition	Operation	Example
EXISTS	TRUE if a subquery returns at least one row.	SELECT deptno   FROM dept d  WHERE EXISTS     (SELECT * FROM emp e           WHERE d.deptno = e.deptno);

LIKE Conditions

The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set.
For example you want to see all employees whose name starts with S char. Then you can use LIKE condition as follows

SQL> select * from emp where ename like ‘S%’ ;

Similarly you want to see all employees whose name ends with “d”

SQL>select * from emp where ename like ‘%d’;

 

You want to see all employees whose name starts with ‘A’ and ends with ‘d’ like ‘Abid’, ’Alfred’, ’Arnold’.
 

SQL>select * from emp where ename like ‘A%d’;

You want to see those employees whose name contains character ‘a’ anywhere in the string.

SQL> select * from emp where ename like ‘%a%’;

To see those employees whose name contains ‘a’ in second position.

SQL>select * from emp where ename like ‘_a%’;

To see those employees whose name contains ‘a’ as last second character.

SQL>select * from emp where ename like ‘%a_’;

To see those employees whose name contain ‘%’ sign.  i.e. ‘%’ sign has to be used as literal not as wild char.

SQL> select * from emp where ename like ‘%\%%’ escape ‘\’;

The UNION [ALL], INTERSECT, MINUS Operators

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows.

select empno,ename,sal from emp
UNION
select empno,ename,salary from oldemp

What if you need to select rows from two tables, but tables have different columns?
In this situation you have to use TO_CHAR function to fill up missing columns.
For Example
This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

select empno, ename, sal, to_char(null) as “Transfer Date” from emp
 UNION
select empno,ename,to_char(null) as “Sal”,tdate from oldemp;

EMPNO     ENAME     SAL       Transfer Date
-----     -----     ------    -------------
101       Sami      5000     
102       Smith              11-jul-2000
201       Tamim              10-AUG-2000
209       Ravi      2400     

UNION ALL Example

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

select empno,ename from emp
union all
select empno,ename from oldemp;

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT empno FROM emp
INTERSECT
SELECT empno FROM oldemp;

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT empno FROM emp
MINUS
SELECT empno FROM oldemp;

SORTING QUERY RESULTS

To sort query result you can use ORDER BY clause in SELECT statement. Sorting Examples.
The following query sorts the employees according to ascending order of salaries.

select * from emp order by sal;

The following query sorts the employees according to descending order of salaries.
 

select * from emp order by sal desc;

The following query sorts the employees according to ascending order of names.

select * from emp order by ename;

The following query first sorts the employees according to ascending order of names.If names are equal then sorts employees on descending order of salaries.

select * from emp order by ename, sal desc;

You can also specify the positions instead of column names. Like in the following query,which shows employees according to ascending order of their names.

select * from emp order by 2;

The following query first sorts the employees according to ascending order of salaries.
If salaries are equal then sorts employees on ascending order of names

select * from emp order by 3, 2;