w3resource
Run SQL Query

SQL Challenges-1: Most experienced manager to execute the schemes

SQL Challenges-1: Exercise-34 with Solution

From the following tables write a SQL query to find those experienced manager who execute the schemes. Return scheme code and scheme manager ID.

Input:

Table: managing_body

Structure:

FieldTypeNullKeyDefaultExtra
manager_idint(11)NOPRI
manager_namevarchar(255)YES
running_yearsint(11)YES

Data:

manager_idmanager_namerunning_years
51James5
52Cork3
53Paul4
54Adam3
55Hense4
56Peter2

Table: scheme

Structure:

FieldTypeNullKeyDefaultExtra
scheme_codeint(11)NOPRI
scheme_manager_idint(11)NOPRI

Data:

scheme_codescheme_manager_id
100151
100153
100154
100156
100251
100255
100351
100452

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, p.scheme_manager_id
FROM scheme p JOIN managing_body e
ON p.scheme_manager_id = e.manager_id
WHERE (scheme_code, e.running_years) IN
(SELECT scheme_code, MAX(running_years)
FROM scheme p JOIN managing_body e
ON p.scheme_manager_id = e.manager_id
GROUP BY scheme_code);

Sample Output:

scheme_code|scheme_manager_id|
-----------|-----------------|
       1001|               51|
       1002|               51|
       1003|               51|
       1004|               52|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Schemes executed by minimum number of employees.
Next: Sales Analysis.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Can a foreign key be NULL and/or duplicate?

First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an Foreign key is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.

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