Oracle Learning Chapter One simple query statement--04

1.5 How to use an arithmetic expression in an SQL statement

You can use an expression in an SQL statement. There are 4 types of operators that can be used in an expression: +,-,*,/

They represent addition, subtraction, multiplication, and division respectively.

Imagine a day when a social group and a union group visit your company, and your boss is asking you to add the cost of lunch and tea to your employees ' wages and print a payroll list in order to build the company's glorious image. So you probably forget it, the cost of about 500 yuan/month. Your query for use case 1-11 is then given a payroll list of the employees the boss asks for.

Example 1-11

Sql> SELECT empno,ename,sal,500+sal  2 from  EMP;

Example 1-11 results

Tip: If you find that the results of your query are paginated every 10 lines, you can use the statement

Sql>set PAGESIZE X--x represents the number of rows displayed, you can set a large value such as 2000

When you show the results shown in example 1-11 to the boss, the boss thinks they should use an annual salary and cannot list the original salary. So you made some changes to the query statement for example 1-11 and generated the SQL statement for example 1-12.

Example 1-12

Sql> SELECT empno,ename,500+sal*12  2 from  EMP;

Example 1-12 results

You'll be surprised to see the results shown in example 1-12. Obviously this is not the result of the boss's request. The reason for this is that the operator has precedence over the mischief.

The precedence of the operator is:

, First multiplication and then add and subtract;

, in the expression, the same precedence operator is evaluated from left to right;

If parentheses are used, the operation in parentheses takes precedence;

If there are multiple parentheses nested, the operation in the inner parentheses takes precedence.

So you have to rewrite the SQL statement for example 1-13 again as follows.

Example 1-13

Sql> SELECT Empno,ename, (500+sal) *12  2 from  EMP;

Example 1-13 results

There is no doubt that example 1-13 shows the result of the employee's salary list that the boss wants. Although you've got the right results, the list of displayed labels is really confusing, especially for your boss and the bosses of those groups, because they know nothing about Oracle databases.

1.6 How to use the alias of a column in an SQL statement

In order to let the bosses understand, you do not hesitate to change the query in example 1-13 again, this time, you use the alias of the column, issued an example 1-14 of the SQL statement

Example 1-14

Sql> select Empno as "Employee number",  2       ename name, (500+sal) *12  "annual Salary"  3 from  EMP;

Example 1-14 results

The result shown in example 1-14 above is exactly what your boss wants.

Oracle has a number of UNIX and C shadows that, when named for a column, use a number of abbreviations, such as salary (payroll)---sal. One of the best things to say in abbreviated terms is that it reduces the amount of input, but for non-computer professionals, these abbreviations are listed as days. Is there a way to meet the quirks of Oracle professionals who are accustomed to using abbreviations, and to make non-computer professionals see the results displayed at a glance? This problem is solved by listing an alias .

The method of giving an alias to a column is simple. You just need to put an as or a space between the name and the alias. Although you should use the AS keyword from a statement's legibility perspective, you can use this keyword to enter more than two characters, so you will find that many Oracle professionals rarely use the AS keyword.

Aliases are useful for working with columns represented by an expression. You may have noticed that aliases appear in uppercase when they are not enclosed in double quotes. If the alias contains special characters, or if you want the aliases to appear as they are, you'll use double quotes to enclose the aliases.

1.7 Join Operators

Your boss is satisfied with the salary list of the employee you did for him, but he iang the result is a complete English sentence, and the Expression's column label (that is, the table header) is: Employee ' s Salary. In this way, the heads of those groups are more likely to understand. To do this, you have to modify the query statement again, writing down the SQL statement in Example 1-15: (poor programmer, is the constant change!) ）

Example 1-15

sql> SELECT ename| | ' Annual salary is ' | | (500+sal) *12 "Employee ' s Salary"  2 from  EMP;

Example 1-15 results

When you submit this report to the boss, the boss's bright smile tells you, this is the report he expected, if you are using the Chinese system, you can also use example 1-16 with the Chinese query statement.

Example 1-16

sql> SELECT ename| | ' Annual salary for ' | | (500+sal) *12 "Employee's annual salary"  2 from  EMP;

Example 1-16 results

In this query statement, we used the text string (liteal) and the join operator.

A text string is a character, number, or expression contained in a SELECT clause, not an alias for any column or column. If the text characters are from the date type and the character type, you must enclose them in single quotation marks. Each string is output once in each row of output results.

The join operator is composed of two vertical bars (| | ) indicates that it connects one or more columns or strings together.

1.8 DISTINCT Operator

Imagine that you have just been hired by the company as an Oracle (DBA) administrator, and you want to know how many departments your company has, and you might issue a query statement for example 1-17.

Example 1-17

Sql> SELECT deptno  2 from  EMP;

Example 1-17 results

Oracle displays the entire record, which is the default display for Oracle. If your company is a large multinational corporation with countless employees, you can imagine the consequences of this query.

You can use distinct to help you get rid of duplicate rows. Consider the query for example 1-18 statements.

Example 1-18

sql> SELECT DISTINCT deptno  2 from  EMP;

Example 1-18 results

Obviously, the result of example 1-18 is more clear than the result of 1-17.

Note: When querying larger tables, avoid using distinct as much as possible, because the Oracle system accomplishes the DISTINCT function by sequencing, so it can reduce the efficiency of the Oracle system. You can usually do the same thing in different ways, for example, you can use the query statement in example 1-19 to get the information for the department you want.

Example 1-19

Sql> SELECT deptno  2 from  dept;

Example 1-19 results

Example 1-19 queries get all the department numbers as in the previous socialize, but there is no impact on the Oracle system because the department (DEPTNO) is unique in the Dept table itself.

The distinct can be used for multiple columns, where the result is displayed with only one row for each combination of columns. For example 1-20.

Example 1-20

sql> SELECT DISTINCT deptno,job  2 from  EMP;

Example 1-20 results

So far, we've discussed the most basic query statements. The format of the basic query statement is given below.

1.9 Format of basic query statements

In this chapter, Omen only learns a basic query statement. It contains only two clauses select and fro, in the following format

SELECT *| {[DISTINCT] list,...}

From table name:

which

List: column names | expressions [aliases]

Where the SELECT * from table name; Select all columns for the table specified from the table name;

SELECT DISTINCT list, ...

From table name;

Select the columns specified by the list for the table you specify from the table name, but do not display duplicate rows of data (records).

-----------------------------------↓↓↓ Below is a summary of this chapter ↓↓↓-----------------------------------------

1.10 What should be mastered.

Before you learn the next chapter, check to see if you have mastered the following: (seems to have forgotten almost, Khan!) ）

1. Which two clauses in a query statement are required?

2, what is the projection operation?

3, how to select a particular column in the query statement in the intense order.

4, writing query statement of the Convention.

5. What is a statement?

6. What is a clause?
7. The default display format for column headings.

8. The data default display format of the character and the rational date.

9. The default display format for numeric type data.

10. Arithmetic expressions in SQL statements and their precedence.

11. Aliases for columns in SQL statements and their use.

12. How to use text strings (literal) and join operators (| | ）？

13, how to use the DISTINCT keyword and the use of it may produce problems.

Oracle Learning Chapter One simple query statement--04