Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.