w3resource

SQL Challenges-1: Average experience for each scheme

SQL Challenges-1: Exercise-32 with Solution

From the following tables write a SQL query to display those managers who have average experience for each scheme.

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-1:

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 s.scheme_code  , 
ROUND(SUM(m.running_years) * 1.0/NULLIF(COUNT(DISTINCT m.manager_id), 0) ,2) AS 'Average year of experience'
FROM scheme s JOIN managing_body m 
ON m.manager_id  = s.scheme_manager_id
GROUP BY s.scheme_code;

Sample Output:

scheme_code|Average year of experience|
-----------|--------------------------|
       1001|                      3.50|
       1002|                      4.50|
       1003|                      5.00|
       1004|                      3.00|

OR

Sample Solution-2:

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 distinct scheme_code,
ROUND(y / n,2) AS 'Average year of experience'
FROM
(SELECT scheme_code, SUM(running_years) AS y, COUNT(*) AS n 
FROM scheme s 
INNER JOIN managing_body m ON s.scheme_manager_id = m.manager_id 
GROUP BY scheme_code) t;

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Students achieved 100 percent for the first year of each examination of every subject.
Next: Schemes executed by minimum number of employees.



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