SQL Exercise: Display the unique department with jobs

SQL employee Database: Exercise-10 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

10. From the following table, write a SQL query to find the unique department with jobs. Return department ID, Job name.

Sample table: employees

Pictorial Presentation:

SQL exercises on employee Database: Display the unique department with jobs

Sample Solution:

FROM employees ;

Sample Output:

 dep_id | job_name
   3001 | MANAGER
   2001 | ANALYST
   3001 | SALESMAN
   1001 | MANAGER
   1001 | PRESIDENT
   2001 | MANAGER
   2001 | CLERK
   1001 | CLERK
   3001 | CLERK
(9 rows)


The said query in SQL that selects distinct combinations of the dep_id and job_name columns from the 'employees' table. The "DISTINCT" keyword ensures that each combination of dep_id and job_name appears only once in the result set, even if there are multiple employees with the same job and department.

A query like this might be used to get all unique job titles and departments in the company based on the "employees" table.

Relational Algebra Expression:

Relational Algebra Expression: Display the unique department with jobs.

Relational Algebra Tree:

Relational Algebra Tree: Display the unique department with jobs.

Practice Online

Sample Database: employee

employee database structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: List employees id, salary, and commission.
Next SQL Exercise: List the employees not in department 2001.

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.