w3resource

SQL Challenges-1: Find the Team Size

SQL Challenges-1: Exercise-47 with Solution

From the following table write a SQL query to find the number of employees are working in the department of each employees. Return employee Id and number of employees are working in their department.

Input:

Table: emp_test_table

Structure:

FieldTypeNullKeyDefaultExtra
employee_idint(11)NOPRI
first_namevarchar(25)YES
department_idint(11)YES

Data:

employee_idfirst_namedepartment_id
100Steven90
101Neena90
102Lex90
103Alexander60
104Bruce60
105David60
106Valli60
107Diana60
108Nancy 100
109Daniel100
110John100

Sample Solution:

SQL Code(MySQL):

CREATE TABLE emp_test_table (
employee_id integer NOT NULL UNIQUE,
first_name varchar(25),
department_id integer);


insert into emp_test_table values(100,'Steven     ',90); 
insert into emp_test_table values(101,'Neena      ',90); 
insert into emp_test_table values(102,'Lex        ',90); 
insert into emp_test_table values(103,'Alexander  ',60); 
insert into emp_test_table values(104,'Bruce      ',60); 
insert into emp_test_table values(105,'David      ',60); 
insert into emp_test_table values(106,'Valli      ',60); 
insert into emp_test_table values(107,'Diana      ',60); 
insert into emp_test_table values(108,'Nancy      ',100); 
insert into emp_test_table values(109,'Daniel     ',100); 
insert into emp_test_table values(110,'John       ',100); 



SELECT em1.employee_id, COUNT(em1.department_id) AS employees_in_department
FROM emp_test_table em1
JOIN emp_test_table em2
ON em1.department_id=em2.department_id
GROUP BY em1.employee_id;

Sample Output:

employee_id|employees_in_department|
-----------|-----------------------|
        100|                      3|
        101|                      3|
        102|                      3|
        103|                      5|
        104|                      5|
        105|                      5|
        106|                      5|
        107|                      5|
        108|                      3|
        109|                      3|
        110|                      3|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Students and Examinations.
Next: Show running quantiry for each unit type of item.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

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