AdventureWorks Database: Find all rows outside a specified range of rate
143. From the following tables write a query in SQL to find all rows outside a specified range of rate between 27 and 30. Sort the result in ascending order on rate.
Sample table: HumanResources.vEmployee
businessentityid|title|firstname |middlename |lastname |suffix|jobtitle |phonenumber |phonenumbertype|emailaddress |emailpromotion|addressline1 |addressline2|city |stateprovincename|postalcode|countryregionname|additionalcontactinfo|
----------------+-----+-----------+----------------+-----------------+------+----------------------------------------+-------------------+---------------+--------------------------------+--------------+--------------------------+------------+-------------+-----------------+----------+-----------------+---------------------+
1| |Ken |J |Sánchez | |Chief Executive Officer |697-555-0142 |Cell |[email protected] | 0|4350 Minute Dr. | |Newport Hills|Washington |98006 |United States | |
2| |Terri |Lee |Duffy | |Vice President of Engineering |819-555-0175 |Work |[email protected] | 1|7559 Worth Ct. | |Renton |Washington |98055 |United States | |
3| |Roberto | |Tamburello | |Engineering Manager |212-555-0187 |Cell |[email protected] | 0|2137 Birchwood Dr | |Redmond |Washington |98052 |United States | |
4| |Rob | |Walters | |Senior Tool Designer |612-555-0100 |Cell |[email protected] | 0|5678 Lakeview Blvd. | |Minneapolis |Minnesota |55402 |United States | |
5|Ms. |Gail |A |Erickson | |Design Engineer |849-555-0139 |Cell |[email protected] | 0|9435 Breck Court | |Bellevue |Washington |98004 |United States | |
6|Mr. |Jossef |H |Goldberg | |Design Engineer |122-555-0189 |Work |[email protected] | 0|5670 Bel Air Dr. | |Renton |Washington |98055 |United States | |
7| |Dylan |A |Miller | |Research and Development Manager |181-555-0156 |Work |[email protected] | 2|7048 Laurel | |Kenmore |Washington |98028 |United States | |
8| |Diane |L |Margheim | |Research and Development Engineer |815-555-0138 |Cell |[email protected] | 0|475 Santa Maria | |Everett |Washington |98201 |United States | |
9| |Gigi |N |Matthew | |Research and Development Engineer |185-555-0186 |Cell |[email protected] | 0|7808 Brown St. | |Bellevue |Washington |98004 |United States | |
10| |Michael | |Raheem | |Research and Development Manager |330-555-2568 |Work |[email protected] | 2|1234 Seaside Way | |San Francisco|California |94109 |United States | |
-- more --
Sample table: HumanResources.EmployeePayHistory
businessentityid|ratechangedate |rate |payfrequency|modifieddate |
----------------+-----------------------+-------+------------+-----------------------+
1|2009-01-14 00:00:00.000| 125.5| 2|2014-06-30 00:00:00.000|
2|2008-01-31 00:00:00.000|63.4615| 2|2014-06-30 00:00:00.000|
3|2007-11-11 00:00:00.000|43.2692| 2|2014-06-30 00:00:00.000|
4|2007-12-05 00:00:00.000| 8.62| 2|2007-11-21 00:00:00.000|
4|2010-05-31 00:00:00.000| 23.72| 2|2010-05-16 00:00:00.000|
4|2011-12-15 00:00:00.000|29.8462| 2|2011-12-01 00:00:00.000|
5|2008-01-06 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
6|2008-01-24 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
7|2009-02-08 00:00:00.000|50.4808| 2|2014-06-30 00:00:00.000|
8|2008-12-29 00:00:00.000|40.8654| 2|2014-06-30 00:00:00.000|
-- more --
Sample Solution:
-- Selecting FirstName, LastName, and Rate columns from the vEmployee view and EmployeePayHistory table for employees whose rate falls outside the range of 27 to 30
SELECT
-- Selecting the FirstName column from the vEmployee view
e.FirstName,
-- Selecting the LastName column from the vEmployee view
e.LastName,
-- Selecting the Rate column from the EmployeePayHistory table
ep.Rate
-- Selecting data from the vEmployee view
FROM
HumanResources.vEmployee e
-- Joining the vEmployee view with the EmployeePayHistory table based on BusinessEntityID
JOIN
HumanResources.EmployeePayHistory ep
ON
e.BusinessEntityID = ep.BusinessEntityID
-- Filtering records where the Rate is not between 27 and 30
WHERE
ep.Rate NOT BETWEEN 27 AND 30
-- Ordering the result set by Rate
ORDER BY
ep.Rate;
Explanation:
- This SQL code retrieves information about employees' first names, last names, and pay rates from the vEmployee view and the EmployeePayHistory table.
- The SELECT statement specifies the columns to be included in the result set.
- The JOIN clause connects the vEmployee view with the EmployeePayHistory table based on the BusinessEntityID column.
- The WHERE clause filters records to include only those where the pay rate falls outside the range of 27 to 30.
- The ORDER BY clause sorts the result set by the pay rate in ascending order.
- The result set will contain columns for first name, last name, and pay rate for employees whose pay rate is not between 27 and 30.
Sample Output:
firstname |lastname |rate | -----------+-----------------+-------+ Marc |Ingle | 6.5| George |Li | 6.5| David |Johnson | 6.5| John |Frum | 6.5| Russell |King | 6.5| David |Lawrence | 6.5| Reed |Koch | 6.5| Benjamin |Martin | 6.75| John |Frum | 7| George |Li | 7.25| Marc |Ingle | 7.25| ...
Go to:
PREV : Find marketing Assistants with more than 41 vacation hours.
NEXT : Retrieve rows whose datetime values are between '20111212' and '20120105'.
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.