Selecting All Columns
Select * from emp;
Selecting Specific Columns
Select deptno, loc from emp;
Arithmetic Expressions:
SELECT ename, sal, sal+300 FROM emp;
Operator Precedence
SELECT ename, sal, 12*sal+100 FROM emp;
Using Parentheses
SELECT ename, sal, 12*(sal+100) FROM emp;
Null Value
SELECT ename, job, comm FROM emp;
Null Values in Arithmetic Expressions
SELECT ename, 12*sal+comm FROM emp WHERE ename='KING';
select ename, sal, 12*sal+comm from emp where ename='JAMES';
Column Aliases
SELECT ename AS name, sal salary FROM emp;
SELECT ename, sal*1.1 “New Salary” FROM emp;
Concatenation Operator
SELECT ename||job AS "Employees" FROM emp;
SELECT ename || ' ' || 'is' || job "Details" from emp;
SELECT ename || ' ' || 'is' ||' ' || job from emp;
Literal Character Strings
SELECT ename ||' '||'is a'||' '||job AS "Employee Details" FROM emp;
Eliminating Duplicate Rows
SELECT DISTINCT deptno FROM emp;
Displaying Table Structure
DESC emp;
Limiting Rows Using a Selection
SELECT ename, job, deptno FROM emp WHERE job='CLERK';
Character Strings and Dates
SELECT ename, job, deptno FROM emp WHERE ename = 'JAMES';
Comparison Operators
SELECT ename, sal, comm FROM emp WHERE sal<=comm;
Other Comparison Operators:
Operator Meaning
BETWEEN Between two values (inclusive)
...AND...
Match any of a list of values
IN(list)
Match a character pattern
LIKE
Is a null value
IS NULL
BETWEEN, AND Operator
SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500;
IN Operator
SELECT empno, ename, sal, mgr FROM emp WHERE mgr IN (7902, 7566, 7788);
LIKE Operator
SELECT ename FROM emp WHERE ename LIKE 'S%';
SELECT ename FROM emp WHERE ename LIKE '%s%'; Wrong query because it is case sensitive
SELECT ename FROM emp WHERE ename LIKE '%S%';
SELECT ename FROM emp WHERE ename LIKE '_A%';
SELECT ename FROM emp WHERE ename LIKE '_L___';
IS NULL
SELECT ename, mgr FROM emp WHERE mgr IS NULL;
AND Operator
SELECT ename, mgr, comm FROM emp WHERE mgr IS NULL AND comm IS NULL;
OR Operator
SELECT ename, mgr, comm FROM emp WHERE mgr IS NULL OR comm IS NULL;
NOT Operator
SELECT ename, sal FROM emp WHERE sal NOT BETWEEN 1000 AND 1500;
SELECT ename, job FROM emp WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
SELECT ename, mgr FROM emp WHERE mgr IS NOT NULL;
SELECT ename FROM emp WHERE ename NOT LIKE 'S%';
Rules of Precedence
SELECT ename, job, sal FROM emp WHERE job='SALESMAN' OR job='PRESIDENT' AND
sal>1500;
ORDER BY Clause
SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate;
Sorting by Column Alias
SELECT empno, ename, sal*12 annsal FROM emp ORDER BY annsal;
Sorting by Multiple Columns
SELECT ename, deptno, sal FROM emp ORDER BY sal, deptno DESC;
SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC;
SELECT ename, deptno, sal FROM emp ORDER BY sal DESC, deptno;
SELECT max(avg(sal)) FROM emp GROUP BY deptno;
SELECT avg(sal) FROM emp GROUP BY deptno;
Case Conversion Function:
Lower:
select empno, ename, deptno from emp where lower(ename) = 'blake';
Upper:
select empno, ename, deptno from emp where Upper(ename)= 'BLAKE';
Initcap:
select empno, ename, deptno from emp where initcap(ename)= 'Blake';
Character Manipulation Function:
Concatination:
select empno, concat(ename, sal) from emp;
Sub String:
select ename, substr(ename, 1, 3) from emp;
Length:
select ename, length(ename) from emp;
In String:
select ename, instr(ename, 'R') from emp;
Left Padding:
select ename, sal, lpad(sal, 10, '#') from emp;
Number Functions:
Round:
Select round(45.789, 2) from dual;
Select round(45.789, -1) from dual;
Select round(51.789, -2) from dual;
Trunc:
Select trunc(45.789, 2) from dual;
Select trunc(45.789, -1) from dual;
Select trunc(51.789, -2) from dual;
MOD Function:
Select ename, sal, comm, mod(sal, comm) from emp;
Arithmatic Operators with Dates:
Select ename, (sysdate-hiredate) from emp;
Select ename, (sysdate-hiredate) from emp where lower(ename) = 'smith';
Date Functions:
To Character:
Select ename, to_char(hiredate, 'fmDD Month YYYY') from emp;
ENAME TO_CHAR(HIREDATE, ‘FMDD MONTH YYYY’)
SMITH 17 December 1980
ALLEN 2 April 1987
WARD 25 January 1992
Select ename, to_char(hiredate, 'DD Month YYYY') from emp;
ENAME TO_CHAR(HIREDATE, ‘DD MONTH YYYY’)
SMITH 17 December 1980
ALLEN 12 December 1987
WARD 25 December 1992
Select ename, sal, to_char(sal, ‘$999999’) from emp;
ENAME SAL TO_CHAR(SAL, ‘$999999’)
SMITH 800 $800
ALLEN 1600 $1600
WARD 1250 $1250
Select ename, sal, to_char(sal, ‘$999999’) from emp where ename=’SCOTT’;
ENAME SAL TO_CHAR(SAL, ‘$999999’)
SCOTT 3000 $3000
