Four simple queries of Oracle Database

Time:2019-10-30

IV. simple query

The main feature of simple query is to display all data rows in a data table, and then use Select clause to control the required output columns.

4.1 basic grammar

Example: query data in EMP table (all data query)

SELECT * FROM emp;

After getting all the data, you can find that some columns will display null information. Null means no content, but null! = 0. Null means temporarily unknown content.

  • Simple query syntax:
Select [distinct] * | column name [as] [column alias], column name [as] [column alias],...
From table name [table alias];

In the whole simple query, there are two sentences:

Select clause: there are the following contents in this clause:

*: query all data columns;

Column name: indicates the specified column to display, and the alias can also be set for the column.

Distinct: indicates the display of de duplicated data.

From clause: defines the data table to be used, which can be understood as the data source.

Example: query the number, name and basic salary of each employee for browsing.

SELECT empno, ename, sal
FORM emp;

On the issue of execution order, the execution order of these two clauses:

Step 1: execute the from clause to determine the data source

Step 2: execute the select clause to determine the data columns to display

4.2 other query operations

Example: now you want to query the position information of all employees in the company

Select job from EMP; there will be duplicate positions.

Select distinct job from EMP;

Although using distinct can eliminate all the duplicate data, it is only limited to the situation that the contents of all columns are the same. If you add an empno field to the above query,

SELECT DISTINCT empno,job FROM emp;

You will find that all the data are displayed. Because the positions corresponding to each number are unique and different, distinct can only be used when all the contents are the same.

In addition to the above basic query, the select clause in simple query also supports four operations.

Example: it is required to query the number, name, annual basic wage and daily basic wage of all employees through the database as the payment standard of year-end bonus.

SELECT empno,ename,sal*12,sal/30 
FROM emp;

Annual salary = monthly salary * 12, daily salary = monthly salary / 30

The result is decimal. The processing of decimal part is left to the following single line function.

ExampleAt present, every employee of the company can receive 5000 year-end bonus at the end of the year. It is required to inquire the employee number, employee name and the annual basic salary after the increase (excluding Commission).

SELECT empno,ename,sal*12+5000
FROM emp;

Example: the company increases the subsidy of 200 yuan for employees every month. At this time, query the employee number, name and annual basic wage of each employee.

SELECT empno,ename,(sal+200)*12+5000
FROM emp;

In the result table (SAL + 200) * 12 + 5000 has no clear meaning. It can be called annual salary.

Select empno, ename, (SAL + 200) * 12 + 5000 annual salary
FROM emp;

You can also use an as to set the alias.

Select empno, ename, (SAL + 200) * 12 + 5000 as annual salary
FROM emp;

Whether to add as here does not make any difference to the final result, but we must remind the reader that if you set the alias, if it is certainly useless in the program, then it is not useful in the display, and you should remember, do not use Chinese, now it is just touching, you can use Chinese, when you learn the program in the future, you must avoid Chinese!

Example: set alias for query results

Select empno as employee No., name of ename as employee, (SAL + 200) * 12 + 5000 as annual salary
FROM emp;

There is a new question at this time. What kind of annual salary is given? So I hope that I can output a logo, and I will directly output a RMB logo below.

Select empno as employee No., name of ename as employee, (SAL + 200) * 12 + 5000 as annual salary,'¥ 'as currency
FROM emp;

The identity of this currency in the query result is actually a string constant, which is defined in the way of direct output.

Example: use “|” for connection display

In simple query, you can directly use “|” to connect string or column data.

First observe the connection:

SELECT empno || ename FROM emp;

The result shows that the employee number and name are linked together.

Select 'No.:' | empno | 'employee information is:' | ename | 'basic salary is:' | Sal employee information
FROM emp;

Employee information is the title name of the entire table set.

Note: this learning material is based on Li Xinghua’s Oracle development practice

Recommended Today

SQL exercise 20 – Modeling & Reporting

This blog is used to review and sort out the common topic modeling architecture, analysis oriented architecture and integration topic reports in data warehouse. I have uploaded these reports to GitHub. If you are interested, you can have a lookAddress:https://github.com/nino-laiqiu/TiTanI recorded a relatively complete development process in my hexo blog deployed on GitHub. You can […]