AdventureWorks Database: Identify Quality Assurance personnel who work evenings or nights

SQL Query - AdventureWorks: Exercise-157 with Solution

157. From the following table write a query in SQL to retrieve the names of Quality Assurance personnel working the evening or night shifts. Return first name, last name, shift.

businessentityid|title|firstname  |middlename      |lastname         |suffix|shift  |department                |groupname                           |startdate |enddate   |
----------------+-----+-----------+----------------+-----------------+------+-------+--------------------------+------------------------------------+----------+----------+
               1|     |Ken        |J               |Sánchez          |      |Day    |Executive                 |Executive General and Administration|2009-01-14|          |
               2|     |Terri      |Lee             |Duffy            |      |Day    |Engineering               |Research and Development            |2008-01-31|          |
               3|     |Roberto    |                |Tamburello       |      |Day    |Engineering               |Research and Development            |2007-11-11|          |
               4|     |Rob        |                |Walters          |      |Day    |Engineering               |Research and Development            |2007-12-05|2010-05-30|
               4|     |Rob        |                |Walters          |      |Day    |Tool Design               |Research and Development            |2010-05-31|          |
               5|Ms.  |Gail       |A               |Erickson         |      |Day    |Engineering               |Research and Development            |2008-01-06|          |
               6|Mr.  |Jossef     |H               |Goldberg         |      |Day    |Engineering               |Research and Development            |2008-01-24|          |
               7|     |Dylan      |A               |Miller           |      |Day    |Research and Development  |Research and Development            |2009-02-08|          |
               8|     |Diane      |L               |Margheim         |      |Day    |Research and Development  |Research and Development            |2008-12-29|          |
               9|     |Gigi       |N               |Matthew          |      |Day    |Research and Development  |Research and Development            |2009-01-16|          |
              10|     |Michael    |                |Raheem           |      |Day    |Research and Development  |Research and Development            |2009-05-03|          |
			-- more --

Click to view Full table

Sample Solution:

-- Selecting first name, last name, and shift from the vEmployeeDepartmentHistory view
SELECT 
    -- Selecting the FirstName column from the view
    FirstName, 
    -- Selecting the LastName column from the view
    LastName, 
    -- Selecting the Shift column from the view
    Shift   
-- From the vEmployeeDepartmentHistory view
FROM 
    HumanResources.vEmployeeDepartmentHistory  
-- Filtering records to include only those where the department is 'Quality Assurance' and the shift is either 'Evening' or 'Night'
WHERE 
    Department = 'Quality Assurance'  
    AND (Shift = 'Evening' OR Shift = 'Night');

Explanation:

• This SQL code retrieves the first name, last name, and shift of employees working in the Quality Assurance department during either the Evening or Night shifts.
• The SELECT statement specifies the columns to be included in the result set.
• The FROM clause indicates the view from which data is being retrieved, which is the HumanResources.vEmployeeDepartmentHistory view.
• The WHERE clause filters records to include only those where:
• The department is 'Quality Assurance'
• The shift is either 'Evening' or 'Night'.

Sample Output:

firstname|lastname      |shift  |
---------+--------------+-------+
Sootha   |Charncherngkha|Night  |
Andreas  |Berglund      |Evening| 

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find all Silver colored bicycles with a standard price under $400.
Next: List people with three-letter first names ending in 'an'.