Questions type examen

(world.sql)

1. Quelle langue est parlée dans chaque pays (SANS INNER JOIN)?

SELECT Co.Name,Cl.Language FROM Country AS Co, CountryLanguage AS Cl
WHERE Co.Code = Cl.CountryCode;

2. Quelle langue est parlée chez les suedois?

SELECT Cl.Language FROM Country AS Co, CountryLanguage AS Cl
WHERE Co.Code = Cl.CountryCode
AND Co.Name='Sweden';

3. Quelle langue est parlée dans chaque pays (AVEC INNER JOIN) [nom pays, langue]?

SELECT Co.Name,Cl.Language FROM
Country AS Co INNER JOIN CountryLanguage AS Cl
ON ( Co.Code = Cl.CountryCode );

4. Le pays qui a le plus grand nombre de villes

SELECT Count(Ci.ID) AS NombreVilles, Co.Name FROM
Country AS Co INNER JOIN City as Ci
ON (Co.Code = Ci.CountryCode)
GROUP BY Co.Name
ORDER BY NombreVilles DESC
LIMIT 1;

5. Liste des pays qui ont 1 à n villes et dont le nombre d’habitant est supérieur à 7 millions

SELECT Co.Name, Co.Population FROM
Country AS Co INNER JOIN City as Ci
ON (Co.Code = Ci.CountryCode)
WHERE Ci.Population>=7000000
GROUP BY Co.Name DESC
HAVING Count(Ci.ID)>=2;

6. Liste des pays qui n’ont pas de langues atitrée

SELECT Co.Name,Cl.Language FROM
Country AS Co LEFT JOIN CountryLanguage AS Cl
ON (Co.Code = Cl.CountryCode)
WHERE Cl.Language IS NULL;

7. La liste des pays dépendants et indépendants formatés en une colonne

SELECT
IFNULL(
CONCAT(Name,' indépendant ',IndepYear),
CONCAT(Name,' pas indépendant ')
) AS 'Indépendance?' FROM Country LIMIT 10;

8. Lister les pays de l’amerique du sud qui ont une surface supérieure au paraguay

SELECT Co1.Name AS 'Pays de comparaison',Co2.Name AS 'Pays comparé',CO2.Population AS 'Population',Co2.SurfaceArea AS 'Surface'
FROM Country Co1 INNER JOIN Country Co2
ON (Co2.Continent='South America')
WHERE Co1.Name='Paraguay'
AND Co2.SurfaceArea>=Co1.SurfaceArea;

9. Lister tout les pays qui ont une population et une surface supérieur ou egale au népal par ordre decroissant (region, population, surface)

SELECT Co1.Name AS 'Pays de comparaison',Co2.Name AS 'Pays comparé',Co2.SurfaceArea AS 'Surface',Co2.Population AS 'Population'
FROM Country Co1 INNER JOIN Country Co2
WHERE Co1.Name='Nepal'
AND Co2.SurfaceArea>=Co1.SurfaceArea
AND Co2.Population>=Co1.Population
ORDER BY Co2.SurfaceArea DESC;

10. A partir de la table personnes, ajouter la colonne domaine (tinyint) et la colonne salaire (float).

Affecter à Pierre, le domaine 42, salaire 1500.22

Paul le domaine 42, salaire 966.22

Jacques domaine 41, salaire 2500.22

Marc domaine 40, salaire 122.02

Medhi domaine 41, salaire 2000

La direction souhaite augmenter de 40% les salaires (brut). (brut vers net 30%)

La société souhaite visualiser la simulation en voyant le salaire brut, le montant de l’augmentation, le salaire net et le total.

 CREATE TABLE personnes (
	id mediumint AUTO_INCREMENT PRIMARY KEY,
	nom varchar(10)
);
ALTER TABLE personnes ADD domaine tinyint, ADD salaire float;
INSERT INTO personnes(nom,domaine,salaire) VALUES
('Pierre',42,1500.22),
('Paul',42,966.22),
('Jacques',41,2500.22),
('Marc',40,122.02),
('Medhi',41,2000);

SELECT
nom,
salaire,
(salaire*1.4) AS BrutAug,
(salaire*0.4) AS MontantAug,
((salaire*1.4)/1.3) AS NETAug,
SUM((salaire*0.4) AS Cout
FROM personnes
GROUP BY nom WITH ROLLUPG

11. La direction souhaiterais augmenter les salaires inférieurs ou égal à 1500, de 20% et ceux qui sont supérieurs à 1500 et inférieur ou égal a 2000 de 10%, pas d’augmentation pour les autres.

SELECT nom,salaire,
CASE
	WHEN salaire<=1500 THEN TRUNCATE(salaire*1.2,2)
	WHEN salaire>1500 OR salaire<=2000 THEN TRUNCATE(salaire*1.1,2)
	ELSE TRUNCATE(salaire,2)
END as 'salaire aug'
FROM personnes ;

12. Combien de pays dont le nom commence par 'S' suivit par au moins 3 lettres, d'un 'T', suivi de n caracteres y a t'il?

SELECT Co.Name FROM Country AS Co
WHERE Co.Name LIKE 'S___t%';

13. Lister la moyenne d'esperence de vie de chaque continent arrondi a l'entier

SELECT Co.Continent,ROUND(AVG(Co.LifeExpectancy)) AS 'Moyenne d'esperance de vie'
FROM Country AS Co
GROUP BY Co.Continent;

14. Trouver dans le manuel le chapitre parlant de DATE FORMAT

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

15. Quel sera le jour et l'heure dans 500jours et 42minutes

SELECT CONCAT("On sera un ",DATE_FORMAT(DATE_ADD(NOW(), INTERVAL '500 00:42' DAY_MINUTE),'%W %H:%i:%s')) AS 'Jour et Heure dans 500j et 42min';

16. Lister les continents avec la population totale pour chacun d'eux

SELECT Co.Continent, SUM(Population) AS 'Pop Totale'
FROM Country Co
GROUP BY Co.Continent;

17. Lister les 5 pays qui ont eu le plus de gouvernement par ordre decroissant.

SELECT GovernmentForm,Count(GovernmentForm) AS Nb
FROM Country
GROUP BY GovernmentForm
ORDER BY Nb DESC;

18. Lister les continents et la moyenne de la surface de chacun d'eux

SELECT Co.Continent, AVG(SurfaceArea) AS 'Surface Totale'
FROM Country Co
GROUP BY Co.Continent;

19. Afficher le/les pays ayant l'esperance de vie la plus petite et étant inférieur a 40ans pour chaque continent

SELECT Co.Name,ROUND(AVG(Co.LifeExpectancy)) AS Esperance
FROM Country AS Co
GROUP BY Co.Name
HAVING Esperance<40
ORDER BY Esperance;