## 3.1 Comparing values: <, >, =, <>

One of the most common keywords that are used when writing SQL queries is the `WHERE`. It is with its help that we tell MySQL which table rows we want to filter (select).

After the keyword, `WHERE`you can write very complex and tricky conditions. So what operations can this condition contain?

Operator Description Example
< Less salary < 40000
> More salary > 100000
<= Less or equal age <= 25
>= More or equal age >= 18
= Equals occupation = 'Programmer'
<>, != Not equal Year(join_date) != 2015

Unlike the Java language, for strict equality, one equals character is used here, and not two .

If you want to check that the values ​​are not equal, then this can be done in two ways: `<>`either `!=`The first approach is used in languages ​​such as Pascal, the second is typical for languages ​​such as C ++ and Java.

For assignment in SQL, it is used `:=`as in Pascal, but the assignment itself is used very rarely.

Let's write a query where we display all the employees who were not hired in 2015.

``````
SELECT * FROM employee WHERE YEAR(join_date) != 2015
``````

And we get the following query result:

id name occupation salary age join_date
1 Ivanov Ivan Programmer 100,000 25 2012-06-30
2 Petrov Petr Programmer 80,000 23 2013-08-12
3 Ivanov Sergey Tester 40,000 thirty 2014-01-01
6 Vaska cat 1,000 3 2018-01-01

## 3.2 Logical operations AND, OR, NOT

You can also `WHERE`use the logical operations `AND`, `OR`and `NOT`. And they are written exactly as I described them here. None `&&`, `||`or`!`

Let's write a SQL query where we select employees who are over 20 years old and receive a salary of less than 50K.

``````
SELECT * FROM employee WHERE age > 20 AND salary < 50000
``````

And we get the following query result:

id name occupation salary age join_date
3 Ivanov Sergey Tester 40,000 thirty 2014-01-01
5 Kirienko Anastasia Office Manager 40,000 25 2015-10-10

Too easy. Let's write a query where we select employees who receive more than 100K and do not work as a director:

``````
SELECT * FROM employee WHERE salary >= 100000 AND NOT occupation = 'Director'
``````

Here we specifically `!=`used the operator instead of the symbol `NOT`.

And we get the following query result:

id name occupation salary age join_date
1 Ivanov Ivan Programmer 100,000 25 2012-06-30

## 3.3 BETWEEN: value range

Also in SQL there is a special operator `BETWEEN`with which you can check whether a variable lies between two values. This operator is used inside boolean expressions. The general form of such an operator is:

``````
variable BETWEEN bottom line AND top_bound
``````

In this case, unlike the Java language, both the lower and upper boundaries are included in the allowed interval.

Let's write an example where we select all employees with a salary from 40K to 100K. The request will look like:

``SELECT * FROM employee WHERE salary BETWEEN 40000 AND 100000``

And we get the following query result:

id name occupation salary age join_date
1 Ivanov Ivan Programmer 100,000 25 2012-06-30
2 Petrov Petr Programmer 80,000 23 2013-08-12
3 Ivanov Sergey Tester 40,000 thirty 2014-01-01
5 Kirienko Anastasia Office Manager 40,000 25 2015-10-10

The operator `BETWEEN`supports not only numeric types, but also dates and even string types . In the case of working with strings, the lexicographic order is used: aaba comes before aabb

## 3.4 IN: list of values

In addition, SQL has a special operator `IN`with which you can check whether a variable is contained in the specified list. This operator is used inside boolean expressions. The general form of such an operator is:

``variable IN (value 1 , value 2 , ... value N)``

Let's write a query in which we select employees who are 20, 25 or 30 years old. The request will look like:

``SELECT * FROM employee WHERE age IN (20, 25, 30)``

And we get the following query result:

id name occupation salary age join_date
1 Ivanov Ivan Programmer 100,000 25 2012-06-30
3 Ivanov Sergey Tester 40,000 thirty 2014-01-01
5 Kirienko Anastasia Office Manager 40,000 25 2015-10-10

And now let's write a query, where, on the contrary, we select employees whose age is `not` 20, 25 or 30. The query will look like:

``SELECT * FROM employee WHERE age NOT IN (20, 25, 30)``

And we get the following query result:

id name occupation salary age join_date
2 Petrov Petr Programmer 80,000 23 2013-08-12
4 Rabinovich Moisha Director 200,000 35 2015-05-12
6 Vaska cat 1,000 3 2018-01-01

Everything worked as expected. Beauty.

## 3.5 LIKE: string pattern

And finally, another important and very often used operator - `LIKE`. It is used when comparing strings. It can be used to set a string pattern. This operator is used inside boolean expressions. The general form of such an operator is:

``variable LIKE 'sample'``

Special characters can be used in the pattern: `%`and `_`.

• Underscore means any character, always 1 pc.
• Percent means any character and any number of times (including 0 times).

Let's write a query that will select all people whose last name begins with the letter "I". The request will look like:

``SELECT * FROM employee WHERE name LIKE 'I%'``

And we get the following query result:

id name occupation salary age join_date
1 Ivanov Ivan Programmer 100,000 25 2012-06-30
3 Ivanov Sergey Tester 40,000 thirty 2014-01-01

Now let's complicate the task - the name should contain "o" and the name should end with "a". The request will look like:

``SELECT * FROM employee WHERE name LIKE '%o%a'``

And we get the following query result:

id name occupation salary age join_date
4 Rabinovich Moisha Director 200,000 35 2015-05-12

Okay, let's make it a little more difficult. Let's filter out all employees whose age starts with the number "3". The request will look like:

``SELECT * FROM employee WHERE age LIKE '3%'``

And we get the following query result:

id name occupation salary age join_date
3 Ivanov Sergey Tester 40,000 thirty 2014-01-01
4 Rabinovich Moisha Director 200,000 35 2015-05-12
6 Vaska cat 1,000 3 2018-01-01

By the way, our age column is of type `int`, so `LIKE`it works not only with strings. What can not but rejoice.

1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked
1
Module 4. Working with databases, level 1, lesson 2
Locked