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.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

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


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