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.

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.