Logical Operations in SQL Query in Python
In the selection condition, you can make more complex combinations using the OR and AND commands. They work the same way as their Python counterparts, the if construct. Let's look at some examples.
Example
Let's select users with a salary of 500 and an age of 23 years:
query = "SELECT * FROM users WHERE salary=500 AND age=23"
The result of the executed code:
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
Example
Let's select users with a salary of 500 OR age of 23 years:
query = "SELECT * FROM users WHERE salary=500 OR age=23"
The result of the executed code:
{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
Example
Let's select users with a salary from 450 to 900:
query = "SELECT * FROM users WHERE salary>450 AND salary<900"
The result of the executed code:
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
Example
Let's select users aged from 23 to 27 years inclusive:
query = "SELECT * FROM users WHERE age>=23 AND age<=27"
The result of the executed code:
{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
Example
Complex combinations of OR and AND commands can be grouped using parentheses to show precedence of conditions:
query = "SELECT * FROM users WHERE (age<20 AND age>27) OR (salary>300 AND salary<500)"
The result of the executed code:
{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
Practical tasks
Select users aged between 25 (not inclusive) and 28 (inclusive).
Select user user1.
Select users user1 and user2.
Select everyone except user user3.
Select all users aged 27 years or with a salary of 1000.
Select all users aged 27 years or with a salary not equal to 400.
Select all users aged between 23 years (inclusive) and 27 years (exclusive) or with a salary of 1000.
Select all users aged between 23 years and 27 years or with a salary between 400 and 1000.
