AdventureWorks Database: Contacts designated as a manager

Last update on December 20 2024 11:57:24 (UTC/GMT +8 hours)

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.