The point of using dummy text for your paragraph is that it has a mRestricting and Sorting Data
____________________________
*********************************
A SELECT Statement retrieves all the records from a Table.
Assume we need to display those Employee Records belonging to Department 10.
So for this we need to use the WHERE clause.
Syntax
SELECT *|{[DISTINCT] column|expression [alias],…}
FROM table
[WHERE condition(s)];
The WHERE clause follows the FROM clause and contains the condition that must be met. If the condition is true the row meeting that condition is returned.
WHERE – restricts the query to rows that meet the condition
condition – is composed of column names and a comparison operator
SELECT empno,ename,job,deptno
FROM emp
/
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno = 10
/
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno = 100
/
*************************************************
*************************************************
2
Character Strings and Dates
***************************
a. Character strings and date values are enclosed in single quotation marks.
b. Character values are case sensitive and date values are format sensitive
c. The default date format is DD-MON-RR
SELECT empno,ename,job,deptno
FROM emp
WHERE ename = ‘Smith’
/
SELECT empno,ename,job,deptno
FROM emp
WHERE ename = ‘SMITH’
/
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate = ‘December 17 1980’
/
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate = ’17-DEC-80′
/
*************************************************
*************************************************
3.
Comparison Conditions
********************
= – Equal To
> – Greater Than
>= – Greater Than or equal to
< – Less than
<= – Less than or equal to
<>/!= – Not Equal to
BETWEEN…AND… – Between two values(inclusive)
IN(set) – Match any of a list of values
LIKE – Match a Character pattern
IS NULL – Is a NULL value
SELECT empno,ename,sal
FROM
EMP
WHERE sal > 800
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal >= 800
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal < 3000
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal <= 3000
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal <> 800
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal ^= 800
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal BETWEEN 200 and 3000
/
SELECT empno,ename,sal
FROM
EMP
WHERE sal >=800 and sal <=3000
/
SELECT empno,ename,hiredate
FROM
EMP
WHERE hiredate BETWEEN ’01-JAN-80′ AND ’31-DEC-80′
/
SELECT empno,ename,hiredate
FROM
EMP
WHERE ename BETWEEN ‘SMITH’ AND ‘KING’
/
SELECT empno,ename,deptno
FROM
EMP
WHERE deptno IN(40,10,20)
/
SELECT empno,ename,deptno
FROM
EMP
WHERE deptno NOT IN(40,10,20)
/
LIKE Operator
4 Types in LIKE Operator :
1.First Character Ex. S%
2.Last Character Ex.%S
3.Second Character Ex._S%
4.Includes Character Ex.%S%
SELECT empno,ename,sal
FROM
EMP
WHERE ename LIKE ‘SM%’
/
SELECT empno,ename,sal
FROM
EMP
WHERE ename LIKE ‘M%’
/
SELECT empno,ename,sal
FROM
EMP
WHERE ename LIKE ‘%M_M%’
/
SELECT empno,ename,sal
FROM
EMP
WHERE ename LIKE ‘%M%’
/
— Query to display the data of those Employees having ‘S’ in there names —
SELECT empno,ename,sal
FROM
EMP
WHERE ename LIKE ‘%S%’
/
SELECT empno,ename,sal,comm
FROM emp
WHERE comm IS NULL
/
SELECT empno,ename,sal,comm
FROM emp
WHERE comm IS NOT NULL
/
*************************************************
*************************************************
4.
Logical Conditions
******************
AND – Returns TRUE if both component conditions are true
OR – Returns TRUE if either component condition is true
NOT – Returns TRUE if the following condition is false
A Logical Condition combines the result of two component conditions to produce a single result based on those conditions. A row is returned only if the result of the condition is true.
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal > 800
AND deptno = 10
/
SELECT ename,job,sal
FROM emp
WHERE job = ‘CLERK’ AND sal > 3000
OR job = ‘SALESMAN’
/
SELECT ename,job,sal
FROM emp
WHERE job IN (‘CLERK’,’SALESMAN’) AND sal > 3000
OR job = ‘SALESMAN’
/
SELECT empno,ename,sal
FROM emp
WHERE sal > 800
AND deptno = 50
/
SELECT empno,ename,sal
FROM emp
WHERE sal > 800
OR deptno = 50
/
SELECT empno,ename,deptno
FROM emp
WHERE deptno NOT IN (10,20)
/
The NOT operator can also be used with other SQL operators such as BETWEEN,LIKE and NULL
SELECT empno,ename,sal
FROM emp
WHERE
sal NOT BETWEEN 800 AND 2000
/
SELECT empno,ename,job
FROM emp
WHERE
ename NOT LIKE ‘%A%’
/
SELECT empno,ename,comm
FROM EMP
WHERE COMM IS NOT NULL
/
*************************************************
*************************************************
5.
Using the ORDER BY Clause
*************************
Sort the rows using the ORDER BY Clause
– ASC – Ascending order by default
– DESC – Descending Order
****The ORDER BY clause comes last in the SELECT statement.
— Sort Records in Ascending Order (Default Sorting) —
SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY sal
/
SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY sal desc
/
— NUll values getting displayed at last while using ASC in ORDER BY —
SELECT EMPNO,comm,SAL FROM EMP
ORDER BY comm
/
SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY ename
/
SELECT EMPNO,ENAME,hiredate FROM EMP
ORDER BY hiredate
/
— Sort Records in Descending Order —
SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY sal desc
/
— NUll values getting displayed at first while using DESC in ORDER BY —
SELECT EMPNO,comm,SAL FROM EMP
ORDER BY comm desc
/
SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY ename desc
/
SELECT EMPNO,ENAME,HIREDATE FROM EMP
ORDER BY hiredate desc
/
— Sort Records using Aliases —
SELECT empno,ename,sal * 12 annsal
FROM emp
ORDER BY annsal
/
SELECT empno,ename,sal * 12 annsal
FROM emp
ORDER BY sal * 12
/
SELECT empno,ename,sal * 12 annsal
FROM emp
ORDER BY sal
/
— Sorting the Col which is not selected in the SELECT Clause —
SELECT empno,ename,deptno
FROM emp
ORDER BY sal
/
— Sorting by Multiple Columns —
SELECT empno,sal,deptno
FROM emp
ORDER BY deptno,sal desc
/
SELECT empno,sal,deptno
FROM emp
ORDER BY deptno desc,sal desc
/
SELECT empno,sal,deptno
FROM emp
ORDER BY 3,2,1
/
*************************************************
*************************************************
6.
Substitution Variables
**********************
Use substitution variables to
temporarily store values with & and &&.
Use these in
1.WHERE conditions
2.ORDER BY clause
3.Column Expressions
4.Table Names
5.Entire SELECT statement
— Query to allow the User to enter value for Employee Name at runtime using & —
SELECT empno,ename,sal
FROM
emp WHERE ename = ‘&ENAME’
/
SELECT empno,ename,sal
FROM
emp WHERE ename = ‘&1’
/
— Query to allow the User to enter value for Employee Name at runtime using & —
SELECT empno,ename,sal
FROM
emp WHERE sal = &salary
/
— Query to allow the User to enter value for Employee Name at runtime using & —
SELECT empno,ename,hiredate
FROM
emp WHERE hiredate = ‘&date’
/
— Query to allow the User to enter value for Character values with Single Quotes enclosed —
SELECT empno,ename,sal
FROM
emp WHERE ename = &ENAME
/
SELECT ‘&ABC’ x FROM dual
/
— Do not allow the System to scan Substitution Variables —
SET SCAN OFF
SELECT ‘&ABC’ x FROM dual
/
*************************************************
*************************************************
7.
Specifying Column Names,expressions and Text using Substitution Variables
*************************************************************************
SELECT empno,ename,deptno,&col_name
FROM emp
WHERE &condition
ORDER BY &order_by
/
SELECT empno,ename,sal
FROM
emp WHERE ENAME = ‘&ENAME’
/
SELECT empno,hiredate,ename,sal
FROM
emp WHERE hiredate = &hiredate
/
SELECT empno,ename,sal
FROM
emp WHERE ename = ‘&&ENAME’
/
SELECT empno,ename,sal
FROM
emp WHERE ename = ‘&&ENME’
/
SELECT empno,ename,sal
FROM
emp WHERE ename = ‘&ENAME’
/
*************************************************
*************************************************ore-or-less normal distribution of letters. making it look like readable English.