SQL base order BY ordering and substituting variables (vi)

Use the ORDER BY clause to sort:

–ASC: Ascending, default

–desc: Descending

The ORDER by clause at the end of the SELECT statement can use a column alias :

1. Find the number of wages between 7000-10000 and go to heavy, sort ascending

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

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/44/wKiom1i6m1qjTzYBAABaC62CM7o622.jpg-wh_500x0-wm_ 3-wmp_4-s_415438235.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6m1qjtzybaabac62cm7o622.jpg-wh_50 "/>

2. Find names, departments and ascending according to the date of entry

Select Last_name,department_id,hire_date from Employees order by Hire_date;

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/8E/44/wKiom1i6nyjxDGy5AACpAlZfDaQ312.jpg-wh_500x0-wm_ 3-wmp_4-s_965547573.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6nyjxdgy5aacpalzfdaq312.jpg-wh_50 "/>

Select Last_name,department_id,hire_date from Employees ORDER by hire_date Desc;

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8E/42/wKioL1i6n37zNnKoAACrvGHUuP0110.jpg-wh_500x0-wm_ 3-wmp_4-s_269419881.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6n37znnkoaacrvghuup0110.jpg-wh_50 "/>

Another use is to sort by the first few columns, and the following example uses the third column

Select Last_name,department_id,hire_date from Employees order by 3;

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8E/44/wKiom1i6oCTTJzVhAACI8Hd5DXk085.jpg-wh_500x0-wm_ 3-wmp_4-s_2250996515.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6octtjzvhaaci8hd5dxk085.jpg-wh_50 "/>

There is also a way to sort by multiple columns, and in different ways, there is a rule according to the first example of the strategy first, and then the second case. The following example is based on department_id Ascending, and then in descending order by the second example

Select Last_Name, department_id, salary from Employees order by department_id, salary desc;

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8E/44/wKiom1i6oReCyftZAACp4P0SXzc701.jpg-wh_500x0-wm_ 3-wmp_4-s_1458556000.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6orecyftzaacp4p0sxzc701.jpg-wh_50 "/>

3, the name of the Commission employees, wages and commissions, according to the descending order of wages?

Select last_name,salary,commission_pct from employees where commission_pct are NOT null ORDER BY 2 DESC, 3 ASC;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8E/42/wKioL1i6xFGBPKAvAACVMa7R_Wo330.jpg-wh_500x0-wm_ 3-wmp_4-s_764681742.jpg "title=" qq20170304213324.jpg "alt=" Wkiol1i6xfgbpkavaacvma7r_wo330.jpg-wh_50 "/>

4, with the substitution variable, given the name, check the employee ID, wages, such as the output salary of more than 10000 employee information

Select Last_name,employee_id,salary from Employees where salary > &slary;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8E/42/wKioL1i6xXyiPACVAADGUcyeOBE445.jpg-wh_500x0-wm_ 3-wmp_4-s_145614717.jpg "title=" qq20170304213324.jpg "alt=" Wkiol1i6xxyipacvaadgucyeobe445.jpg-wh_50 "/>

5. Specify the contents of the 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

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8E/45/wKiom1i6xoOCVssXAACuFyPNPN0690.jpg-wh_500x0-wm_ 3-wmp_4-s_867772374.jpg "title=" qq20170304213324.jpg "alt=" Wkiom1i6xoocvssxaacufypnpn0690.jpg-wh_50 "/>

Substitution variables

Use alternate variables:

– Use & To specify variables

– Each time you reuse a variable value, if you do not want to prompt the user, use the &&

With alternative variables, you can override the following:

–where conditions

–order BY clause

– Column Expression

– Table name

– Entire SELECT statement

– Character and date substitution variables are quoted as: ' First_Name '

1. Output Employee ID

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8E/42/wKioL1i6oo-yGfCQAACcY2x71jA826.jpg-wh_500x0-wm_ 3-wmp_4-s_188040581.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6oo-ygfcqaaccy2x71ja826.jpg-wh_50 "/>

2, enter the job_id for the specified content of the employee name, Department ID, annual salary and other information

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

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8E/44/wKiom1i6rjmRPzntAACS26B5-FQ681.jpg-wh_500x0-wm_ 3-wmp_4-s_3130512010.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6rjmrpzntaacs26b5-fq681.jpg-wh_50 "/>

3. Specify column names for querying and sorting

Select employee_id, last_name, job_id, &&column_name from Employees order by &column_name;

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8E/44/wKiom1i6r2TCEb3hAAEWrRFl2yQ693.jpg-wh_500x0-wm_ 3-wmp_4-s_3575968254.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6r2tceb3haaewrrfl2yq693.jpg-wh_50 "/>

Defining variables

1. Using the DEFINE command

---Use the Define command to create and assign values for 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

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/44/wKiom1i6sOfg671PAABpsSxzK5U378.jpg-wh_500x0-wm_ 3-wmp_4-s_573694046.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6sofg671paabpssxzk5u378.jpg-wh_50 "/>

2. Using the VERIFY command

Use the VERIFY command to display the substitution variables before and after the SQL developer

Replace the value of the replacement variable:

SET VERIFY on

SELECT employee_id,last_name,salary

From Employees

WHERE employee_id = &employee_num;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/42/wKioL1i6snHRg-pBAABk3NxKmko970.jpg-wh_500x0-wm_ 3-wmp_4-s_2524977772.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6snhrg-pbaabk3nxkmko970.jpg-wh_50 "/>

This article from "Record Bit Learning Life" blog, reproduced please contact the author!

SQL base order BY ordering and substituting variables (vi)