w3resource

AdventureWorks Database: Find all rows outside a specified range of rate

SQL Query - AdventureWorks: Exercise-143 with Solution

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


Click to view Full table

Sample table: HumanResources.EmployeePayHistory


Click to view Full table

Sample Solution:

SELECT e.FirstName, e.LastName, ep.Rate  
FROM HumanResources.vEmployee e   
JOIN HumanResources.EmployeePayHistory ep   
    ON e.BusinessEntityID = ep.BusinessEntityID  
WHERE ep.Rate NOT BETWEEN 27 AND 30  
ORDER BY ep.Rate;

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|
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find marketing Assistants with more than 41 vacation hours.
Next: Retrieve rows whose datetime values are between '20111212' and '20120105'.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between primary key and unique key:

Primary Key:

  • There can only be one primary key constraint in a table
  • In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
  • Primary Key is a unique key identifier of the record

Unique Key:

  • Can be more than one unique key in one table
  • Unique key can have NULL values
  • It can be a candidate key
  • Unique key can be NULL ; multiple rows can have NULL values and therefore may not be considered "unique"

Ref : https://bit.ly/2XNgBd0