Guillaume Redoulès

Keywords to use with WHERE

  2. SQL
  3. Basics

Keywords to use with WHERE

#load the extension
%load_ext sql 
#connect to the database
%sql sqlite:///mydatabase.db 

'Connected: @mydatabase.db'

Assignment operator

The assignment operator is =.

%sql SELECT * FROM tutyfrutty WHERE color = "red"

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
2 Apple red 52
7 Cranberry red 308

Comparison operators

Comparison operation can be done in a SQL querry. They are the following :

  • Equality : =
  • Greater than : >
  • greater than or equal to : >=
  • less than : <
  • less than or equal to : <=
  • not equal to : <>, !=
  • not greater than : !>
  • not less than : !<
%sql SELECT * FROM tutyfrutty WHERE kcal = 47

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
1 Orange orange 47 
%sql SELECT * FROM tutyfrutty WHERE kcal > 47

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
0 Banana yellow 89
2 Apple red 52
7 Cranberry red 308 
%sql SELECT * FROM tutyfrutty WHERE kcal >= 47

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
0 Banana yellow 89
1 Orange orange 47
2 Apple red 52
7 Cranberry red 308 
%sql SELECT * FROM tutyfrutty WHERE kcal < 47

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
3 lemon yellow 15
4 lime green 30
5 plum purple 28 
%sql SELECT * FROM tutyfrutty WHERE kcal <= 47

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
1 Orange orange 47
3 lemon yellow 15
4 lime green 30
5 plum purple 28 
%sql SELECT * FROM tutyfrutty WHERE kcal <> 47

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
0 Banana yellow 89
2 Apple red 52
3 lemon yellow 15
4 lime green 30
5 plum purple 28
7 Cranberry red 308

Logical operators

Logical operators test a condition and return a boolean. The logicial operators in SQL are :

  • ALL : true if all the condtions are true
  • AND : true is both conditions are true
  • ANY : true if any one of the conditions are true
  • BETWEEN : true if the operand in withing a range of values
  • EXISTS : true if the subquery contains any rows
  • IN : true if the condition is present in a row
  • LIKE : true if a pattern is matched
  • NOT : True if the operand is false, false otherwise
  • OR : True is either condition is true
  • SOME : true is any of the conditions is true
%sql SELECT * FROM tutyfrutty WHERE color ="yellow" AND kcal < 100

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
0 Banana yellow 89
3 lemon yellow 15 
%sql SELECT * FROM tutyfrutty WHERE color ="yellow" OR kcal > 300

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
0 Banana yellow 89
3 lemon yellow 15
7 Cranberry red 308 
%sql SELECT * FROM tutyfrutty WHERE fruit LIKE 'l%'

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
3 lemon yellow 15
4 lime green 30 
%sql SELECT * FROM tutyfrutty WHERE NOT color = "yellow"

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
1 Orange orange 47
2 Apple red 52
4 lime green 30
5 plum purple 28
7 Cranberry red 308 
%sql SELECT * FROM tutyfrutty WHERE  kcal BETWEEN 40 AND 100

 * sqlite:///mydatabase.db
Done.
index fruit color kcal
0 Banana yellow 89
1 Orange orange 47
2 Apple red 52

Bitwise operators

Some bitwise operators exist in SQL. They will not be demonstrated here. They are the following :

  • AND : &
  • OR : |
  • XOR : ^
  • NOT : ~