Practical 11 - Combining Conditions - CSU357P - Shoolini U

Practical 11: Combining Conditions

11.1. Example 1: Using AND Operator

SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE salary >= 50000.00 AND title = 'Programmer';

This statement will select the employeeid, firstname, lastname, title, and salary from the employee_info table where the salary is greater than or equal to 50000.00 AND the title is equal to 'Programmer'. Both of these conditions must be true in order for the rows to be returned in the query. If either is false, then it will not be displayed.

Although they are not required, you can use parentheses around your conditional expressions to make it easier to read:

SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE (salary >= 50000.00) AND (title = 'Programmer');

11.2. Example 2: Using OR Operator

SELECT firstname, lastname, title, salary
FROM employee_info
WHERE (title = 'Sales') OR (title = 'Programmer');

This statement will select the firstname, lastname, title, and salary from the employee_info table where the title is either equal to 'Sales' OR the title is equal to 'Programmer'.

11.3. Review Exercises & Answers

Use the tables items_ordered and customers for the exercises.

11.3.1. Exercise #1

Select the customerid, order_date, and item from the items_ordered table for all items unless they are 'Snow Shoes' or 'Ear Muffs'. Display the rows as long as they are not either of these two items.

SELECT customerid, order_date, item
FROM items_ordered
WHERE (item <> 'Snow shoes') AND (item <> 'Ear muffs');

Note: It is correct to use an AND here. If you were to use an OR, then either side of the OR will be true, and every row will be displayed. For example, when it encounters 'Ear muffs', it will evaluate to True since 'Ear muffs' are not equal to 'Snow shoes'.

11.3.2. Exercise #2

Select the item and price of all items that start with the letters 'S', 'P', or 'F'.

SELECT item, price
FROM items_ordered
WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%');