Saturday, October 15, 2016

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

1 comment:

  1. I ran the code for the fourth question but it says identifier is too long

    ReplyDelete