Oracle Basic Queries

• SQL (structured query Language) Structured Query language.

A query is an operation that retrieves data from one or more tables or views and does not alter the data in the table.

Querying data is the core operation of the database and is the most frequently used operation.

• Select the basic syntax format for a statement is:

SELECT [DISTINCT] * | column name 1[as C1], column name 2[as C2] ..., column name N, group function (...)

from Table Name 1 alias 1, table name 2 alias 2 ...

[where condition]

[group by column name 1, column Name 2 ..., column name N]

[ having condition]

[Order by sort column Name 1 ASC | DESC, sort column Name 2 ASC | DESC ...]

• SQL Execution Plan

(8) SELECT (9) DISTINCT (one) <top num> <select list>
(1) from [left_table]
(3) <join_type> join <right_table>
(2) On <join_condition>
(4) WHERE <where_condition>
(5) GROUP by <group_by_list>
(6) With <cube | Rollup>
(7) Having (ten) ORDER by <order_by_list>

Cases:

1) Check out the number and name of all departments in the Department table

Selectd.id,d.name

from S_dept D;

2) Check out the number and name of all departments in the department table, location

Select d.id,d.name,d.region_id

from S_dept D;

3) Check out the employee number, name, salary and position in the employee table

Selecte.id,e.first_name,e.salary,e.title

Froms_emp e;

4) Find out all the information in the employee table

SELECT * from S_emp;

• Alias of table, alias of column

distinct keywords: go to weight, must be placed in the beginning of the field, will be used for all subsequent fields

itself is a double-loop query, when the amount of data is large, not applicable

       || indicates stitching

NVL () represents a null-value substitution function

Example:

1) Check all positions in the company from the S_emp table

Select distinct title from S_emp;

• Sort: Order By column name 1 ASC | DESC, Column Name 2 ASC |    Desc .... [Default Ascending]

Example:

2) Check out all employee information, in descending order of their salary

SELECT * FROM S_emp e order by e.salary;

3) Check out all employee information according to their salary in descending order, if the salary is the same, according to the date of entry in ascending order

SELECT * FROM S_emp E

Order by E.saaryldesc, E.START_DATEASC;

Oracle Basic Queries