AdventureWorks Database: Find marketing Assistants with more than 41 vacation hours
142. From the following table write a query in SQL to select employees who have the title of Marketing Assistant and more than 41 vacation hours.
Sample table: HumanResources.Employeebusinessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode| ----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+ 1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ | 2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ | 3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ | 4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ | 5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ | 6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ | 7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ | 8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ | 9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ | 10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ | -- more --
Sample Solution:
-- Selecting BusinessEntityID, LoginID, JobTitle, and VacationHours from the Employee table for Marketing Assistants with more than 41 vacation hours
SELECT
-- Selecting the BusinessEntityID column from the Employee table
BusinessEntityID,
-- Selecting the LoginID column from the Employee table
LoginID,
-- Selecting the JobTitle column from the Employee table
JobTitle,
-- Selecting the VacationHours column from the Employee table
VacationHours
-- Selecting data from the Employee table
FROM
HumanResources.Employee
-- Filtering records where JobTitle is 'Marketing Assistant' and VacationHours is greater than 41
WHERE
JobTitle = 'Marketing Assistant'
AND
VacationHours > 41 ;
Explanation:
- This SQL code selects information about employees who hold the job title 'Marketing Assistant' and have more than 41 vacation hours.
- The SELECT statement specifies the columns to be included in the result set.
- The WHERE clause filters records to include only those where the JobTitle is 'Marketing Assistant' and VacationHours are greater than 41.
- The result set will contain columns for BusinessEntityID, LoginID, JobTitle, and VacationHours for employees who meet the specified criteria.
Sample Output:
businessentityid|loginid |jobtitle |vacationhours| ----------------+----------------------+-------------------+-------------+ 17|adventure-works\kevin0|Marketing Assistant| 42| 19|adventure-works\mary2 |Marketing Assistant| 43|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find unit price, ID number, and modulus of division.
Next: Find all rows outside a specified range of rate.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics