﻿ SQL Challenges-1: Find the Team Size - 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:

﻿

## 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