30 minutes getting started with Oracle sql statement
SELECT * | {[DISTINCT] column | expression [alias],...}
FROM table
SELECT * FROM departments t
SELECT department_id, location_id FROM departments
SELECT last_name, salary, salary + 300 FROM employees
SELECT last_name, salary, 12*salary+100 FROM employees
SELECT last_name, salary, 12 * (salary+100) FROM employees
SELECT last_name, job_id, salary, commission_pct FROM employees
SELECT last_name, 12*salary*commission_pct FROM employees
SELECT last_name AS name, commission_pct comm FROM employees
SELECT last_name "Name", salary*12 "Annual Salary" FROM employees
SELECT last_name | | job_id AS "Employees" FROM employees
SELECT last_name | |'is a'| | job_id
AS "Employee Details"
FROM employees
SELECT department_name | |
Q'[, it's assigned Manager Id:]'
| | manager_id |
AS "Department and Manager"
FROM departments
SELECT department_id
FROM employees
SELECT DISTINCT department_id
FROM employees
DESC [RIBE] tablename
DESC employees
SELECT * | {[DISTINCT] column | expression [alias],...}
FROM table
[WHERE condition (s)]
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen'
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100,101,201)
SELECT first_name
FROM employees
WHERE first_name LIKE'S%'
SELECT last_name
FROM employees
WHERE last_name LIKE'_ o%'
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > = 10000
AND job_id LIKE'% MAN%'
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > = 10000
OR job_id LIKE'% MAN%'
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG',' ST_CLERK', 'SA_REP')
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = & employee_num
SELECT last_name, department_id, salary*12
FROM employees
WHERE job_id ='& job_title'
SELECT employee_id, last_name, job_id,&column_name
FROM employees
WHERE & condition
ORDER BY & order_column
SELECT employee_id, last_name, job_id, & & column_name
FROM employees
ORDER BY & column_name
DEFINE employee_num = 200
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = & employee_num
UNDEFINE employee_num
SET VERIFY ON
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = & employee_num
LOWER LOWER ('SQL Course')
UPPER UPPER ('SQL Course')
INITCAP INITCAP ('SQL Course')
CONCAT CONCAT ('Hello',' World')
SUBSTR SUBSTR ('HelloWorld',1,5)
LENGTH LENGTH ('HelloWorld')
INSTR INSTR ('HelloWorld',' W')
LPAD | RPAD LPAD (salary,10,'*')
RPAD (salary, 10,'*')
TRIM TRIM ('H' FROM 'HelloWorld')
REPLACE REPLACE ('JACK and JUE','J','BL')
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins'
No rows selected
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER (last_name) = 'higgins'
SELECT employee_id, CONCAT (first_name, last_name) NAME
Job_id, LENGTH (last_name)
INSTR (last_name,'a') "Contains'asides?"
FROM employees
WHERE SUBSTR (job_id, 4) = 'REP'
ROUND (45.926, 2)
TRUNC (45.926, 2)
MOD (1600, 300)
SELECT ROUND (45.923), ROUND (45.923)
ROUND (45.923)
FROM DUAL
SELECT ROUND (45.923), ROUND (45.923)
ROUND (45.923)
FROM DUAL
SELECT last_name, (SYSDATE-hire_date) / 7 AS WEEKS
FROM employees
WHERE department_id = 90
MONTHS_BETWEEN
MONTHS_BETWEEN ('01mi SEPMI 95m 91m JANMI 94')
ADD_MONTHS ADD_MONTHS ('11MJANPUR 94MJ 6)
NEXT_DAY NEXT_DAY ('01, SEP, 95).
LAST_DAY LAST_DAY ('01Mutual FEBMei95')
ROUND
TRUNC
ROUND (SYSDATE,'MONTH')
ROUND (SYSDATE, 'YEAR')
TRUNC (SYSDATE, 'MONTH')
TRUNC (SYSDATE, 'YEAR')
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
SELECT last_name
TO_CHAR (hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees
SELECT TO_CHAR (salary,'$99999.000') SALARY
FROM employees
WHERE last_name = 'Ernst'
SELECT last_name
UPPER (CONCAT (SUBSTR (LAST_NAME, 1,8),'_ US'))
FROM employees
WHERE department_id = 60
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2,..., exprn)
NVL (commission_pct,0)
NVL (hire_date,'01-JAN-97')
NVL (job_id,'No Job Yet')
SELECT last_name, salary, NVL (commission_pct, 0)
(salary*12) + (salary*12*NVL (commission_pct, 0)) AN_SAL
FROM employees
SELECT last_name, salary, commission_pct
NVL2 (commission_pct
'SAL+COMM',' SAL') income
FROM employees WHERE department_id IN (50,80)
SELECT first_name, LENGTH (first_name) "expr1"
Last_name, LENGTH (last_name) "expr2"
NULLIF (LENGTH (first_name), LENGTH (last_name)) result
FROM employees
SELECT last_name
COALESCE (manager_id,commission_pct,-1) comm
FROM employees
ORDER BY commission_pct
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
SELECT last_name, job_id, salary
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees
DECODE (col | expression, search2, result1
[, search3, result2,...,]
[, default])
SELECT last_name, job_id, salary
DECODE (job_id, 'IT_PROG', 1.10*salary
'ST_CLERK', 1.15*salary
'SA_REP', 1.20*salary
Salary)
REVISED_SALARY
FROM employees
