SQL Challenges-1: Schemes executed by minimum number of employees

Last update on December 20 2024 11:39:28 (UTC/GMT +8 hours)

From the following tables write a SQL query to find those schemes which executed by minimum number of employees. Return scheme code.

Input:

Table: managing_body

Structure:

Field	Type	Null	Key
manager_id	int(11)	NO	PRI
manager_name	varchar(255)	YES
running_years	int(11)	YES

Data:

manager_id	manager_name	running_years
51	James	5
52	Cork	3
53	Paul	4
54	Adam	3
55	Hense	4
56	Peter	2

Table: scheme

Structure:

Field	Type	Null	Key	Default	Extra
scheme_code	int(11)	NO	PRI
scheme_manager_id	int(11)	NO	PRI

Data:

scheme_code	scheme_manager_id
1001	51
1001	53
1001	54
1001	56
1002	51
1002	55
1003	51
1004	52

Sample Solution:

SQL Code(MySQL):

CREATE TABLE managing_body (manager_id int NOT NULL UNIQUE, manager_name varchar(255), running_years int);

INSERT INTO managing_body VALUES(51,'James',5);
INSERT INTO managing_body VALUES(52,'Cork',3);
INSERT INTO managing_body VALUES(53,'Paul',4);
INSERT INTO managing_body VALUES(54,'Adam',3);
INSERT INTO managing_body VALUES(55,'Hense',4);
INSERT INTO managing_body VALUES(56,'Peter',2);

CREATE TABLE scheme (scheme_code int NOT NULL , scheme_manager_id int NOT NULL, 
PRIMARY KEY(scheme_code,scheme_manager_id));
INSERT INTO scheme VALUES(1001,	51);
INSERT INTO scheme VALUES(1001,	53);
INSERT INTO scheme VALUES(1001,	54);
INSERT INTO scheme VALUES(1001,	56);
INSERT INTO scheme VALUES(1002,	51);
INSERT INTO scheme VALUES(1002,	55);
INSERT INTO scheme VALUES(1003,	51);
INSERT INTO scheme VALUES(1004,	52);

SELECT scheme_code
FROM scheme
GROUP BY scheme_code
HAVING COUNT(scheme_code) = 
(SELECT MIN(No_Of_Emp) FROM (SELECT COUNT(*) AS No_Of_Emp 
FROM scheme 
GROUP BY scheme_code)ss1);

Sample Output:

scheme_code|
-----------|
       1003|
       1004|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Average experience for each scheme.
Next: Most experienced manager to execute the schemes.