{"id":19913,"date":"2024-02-01T05:22:11","date_gmt":"2024-02-01T04:22:11","guid":{"rendered":"https:\/\/complex-systems-ai.com\/?page_id=19913"},"modified":"2024-02-13T07:18:46","modified_gmt":"2024-02-13T06:18:46","slug":"exercices-sql-requetes","status":"publish","type":"page","link":"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/","title":{"rendered":"17 Corrected exercises SQL queries"},"content":{"rendered":"<div data-elementor-type=\"wp-page\" data-elementor-id=\"19913\" class=\"elementor elementor-19913\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-22190cd elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"22190cd\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-dbfe6b2\" data-id=\"dbfe6b2\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-51af85d elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"51af85d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Software analysis<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-aef01c9\" data-id=\"aef01c9\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-417a081 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"417a081\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/complex-systems-ai.com\/en\/\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Home page<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-cb0b978\" data-id=\"cb0b978\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-eb7ca08 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"eb7ca08\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/fr.wikipedia.org\/wiki\/UML_(informatique)\" target=\"_blank\" rel=\"noopener\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Wiki<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-b89cd96 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"b89cd96\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-69075c6\" data-id=\"69075c6\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-f194ab1 elementor-widget elementor-widget-heading\" data-id=\"f194ab1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercices-corriges-SQL-requetes\" >Corrected SQL query exercises<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-flashback-sur-modele-entite-association\" >Flashback exercise on entity-association model<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-preliminaire-pour-comprendre-le-SQL\" >Preliminary exercise to understand SQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-1\" >Exercise 1<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-2\" >Exercise 2<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-3\" >Exercise 3<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-4\" >Exercise 4<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#BDD-Voiturestxt\" >BDD Cars.txt<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-1-2\" >Exercise 1<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-2-2\" >Exercise 2<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-3-2\" >Exercise 3<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-4-2\" >Exercise 4<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-5\" >Exercise 5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-6\" >Exercise 6<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-7\" >Exercise 7<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-8\" >Exercise 8<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-9\" >Exercise 9<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-10\" >Exercise 10<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#BDD-Vinstxt\" >BDD Vins.txt<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-1-3\" >Exercise 1<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-2-3\" >Exercise 2<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-exercises-sql-queries-beginner\/#Exercice-3-3\" >Exercise 3<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercices-corriges-SQL-requetes\"><\/span>Corrected SQL query exercises<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-e74ecd4 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"e74ecd4\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-8cd51fb\" data-id=\"8cd51fb\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-439ea76 elementor-widget elementor-widget-text-editor\" data-id=\"439ea76\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>These corrected SQL query exercises are for beginners to advanced users.<\/p><p><img decoding=\"async\" class=\"aligncenter wp-image-11096 size-full\" src=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2020\/09\/cropped-Capture.png\" alt=\"SQL queries\" width=\"97\" height=\"97\" title=\"\"><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-fe88518 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"fe88518\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-9a87ec5\" data-id=\"9a87ec5\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-c224277 elementor-widget elementor-widget-heading\" data-id=\"c224277\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-flashback-sur-modele-entite-association\"><\/span>Flashback exercise on entity-association model<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5e30768 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5e30768\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-19eb9b3\" data-id=\"19eb9b3\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-7bcc801 elementor-widget elementor-widget-text-editor\" data-id=\"7bcc801\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>A real estate agency wants to manage its real estate portfolio. It wants to store all rental accommodation in its base.<\/p><p>To do this, it assigns an identifier to each accommodation, its address, its surface area and the rent. A home is located in a neighborhood which is assigned a number and a label. Each accommodation corresponds to a type of accommodation with fixed charges associated with it. In addition, the agency also stocks the tenants of its park. These are identified by a number, are characterized by their surnames, first names, date of birth and telephone number. Several tenants may be associated with a dwelling. On the other hand, an individual can only be referenced in one accommodation.<\/p><p>You will propose the entity-association model of these specifications<\/p><p>And you will deduce the logical model by identifying the primary and foreign keys.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-8ab18e3 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"8ab18e3\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-ed6c8ea\" data-id=\"ed6c8ea\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-3d4a086 elementor-widget elementor-widget-toggle\" data-id=\"3d4a086\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-6421\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-6421\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-6421\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-6421\"><p><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone wp-image-19932 size-large\" src=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd6-1024x576.png\" alt=\"association entity\" width=\"1024\" height=\"576\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd6-1024x576.png 1024w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd6-300x169.png 300w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd6-768x432.png 768w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd6-18x10.png 18w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd6.png 1366w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/p><p><img decoding=\"async\" class=\"alignnone wp-image-19933 size-large\" src=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd7-1024x576.png\" alt=\"association entity\" width=\"1024\" height=\"576\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd7-1024x576.png 1024w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd7-300x169.png 300w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd7-768x432.png 768w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd7-18x10.png 18w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd7.png 1366w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-88762af elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"88762af\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-d3cb937\" data-id=\"d3cb937\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-72f1135 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"72f1135\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-1ef55ee elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1ef55ee\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-35b5030\" data-id=\"35b5030\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-081833a elementor-widget elementor-widget-heading\" data-id=\"081833a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-preliminaire-pour-comprendre-le-SQL\"><\/span>Preliminary exercise to understand SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-da213af elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"da213af\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-92af07c\" data-id=\"92af07c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-5a6c987 elementor-widget elementor-widget-text-editor\" data-id=\"5a6c987\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>A small business created a relational database to store information about its employees and the departments to which they belong. The database contains the following tables (primary keys are underlined):<\/p><p>EMPLOYEE(<u>employee_id<\/u>, last name, first name, profession, date_hiring, salary, commission, id_department) contains the list of employees, defined by an internal system identifier, their last name (unique), their first name, their profession (executive, manager, engineer, salesperson, technician and intern), their date of hire, their salary (positive integer, an intern may not be paid), their commission (an employee may not have a commission) and the identifier of the department to which the employee belongs.<\/p><p>DEPARTMENT(<u>department_id<\/u>, name, director, city) contains the list of departments defined by a unique identifier, their name (sales, production, development and human resources), the identifier of the employee director of the department and the city, location of the department.<\/p><p>Here is the database schema in graphical form:<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-19934 size-full\" src=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd8.png\" alt=\"association entity\" width=\"360\" height=\"162\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd8.png 360w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd8-300x135.png 300w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd8-18x8.png 18w\" sizes=\"(max-width: 360px) 100vw, 360px\" \/><\/p><p>The database contains the following data:<\/p><p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-19935 size-full\" src=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd9.png\" alt=\"association entity\" width=\"668\" height=\"347\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd9.png 668w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd9-300x156.png 300w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/bdd9-18x9.png 18w\" sizes=\"(max-width: 668px) 100vw, 668px\" \/><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-f422d85 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"f422d85\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-2b92a96\" data-id=\"2b92a96\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-5883c64 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"5883c64\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-fb7042d elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"fb7042d\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-503fa8c\" data-id=\"503fa8c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-cd1ff90 elementor-widget elementor-widget-heading\" data-id=\"cd1ff90\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-1\"><\/span>Exercise 1<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-add4974 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"add4974\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-ebef530\" data-id=\"ebef530\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-1cd1a8a elementor-widget elementor-widget-text-editor\" data-id=\"1cd1a8a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>For each of the following queries, give its meaning in French (be careful not to paraphrase the SQL code) and indicate the result.<\/p><p>\u00a0<\/p><p>1) SELECT DEPARTMENT.department_id, DEPARTMENT.name<\/p><p>FROM DEPARTMENT<\/p><p>WHERE DEPARTMENT.Id_departement NOT IN (SELECT EMPLOYE.Id_departement FROM EMPLOYE);<\/p><p>2) SELECT E2.name AS \u201cDirector Name\u201d, COUNT(E1.name) AS \u201cNumber of Employees\u201d<\/p><p>FROM EMPLOYEE E1, EMPLOYEE E2, DEPARTMENT D<\/p><p>WHERE E1.Id_departement = D.Id_departement<\/p><p>AND E2.Id_employe = D.Director<\/p><p>GROUP BY E2.name<\/p><p>ORDER BY 1;<\/p><p>3) SELECT name, salary FROM EMPLOYEE<\/p><p>WHERE salary &gt; ALL (SELECT salary<\/p><p>FROM EMPLOYEE<\/p><p>WHERE profession = &#039;Engineer&#039;);<\/p><p>4) SELECT E1.name AS \u201cEmployee Name\u201d, E1.hire_date, E2.name AS \u201cDirector Name\u201d, E2.hire_dateFROM EMPLOYEE E1, EMPLOYEE E2, DEPARTMENT DWHERE E2.Id_employe = D.DirectorAND E1.Id_departement = D.Id_departmentAND E1.Date_hiring &lt; E2.Date_hiring;<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-be5d8fb elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"be5d8fb\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-1c7031b\" data-id=\"1c7031b\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-98d9f92 elementor-widget elementor-widget-toggle\" data-id=\"98d9f92\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1601\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1601\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1601\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1601\"><p>1) Which departments have no employees?<\/p><p>No rows selected<\/p><p>2) What is the number of employees per director?<\/p><p>Name Director Number of Employees<\/p><p>\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013<\/p><p>Jackson 3<\/p><p>Lavergne 1<\/p><p>McDonalds 4<\/p><p>Valiant 2<\/p><p>3) What is the salary and name of employees earning more than all engineers?<\/p><p>Name Salary<\/p><p>\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014- \u2014\u2014\u2014\u2014\u2013<\/p><p>Furnon 6000<\/p><p>Babin 7000<\/p><p>McDonalds 10000<\/p><p>4) What is the name and date of hire of employees hired before their manager; also give the name and date of hiring of their director.<\/p><p>Name Employee Date_hired Name Director Date_hired<\/p><p>\u2014\u2014\u2014\u2014\u2014\u2014\u2014- \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014- \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p><p>Sutton 01-04-1989 McDonald 06-05-2001<\/p><p>Furnon 06-11-1989 McDonald 05-06-2001<\/p><p>Babin 08-08-2000 McDonald 06-05-2001<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-433b546 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"433b546\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-77a291f\" data-id=\"77a291f\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-dc76e40 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"dc76e40\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-4ecafcb elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"4ecafcb\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-b00ed46\" data-id=\"b00ed46\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-c6870ea elementor-widget elementor-widget-heading\" data-id=\"c6870ea\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-2\"><\/span>Exercise 2<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-f183bfc elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"f183bfc\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-6eaa589\" data-id=\"6eaa589\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-18ef6c6 elementor-widget elementor-widget-text-editor\" data-id=\"18ef6c6\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>For each query, indicate whether (Yes or No) it gives the requested result (several queries can give the right result)<\/p><p>6) Give the salary and name of employees earning more than the lowest salary of engineers:.<\/p><p>6a SELECT DISTINCT E1.name, E1.salaryFROM EMPLOYEE E1, EMPLOYEE E2WHERE E2.Profession = &#039;Engineer&#039;AND E1.salary &gt; MIN(E2.salary);<\/p><p>6b SELECT name, salary FROM EMPLOYEWHERE salary &gt; ALL (SELECT MIN(salary)FROM EMPLOYEWHERE profession = &#039;Engineer&#039;);<\/p><p>6c SELECT name, salary FROM EMPLOYEWHERE salary &lt; ANY (SELECT MIN(salary)FROM EMPLOYE<\/p><p>WHERE profession = &#039;Engineer&#039;);<\/p><ul><li>Give the names of employees with the same profession and the same director as &#039;Furnon&#039;:<\/li><\/ul><p>7a SELECT name, professionFROM EMPLOYEEWHERE name &lt;&gt; &#039;Furnon&#039;AND Id_departement IN (SELECT D2.Id_departementFROM EMPLOYEE E, DEPARTMENT D1, DEPARTMENT D2WHERE E.name = &#039;Furnon&#039;AND D1.Id_departement = E.Id_departementAND D1.Directeur = D2.Directeur );<\/p><p>7b SELECT E.nomFROM EMPLOYEE E, DEPARTMENT DWHERE E.name &lt;&gt; &#039;Furnon&#039; AND E.Id_departement = D.Id_departementAND (profession, director) = (SELECT profession, D1.directorFROM EMPLOYEE E1, DEPARTMENT D1, DEPARTMENT D2WHERE E1.name = &#039;Furnon&#039;AND E1.Id_departement = D1.Id_departementAND D1.Directeur = D2.Directeur);<\/p><p>8) Give the names of employees of the &#039;Commercial&#039; department hired on the same day as an employee of the &#039;Production&#039; department:<\/p><p>8a SELECT DISTINCT E1.name FROM EMPLOYEE E1, DEPARTMENT D1, EMPLOYEE E2, DEPARTMENT D2WHERE E1.Id_departement = D1.Id_departementAND E2.Id_departement = D2. Id_departement AND D1.name = &#039;Commercial&#039;AND D2.name = &#039;Production&#039;AND E1.Date_hire = E2.Date_hire;\u00a0<\/p><p>\u00a08b SELECT E.nameFROM EMPLOYEE E, DEPARTMENT DWHERE E.Id_departement = D.Id_departmentAND D.name = &#039;Commercial&#039;AND E.Date_hire IN (SELECT E1.Date_hireFROM EMPLOYEE E1, DEPARTMENT D1WHERE E1. Id_departement = D1. Id_departementAND D1.name = &#039;Production&#039;);<\/p><p>8c SELECT E.nameFROM EMPLOYEE E, DEPARTMENT DWHERE E.Id_departement = D.Id_departementAND D.name = &#039;Commercial&#039;AND E.Date_hire EXISTS (SELECT E1.Date_hireFROM EMPLOYEE E1, DEPARTMENT D1WHERE E1. Id_departement = D1. Id_departementAND D1.name = &#039;Production&#039;);<\/p><p>9) Give the names of employees with the maximum salary in each department:<\/p><p>9a SELECT nameFROM EMPLOYEWHERE (Id_department, salary) IN (SELECT Id_department, MAX(salary)FROM EMPLOYEGROUP BY Id_department);\u00a0<\/p><p>\u00a09b SELECT nameFROM EMPLOYEE EWHERE salary = (SELECT MAX(salary)FROM EMPLOYEE E1WHERE E1. Id_departement = E. Id_departement);<\/p><p>9c SELECT E1.name, E1.department_id, MAX(E1.salary)<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM EMPLOYEE E1, EMPLOYEE E2<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE E1. Id_department = E2. Id_department<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY E1.name, E1.id_department;<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-f69e3c3 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"f69e3c3\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-64df36c\" data-id=\"64df36c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-8164a81 elementor-widget elementor-widget-toggle\" data-id=\"8164a81\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1351\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1351\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1351\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1351\"><p>6a NO<\/p><p>6b YES<\/p><p>6c NO<\/p><p>7a NO<\/p><p>7b YES<\/p><p>8a YES<\/p><p>\u00a08b O<span style=\"font-size: 1.125rem;\">UI<\/span><\/p><p>8c NO<\/p><p>9a YES<\/p><p>9b YES<\/p><p>9c NO<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-1666108 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1666108\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-9a764ee\" data-id=\"9a764ee\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-49b29e4 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"49b29e4\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-e128cf9 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"e128cf9\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-0eca291\" data-id=\"0eca291\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-1b89a19 elementor-widget elementor-widget-heading\" data-id=\"1b89a19\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-3\"><\/span>Exercise 3<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-e51a95b elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"e51a95b\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-bdbf5af\" data-id=\"bdbf5af\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-0b55bdd elementor-widget elementor-widget-text-editor\" data-id=\"0b55bdd\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>11) Please give the SQL commands to display the list of all departments with the last name, first name, salary and identifier of the employees assigned to them. Please note: a department may not have any employee(s).<\/p><p>12) Please give the SQL commands to display the list of jobs with the lowest average salary; also give their average salary.<\/p><p>13) Please give the SQL commands to display the employees of the &#039;Production&#039; department whose salary is less than 5000 $ and the employees of the &#039;Commercial&#039; department whose salary exceeds the average salary.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-4cbc428 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"4cbc428\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-2fdb42c\" data-id=\"2fdb42c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-ebc93c4 elementor-widget elementor-widget-toggle\" data-id=\"ebc93c4\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-2471\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2471\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-2471\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2471\"><p>11)<\/p><p>SELECT D.last name, E.employee_id, E.last name, E.first name, E.salary<\/p><p>FROM EMPLOYEE E, DEPARTMENT D<\/p><p>WHERE D.Id_departement = E.Id_departement(+)<\/p><p>ORDER BY D.surname, E.surname, E.firstname;<\/p><p>12)\u00a0<\/p><p>\u00a0SELECT profession, AVG(salary) FROM EMPLOYEGROUP BY professionHAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM EMPLOYE<\/p><p>GROUP BY profession);<\/p><p>13)\u00a0 \u00a0\u00a0<\/p><p>SELECT E1.EmployeeId, E1.Name, E1.Salary<\/p><p>FROM EMPLOYEE E1, DEPARTMENT D1<\/p><p>WHERE D1.Id_departement = E1.Id_departement<\/p><p>AND D1.Name = &#039;production&#039;<\/p><p>AND E1.Salary &lt; 5000<\/p><p>UNION<\/p><p>SELECT E2.EmployeeId, E2.Name, E2.Salary<\/p><p>FROM EMPLOYEE E2, DEPARTMENT D2<\/p><p>WHERE D2.Id_departement = E2.Id_departement<\/p><p>AND D2.Name = &#039;commercial&#039;<\/p><p>AND E2.Salary &gt; (SELECT AVG(E3.Salary) FROM EMPLOYEE E3)<\/p><p>ORDER BY 1, 2;<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-3fd276d elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"3fd276d\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-3294e64\" data-id=\"3294e64\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-83aeaac elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"83aeaac\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-7deca38 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"7deca38\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-bf91edf\" data-id=\"bf91edf\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-7d77449 elementor-widget elementor-widget-heading\" data-id=\"7d77449\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-4\"><\/span>Exercise 4<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-015d06a elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"015d06a\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-e3b2579\" data-id=\"e3b2579\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-4f5d5db elementor-widget elementor-widget-text-editor\" data-id=\"4f5d5db\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>You will give the SQL commands for creating the database tables, including all of the stated constraints that can be specified in the table creation command. Note that deleting a department must result in deleting all of its employees.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-af52457 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"af52457\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-a28b32a\" data-id=\"a28b32a\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-07e752d elementor-widget elementor-widget-toggle\" data-id=\"07e752d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-8281\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-8281\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-8281\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-8281\"><p>CREATE DEPARTMENT TABLE (<\/p><p>Id_department NUMERIC (3) PRIMARY KEY,<\/p><p>Name VARCHAR2 (30) CHECK (Name IN (&#039;Commercial&#039;, &#039;Development&#039;, &#039;Production&#039;, &#039;Human Resources&#039;)),<\/p><p>Director NUMERIC (6) NOT NULL UNIQUE,<\/p><p>City VARCHAR2 (20) NOT NULL<\/p><p>);<\/p><p><span style=\"color: var( --e-global-color-text ); font-family: var( --e-global-typography-text-font-family ), Sans-serif; font-weight: var( --e-global-typography-text-font-weight ); font-size: 1.125rem;\">CREATE EMPLOYEE TABLE (<\/span><\/p><p>Id_employee NUMERIC (6) PRIMARY KEY,<\/p><p>Name VARCHAR2 (30) NOT NULL UNIQUE,<\/p><p>First name VARCHAR2 (30) NOT NULL,<\/p><p>Profession VARCHAR2 (30) NOT NULL CHECK (Profession IN (&#039;Executive&#039;, &#039;Manager&#039;, &#039;Engineer&#039;, &#039;Salesperson&#039;, &#039;Technician&#039;, &#039;Intern&#039;)),<\/p><p>Hire_date DATE NOT NULL,<\/p><p>Salary INT NOT NULL CHECK (Salary &gt;= 0),<\/p><p>Commission INT DEFAULT 0,<\/p><p>Id_departement NUMERIC (3) CONSTRAINT fk_employe_departement REFERENCES DEPARTMENT(Id_department) ON DELETE CASCADE<\/p><p>);<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-6d44b9c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"6d44b9c\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-c44087f\" data-id=\"c44087f\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-c0eb047 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"c0eb047\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-fe76cec elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"fe76cec\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-f5a9f2c\" data-id=\"f5a9f2c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-f460746 elementor-widget elementor-widget-heading\" data-id=\"f460746\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"BDD-Voiturestxt\"><\/span>BDD Cars.txt<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-ee0d8b4 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ee0d8b4\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-1aeeae9\" data-id=\"1aeeae9\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6de0022 elementor-widget elementor-widget-text-editor\" data-id=\"6de0022\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>The database used models a car rental activity between individuals: <a href=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/voitures.txt\">cars<\/a><\/p><p>The information kept in the database is information concerning the cars, the owners of these cars, the customers and the rentals made.<\/p><p>You take the following relational diagram:<\/p><p>CAR (<u>immat<\/u>, model, brand, category, color, places, purchaseA, counter, priceJ, #codeP )<\/p><p>OWNER (<u>codeP<\/u>, nickname, email, city, yearI)<\/p><p>CUSTOMER (<u>codeC<\/u>, last name, first name, age, license, address, city)<\/p><p>RENTAL <u>(#CodeC, #immat, year, month<\/u>,numLoc, km, duration, cityD, cityA, dateD, dateF)<\/p><p>Primary keys are bolded\/underlined; foreign keys are preceded by un#<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-fcbcbcc elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"fcbcbcc\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-90d11b8\" data-id=\"90d11b8\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-add0a1a elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"add0a1a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-baf2e28 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"baf2e28\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-00c1c7d\" data-id=\"00c1c7d\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6c44fc2 elementor-widget elementor-widget-heading\" data-id=\"6c44fc2\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-1-2\"><\/span>Exercise 1<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-ed67202 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ed67202\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-875985f\" data-id=\"875985f\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-72f2161 elementor-widget elementor-widget-text-editor\" data-id=\"72f2161\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>What brand is the T550 model vehicle?<\/li><li>What are the nicknames of the owners of Paris?<\/li><li>In which brands are vehicles in the `luxury&#039; category found? What should I use to avoid duplicates?<\/li><li>What are the registrations for 4-seater convertibles?<\/li><li>How many different categories of cars are there?<\/li><li>What are the different car categories?<\/li><li>What Peugeot models are available for rental?<\/li><li>List (pseudo) of owners with a null email address\u00a0<\/li><li>What is the mileage of vehicle `75AZ92\u2032<\/li><li>Which customers (name only) live in `Nantes&#039;?<\/li><li>Was using DISTINCT a good option? and why ?<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-d31dda9 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"d31dda9\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-dd69e9c\" data-id=\"dd69e9c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-da6a96c elementor-widget elementor-widget-toggle\" data-id=\"da6a96c\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-2291\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2291\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-2291\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2291\"><p>select brand from car where model = &#039;T550&#039;; \u2014Ferrari<br \/>select nickname from owner where city = &#039;Paris&#039;; \u2014 (4 rows returned)<br \/>select brand from car where category = &#039;Luxury&#039;; \u2014 (3 rows returned, 1 with distinct)<br \/>select immat from car where category = &#039;convertible&#039; and places = 4; \u2014 (1 row returned)<br \/>select count(distinct category) from car; \u2014 (7)<br \/>select distinct category from car; \u2014 (7 rows returned)<br \/>select model from car where brand = &#039;Peugeot&#039;; \u2014 (5 rows returned)<br \/>select nickname from owner where email is null; \u2014 (2 rows returned)<br \/>select counter from car where immat =\u201975AZ92\u2032; \u2014 17560<br \/>select name from client where city =&#039;Nantes&#039;;<br \/>Warning DISTINCT erases homonyms<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-6f13ad4 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"6f13ad4\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-d6f3a65\" data-id=\"d6f3a65\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-92131ea elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"92131ea\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-abcff00 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"abcff00\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-a7d5d6d\" data-id=\"a7d5d6d\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b13e25d elementor-widget elementor-widget-heading\" data-id=\"b13e25d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-2-2\"><\/span>Exercise 2<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-1ce66b6 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1ce66b6\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-0ae6c21\" data-id=\"0ae6c21\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-e5d0c5b elementor-widget elementor-widget-text-editor\" data-id=\"e5d0c5b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>List of vehicles (immat) by year of purchase (from newest to oldest).<\/li><li>Check by adding the year of purchase to the list<\/li><li>List of brands and models ordered by brand and model.<\/li><li>List of cars with at least 4 seats (make, model, immat, seats) in order of decreasing number of seats.<\/li><li>List of owners&#039; cities (without duplicates and in alphabetical order).<\/li><li>List of rentals (list of vehicle registrations) classified in descending order of km traveled<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-8a752ae elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"8a752ae\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-8d05903\" data-id=\"8d05903\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-d304752 elementor-widget elementor-widget-toggle\" data-id=\"d304752\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-2211\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2211\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-2211\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2211\"><p>select immat from car order by purchaseA; \u2014 (20 rows returned)<br \/>select immat, purchaseA from car order by purchaseA; \u2014 (20 rows returned)<\/p><p>select distinct brand, model from car order by brand, model; \u2014 (15 rows returned)<br \/>select brand, model, immat, places from car where places &gt;= 4 order by places DESC; \u2014 (10 rows returned)<br \/>select distinct city from owner order by city ASC; \u2014 (6 rows returned)<br \/>select immat, km from location order by km DESC; \u2014 (133 rows returned)<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-385fde7 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"385fde7\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-1f062b2\" data-id=\"1f062b2\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a666005 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"a666005\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-48863a6 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"48863a6\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-c00530a\" data-id=\"c00530a\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-2557ea8 elementor-widget elementor-widget-heading\" data-id=\"2557ea8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-3-2\"><\/span>Exercise 3<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-a74c5ca elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"a74c5ca\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-7475a20\" data-id=\"7475a20\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-de13b8e elementor-widget elementor-widget-text-editor\" data-id=\"de13b8e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>1) List of names of customers in Lyon?<\/p><p>the same without the duplicates.<\/p><p>How many Lyon customers are there? ( count(*) , count(name) , count(distinct name))<\/p><p>note the results and explain the differences between the 3 results obtained?<\/p><p>2) Same questions but with first names<\/p><p>How many different couples (last name, first name) are there in the database (all cities combined?<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-4c15f04 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"4c15f04\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-fa158a0\" data-id=\"fa158a0\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-acb4858 elementor-widget elementor-widget-toggle\" data-id=\"acb4858\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1811\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1811\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1811\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1811\"><p>\u2014 1 <br \/>select name from client where city =&#039;Lyon&#039;;<br \/>select distinct name from client where city =&#039;Lyon&#039;;<br \/>select count(name) from client where city =&#039;Lyon&#039;;<br \/>select count(*) from client where city =&#039;Lyon&#039;;<br \/>select count(distinct name) from client where city =&#039;Lyon&#039;;<br \/>\u2014 2 <br \/>select last name, first name from client where city = &#039;Lyon&#039;;<br \/>select count(*) from client where city =&#039;Lyon&#039;;<br \/>select count(first name) from client where city =&#039;Lyon&#039;;<br \/>select count(distinct name) from client where city =&#039;Lyon&#039;;<br \/>select last name, first name from client where first name is NULL and city =&#039;Lyon&#039;;<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-01e6eb1 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"01e6eb1\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-39f7565\" data-id=\"39f7565\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-8cc0af1 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"8cc0af1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-8e6bdb2 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"8e6bdb2\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-4c002ca\" data-id=\"4c002ca\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-0c32f90 elementor-widget elementor-widget-heading\" data-id=\"0c32f90\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-4-2\"><\/span>Exercise 4<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5162d56 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5162d56\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-09dabf6\" data-id=\"09dabf6\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-82f0019 elementor-widget elementor-widget-text-editor\" data-id=\"82f0019\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>How many red or blue convertibles are there?<\/li><li>How many cars are there in the category (family with less than 50,000 km, or utility vehicle), black or white?<\/li><li>List (immat, brand, model) of Peugeot and Citroen brand models displayed by brand and model<\/li><li>List of clients from Paris, over 50 years old in alphabetical order<\/li><li>List (immat) of white vehicles except convertibles\u00a0<\/li><li>How many vehicles in the luxury and premium categories are there before 2012 (2012 included)<\/li><li>List of rentals (rental number, with city, year, month) that started and ended in the same city in chronological order (most recent first)<\/li><li>List of cars dating between 2010 and 2012 (2010 and 2012 inclusive)<\/li><li>List of Renault, Peugeot or Citroen brand cars (use IN).<\/li><li>How many owners live neither in Paris, nor Lyon, nor Nantes?\u00a0<\/li><li>List of clients who rented without a license<\/li><\/ol><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 List of customers (codeC) who have made rentals.<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 List of customers (codeC) who have a permit number\u00a0<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 List of customers (codeC) who do not have a license number\u00a0<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 List of customers (codeC) who rented without a license number<\/p><p>12 List (first and last name) of customers living in the same city as an owner\u00a0<\/p><p>13 List (immat) of cars that have been rented (at least once)<\/p><p>14 List (immat) of cars that have never been rented<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-6c6c191 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"6c6c191\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-9c59c72\" data-id=\"9c59c72\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-e34a202 elementor-widget elementor-widget-toggle\" data-id=\"e34a202\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-2381\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2381\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-2381\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2381\"><div>\u2014 1<\/div><div>select count(*) from car where category= \u201cconvertible\u201d and (color = &#039;red&#039; or color = &#039;blue&#039;); \u2014 1<\/div><div>\u2014 2<\/div><div>select distinct category from car; \u2014 (7 rows returned)<\/div><div>select count(*) from car where ((category =&#039;family&#039; and counter &lt;= 50000) or (category =&#039;utility&#039;)) and (color = &#039;black&#039; or color =&#039;white&#039;); \u2014 4<\/div><div>\u2014 3<\/div><div>select distinct brand from car order by brand;\u2013 (6 rows returned)<\/div><div>select brand, model, immat from car where brand = &#039;Peugeot&#039; or &#039;Citroen&#039; order by brand, model; \u2014 (8 rows returned)<\/div><div>\u2014 4<\/div><div>select surname, first name, age from client where city =&#039;Paris&#039; and age &gt; 50 order by surname; \u2014 (1 rows returned)<\/div><div>\u2014 5<\/div><div>select immat from car where color = &#039;white&#039; and category &lt;&gt; &#039;convertible&#039;; \u2014 (8 rows returned)<\/div><div>\u2014 6<\/div><div>select count(*) from car where (categorie = &#039;luxury&#039; or category = &#039;premium&#039;) and purchaseA &lt;= 2012; \u2014 (6 rows returned)<\/div><div>\u2014 7<\/div><div>select numLoc, cityD, year, month from location where cityD = cityA order by year DESC , month DESC;\u2013 (67 rows returned)<\/div><div>\u2014 8-9-10<\/div><div>select immat from car where purchaseA between 2010 and 2012; \u2014 (8 rows returned)<\/div><div>select immat from car where brand in (&#039;Renault&#039;, &#039;Peugeot&#039;, &#039;Citroen&#039;); \u2014 (16 rows returned)<\/div><div>select nickname from owner where city not in (&#039;Paris&#039;, &#039;Lyon&#039;, &#039;Nantes&#039;); \u2014 (5 rows returned)<\/div><div>\u2014 11<\/div><div>select codeC from location;<\/div><div>select codeC from client where permit is not null;<\/div><div>select codeC from client where permit is null;<\/div><div>select distinct codeC , allowed from client\u00a0<\/div><div>where codeC in (select codeC from location)<\/div><div>and permit is null ; \u2014 (2 rows returned)<\/div><div>\u2014 other version<\/div><div>\u00a0<\/div><div>select distinct codeC , allowed from client\u00a0<\/div><div>where exists (select codeC from location)<\/div><div>and permit is null ;\u00a0<\/div><div>\u2014 other version but without the license number<\/div><div>select distinct codeC from location\u00a0<\/div><div>where codeC in (select codeC from client where permit is null);\u00a0<\/div><div>\u00a0<\/div><div>\u2014 12<\/div><div>select last name, first name, city from client\u00a0<\/div><div>where city in (select city from owner); \u2014 (18 rows returned)<\/div><div>\u2014 13<\/div><div>select distinct immat from location; \u2014 (20 rows returned)<\/div><div>\u00a0<\/div><div>\u2014 14<\/div><div>select distinct immat from car\u00a0<\/div><div>where immat not in (select distinct immat\u00a0<\/div><div>from location); \u2014 (1 rows returned)<\/div><div>\u00a0<\/div><div>\u00a0select distinct immat from car where not exists (<\/div><div>select * from location\u00a0<\/div><div>where location.immat = car.immat); \u2014 (1 rows returned)<\/div><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-d816dc1 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"d816dc1\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-17677e6\" data-id=\"17677e6\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-02232a9 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"02232a9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-64b81c0 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"64b81c0\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-b9746b7\" data-id=\"b9746b7\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-cb56049 elementor-widget elementor-widget-heading\" data-id=\"cb56049\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-5\"><\/span>Exercise 5<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-2b40403 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"2b40403\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-f7812a4\" data-id=\"f7812a4\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6e01ac3 elementor-widget elementor-widget-text-editor\" data-id=\"6e01ac3\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>What is the nickname of the owner of the car &#039;56AA46\u2032?<\/li><li>How many rentals for Peugeot 205?<\/li><li>List (make, model, immat, rental number) of cars rented in 4\/2015.<\/li><li>List (make, model, immat, rental number) of cars rented in the year of their purchase.\u00a0<\/li><li>List of Peugeot vehicle immats<\/li><li>List of Peugeot vehicle immats and corresponding rental information by year, month and immat\u00a0<\/li><li>The list of customers who have rented a Ferrari vehicle?<\/li><li>Which Peugeot models have been rented?<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-8ee8165 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"8ee8165\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-2cfabe4\" data-id=\"2cfabe4\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-073f515 elementor-widget elementor-widget-toggle\" data-id=\"073f515\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-7591\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-7591\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-7591\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-7591\"><div>\u2014 1<\/div><div>select nickname\u00a0<\/div><div>from owner p, car v \u2014 Jules<\/div><div>where immat = &#039;56AA46\u2032\u00a0<\/div><div>and p.codeP = v.codeP ;<\/div><div>\u2014 2<\/div><div>select count(*)\u00a0<\/div><div>from location l, car v \u2014 11<\/div><div>where l.immat = v.immat<\/div><div>and v.marque= &#039;Peugeot&#039;\u00a0<\/div><div>and v.model = &#039;205&#039;;<\/div><div>\u2014 3<\/div><div>select v.brand, v.model, v.immat, l.numLoc\u2013 (6 rows returned)<\/div><div>from car v, rental l\u00a0<\/div><div>where v.immat=l.immat<\/div><div>and l.year = &#039;2015&#039;\u00a0<\/div><div>and l.month = &#039;4&#039;;<\/div><div>\u2014 4<\/div><div>select v.brand, v.model, v.immat, l.numLoc\u2013 (11 rows returned)<\/div><div>from car v, rental l\u00a0<\/div><div>where v.immat = l.immat<\/div><div>and l.year = v.purchaseA ;<\/div><div>\u2014 5<\/div><div>select distinct immat \u2014 (5 rows returned)<\/div><div>from car\u00a0<\/div><div>where brand =&#039;Peugeot&#039;;<\/div><div>\u2014 6<\/div><div>select l.* \u2014 (30 rows returned)<\/div><div>from car v, rental l<\/div><div>where v.immat = l.immat<\/div><div>and brand = &#039;Peugeot&#039;<\/div><div>order by l.year, l.month, l.immat;<\/div><div>\u2014 7<\/div><div>Select c.surname, c.first name, v.brand, v.model \u2014 (13 rows returned)<\/div><div>from location l, car v, customer c\u00a0\u00a0<\/div><div>where c.codeC = l.codeC<\/div><div>and l.immat = v.immat<\/div><div>and v.brand = &#039;Ferrari&#039;;<\/div><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-32ec91b elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"32ec91b\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-d459797\" data-id=\"d459797\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-341d414 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"341d414\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-a2e7f54 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"a2e7f54\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-2932797\" data-id=\"2932797\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-5e0498d elementor-widget elementor-widget-heading\" data-id=\"5e0498d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-6\"><\/span>Exercise 6<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5968f86 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5968f86\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-3f6d80a\" data-id=\"3f6d80a\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-d8232ce elementor-widget elementor-widget-text-editor\" data-id=\"d8232ce\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>List of brands with more than 3 cars available for rental<\/li><li>List of immat of vehicles having covered more than 800 km in rental<\/li><li>List of car models that have been rented at least 3 times<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-56e7680 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"56e7680\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-cdbc482\" data-id=\"cdbc482\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a16cddb elementor-widget elementor-widget-toggle\" data-id=\"a16cddb\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1691\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1691\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1691\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1691\"><p>\u2014 1: list of brands with more than 3 (&gt;) cars available for rental<br \/>select mark \u2014 (2 rows returned)<br \/>from car<br \/>group by brand<br \/>having count(*) &gt; 3;<\/p><p>\u2014 2: list of immats of cars having covered more than 800km in rental<br \/>select immat \u2014 (15 rows returned)<br \/>from location<br \/>group by immat<br \/>having sum(km) &gt; 800;<\/p><p>\u2014 3: car models that have been rented at least 3 times (with number of rentals)<br \/>\u2014 displayed in alphabetical order by model<br \/>select v.model, count(l.numloc) \u2014 (15 rows returned)<br \/>from location l JOIN car v ON l.immat = v.immat<br \/>group by v.modele<br \/>having count(l.numloc) &gt;= 3<br \/>order by v.model;<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-65d66b8 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"65d66b8\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-686f54a\" data-id=\"686f54a\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b4ed3c1 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"b4ed3c1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-2e56c27 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"2e56c27\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-8f5097c\" data-id=\"8f5097c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-f078655 elementor-widget elementor-widget-heading\" data-id=\"f078655\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-7\"><\/span>Exercise 7<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-e9f3908 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"e9f3908\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-83fd2d9\" data-id=\"83fd2d9\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-1b06495 elementor-widget elementor-widget-text-editor\" data-id=\"1b06495\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>Kms traveled in rental by Alain Delon, car (immat) per car<\/li><li>Brands and average km traveled in rental (by brand)<\/li><li>What is the oldest car? (with MIN)<\/li><li>Which car has more km on the odometer than all Peugeots? (with ALL)<\/li><li>Which car\/s have a higher daily price than a Ferrari?<\/li><li>Display the list of cars with a km odometer higher than the general average km odometer.<\/li><li>List of cars with a km odometer higher than the average km odometer for their model?<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-35dfb0d elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"35dfb0d\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-b047178\" data-id=\"b047178\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-490d5da elementor-widget elementor-widget-toggle\" data-id=\"490d5da\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-7661\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-7661\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-7661\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-7661\"><div>\u2014 1: KMs carried out for rental by Alain Delon car (immat) per car<\/div><div>select immat, sum(km) \u2014 (4 rows returned)<\/div><div>from client c JOIN location l on c.codeC = l.codeC\u00a0<\/div><div>where name = &#039;Delon&#039;<\/div><div>group by immat;\u00a0<\/div><div>\u00a0<\/div><div>\u2014 2a: brands and average km traveled in rental by brand<\/div><div>select v.marque, avg(l.km) \u2014 (6 rows returned)<\/div><div>from location l, car v\u00a0<\/div><div>where l.immat = v.immat\u00a0<\/div><div>group by v.marque;<\/div><div>\u00a0<\/div><div>\u2014 2b: same in descending order of averages<\/div><div>\u2014 use of AS<\/div><div>select v.marque, avg(l.km) as average \u2014 (6 rows returned)<\/div><div>from location l, car v\u00a0<\/div><div>where l.immat = v.immat\u00a0<\/div><div>group by v.marque\u00a0<\/div><div>order by average DESC;<\/div><div>\u00a0<\/div><div>\u2014 3: what is the oldest car<\/div><div>select immat \u2014 (3 rows returned)<\/div><div>from car\u00a0<\/div><div>where purchaseA = (select min(purchaseA)\u00a0<\/div><div>from car);<\/div><div>\u00a0<\/div><div>\u2014 4: Which car\/s have more km on the odometer than all Peugeots (ALL)<\/div><div>select immat \u2014 (7 rows returned)<\/div><div>from car<\/div><div>where counter &gt;= ALL (select counter<\/div><div>from car<\/div><div>where brand = &#039;Peugeot&#039;);\u00a0<\/div><div>\u00a0<\/div><div>\u2014 5: Which cars have a higher daily price than a Ferrari<\/div><div>select immat \u2014 (20 rows returned)<\/div><div>from car<\/div><div>where priceJ &gt;= ANY (select priceJ<\/div><div>from car\u00a0<\/div><div>where brand =&#039;Ferrari&#039;);<\/div><div>\u00a0<\/div><div>\u2014 6: list of cars with a km counter higher than the general average km counter<\/div><div>select immat\u2013 (6 rows returned)<\/div><div>from car<\/div><div>where counter &gt; (select avg(counter)\u00a0<\/div><div>from car);<\/div><div>\u00a0<\/div><div>\u2014 7: list of cars with a km counter higher than the average km counter for their model<\/div><div>select v1.immat \u2014 (5 rows returned)<\/div><div>from car v1<\/div><div>where v1.counter &gt; (select avg(v2.counter)\u00a0<\/div><div>from car v2\u00a0<\/div><div>where v2.model = v1.model);<\/div><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-c8b5580 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"c8b5580\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-48c1e1f\" data-id=\"48c1e1f\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-7a0dd57 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"7a0dd57\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5dd51a3 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5dd51a3\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-3e810e0\" data-id=\"3e810e0\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-83ca8a8 elementor-widget elementor-widget-heading\" data-id=\"83ca8a8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-8\"><\/span>Exercise 8<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-b3e7cf8 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"b3e7cf8\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-aceb33d\" data-id=\"aceb33d\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-9734173 elementor-widget elementor-widget-text-editor\" data-id=\"9734173\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li>List of car models rented at least 3 times (with number of rentals)?<\/li><li>Which car has traveled the most km in rental?<\/li><li>Which customer(s) made the most rentals?<\/li><li>Which customer has rented the most different vehicles?<\/li><li>What is the total rental km carried out for the P75 owner?<\/li><li>What is the total rental km carried out for the owner P75 car per car?<\/li><\/ol><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 the same but taking into account that rentals of more than 300 km.\u00a0<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 the same but for vehicles having traveled a total of more than 300 km in rental.\u00a0<\/p><p>For verification, list of rentals made by P75 vehicles.\u00a0<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5541519 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5541519\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-a95af3e\" data-id=\"a95af3e\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-d04b0c0 elementor-widget elementor-widget-toggle\" data-id=\"d04b0c0\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-2181\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2181\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-2181\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2181\"><div>\u2014 1: car models that have been rented at least 3 times (with number of rentals)<\/div><div>\u2014 displayed in alphabetical order by model<\/div><div>select v.model , count(l.numloc) \u2014 (15 rows returned)<\/div><div>from location l, car v<\/div><div>where l.immat = v.immat<\/div><div>group by v.modele<\/div><div>having count(l.numloc) &gt;= 3<\/div><div>order by v.model;\u00a0<\/div><div>\u00a0<\/div><div>\u2014 2: which car has traveled the most km in rental<\/div><div>select immat, sum(km) \u2014 11RS75, 3697km<\/div><div>from location\u00a0<\/div><div>group by immat\u00a0<\/div><div>having sum(km) &gt;= ALL(select sum(km)\u00a0<\/div><div>from location\u00a0<\/div><div>group by immat );<\/div><div>\u00a0<\/div><div>\u2014 3: the customer who has made the most rentals<\/div><div>select C.surname, C.firstname \u2014 (1 rows returned)<\/div><div>from client c JOIN location l ON c.codeC = l.codeC<\/div><div>group by C.nom, C.prenom\u00a0\u00a0<\/div><div>having count(*)<\/div><div>&gt;= ALL (select count(*)\u00a0<\/div><div>from location lo\u00a0<\/div><div>group by lo.codeC);<\/div><div>\u00a0<\/div><div>\u2014 4: the customer who has rented the most different vehicles<\/div><div>select C.surname, C.firstname \u2014 (2 rows returned)<\/div><div>from client c, location l\u00a0<\/div><div>where c.codeC = l.codeC<\/div><div>group by C.nom, C.prenom<\/div><div>having count(distinct immat)\u00a0\u00a0<\/div><div>&gt;= ALL (select count(distinct immat)\u00a0<\/div><div>from location l2\u00a0<\/div><div>group by l2.codeC);<\/div><div>\u00a0<\/div><div>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/div><div>\u00a0<\/div><div>\u2014 5: total rental km carried out for owner&#039;s cars P75<\/div><div>select sum(l.km) \u2014 7192<\/div><div>from location l, car v<\/div><div>where l.immat = v.immat and v.codeP =\u2019P75\u2032;<\/div><div>\u00a0<\/div><div>\u2014 6a: total rental km carried out for the owner P75 car per car<\/div><div>select l.immat, sum(l.km) \u2014 (4 rows returned)\u00a0\u00a0<\/div><div>from location l, car v<\/div><div>where l.immat = v.immat and v.codeP =\u2019P75\u2032<\/div><div>group by l.immat;<\/div><div>\u00a0<\/div><div>\u2014 6b: same as (b) but only taking into account rentals of more than 300 km<\/div><div>select l.immat, sum(l.km)\u2013 (3 rows returned)\u00a0<\/div><div>from location l, car v<\/div><div>where l.immat = v.immat and v.codeP =\u2019P75\u2032<\/div><div>and l.km &gt; 300<\/div><div>group by l.immat;<\/div><div>\u00a0<\/div><div>\u2014 6c same as (b) but only for vehicles having traveled a total of more than 300 km in rental<\/div><div>select l.immat, sum(l.km) \u2014 (4 rows returned)\u00a0<\/div><div>from location l, car v<\/div><div>where l.immat = v.immat and v.codeP =\u2019P75\u2032<\/div><div>group by l.immat<\/div><div>having sum(l.km) &gt; 300;<\/div><div>\u00a0<\/div><div>\u2014 6d: for verification, list of rentals made by the vehicles of P75\u00a0<\/div><div>select l.immat, l.km \u2014 (25 rows returned)<\/div><div>from location l, car v<\/div><div>where l.immat = v.immat and v.codeP =\u2019P75\u2032<\/div><div>order by l.immat, l.km DESC;<\/div><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-879c3d9 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"879c3d9\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-dab2f39\" data-id=\"dab2f39\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-886bd2b elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"886bd2b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-a09fa88 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"a09fa88\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-fa36726\" data-id=\"fa36726\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-cb82303 elementor-widget elementor-widget-heading\" data-id=\"cb82303\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-9\"><\/span>Exercise 9<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-9dc0f7d elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"9dc0f7d\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-2e30221\" data-id=\"2e30221\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-e25b87a elementor-widget elementor-widget-text-editor\" data-id=\"e25b87a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>\u00a0The query in question is: which customers (names) use the same license number?<\/p><p>To guide you a series of progressive queries.<\/p><ol><li>For each permit number, display the number of times it is used.<\/li><li>List of license numbers that have been used more than once?<\/li><li>List of names of customers with a license number used more than once.\u00a0<\/li><li>List of names of customers whose license number is equal to the license number of another customer (with IN)<\/li><li>Alternate writing for (4) using explicit join equalities (SQL1).\u00a0<\/li><li>If there are duplicates left, how do I remove them?<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-bb3efb6 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"bb3efb6\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-42efa62\" data-id=\"42efa62\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a8ecd3b elementor-widget elementor-widget-toggle\" data-id=\"a8ecd3b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1771\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1771\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1771\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1771\"><div>\u2014 1: number of times a permit number has been used<\/div><div>select c1.permis, count(*) \u2014 (8 rows returned)<\/div><div>from client c1\u00a0<\/div><div>group by c1.permis<\/div><div>order by c1.permit;<\/div><div>\u00a0<\/div><div>\u2014 2: number of permits used more than once\u00a0<\/div><div>select c1.permis \u2014 (5 rows returned)<\/div><div>from client c1\u00a0<\/div><div>group by c1.permis<\/div><div>having count(*)&gt; 1<\/div><div>order by c1.permit;<\/div><div>\u00a0<\/div><div>\u2014 3: customers with a license number (also display the license number) used more than once<\/div><div>select c2.name \u2014 (14 rows returned)<\/div><div>from client c2<\/div><div>where c2.permit IN ( select c1.permit<\/div><div>from client c1\u00a0<\/div><div>group by c1.permis<\/div><div>having count(*)&gt; 1 )\u00a0<\/div><div>order by c2.name;<\/div><div>\u00a0<\/div><div>\u2014 4: list of names of customers using the license number (show license number) of another customer\u00a0<\/div><div>select c1.name, c1.permit \u2014 (14 rows returned)<\/div><div>from client c1<\/div><div>where c1.permit IN ( select c2.permit<\/div><div>from client c2\u00a0<\/div><div>where c2.permit = c1.permit<\/div><div>and c2.name != c1.name)<\/div><div>order by c1.name;<\/div><div>\u00a0<\/div><div>\u2014 5: other writing with explicit join equalities\u00a0<\/div><div>\u2014 (self-join is now better visible)<\/div><div>select c1.name, c2.name, c1.permit \u2014 (38 rows returned)<\/div><div>from client c1, client c2<\/div><div>where c2.permit = c1.permit and c2.name != c1.name\u00a0<\/div><div>order by c1.name;<\/div><div>\u00a0<\/div><div>\u2014 6: other writing with explicit join equalities\u00a0<\/div><div>\u2014 (self-join is now more visible)<\/div><div>select c1.name, c2.name, c1.permit \u2014 (19 rows returned)<\/div><div>from client c1, client c2<\/div><div>where c2.permit = c1.permit and c2.name &gt; c1.name\u00a0<\/div><div>order by c1.name;<\/div><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-da32897 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"da32897\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-e25920e\" data-id=\"e25920e\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-97ff901 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"97ff901\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-9702ecf elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"9702ecf\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-772ab86\" data-id=\"772ab86\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-d3d8a0a elementor-widget elementor-widget-heading\" data-id=\"d3d8a0a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-10\"><\/span>Exercise 10<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-bf65305 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"bf65305\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-4837323\" data-id=\"4837323\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-68fdf3c elementor-widget elementor-widget-text-editor\" data-id=\"68fdf3c\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>\u00a0The query targeted is: which customers (the names) rented all the cars?<\/p><p>(a) List of cars that have not been rented; to write using NOT EXISTS. (rewording: cars for which there is no rental with this registration number).<\/p><p>(b) List of names of customers for whom there are vehicles that he (the customer) has not rented.<\/p><p>(c) List of names of customers who have rented all vehicles in the fleet.<\/p><p>\u00a0(rewording: list of names of customers for whom there is no vehicle that they have not rented.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-eb096cb elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"eb096cb\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-3a4a101\" data-id=\"3a4a101\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a472682 elementor-widget elementor-widget-toggle\" data-id=\"a472682\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1721\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1721\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1721\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1721\"><p>\u2014 The query in question is: which customers (the names) rented all the cars?<br \/>\u2014 (a) List of cars which have not been rented; to write using NOT EXISTS.<\/p><p>SELECT car.immat \u2014 1 row<br \/>from car<br \/>where not exists (select * from location where location.immat=car.immat);<\/p><p>\u2014 (b) List of names of customers for whom there are vehicles that he (the customer) has not rented.<br \/>SELECT DISTINCT last name, first name<br \/>from customer, car<br \/>where not exists (select codeC from location where location.immat=voiture.immat and client.codeC=location.codeC);<\/p><p>\u2014 (c) List of names of customers who have rented all vehicles in the fleet.<br \/>\u2014 (rewording: list of names of customers for whom there is no vehicle that they have not rented.<\/p><p>select customer.name <br \/>from location, customer <br \/>where location.codeC= client.codeC <br \/>group by client.codeC <br \/>having count(distinct immat)&gt;= (select (count(distinct immat)) from car);<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-10b326b elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"10b326b\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-9ac33ea\" data-id=\"9ac33ea\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-ec4d040 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"ec4d040\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-52ef147 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"52ef147\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-f638e04\" data-id=\"f638e04\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-54a467e elementor-widget elementor-widget-heading\" data-id=\"54a467e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"BDD-Vinstxt\"><\/span>BDD Vins.txt<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-9ea8b39 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"9ea8b39\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-72585dd\" data-id=\"72585dd\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6649f53 elementor-widget elementor-widget-text-editor\" data-id=\"6649f53\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>We want to use the following relational diagram to manage a wine cellar. The database representing this cellar has the following relational schema: <a href=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/vins.txt\">wines<\/a><\/p><p><span style=\"font-weight: bolder;\">WINE<\/span>\u00a0<u>(numV<\/u>, vintage, year, degree)<\/p><p><span style=\"font-weight: bolder;\">PRODUCER<\/span>\u00a0(\u00a0<u>numP<\/u>, last name, first name, region)<\/p><p><span style=\"font-weight: bolder;\">HARVEST<\/span>\u00a0(\u00a0<u>nprod#, nvin#<\/u>, quantity)<\/p><p>And the following csvs: <a href=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/vin.csv\">wine,<\/a> <a href=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/recolte.csv\">harvest,<\/a> <a href=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/producteur.csv\">producer.<\/a><\/p><p style=\"margin-bottom: 6.0pt; text-align: justify;\">Using the following statement, you translate each line of the .csv file into a tuple in the corresponding table<\/p><p style=\"margin-bottom: 6.0pt; text-align: justify;\"><span style=\"font-family: 'Arial',sans-serif;\">LOAD DATA INFILE &#039;absolute path\/XXX.csv&#039; INTO TABLE XXX FIELDS TERMINATED BY &#039;;&#039; LINES TERMINATED BY &#039;\\r\\n&#039;;<\/span><\/p><p>\u00a0<\/p><p style=\"margin-bottom: 6.0pt; text-align: justify;\"><span style=\"font-family: 'Arial',sans-serif;\">LOAD DATA INFILE &#039;C:\/XXX.csv&#039; INTO TABLE XXX FIELDS TERMINATED BY &#039;;&#039; LINES TERMINATED BY &#039;\\r\\n&#039;; if the XXX.csv file is under the root<\/span><\/p><p>A wine is characterized by a whole number, a vintage, a year of production and a degree. All wines are represented by the VIN relationship. The key to the VIN relationship is the numV attribute.<\/p><p>A producer is characterized by an integer number, a last name, a first name and a region. All producers are represented by the PRODUCER relationship. The key to the PRODUCER relationship is the numP attribute. A producer produces one or more wines. Conversely, a wine is produced by one or more producers (possibly none!).<\/p><p>All production is represented by the HARVEST relationship. A tuple of the relation RECOLTE represents a particular production of a wine of number nvin by a producer of number nprod in a certain quantity. The key to the HARVEST relationship is the attribute group (nvin, nprod).<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-a57abbb elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"a57abbb\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-7347e65\" data-id=\"7347e65\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-e721253 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"e721253\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-79597df elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"79597df\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-4c74470\" data-id=\"4c74470\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-ca72bff elementor-widget elementor-widget-heading\" data-id=\"ca72bff\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-1-3\"><\/span>Exercise 1<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-603c254 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"603c254\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-4ef9d5d\" data-id=\"4ef9d5d\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-9ce8df9 elementor-widget elementor-widget-text-editor\" data-id=\"9ce8df9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>1- Create the database from the files provided.<\/p><p>By default, files are only readable from the directory specified in the secure_file_priv variable.<\/p><p>SHOW VARIABLES LIKE \u201csecure_file_priv\u201d;<\/p><p>Drop your files in this directory and load them to populate your tables.<\/p><p>LOAD DATA INFILE &#039;C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/producer.csv&#039;<\/p><p>\u00a0\u00a0\u00a0 INTO TABLE producer<\/p><p>\u00a0\u00a0\u00a0 FIELDS<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TERMINATED BY &#039;;&#039;<\/p><p>\u00a0\u00a0\u00a0 LINES<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STARTING BY \u00bb\u00a0\u00a0<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TERMINATED BY &#039;\\r\\n&#039;;<\/p><p><span style=\"color: var( --e-global-color-text ); font-family: var( --e-global-typography-text-font-family ), Sans-serif; font-weight: var( --e-global-typography-text-font-weight ); font-size: 1.125rem;\">2- Display the information contained in the VIN relationship.<\/span><\/p><p>3- Give the list of producers who do not have a first name.<\/p><p>4- Give the list of distinct regions of wine production.<\/p><p>5- Give the list of wines from 1980.<\/p><p>6- Give the list of names and first names of wine producers not belonging to the following regions: Corsica, Beaujolais, Burgundy and Rh\u00f4ne.<\/p><p>7- What is the list of vintages harvested in 1979? Display the vintage, producer number and quantity produced.<\/p><p>8- What are the names of the Cru Etoile producers, their regions and the quantity of wines harvested?<\/p><p>9- What is the number of harvests?<\/p><p>10-How many wine producers are there in the Savoie and Jura region?<\/p><p>11-How many wine producers are there who have harvested at least one wine in the Savoie and Jura region?<\/p><p>12-What are the quantities of wine produced by region. Give the list ordered by decreasing quantity.<\/p><p>13- What is the quantity of wine produced of degree &gt;12 per wine number?<\/p><p>14- What is the highest degree of vintage or vintages?<\/p><p>15- Give the ordered list of wines.<\/p><p>16- Give the ordered list of harvested wines.<\/p><p>17- So what is the unharvested raw material?<\/p><p>18- Give the ordered list of vintages and the quantity per vintage?<\/p><p>19- What is the average strength of the wines?<\/p><p>20- What are the vintages (ordered by degree and year) of higher degree than the average degree of the vintages?<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-127face elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"127face\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-0b3f67c\" data-id=\"0b3f67c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-4fd8ede elementor-widget elementor-widget-toggle\" data-id=\"4fd8ede\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-8371\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-8371\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-8371\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-8371\"><p>select * from wine; # 101 rows<br \/>select * from producer; # 200 rows<br \/>select * from harvest; # 200 rows<\/p><p>#Question 2 \u2013 7<br \/>select distinct name from producer where first name is null; # 0 rows<br \/>select distinct name from producer where first name = \u00bb or first name is null; # 12 rows<\/p><p>select distinct region from producer; # 11 rows<br \/>select degree, vintage from wine where year=1980 order by degree desc; # 18 rows<br \/>select name,first name,region from producer where region not in (&#039;Corsica&#039;, &#039;Beaujolais&#039;, &#039;Burgundy&#039;, &#039;Rhone&#039;) order by name asc; # 164 rows<br \/>select nprod, vintage, quantity from wine v, harvest r where year=1979 and v.numV=r.nprod order by nprod; # 5 rows<\/p><p>#Question 8- 13<br \/>select cru,name,region,quantity FROM WINE v,PRODUCER p,HARVEST r WHERE cru=&#039;Star&#039; AND p.numP=v.numV AND p.numP=r.nprod; #1 row<br \/>select COUNT(*) FROM HARVEST; # \u2013&gt; 665<br \/>select COUNT(*) FROM PRODUCER WHERE region in (&#039;Savoie&#039;,&#039;Jura&#039;); # \u2013&gt; 57<br \/>select COUNT(*), quantity FROM PRODUCER p,HARVEST r WHERE region in(&#039;Savoie&#039;,&#039;Jura&#039;) AND p.numP=r.nprod AND quantity&gt;0 GROUP BY quantity; # 22 rows<br \/>select SUM(quantity) FROM HARVEST R, PRODUCER P WHERE R.nprod=P.numP GROUP BY region ORDER BY quantity DESC; # 10 rows<br \/>select SUM(quantity) from harvest r, wine v where v.numV=r.nvin and v.degre&gt;12 group by nvin; # 14 rows<br \/><br \/># Question 14-20<br \/>select raw FROM VIN WHERE degree in (select MAX(degree) FROM VIN); # \u2013&gt; Holy Love<br \/>select cru FROM WINE ORDER BY cru ASC; #101 rows<br \/>select distinct cru from vin v , harvest r where v.numV=r.nvin order by cru; # 61 rows<br \/>select cru from vin v where v.cru not in (select cru from vin v, harvest r where v.numV=r.nvin); # \u2013&gt; Saint Veran<br \/>select cru, SUM(quantity) from vin v ,harvest r where v.numV=r.nvin group by cru order by cru; # 61 rows<br \/>select avg(degree) from vin v; # \u2013&gt; 11.35<br \/>select cru,degree,year from vin where degree &gt; (select avg(degree) from vin v) order by cru,degree,year; # 48 rows<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-a964be8 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"a964be8\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-adb1404\" data-id=\"adb1404\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-8dfd415 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"8dfd415\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-040e125 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"040e125\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-064d334\" data-id=\"064d334\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-7c9605b elementor-widget elementor-widget-heading\" data-id=\"7c9605b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-2-3\"><\/span>Exercise 2<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-1023b45 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1023b45\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-0f9f30b\" data-id=\"0f9f30b\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-c9b9eff elementor-widget elementor-widget-text-editor\" data-id=\"c9b9eff\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>1 \u2013 Give all the wines whose vintage begins with the letter &#039;S&#039;<\/p><p>2 \u2013 Give all distinct wines whose production date is between 1980 and 1981<\/p><p>3 \u2013 Select all productions from producer number 606<\/p><p>4 \u2013 Give by producer number, the total quantity of wine produced. Check with question 2<\/p><p>5 \u2013 Give by producer number, the total quantity of wine produced if it is greater than 400 and all ordered in ascending order of the quantities calculated using an alias on the sum<\/p><p>6 \u2013 Look for the oldest Pommard vintage. Post it with the comment: \u201cthe one I prefer\u201d<\/p><p>7 \u2013 What are the numbers of the producers who harvested the most different wines?<\/p><p>8 \u2013 Add a column to the Producer table corresponding to his date of birth,<\/p><p>Modify the following existing producers in the database by assigning them their date of birth<\/p><p>birthdate = &#039;1960-12-11\u2032 name=&#039;Six&#039;;<\/p><p>birthdate = &#039;1962-12-11\u2032 name=&#039;Lasnier&#039;;<\/p><p>birthdate = &#039;1970-12-11\u2032 name=&#039;Moniot&#039;;<\/p><p>birthdate = &#039;1970-12-11\u2032 name=&#039;Boxler&#039;;<\/p><p>birthdate = &#039;1980-12-11\u2032 name=&#039;Jayer&#039;;<\/p><p>birthdate = &#039;1961-05-10\u2032 name=&#039;Grivot&#039;;<\/p><p>birthdate = &#039;1980-12-11\u2032 name=&#039;Tortochot&#039;;<\/p><p>9 \u2013 Select the producers for which the date of birth is not zero<\/p><p>10 \u2013 Calculate Grivot\u2019s age<\/p><p>11 \u2013 Calculate the number of producers by age<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-7d8a195 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"7d8a195\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-34d766a\" data-id=\"34d766a\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a49b413 elementor-widget elementor-widget-toggle\" data-id=\"a49b413\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-1721\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1721\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-1721\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1721\"><p>1- select cru from vin where cru like &#039;s%&#039;;<\/p><p>2- select distinct cru, annee from vin where annee between 1980 and 1981;<\/p><p>3- select * from harvest where nprod = 606;<\/p><p>4- select nprod, sum(qte) from harvest group by<br \/>nprod;<\/p><p>5- select nprod, sum(qte) as sum from harvest group by<br \/>nprod having sum(qte) &gt; 400 order by sum;<\/p><p>6- select cru, annee from vin where cru=&#039;Pommard&#039;;<\/p><p>select concat (cru, &#039;the one I prefer&#039;), year <br \/>from vin where cru = &#039;Pommard&#039; and annee &gt;= (select max(annee) from vin where cru=&#039;Pommard&#039;);<\/p><p>7- select nprod, name from harvest, producer where harvest.nprod = producer.num <br \/>group by nprod <br \/>having count(distinct nvin) &gt;= All <br \/>(select count(distinct nvin) as nbvindifferent from harvest group by nprod <br \/>);<\/p><p><br \/>select nprod, name from harvest, producer where harvest.nprod = producer.num <br \/>group by nprod <br \/>having count(distinct nvin) &gt;= <br \/>(select max(nbvindifferent) from <br \/>(select count(distinct nvin) as nbvindifferent from harvest group by nprod) P);<\/p><p><br \/>select max(nbvindifferent) from <br \/>(select count(distinct nvin) as nbvindifferent from harvest group by nprod) P;<\/p><p>select nprod, count(distinct nvin) as nbvindifferent <br \/>from harvest group by nprod order by nbvindifferent desc;<\/p><p>select * from harvest where nprod=355;<\/p><p><br \/>8- alter table producer add column birth datetime;<\/p><p>9- select * from producer where num &lt; 5;<br \/>update producer set birth = &#039;1990\/10\/10&#039; where num = 1;<\/p><p>10- SELECT name, Year(current_date()) \u2013 YEAR(producer.birth) as AGE FROM producer <br \/>where birth is not null;<\/p><p>11- select count(*) as number, Year(current_date()) \u2013 year(producer.birth)as age <br \/>from producer where birth is not null group by age;<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-4b639be elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"4b639be\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-dc213c4\" data-id=\"dc213c4\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-01152b2 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"01152b2\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-04a2253 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"04a2253\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-e6d630f\" data-id=\"e6d630f\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b9de858 elementor-widget elementor-widget-heading\" data-id=\"b9de858\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercice-3-3\"><\/span>Exercise 3<span class=\"ez-toc-section-end\"><\/span><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-138904b elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"138904b\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-a81e389\" data-id=\"a81e389\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-eda705a elementor-widget elementor-widget-text-editor\" data-id=\"eda705a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Resume the VINs database and answer the following questions:<\/p><p>1 \u2013 What are the names of the producers of wine No. 5;<\/p><p>2 \u2013 Who are the producers who produced the &#039;Pommard&#039; wine?<\/p><p>3 \u2013 What are the vintages harvested?<\/p><p>4 \u2013 What is the list of vintages harvested in 1979? Display the vintage, producer number and quantity produced.<\/p><p>5- What are the names of the Cru Etoile producers, their regions and the quantity of<\/p><p>wines harvested?<\/p><p>6 \u2013 What is the number of producers of vintages harvested in Savoie and the Jura?<\/p><p>7 \u2013 So what is the unharvested raw material?<\/p><p>8 \u2013 Give the ordered list of vintages and the quantity per vintage?<\/p><p>9 \u2013 Give the names of the producers who harvested the most different wines<\/p><p>10 \u2013 Give the list of names of producers who did not harvest<\/p><p>11 \u2013 What are the names of people with the same first name<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-1a6c50e elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1a6c50e\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-4e6303a\" data-id=\"4e6303a\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-ce6d1ba elementor-widget elementor-widget-toggle\" data-id=\"ce6d1ba\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"toggle.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle\">\n\t\t\t\t\t\t\t<div class=\"elementor-toggle-item\">\n\t\t\t\t\t<div id=\"elementor-tab-title-2161\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2161\" aria-expanded=\"false\">\n\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon elementor-toggle-icon-left\" aria-hidden=\"true\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-closed\"><i class=\"fas fa-caret-right\"><\/i><\/span>\n\t\t\t\t\t\t\t\t<span class=\"elementor-toggle-icon-opened\"><i class=\"elementor-toggle-icon-opened fas fa-caret-up\"><\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"elementor-toggle-title\" tabindex=\"0\">Solution<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<div id=\"elementor-tab-content-2161\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2161\"><div>1- select name from producer, harvest where producer.num = harvest.nprod and nvin=5;<\/div><div>\u00a0<\/div><div>2- select last name, first name from producer, harvest, wine where producer.num = harvest.nprod and harvest.nvin=vin.num and cru = &#039;Pommard&#039;;<\/div><div>\u00a0<\/div><div>3- select distinct cru from harvest, vin where harvest.nvin= vin.num;<\/div><div>\u00a0<\/div><div>4- select\u00a0<\/div><div>\u00a0 \u00a0 p.num, p.nom, raw, qte<\/div><div>from<\/div><div>\u00a0 \u00a0 wine v,<\/div><div>\u00a0 \u00a0 to harvest,<\/div><div>\u00a0 \u00a0 producer p<\/div><div>where<\/div><div>\u00a0 \u00a0 date = 1979 and v.num = r.nvin<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 and p.num = r.nprod<\/div><div>order by nprod;<\/div><div>\u00a0<\/div><div>5-select\u00a0<\/div><div>\u00a0 \u00a0 name, vintage, region, qty<\/div><div>from<\/div><div>\u00a0 \u00a0 to harvest,<\/div><div>\u00a0 \u00a0 producer p,<\/div><div>\u00a0 \u00a0 wine v<\/div><div>where<\/div><div>\u00a0 \u00a0 p.num = r.nprod and v.num = r.nvin<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 and v.cru = &#039;Star&#039;;<\/div><div>\u00a0<\/div><div>6- select\u00a0<\/div><div>\u00a0 \u00a0 count(*)<\/div><div>from<\/div><div>\u00a0 \u00a0 producer p,<\/div><div>\u00a0 \u00a0 to harvest<\/div><div>where<\/div><div>\u00a0 \u00a0 region in (&#039;Savoie&#039;, &#039;Jura&#039;)<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 and p.num = r.nprod;<\/div><div>\u00a0<\/div><div>select\u00a0<\/div><div>\u00a0 \u00a0 count(*)<\/div><div>from<\/div><div>\u00a0 \u00a0 producer p,<\/div><div>\u00a0 \u00a0 to harvest<\/div><div>where<\/div><div>\u00a0 \u00a0 \u00a0p.num = r.nprod and (p.region=&#039;Jura&#039; or p.region=&#039;Savoie&#039;);<\/div><div>\u00a0<\/div><div>7- select\u00a0<\/div><div>\u00a0 \u00a0 believed<\/div><div>from<\/div><div>\u00a0 \u00a0 wine<\/div><div>where<\/div><div>\u00a0 \u00a0 vin.num not in (select\u00a0<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 harvest.nvin<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 from<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 harvest);<\/div><div>\u00a0<\/div><div>8- select\u00a0<\/div><div>\u00a0 \u00a0 \u00a0raw, sum(qty)\u00a0<\/div><div>from<\/div><div>\u00a0 \u00a0 harvest,<\/div><div>\u00a0 \u00a0 wine<\/div><div>where<\/div><div>\u00a0 \u00a0vin.num = harvest.nvin<\/div><div>group by nvin;<\/div><div>\u00a0<\/div><div>\u00a0<\/div><div>select\u00a0<\/div><div>\u00a0 \u00a0 raw, sum(qty)<\/div><div>from<\/div><div>\u00a0 \u00a0 wine v<\/div><div>\u00a0 \u00a0 \u00a0 \u00a0 LEFT OUTER JOIN<\/div><div>\u00a0 \u00a0 harvest r ON v.num = r.nvin<\/div><div>group by cru<\/div><div>order by raw;<\/div><div>\u00a0<\/div><div>\/*requests 9 and 10 see above*\/<\/div><div>\u00a0<\/div><div>11- select P1.name, P2.name from Producer P1, Producer P2<\/div><div>where P1.firstname = P2.firstname;<\/div><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"<p>Software analysis Home page Wiki Corrected SQL query exercises These corrected SQL query exercises are for beginners to advanced users. Flashback exercise on model\u2026 <\/p>","protected":false},"author":1,"featured_media":0,"parent":4609,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-19913","page","type-page","status-publish","hentry"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/19913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/comments?post=19913"}],"version-history":[{"count":22,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/19913\/revisions"}],"predecessor-version":[{"id":20511,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/19913\/revisions\/20511"}],"up":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/4609"}],"wp:attachment":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/media?parent=19913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}