IPT 2015–2016 L’objectif de ce TP est d’apprendre à manipuler des bases de donnée et à effectuer des requêtes SQL simples. On travaillera principalement avec une base de données 14-geo.db disponible sur le serveur. On pourra l’ouvrir en tapant dans un terminal : — departements est la même chose pour les départements, avec des colonnes en moins, et chef_lieu (l’identifiant de la commune qui est chef lieu du département). On fera attention au fait que chaque table a une clé exterieure dans l’autre. Les valeurs indiquées entre parenthèses sont des indications sur la réponse. sqliteman /home/users/etourniaire/Documents/TP/14-geo.db Si vous voulez refaire ce TP chez vous (ou si vous préférez), vous pouvez utiliser l’addon pour firefox SQLite Manager : — Lancer Firefox — Dans le menu Outils 1 , choisir SQLite Manager — S’il est absent, aller dans Add-ons, le chercher, l’installer, et revenir à l’étape précédente. Dans les deux cas, il est indispensable 2 de garder une trace de ce que vous faites dans cette séance. Vous devez donc ouvrir un fichier texte dans lequel vous recopierez les requêtes qui auront fonctionné, avec des commentaires. Par convention, il est indiqué de précéder vos commentaires de -- (syntaxe de commentaires dans le langage SQL). Note : ce TP évite les questions de création / modification de base de donnée. Mais en vrai, c’est pas sorcier. Ï Question 1 Donner le nombre total de communes et de départements. (96 et 36613) Ï Question 2 Choisir une commune (une que vous aimez bien !), trouver sa population et sa superficie, obtenir ce résultat en une seule requête SQL. Faire calculer sa densité par SQL. Ï Question 3 (440 200) Donner le nombre d’habitants dans la commune la plus peuplée. Ï Question 4 Quel est le numéro du département correspondant au Rhône ? Combien y a-t-il de communes dans le Rhône ? (301) Ï Question 5 Quelles sont les 5 communes les plus denses ? (le 20e arrondissement est en 4e position) 1 Une base de donnée géographique Une fois le logiciel lancé, vous avez deux tables dont la structure est assez explicite, néanmoins : — communes est une table avec des informations sur les communes de France, et pour chaque : — id est un identifiant unique. — postal est le code postal. — nom est le nom (en majuscules) — superficie, population, altitude sont les valeurs qu’on attend, dans des unités raisonnables. — dep_id donne l’identifiant du numéro de département dans lequel se trouve la commune. — x et y sont les longitudes et latitudes d’un point au pif dans la commune (en fait, calculé au pif comme la moyenne des coordonnées sur le contour de la commune 3 ). 1. Il faut parfois appuyer sur Alt pour faire apparaitre le menu. 2. Rappel : vous devrez comprendre vos notes dans environ un an pour les concours ! 3. plus facile que calculer le barycentre surfacique. . . TP n++ Ï Question 6 Donner le nom des trois communes les plus peuplées. Combien y a-t-il de communes affichant 0 habitant ? (Toulouse, Nice sont les deux premières ; 915) Ï Question 7 Combien y a-t-il de noms de communes différents ? (34024) Ï Question 8 Quel est le nom et l’altitude de la commune la plus élevée ? (2713) Ï Question 9 Obtenir les noms des chefs-lieux de chaque département, triés par ordre décroissant de population dans le chef-lieu. (Nantes est troisième) Ï Question 10 Donner les noms des départements des 10 communes les plus larges, en superficie. (Il n’y a que 7 départements) Ï Question 11 Donner les 5 départements ayant le plus de communes. Combien en ont-ils chacun ? (L’Aisne est deuxième, avec 816 communes) 1/2 IPT 2015–2016 Ï Question 12 Donner les trois département dont les chef-lieux sont les plus élevés. (la Lozère est deuxième) Et la signification des attributs : — Dans la première table, chaque station possède un numéro ns, un nom, un type (« mer » ou « montagne ») et une capacité capch. — Dans hotels, chaque hôtel est identifié par le numéro de la station où se trouve l’hôtel et le numéro de l’hôtel. Chaque hôtel a un nom, une adresse, un numéro de téléphone, une catégorie (nombre d’étoiles) et un nombre de chambres. — Dans rooms, chaque chambre est identifiée par le numéro de la station, de l’hôtel, et un numéro de chambre dans chaque hôtel. Chaque chambre a un type (S = « simple », D pour « avec douche », DWC pour « avec douche et WC » et SDB pour avec salle de bain) et un prix. — Dans guests, chaque client a un numéro, un nom, une adresse et un numéro de téléphone. — Dans bookings, chaque réservation indique la chambre (avec ns, nh, nch) réservée, le jour de l’année (entier entre 1 et 365) et le client. Ï Question 13 Déterminer l’altitude moyenne des communes de chaque département, triés par cette altitude. On pourra essayer de pondérer par la superficie. (La Savoie est deuxième ou troisième, selon le cas) Ï Question 14 Dans quels départements peut-on trouver une commune d’altitude supérieure à 1000 m ? (il y en a 30) Ï Question 15 Donner la liste des 5 départements les plus denséments peuplés. (Valde-Marne et Val-d’Oise sont les deux derniers) Ï Question 16 Comme la question 9, mais triés par ordre décroissante de population dans le département. (Le Rhône est en 4e position) Ï Question 17 Quelle est la ville qui contient la célèbre abbaye dont les coordonnées GPS sont (46.434031, 4.659617) ? (utilisez une application de cartographie 4 ) Ï Question 18 Donner les coordonnées du français moyen, c’est à dire le barycentre de tous les français. À quelle altitude se situe-t-il ? Est-ce plutôt au-dessus ou en-dessous du niveau du sol ? 2 Des hôtels Ouvrir le fichier 14-reservations.sqlite 5 dans votre logiciel de gestion de base de donnée. La base de donnée contient 5 tables dont voici le schéma relationnel : — — — — — resorts (ns,noms,types,capch) hotels (ns, nh, nomh, adrh, telh, cath, nbch) rooms (ns, nh, nch, typch, prix) guests (nc1, nomc1, adrc1, telc1) bookings (ns, nh, nch, jour, nc1) TP n++ Des indications sur les réponses sont fournies entre parenthèses. Ï Question 19 Lister les hôtels se trouvant dans une station balnéaire, triés par nombre d’étoile décroissants (37). Lister les chambres d’hôtels à deux étoiles dans une station de montagne avec un prix inférieur ou égal à 50 euros (153). Donner la liste des noms des stations au bord de la mer ayant des hôtels 4 étoiles (3). Ï Question 20 Donner le nombre de clients ayant réservé une chambre avec douche dans un hôtel d’une station balnéaire (entre 500 et 600). Donner les noms des clients dont l’adresse est celle d’un hôtel (2). Donner le nombre de clients ayant réservé à la mer et à la montagne (entre 700 et 800). Ï Question 21 Donner la liste des hôtels avec leur nom, adresse, catégorie, et le nombre de réservations dans l’année, triés par nombre de réservations décroissant (le troisième a 900 réservations). Quel hôtel n’a aucune réservation ? Ï Question 22 Il y a des informations redondantes dans cette base de donnée. Lesquelles ? Vérifier la cohérence à l’aide d’une requête. Ï Question 23 Donner la liste des noms de clients ayant réservé plusieurs chambres, le même jour, dans le même hôtel (8). Donner la liste des hôtels 4 étoiles n’ayant que des chambres avec salle de bain (11). 4. Ou vos connaissance de l’histoire du Moyen Âge classique. . . 5. Les fichiers SQLite se terminent le plus souvent par .sqlite ou .db. 2/2
