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

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

## SQL Challenges-1: Exercise-33 with Solution

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:

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

Data:

manager_idmanager_namerunning_years
51James5
52Cork3
53Paul4
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(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:

﻿

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