Shulou(Shulou.com)06/01 Report--

Sort using the ORDER BY clause:

-ASC: ascending order, default

-DESC: descending order

The ORDER BY clause ends at the end of the SELECT statement, using column aliases:

1. Find the number of wages between 7000 and 10000 and remove the duplicates. Sort in ascending order.

Select distinct salary from employees where salary between 7000 and 10000 order by salary

2. Find the name, department and ascend the order according to the entry date

Select last_name,department_id,hire_date from employees order by hire_date

Select last_name,department_id,hire_date from employees order by hire_date desc

Another use is to sort by which column. The following example uses the third column.

Select last_name,department_id,hire_date from employees order by 3

Another use is to sort by multiple columns, and in different ways, there is a rule that comes first according to the strategy of the first example, and then the second example. The following example is based on the ascending order of department_id, and then the descending order of the second example.

Select last_name, department_id, salary from employees order by department_id, salary desc

3. List the name, salary and commission of the employee with commission, arranged in descending order of salary.

Select last_name,salary,commission_pct from employees where commission_pct is not null order by 2 desc, 3 asc

4. Use substitution variable, given name, check employee id, salary, for example, output employee information with salary greater than 10000.

Select last_name,employee_id,salary from employees where salary > & slary

5. Specify the contents of manager_id and sort by the specified column last_name

Select employee_id,last_name,salary,department_id from employees where manager_id= & mgr_num order by & order_col

Substitution variable

Use alternate variables:

-use & to specify variables

-each time you reuse the value of the variable, if you do not want to prompt the user, use &

Using alternative variables, you can replace the following:

-WHERE condition

-ORDER BY clause

-column expression

-Table name

-entire SELECT statement

-character and date substitution variables should be replaced by single quotation marks such as: 'first_name'

1. Output employee id

2. Enter the name, department ID, annual salary and other information of the employee whose job_id is specified.

Select last_name, department_id, salary*12 from employees where job_id ='& job_title'

3. Specify the column name for query and sorting

Select employee_id, last_name, job_id, & & column_name from employees order by & column_name

Define variable

1. Use the DEFINE command

-use the DEFINE command to create and assign the value of a variable.

-use the UNDEFINE command to delete a variable

DEFINE employee_num = 200

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = & employee_num

UNDEFINE employee_num

2. Use the VERIFY command

Use the VERIFY command to display alternative variables before and after SQL developers

Replace the value of the replacement variable:

SET VERIFY ON

SELECT employee_id,last_name,salary

FROM employees

WHERE employee_id = & employee_num

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.