Oracle Queries and functions

Objective

Scott is a normal user with a new system already in place, and the username is Scott,scott as an example.

At the beginning of learning Oracle, it is common to Scott users.

Use the Scott user to contact SQL statement Learning in Oracle

Table structure under the Scott user

1 structure of the query statement:

Select [Column Name] [*] FROM table name [where condition] [GROUP by group condition] [having filter] [ORDER by order]

2 Query 2.1 Basic query

syntax : SELECT * from table name [where condition]

2.1.1 Querying all employees

Select COUNT (*) from EMP;

Writing a constant directly is more efficient than writing *
Select COUNT (1) from EMP;

2.1.2 Query total number of employees

Select COUNT (1) from EMP;

2.2 Alias Query

Alias query: Using the AS keyword, you can omit
Aliases cannot have special characters or keywords, and double quotation marks if any

2.2.2 Using aliases to query names and wages

Select Ename Name, sal payroll from EMP;

Select Ename "Name", Sal payroll from EMP;

2.3 Removing duplicate data

Removing duplicate data distinct
Multi-column deduplication: Each column is the same to be counted as a repetition

2.3.1 Single-row removal of duplicates

SELECT distinct job from EMP;

2.3. More than 2 columns to remove duplicate

SELECT distinct Job,deptno from EMP;

Arithmetic in 2.4 queries

2.4.1

Virtual tables in dual:oracle, pseudo-tables, mainly used to complement grammatical structures

Select from dual;

Oracle Queries and functions