Where filtering and comparison operators for SQL Basics (v)

Use the WHERE clause to filter out rows that do not meet the criteria:

Considerations for where Filtering:

The WHERE clause follows the FROM clause.

A WHERE clause followed by a conditional expression

column names, expressions, constants

Comparison operators: =, <=, between, in, like, and NULL

– Logical operators: and, OR and not

Substitution variables

DEFINE and VERIFY commands

Literal value

The WHERE clause cannot be followed by a column alias

The syntax is as follows:

SELECT *| {[DISTINCT] column|expression [alias],...}

From table

[WHERE condition (s)];

1, for example, now find out the Department ID 90 in the Department of what employees

Select last_name,department_id from Employees where department_id = 90;

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/41/wKioL1i6eOzQWUrMAAA2drs-_Xs533.jpg-wh_500x0-wm_ 3-wmp_4-s_1848683360.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6eozqwurmaaa2drs-_xs533.jpg-wh_50 "/>

2, find the Department ID 100 personnel information, such as: Name, employee number, etc.

Select first_name,employee_id,job_id,department_id from Employees where department_id = 100;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8E/41/wKioL1i6fOOAmUmfAABnZCbfONg496.jpg-wh_500x0-wm_ 3-wmp_4-s_3531389217.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6fooamumfaabnzcbfong496.jpg-wh_50 "/>

3. When the condition is a character and the date to be enclosed in single quotation marks.

Character case sensitive, date format sensitive.

The default date format is DD-MON-RR

3.1 Find information such as employee Departmen ID last_name for Whalen

Select last_name,job_id,department_id from Employees where last_name = ' whalen ';

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8E/41/wKioL1i6fq_gPz8yAAA3_opazMs478.jpg-wh_500x0-wm_ 3-wmp_4-s_150120445.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6fq_gpz8yaaa3_opazms478.jpg-wh_50 "/>

3.2 Find employee first_name and department ID for the December 19, 07 entry date

Select First_name,department_id,hire_date from Employees where hire_date = ' 19-dec-07 ';

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8E/44/wKiom1i6f2-RlJV5AABB2E6dNlQ190.jpg-wh_500x0-wm_ 3-wmp_4-s_2372986583.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6f2-rljv5aabb2e6dnlq190.jpg-wh_50 "/>

4. Comparison operators

Operator	Meaning
=	Equals
>	Greater than
>=	Greater than or equal
<	Less than
<=	Less than or equal
<>	Not equal to
Between ... And ...	In.... And.... Between
In (set)	Among these
Like	Select similar values Selection criteria can contain characters or numbers % represents one or more characters _ Represents a character
Is NULL	When the time is empty

4.1 Find the name and department ID number of the employee whose salary is less than 2800

Select Last_name,department_id,salary from employees where salary < 2800 order by salary;

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8E/44/wKiom1i6gtDTmFzRAAB_rjkDHhs350.jpg-wh_500x0-wm_ 3-wmp_4-s_2368354086.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6gtdtmfzraab_rjkdhhs350.jpg-wh_50 "/>

4.2 Find the names and job_id of employees with wages greater than or equal to 3000, and in ascending order

Select Last_name,job_id,salary from Employees where salary>=3000 orader by salary;

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8E/41/wKioL1i6g7niUKjBAAC1qiBNNwg648.jpg-wh_500x0-wm_ 3-wmp_4-s_3768910783.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6g7niukjbaac1qibnnwg648.jpg-wh_50 "/>

4.3 Find the employee name with a salary greater than or equal to 10000 and enter it in the format XXX's salary is 10000

Select First_name| | Q ' [' s salary is] ' | | Salary as "salary is 10000 of EMP"

From Employees

where salary=10000;

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8E/44/wKiom1i6hNPxKpwHAABk6KRn6a4297.jpg-wh_500x0-wm_ 3-wmp_4-s_1820267966.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6hnpxkpwhaabk6krn6a4297.jpg-wh_50 "/>

4.4 Find the employee name and employee ID in the payroll between 8000-10000

Select Employee_id,first_name,salary from employees where salary between 8000 and 10000 order by salary;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8E/44/wKiom1i6hmziRueEAACxFGtI-Bc751.jpg-wh_500x0-wm_ 3-wmp_4-s_1545543279.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6hmzirueeaacxfgti-bc751.jpg-wh_50 "/>

4.5 Find employee names for salaries of 7000, 3100, 8100, 9000, 10000

Select First_name,salary from Employees where salary in (7000,3100,8000,9000,10000);

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

4.6 Find job_id with employee names beginning with S

Select first_name,job_id from employees where first_name like ' s% ';

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/8E/41/wKioL1i6jvDRGhIYAABngTBPwbE399.jpg-wh_500x0-wm_ 3-wmp_4-s_139812800.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6jvdrghiyaabngtbpwbe399.jpg-wh_50 "/>

4.7 Find job_id with s in Employee name

Select first_name,job_id from employees where first_name like '%s ';

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/8E/41/wKioL1i6j0rRL3QqAABKeqAgGdI656.jpg-wh_500x0-wm_ 3-wmp_4-s_3264137235.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6j0rrl3qqaabkeqaggdi656.jpg-wh_50 "/>

4.8 Find the first name the second letter is O employee information

Select first_name,job_id from employees where first_name like ' _o% ';

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8E/44/wKiom1i6j-igQ-TMAABgaC0feZU087.jpg-wh_500x0-wm_ 3-wmp_4-s_3276713281.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6j-igq-tmaabgac0fezu087.jpg-wh_50 "/>

