18.4 IS NULL, BETWEEN, and Practice

Now you can practice combining each of the techniques you've learned together

1. Write a SQL statement that shows each of the employees hired between 1/1/1997 and 12/31/2002 (inclusive).

   • BETWEEN

2. Write a SQL statement that will find employees who make $20,000 or more but no more than $80,000 who have a "Good" performance rating, as well as anyone who works in the Miami office. Display FirstName, LastName, Salary, Performance, and LocationCity. Order by Salary.

   • tricky parentheses

3. Write a SQL statement that will find all Managers who were hired after the year 2002, as well as anyone who works in the Chicago office. Display FirstName, LastName, HireDate, LocationCity, and Position Title. Order by HireDate in descending order.

   • more tricky parentheses

4. We need to clean our data and make sure every employee record is complete. Are there any employees who are missing data in the gender field? Write a SQL statement to display them.

   • IS NULL

5. Show the first and last name only of employees who received a “Good” performance rating and earn more than $50,000.

6. From the EMPLOYEE table, select the SSN, first and last names, and performance rating of all employees who work at location 1.

7. Select records for employees who have either “Emily” or “Frank” as their first name. Show the first and last name of these employees as well as their salary.

8. Select employees with the first name of Holly and received a “Good” performance rating, but in the same query select all employees who received an “Average” performance rating. Display SSN and first and last names in the results.

9. Create a query that shows only the last and first name, location city, and position title of employees who earn less than $50,000.

10. Select all of the employees who received either an “Average” or “Poor” performance rating and work in Chicago. Show first name, last name, and performance rating from the Employee table and location from the Location table.

11. Show all of the employees who are managers and have received a performance rating (field is not empty). Show the last name, salary, performance rating, location, and position title.

    • IS NOT NULL

12. Show the last name, salary, position title, and location city of all the Managers who earn more than $50,000 and work in Chicago or Miami.

13. Create a query that selects Trainees who earn less than $19,000 or more than $22,000. Show first and last name, city location, salary, and position title.