SQL statements for Oracle databases use the

SQL statements for Oracle databases use the

SCOTT e-r Model Diagram

DEPT Department Table EMP Employee table BONUS Bonus Table GRADE salary Grade

DEPTNO Department number EMPNO employee number ENAME employee name GRADE salary Grade

Dname Department name ename employee name Job Job position Losal minimum salary

LOC Department Position job Jobs SAL Payroll hisal most highly paid

MGR leader Number COMM Bonus

HireDate Hire Date

SAL Payroll

COMM Bonuses

DEPTNO Department Number

Grammatical structure

Select Column name/table name---Used to select data from the table

From table name

Where condition

Example select * from tab; List all Tables

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1.select statements

Select Column name from table name

SELECT * FROM table name

Example 1: List all employee names in EMP

Sql> select ename from emp;

Ename

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

Adams

Ename

----------

JAMES

FORD

MILLER

Rows selected.

Example 2: List all information for the EMP table

Sql> select * from EMP;

EMPNO ename JOB MGR hiredate SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7369 SMITH Clerk 7902 17-dec-80 800

20

7499 ALLEN salesman 7698 20-feb-81 1600 300

30

7521 WARD salesman 7698 22-feb-81 1250 500

30

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2.DISTINCT statement---Used to return a unique different value

Syntax: SELECT DISTINCT list name from table name

Example: List all employees who do not have duplicate names in the EMP table

Sql> SELECT distinct ename from EMP;

Ename

----------

ALLEN

JONES

FORD

CLARK

MILLER

SMITH

WARD

MARTIN

SCOTT

TURNER

Adams

Ename

----------

BLAKE

KING

JAMES

Rows selected.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

3.where statements

Syntax: SELECT list name from table name where column operator value

Operator

= equals

<> Not equal to

> Greater than

< less than

>= greater than or equal to

<= less than or equal to

Between.  and.. Within a range

Like search for a pattern

Example: List all the information that the employee named Scott in the EMP table

Sql> SELECT * from emp where ename= ' SCOTT ';

EMPNO ename JOB MGR hiredate SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7788 SCOTT ANALYST 7566 19-apr-87 3000

20

Characters use single quotation marks, values are not quoted

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The 4.and&or operator----is used to filter records based on more than one condition

Two or more conditions can be combined in the where sub-statement.

If both the first condition and the second condition are true, the AND operator displays a record

If the first condition and the second condition have only one set, the OR operator displays a record

Example 1:and--list all information in the EMP table with employee named King and salary 5000

Sql> SELECT * from emp where ename= ' KING ' and sal=5000;

EMPNO ename JOB MGR hiredate SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7839 KING President 17-nov-81 5000

10

Example 2:or---List all information for employees named King or Payroll 3000 in the EMP table

Sql> SELECT * from emp where ename= ' KING ' or sal=3000;

EMPNO ename JOB MGR hiredate SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7788 SCOTT ANALYST 7566 19-apr-87 3000

20

7839 KING President 17-nov-81 5000

10

7902 FORD ANALYST 7566 03-dec-81 3000

20

Example 3:and&or---Lists all information about employees named Scott or King in the EMP table with a salary of 3000

Sql> SELECT * from emp where (ename= ' SCOTT ' or ename= ' KING ') and sal=3000;

EMPNO ename JOB MGR hiredate SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7788 SCOTT ANALYST 7566 19-apr-87 3000

20

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

5.order by statement

The order BY statement is used to sort by the specified set of result sets, and the default L

Desc is descending

ASC to Ascending

Example 1: Sort by salary from high to low

Sql> Select Ename,sal from emp order by Sal Desc;

ename SAL

---------- ----------

KING 5000

FORD 3000

SCOTT 3000

JONES 2975

BLAKE 2850

CLARK 2450

ALLEN 1600

TURNER 1500

MILLER 1300

WARD 1250

MARTIN 1250

ename SAL

---------- ----------

ADAMS 1100

JAMES 950

SMITH 800

Rows selected.

Example 2: Sort by salary from low to high

Sql> Select Ename,sal from emp order by SAL ASC;

ename SAL

---------- ----------

SMITH 800

JAMES 950

ADAMS 1100

WARD 1250

MARTIN 1250

MILLER 1300

TURNER 1500

ALLEN 1600

CLARK 2450

BLAKE 2850

JONES 2975

ename SAL

---------- ----------

SCOTT 3000

FORD 3000

KING 5000

Rows selected.

This article is from the "Endmoon" blog, make sure to keep this source http://endmoon.blog.51cto.com/10533729/1683146

SQL statements for Oracle databases use the