4.9 Find the name of the employee whose third letter is E and a

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://s2.51cto.com/wyfs02/M00/8E/45/wKiom1i6yAjQ9elBAABJ9u5eXRw340.jpg-wh_500x0-wm_ 3-wmp_4-s_1068022052.jpg "title=" qq20170304213324.jpg "alt=" Wkiom1i6yajq9elbaabj9u5exrw340.jpg-wh_50 "/>

4.9.1 Find manager_id Empty name

Select first_name,manager_id from Employees where manager_id is null;

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

4.9.2 find the name of the employee who entered the job in 04

Select Last_name,hire_date from employees where hire_date like '%04 ';

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8E/42/wKioL1i6wjywrGLoAABkn0EPy-A049.jpg-wh_500x0-wm_ 3-wmp_4-s_3893755421.jpg "title=" qq20170304213324.jpg "alt=" Wkiol1i6wjywrgloaabkn0epy-a049.jpg-wh_50 "/>

5. Logical operators

Operator	Meaning
and	Logic and, two conditions are "true" then return True
OR	Logical OR, where one of the conditions is true returns True
Not	Logical No, returns TRUE if the condition is false

5.1. Find employee information with salary greater than 10000 and job_id containing man

Select First_name,employee_id,salary from Employees where salary>=1000 and job_id like '%man% ';

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/8E/44/wKiom1i6k1-QisbDAABf9fTjlLs288.jpg-wh_500x0-wm_ 3-wmp_4-s_4081346503.jpg "title=" qq20170304155930.jpg "alt=" Wkiom1i6k1-qisbdaabf9ftjlls288.jpg-wh_50 "/>

5.2. Find the names and employee_id of employees whose wages are greater than or equal to 10000 or job_id contain man

Select First_name,employee_id,job_id,salary from Employees where salary>=10000 or job_id like '%man% ';

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8E/41/wKioL1i6k7uScgtOAADeyssuVvQ884.jpg-wh_500x0-wm_ 3-wmp_4-s_2929468047.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6k7uscgtoaadeyssuvvq884.jpg-wh_50 "/>

5.3 Find job_id not in ' hr_emp ', ' St_man ', ' St_clerk ' This job ID inside the employee name and job_id

Select last_name,job_id from employees where job_id not in (' hr_emp ', ' St_man ', ' St_clerk ');

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/8E/41/wKioL1i6lVayVrpkAACZcMOEQHo448.jpg-wh_500x0-wm_ 3-wmp_4-s_1941215025.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6lvayvrpkaaczcmoeqho448.jpg-wh_50 "/>

5.4 Name and salary of employees not in the 5000-12000 range

SQL >select last_name,salary from employees where salary not between, and 12000 order by salary;

Last_Name SALARY

------------------------- ----------

Olson2100

Philtanker2200

Markle2200

Landry2400

Gee 2400

Vargas2500

Patel2500

Colmenares2500

Marlow2500

Sullivan 2500

Perkins 2500

OConnell 2600

Grant2600

Matos2600

Himuro2600

Mikkilineni2700

Seo 2700

Atkinson 2800

Geoni2800

Tobias2800

Jones2800

Rogers2900

Baida2900

Gates2900

Feeney3000

Cabrio3000

Walsh3100

Fleaur3100

Khoo3100

Davies3100

Stiles3200

Nayer3200

Taylor3200

McCain3200

Bissot3300

Mallin3300

Dellinger 3400

Rajs 3500

Dilly 3600

Ladwig3600

Chung3800

Everett 3900

Bell 4000

Bull 4100

Sarchand 4200

Lorentz 4200

Whalen4400

Pataballa 4800

Austin4800

Higgins 12008

Greenberg 12008

Hartstein 13000

Partners 13500

Russell 14000

De Haan 17000

Kochhar 17000

King 24000

Selected rows.

6. Priority (You can change the order of precedence using parentheses)

Priority level
1	Arithmetic operators
2	Connector characters
3	Comparison characters
4	Is (not) NULL, like, [not] in
5	[NOT] Between
6	Not equal to
7	Not
8	and
9	OR

1, find Department for Sa_rep or ad_pres Department of wages more than 15000 employees name, job_id, wages and other information

SELECT last_name, job_id, salary from employees WHERE job_id = ' Sa_rep ' OR job_id = ' ad_pres ' and salary > 15000;

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

2, find job_id for Sa_rep or ad_pres Department of wages more than 15000 employees name, job_id

SELECT last_name, job_id, salary from Employees WHERE (job_id = ' Sa_rep ' OR job_id = ' ad_pres ') and salary > 15000;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8E/41/wKioL1i6mGai14r7AABAbF9I1T0123.jpg-wh_500x0-wm_ 3-wmp_4-s_3007784440.jpg "title=" qq20170304155930.jpg "alt=" Wkiol1i6mgai14r7aababf9i1t0123.jpg-wh_50 "/>

3. The names and wages of employees whose salaries are not 5000-12000, 20 or 50 of the Department

Select Last_name,department_id,salary from employees where salary not between and 12000 and department_id=20 or Depar tment_id=50;

Or

Select Last_name,department_id,salary from employees where salary not between, and 12000 and department_id in (20,50);

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1903235

Where filtering and comparison operators for SQL Basics (v)