w3resource

SQL Exercise: Employee and sanction amount for their department

SQL JOINS: Exercise-27 with Solution

From the following tables write a SQL query to display the first and last names of each employee, as well as the department name and sanction amount.

Sample table:emp_department


Sample table: emp_details


Sample Solution:

SELECT emp_details.emp_fname AS "First Name", emp_lname AS "Last Name", 
    emp_department.dpt_name AS "Department", 
     dpt_allotment AS "Amount Allotted"
       FROM emp_details 
         INNER JOIN emp_department
           ON emp_details.emp_dept = emp_department.dpt_code;

Output of the Query:

First Name	Last Name	Department	Amount Allotted
Alan		Snappy		RD		55000
Maria		Foster		IT		65000
Michale		Robbin		IT		65000
Carlos		Snares		Finance		15000
Enric		Dosio		IT		65000
Jhon		Snares		Finance		15000
Joseph		Dosni		HR		240000
Zanifer		Emily		HR		240000
Kuleswar	Sitaraman	IT		65000
Henrey		Gabriel		HR		240000
Alex		Manuel		IT		65000
George		Mardy		RD		55000
Mario		Saule		Finance		15000

Explanation:

The said SQL query is selecting the employee's first name (emp_details.emp_fname) with an alias 'First Name', last name (emp_lname) with an alias 'Last Name', department name (emp_department.dpt_name) with an alias 'Department', and department allotment (dpt_allotment) with an alias 'Amount Allotted' by joining the emp_details table and emp_department table on the emp_dept column of the emp_details table and the dpt_code column of the emp_department table. The query is joining the two tables together and displaying the selected columns from both tables.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the first name and last name of each employee, along with the name and sanction amount for their department - Duration

Rows:

Query visualization of Display the first name and last name of each employee, along with the name and sanction amount for their department - Rows

Cost:

Query visualization of Display the first name and last name of each employee, along with the name and sanction amount for their department - Cost

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

Previous SQL Exercise: Display employees including their department.
Next SQL Exercise: Find employees and departments with a given budget.

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