Exercice NiuKe 255 : Extraire les prénoms avec un rang impair dans la table employees

Description

Pour la table employees, affichez les prénoms (first_name) dont le rang, lorsqu'ils sont triés par ordre croissant de first_name, est impair. La table est définie comme suit :

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
);

Exemple de données :

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

Après tri, la séquence des prénoms est : Anneke, Bezalel, Georgi, Kyoichi. Les rangs impairs (1 et 3) corrrespondent à Anneke et Georgi. La sortie doit respecter l'ordre original de la table, soit :

Solution

WITH ranked_names AS (
    SELECT first_name, RANK() OVER (ORDER BY first_name) AS ranking
    FROM employees
),
odd_ranked_names AS (
    SELECT first_name
    FROM ranked_names
    WHERE ranking % 2 = 1
)
SELECT e.first_name AS first
FROM employees e
WHERE e.first_name IN (SELECT first_name FROM odd_ranked_names);

Exercice NiuKe 253 : Récupérer les informations des employés avec bonus

Description

À partir des tables employees, emp_bonus et salaries, générez les détails des employés ayant reçu un bonus. Le type de bonus (btype) détermine le pourcentage : 10% pour 1, 20% pour 2, et 30% pour les autres. Le salaire actuel est celui avec to_date = '9999-01-01'. Le bonus doit être arrondi à quatre décimales. Résultat attendu :

Solution

WITH active_salaries AS (
    SELECT emp_no, salary
    FROM salaries
    WHERE to_date = '9999-01-01'
),
joined_data AS (
    SELECT e.emp_no, eb.btype, as_sal.salary, e.first_name, e.last_name
    FROM employees e
    INNER JOIN emp_bonus eb ON e.emp_no = eb.emp_no
    INNER JOIN active_salaries as_sal ON e.emp_no = as_sal.emp_no
)
SELECT emp_no, first_name, last_name, btype, salary,
       CASE btype
           WHEN 1 THEN ROUND(salary * 0.1, 4)
           WHEN 2 THEN ROUND(salary * 0.2, 4)
           ELSE ROUND(salary * 0.3, 4)
       END AS bonus
FROM joined_data
ORDER BY emp_no ASC;

Exercice NiuKe 256 : Identifier les scores apparaissent au moins trois fois

Description

Dans la table grade, trouvez les valeurs de number qui apparaissent trois fois ou plus. Triez les résultats par ordre croissant. Exemple :

CREATE TABLE `grade` (
  `id` int(4) NOT NULL,
  `number` int(4) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO grade VALUES (1,111), (2,333), (3,111), (4,111), (5,333);

Résultat attendu :

Solution

SELECT number
FROM grade
GROUP BY number
HAVING COUNT(*) >= 3
ORDER BY number ASC;

Exercice LeetCode 1294 : Classer la météo par pays en novembre 2019

Description

À l'aide des tables Countries et Weather, déterminez le type de météo pour chaque pays en novembre 2019. La classification est : Cold si la moyenne de weather_state ≤ 15, Hot si ≥ 25, sinon Warm. Les tables sont définies ainsi :

CREATE TABLE Countries (
    country_id INT PRIMARY KEY,
    country_name VARCHAR(100)
);
CREATE TABLE Weather (
    country_id INT,
    weather_state VARCHAR(10),
    day DATE,
    PRIMARY KEY (country_id, day)
);

Exemple de données et sortie attendue :

INSERT INTO Countries VALUES (2,'USA'), (3,'Australia'), (5,'China'), (7,'Peru'), (8,'Morocco'), (9,'Spain');
INSERT INTO Weather VALUES
(2,15,'2019-11-01'), (2,12,'2019-10-28'), (3,-2,'2019-11-10'), (3,0,'2019-11-11'),
(3,3,'2019-11-12'), (5,16,'2019-11-07'), (5,18,'2019-11-09'), (5,21,'2019-11-23'),
(7,25,'2019-11-28'), (7,22,'2019-12-01'), (8,25,'2019-11-05'), (8,27,'2019-11-15'),
(8,31,'2019-11-25'), (9,7,'2019-10-23');

Résultat :

Solution

WITH november_avg AS (
    SELECT country_id, AVG(weather_state) AS avg_state
    FROM Weather
    WHERE YEAR(day) = 2019 AND MONTH(day) = 11
    GROUP BY country_id
)
SELECT c.country_name,
       CASE
           WHEN na.avg_state <= 15 THEN 'Cold'
           WHEN na.avg_state >= 25 THEN 'Hot'
           ELSE 'Warm'
       END AS weather_type
FROM november_avg na
INNER JOIN Countries c ON na.country_id = c.country_id;