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


Click to view Full table

Sample Solution:

SELECT ContactTypeID, Name
    FROM Person.ContactType
    WHERE Name LIKE '%Manager%'
    ORDER BY Name DESC;

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.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook