06 Logical Comparisons and Precedence Rules

• ·         Inverts the value of the condition- NOT
• ·         Both conditions must be true for a record to be selected-AND
• ·         Rules that determine the order in which expressions are evaluated and calculated-precedence rules
• ·         Either condition can be true for a record to be selected-OR

1. Execute the two queries below. Why do these nearly identical statements produce two different results? Name the difference and explain why.

SELECT code, description

FROM d_themes

WHERE code >200 AND description IN('Tropical', 'Football', 'Carnival');

SELECT code, description

FROM d_themes

WHERE code >200 OR description IN('Tropical', 'Football', 'Carnival');

First uses AND, so either the sides must return true, but in second case – OR, more records may come, because either of the sides need to be true for row selection.

2. Display the last names of all Global Fast Foods employees who have “e” and “i” in their last names.

SELECT last_name

FROM f_staffs

WHERE  last_name LIKE '%e%' AND last_name LIKE '%i%';

LAST_NAME

Miller 

3. I need to know who the Global Fast Foods employees are that make more than $6.50/hour and their position is not order taker.

SELECT first_name  ||  ' '  || last_name  as "Full Name"

FROM f_staffs

WHERE  salary > 6.5 AND staff_type = 'Order Taker';

Full Name

Sue Doe

4. Using the employees table, write a query to display all employees whose last names start with “D” and have “a” and “e” anywhere in their last name.

SELECT first_name  ||  ' '  || last_name  as "Full Name"

FROM employees

WHERE  last_name LIKE 'D%' AND last_name LIKE '%a%' AND last_name LIKE '%e%';

Full Name

Curtis Davies

Lex De Haan

5. In which venues did DJs on Demand have events that were not in private homes?

/*did DJs on Demand have events , suggest I need not all venues except private home, but also the one where actually actually event occurred*/

SELECT DISTINCT d_venues.loc_type

FROM  d_events  JOIN d_venues   ON  d_events.venue_id = d_venues.id

WHERE d_venues.loc_type != 'Private Home';

LOC_TYPE

Hotel

6. Which list of operators is in the correct order from highest precedence to lowest precedence?

a. AND, NOT, OR

b. NOT, OR, AND

c. NOT, AND, OR  (this is right, not is the highest, and in bw, or at last)

For questions 7 and 8, write SQL statements that will produce the desired output.

7. Who am I?

I was hired by employer after May 1998 but before June of 1999. My salary is less than $8000 a year and I have an “en” in my last name. (Assume that salary in table is yearly)

SELECT first_name  ||  ' '  || last_name  as "Full Name"

FROM employees

WHERE hire_date > '31-May-1998' AND hire_date < '01-Jun-1999' AND salary < 8000 AND last_name like '%en%';

Full Name

Diana Lorentz

8. What's my email address?

Because I have been working for employer since the beginning of 1996, I make more than $9000 per month. Because I make so much money, I don't get a commission.

 (Assume that table has monthly salary)

Assume that beginning of year is Quarter 1

SELECT LOWER(email) ||  '@institutedomain.com'  as "Email Address"

FROM employees

WHERE  salary > 9000 AND (commission_pct = 0 OR commission_pct IS NULL)  AND hire_date >= '01-Jan-1996' AND hire_date <= '31-Mar-1996'  ;

Email Address

mhartste@institutedomain.com