Question #1

Find all single female employees

SELECT NationalIDNumber,
       MaritalStatus,
       Gender
FROM   HumanResources.Employee
WHERE  MaritalStatus = 'S'
       AND Gender = 'F'

Question #2

Find all employees that have 40 to 80 hours of vacation time.

SELECT NationalIDNumber,
       MaritalStatus,
       Gender,
       VacationHours
  FROM HumanResources.Employee
 WHERE VacationHours >= 40
       AND VacationHours <= 80

Question #3

Find all employees that have 40 to 80 hours of vacation time or 40 to 80 hours of sick time,  and are male.

SELECT NationalIDNumber,
       MaritalStatus,
       Gender,
       VacationHours,
       SickLeaveHours
FROM   HumanResources.Employee
WHERE  (   (VacationHours >= 40 AND VacationHours <= 80)
        OR (SickLeaveHours >= 40 AND SickLeaveHours <= 80)
       )
       AND Gender = 'M'

Notice on this last example we used parenthesis to ensure the order of evaluating the clauses was correct.

7 responses to “Answer To Exercises: Query Results Using Boolean Logic”
  1. Klaus

    Dear Kris,

    In the solution to last line of question #1,

    it should be AND Gender = ‘F

    there is a ‘ missing.

    Thank you for your tutorial. It is great!

    1. Albert

      Hi Kris,

      According to the query in the answer, result shows vacation hours 80 as well while requirement is that it has to be >=40 and <=80.

      National Marital Gender Vacction Sick
      IdNumber Status Hours LeaveHours

      879342154 M M 16 64
      24756624 S M 40 40
      45615666 M M 40 40
      993310268 M M 40 40
      319472946 M M 41 40
      443968955 M M 41 40
      253022876 S M 42 41
      277173473 M M 43 41

      (127 row(s) affected)

      "Find all employees that have 40 to 80 hours of vacation time. Or 40 to 80 hours of sick time and are male."
      I tried the 'AND' which brought the required results.

      24756624 S M 40 40
      45615666 M M 40 40
      993310268 M M 40 40
      319472946 M M 41 40
      443968955 M M 41 40
      253022876 S M 42 41
      277173473 M M 43 41
      420023788 S M 43 41
      363996959 S M 44 42
      243322160 M M 44 42

      (89 row(s) affected)

      Please correct me if I'm wrong in understanding the question ?

      Best wishes
      Albert

      1. Hi,

        You have the right idea.

        The question is to find all records where the employee either has vacation between 40 & 80 hours OR sick leave between 40 & 80 hours…
        Then further restrict the result to only males.

        I reformatted the SQL so you can read the answer better.

    2. OOPS! Thanks for letting me know. It is fixed now!

  2. Ron Jansen van Galen

    Hi Kris,

    Great site you have and I’m busy learning SQL according to your method.

    As for the question 2 and 3 I think the following should be OK too:

    SELECT NationalIDNumber, MaritalStatus, Gender, VacationHours, SickLeaveHours
    FROM HumanResources.Employee
    WHERE (VacationHours BETWEEN 40 AND 80 OR SickleaveHours BETWEEN 40 AND 80)
    AND Gender = ‘M’

    I used BETWEEN instead of >= and <=

    1. Good idea! – It is easier to read too.

      That brings up a good point. Though there may be several ways to write SQL, some are more readable than others. :)

    2. Yes – BETWEEN is also good to use. In fact, with the condition is >= and <=, then I would say it is preferred, since it is easier to read.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.