w3resource

AdventureWorks Database: Contacts designated as a manager

SQL Query - AdventureWorks: Exercise-19 with Solution

19. From the following table write a query in SQL to find the contacts who are designated as a manager in various departments. Returns ContactTypeID, name. Sort the result set in descending order.

Sample table: Person.ContactType
contacttypeid|name                           |modifieddate           |
-------------+-------------------------------+-----------------------+
            1|Accounting Manager             |2008-04-30 00:00:00.000|
            2|Assistant Sales Agent          |2008-04-30 00:00:00.000|
            3|Assistant Sales Representative |2008-04-30 00:00:00.000|
            4|Coordinator Foreign Markets    |2008-04-30 00:00:00.000|
            5|Export Administrator           |2008-04-30 00:00:00.000|
            6|International Marketing Manager|2008-04-30 00:00:00.000|
            7|Marketing Assistant            |2008-04-30 00:00:00.000|
            8|Marketing Manager              |2008-04-30 00:00:00.000|
            9|Marketing Representative       |2008-04-30 00:00:00.000|
           10|Order Administrator            |2008-04-30 00:00:00.000|
           11|Owner                          |2008-04-30 00:00:00.000|
           12|Owner/Marketing Assistant      |2008-04-30 00:00:00.000|
		   -- more --

Click to view Full table

Sample Solution:

-- Selecting ContactTypeID and Name from the Person.ContactType table where the Name contains 'Manager'
SELECT ContactTypeID, Name
    -- Retrieving ContactTypeID and Name columns
    FROM Person.ContactType
    -- Filtering the rows where the Name contains 'Manager'
    WHERE Name LIKE '%Manager%'
    -- Ordering the results by Name in descending order
    ORDER BY Name DESC;

Explanation:

  • The SELECT statement retrieves the ContactTypeID and Name columns from the Person.ContactType table.
  • ContactTypeID, Name: Specifies the columns to be retrieved.
  • The FROM clause indicates the source table for the data, which is Person.ContactType.
  • The WHERE clause filters the rows where the Name column contains the substring 'Manager'.
  • Name LIKE '%Manager%': Filters the rows where the Name column contains the substring 'Manager'. '%' is a wildcard character that matches any sequence of characters.
  • The ORDER BY clause sorts the results by the Name column in descending order.
  • ORDER BY Name DESC: Orders the results in descending order based on the Name column.

Sample Output:

contacttypeid|name                           |
-------------+-------------------------------+
           19|Sales Manager                  |
           15|Purchasing Manager             |
           13|Product Manager                |
            8|Marketing Manager              |
            6|International Marketing Manager|
            1|Accounting Manager             |	

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Specify which groups to include in the results.
Next: List contacts who are Purchasing Manager.

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.