SQL > SQL Commands > And Or

The keywords AND and OR are Boolean operators used to specify compound conditions in the WHERE clause.

Syntax

The syntax for using AND and OR in a compound condition is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{ [AND|OR] "simple condition"}+;

The { }+ means that the expression inside the bracket will occur one or more times. [AND|OR] means that either AND or OR can be used. In addition, we can use the parenthesis sign ( ) to indicate the order of the condition. The condition within the parenthesis sign gets executed first.

Example

We use the following table as our example:

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Dieg o 250  Jan-07-1999 
 San Francisco  300  Jan-08-1999 
 Boston  700  Jan-08-1999 

If we want to select all stores with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information, we key in,

SELECT Store_Name
FROM Store_Information
WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275);

Result:

Store_Name
Los Angeles
San Francisco

Exercises

For these exercises, assume we have a table called Users with the following data:

Table Users

 First_Name  Last_Name  Birth_Date  Gender  Join_Date 
 Sophie  Lee  Jan-05-1960  F  Apr-05-2015 
 Richard  Brown  Jan-07-1975  M  Apr-05-2015 
 Jamal  Santo  Oct-08-1983  M  Apr-09-2015 
 Casey  Healy  Sep-20-1969  M  Apr-09-2015 
 Jill  Wilkes  Nov-20-1979  F  Apr-15-2015 

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT First_Name AND Last_Name FROM Users;
b) SELECT First_Name, Last_Name FROM Users WHERE Join_Date > 'Apr-01-2015' AND Birth_Date < 'Jan-01-1980';
c) SELECT First_Name OR User_Name FROM Users;
d) SELECT * FROM Users WHERE Last_Name = 'Brown' AND Gender = 'F';

2. How many records will be returned by the following query?
SELECT * FROM Users WHERE Gender = 'M' AND Join_Date = 'Apr-09-2015';

3. How many records will be returned by the following query?
SELECT * FROM Users WHERE Gender = 'M' OR Join_Date = 'Apr-05-2015';

Next: SQL IN

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact