w3resource

AdventureWorks Database: Replace string 'Supervisor' to 'Assistant' in jobtitle

SQL Query - AdventureWorks: Exercise-76 with Solution

76. From the following table write a query in SQL to create a new job title called "Production Assistant" in place of "Production Supervisor".

Sample table: HumanResources.Employee
businessentityid|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 --

Click to view Full table

Sample Solution:

-- Selecting the 'jobtitle' column and replacing a portion of the job title with 'Assistant',
-- starting from the 12th character and replacing 10 characters, and aliasing the result as 'New Jobtitle'
SELECT jobtitle, overlay(jobtitle placing 'Assistant' FROM  12 for 10) as "New Jobtitle"

-- From the HumanResources schema's Employee table as e
FROM humanresources.employee e 

-- Filtering the results to include only rows where a specific portion of the job title is 'Supervisor'
WHERE SUBSTR(jobtitle, 12, 10) = 'Supervisor';

Explanation:

  • The SQL query retrieves data from the Employee table within the HumanResources schema.
  • It selects the 'jobtitle' column.
  • The OVERLAY() function is used to replace a portion of the job title with 'Assistant'.
  • The replacement starts from the 12th character of the job title and replaces 10 characters.
  • The result of the replacement is aliased as 'New Jobtitle'.
  • The SUBSTR() function is used to extract a substring of the job title starting from the 12th character and spanning 10 characters.
  • The WHERE clause filters the results to include only rows where the extracted substring matches 'Supervisor'.

Sample Output:

jobtitle                    |New Jobtitle               |
----------------------------+---------------------------+
Production Supervisor - WC60|Production Assistant - WC60|
Production Supervisor - WC60|Production Assistant - WC60|
Production Supervisor - WC10|Production Assistant - WC10|
Production Supervisor - WC50|Production Assistant - WC50|
Production Supervisor - WC60|Production Assistant - WC60|
Production Supervisor - WC30|Production Assistant - WC30|
Production Supervisor - WC40|Production Assistant - WC40|
Production Supervisor - WC10|Production Assistant - WC10|
Production Supervisor - WC50|Production Assistant - WC50|
Production Supervisor - WC10|Production Assistant - WC10|
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find email addresses of employees and groups them by city.
Next: Retrieve employees whose job titles begin with 'Sales'.

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.