Saturday, October 15, 2016

05 Comparison Operators


  • ·         This option identifies that the escape characters should be interpreted literally -ESCAPE
  • ·         Condition tests for null values- IS NULL
  • ·         Displays rows based on a range of values-BETWEEN
  • ·         Including the specified limits and the area between them; the numbers 1-10, inclusive-inclusive
  • ·         Selects rows that match a character pattern-LIKE
  • ·         Tests for values in a specified list of values-IN 


1.      Display the first name, last name, and salary of all Global Fast Foods staff whose salary is between $5.00 and $10.00 per hour.

SELECT first_name, last_name, salary
FROM f_staffs
WHERE salary BETWEEN 5 AND 10;

2. Display the location type and comments for all DJs on Demand venues that are Private Home.

SELECT loc_type, comments
FROM d_venues
WHERE loc_type = 'Private Home';

3. Using only the less than, equal, or greater than operators, rewrite the following query:
SELECT first_name, last_name
FROM f_staffs
WHERE salary BETWEEN 20.00 and 60.00;

SELECT first_name, last_name
FROM f_staffs
WHERE salary >= 20.00  and salary  <= 60.00;

4. Create a list of all the DJs on Demand CD titles that have “a” as the second letter in the title.

SELECT title
FROM d_cds
WHERE title LIKE  '_a%';

5. Who are the partners of DJs on Demand who do not get an authorized expense amount?

SELECT *
FROM d_partners
WHERE auth_expense_amt = 0 OR auth_expense_amt  IS NULL;

6. Select all the employees whose last names end with “s”. Change the heading of the column to read Possible Candidates.


SELECT first_name  ||  ' '  || last_name  as "Possible Candidates"
FROM employees
WHERE last_name LIKE '%s';
7. Which statement(s) are valid?
a. WHERE quantity <> NULL;
b. WHERE quantity = NULL;
c. WHERE quantity IS NULL;
d. WHERE quantity != NULL;
8. Write a SQL statement that lists the songs in the DJs on Demand inventory that are type code 77, 12, or 1.

SELECT title as "Song"
FROM d_songs

WHERE type_code IN (77, 12 , 1);

1 comment:

  1. I must say, I thought this was a pretty interesting read when it comes to this topic. Liked the material. . .
    ORM

    ReplyDelete