{"id":19897,"date":"2024-02-01T04:11:08","date_gmt":"2024-02-01T03:11:08","guid":{"rendered":"https:\/\/complex-systems-ai.com\/?page_id=19897"},"modified":"2024-02-11T21:34:48","modified_gmt":"2024-02-11T20:34:48","slug":"exercices-sql-debutant","status":"publish","type":"page","link":"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-sql-exercises-for-beginners\/","title":{"rendered":"6 Corrected SQL exercises for beginners"},"content":{"rendered":"<div data-elementor-type=\"wp-page\" data-elementor-id=\"19897\" class=\"elementor elementor-19897\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-ddd6c89 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ddd6c89\" 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-75b0775\" data-id=\"75b0775\" 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-743030e elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"743030e\" 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-ac6087c\" data-id=\"ac6087c\" 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-855cc2b elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"855cc2b\" 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-c32c1f0\" data-id=\"c32c1f0\" 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-99edfc6 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"99edfc6\" 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-48bfc8a elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"48bfc8a\" 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-913ff6b\" data-id=\"913ff6b\" 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-e4cd9a4 elementor-widget elementor-widget-heading\" data-id=\"e4cd9a4\" 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-sql-exercises-for-beginners\/#Exercices-corriges-SQL-debutant\" >Corrected SQL exercises for beginners<\/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-sql-exercises-for-beginners\/#Enonce\" >States<\/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-sql-exercises-for-beginners\/#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-4\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-sql-exercises-for-beginners\/#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-5\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-sql-exercises-for-beginners\/#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-6\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-sql-exercises-for-beginners\/#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-7\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-sql-exercises-for-beginners\/#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-8\" href=\"https:\/\/complex-systems-ai.com\/en\/software-analysis\/corrected-sql-exercises-for-beginners\/#Exercice-6\" >Exercise 6<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"elementor-heading-title elementor-size-default\"><span class=\"ez-toc-section\" id=\"Exercices-corriges-SQL-debutant\"><\/span>Corrected SQL exercises for beginners<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-b09a935 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"b09a935\" 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-e0dbbf5\" data-id=\"e0dbbf5\" 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-e99abc4 elementor-widget elementor-widget-text-editor\" data-id=\"e99abc4\" 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 beginner SQL exercises are aimed, as the name suggests, at people who are not initiated into the SQL language. Other exercises will cover databases in more depth.<\/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 beginner\" 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-d20569c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"d20569c\" 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-c6429b0\" data-id=\"c6429b0\" 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-50a4337 elementor-widget elementor-widget-heading\" data-id=\"50a4337\" 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=\"Enonce\"><\/span>States<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-837557e elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"837557e\" 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-6d4e8d9\" data-id=\"6d4e8d9\" 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-960b7a9 elementor-widget elementor-widget-text-editor\" data-id=\"960b7a9\" 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. The information kept in the database is information concerning the cars, the owners of these cars, the customers and the rentals made. This is the database used as an example during the course (in its full version\u2026 some attributes have been added compared to the course version)<\/p><p>The relational diagram is as follows:<\/p><p>CAR (<strong><u>immat :VARCHAR<\/u><\/strong>, model: VARCHAR, brand: VARCHAR, category: VARCHAR, color: VARCHAR, places: INTEGER, purchaseA: INTEGER, counter: INTEGER, priceJ: DECIMAL, #codeP: INTEGER)<\/p><p>OWNER (<strong><u>codeP:VARCHAR(4),<\/u><\/strong> nickname: VARCHAR, email: VARCHAR, city: VARCHAR, year: INTEGER)<\/p><p>CUSTOMER <strong><u>(Ccode:VARCHAR(4)<\/u><\/strong>, last name: VARCHAR, first name: VARCHAR, age: INTEGER, license: VARCHAR, address: VARCHAR, city: VARCHAR)<\/p><p>RENTAL <strong><u>(#codeC:VARCHAR(4), #immat:VARCHAR, year:INTEGER, month:INTEGER,<\/u><\/strong> locnum:INTEGER, km:INTEGER, duration:INTEGER, cityD:VARCHAR, cityA:VARCHAR, dateD:DATE, dateF:DATE)<\/p><p>Primary keys are bolded\/underlined; foreign keys are preceded by un#<\/p><p>A customer is identified by his customer code (Ccode) and the information stored is his last name, first name, age, street name, city name and his license number.<\/p><p>An owner is identified by his (owner code) and the information stored is his name, city and email.<\/p><p>A car is identified by its (registration number) and the information stored is the make, model, color, category, number of seats, year of purchase, price per day and km odometer which is updated after each new rental.<\/p><p>The color can take its values from the following: white, red, green, black, purple and blue<\/p><p>The number of seats in cars cannot exceed 6 (6 included)<\/p><p>A rental is identified by the (customer code, vehicle registration and a date broken down into month and year).<\/p><p>In addition, for each rental, a rental number, the mileage traveled, the duration, the cities of departure and arrival, and the start and end dates are kept.<\/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-4b51fb1 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"4b51fb1\" 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-fda5bc8\" data-id=\"fda5bc8\" 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-d9d2397 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"d9d2397\" 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-bbf5498 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"bbf5498\" 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-828185a\" data-id=\"828185a\" 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-6b6fd68 elementor-widget elementor-widget-heading\" data-id=\"6b6fd68\" 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-0a870e9 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"0a870e9\" 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-2d944a1\" data-id=\"2d944a1\" 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-3318259 elementor-widget elementor-widget-text-editor\" data-id=\"3318259\" 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>Create the Entity-Association diagram corresponding to the specifications and which obviously suits the relational diagram provided.<\/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-767ea1c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"767ea1c\" 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-ef94cfa\" data-id=\"ef94cfa\" 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-e9174df elementor-widget elementor-widget-toggle\" data-id=\"e9174df\" 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-2441\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-2441\" 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-2441\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-2441\"><p>It&#039;s all in the statement...<\/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-7eacec0 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"7eacec0\" 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-7a6df0b\" data-id=\"7a6df0b\" 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-76d3b82 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"76d3b82\" 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-ed089b7 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ed089b7\" 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-9315eeb\" data-id=\"9315eeb\" 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-50143b7 elementor-widget elementor-widget-heading\" data-id=\"50143b7\" 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-e14d047 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"e14d047\" 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-f94a232\" data-id=\"f94a232\" 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-6575993 elementor-widget elementor-widget-text-editor\" data-id=\"6575993\" 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>From the relational diagram provided upstream, create your database on MySQL <strong>respecting the names.<\/strong><\/p><p><strong>For the color constraint: color ENUM(&#039;white&#039;,&#039;red&#039;,&#039;green&#039;,&#039;black&#039;,&#039;purple&#039;,&#039;blue&#039;),<\/strong><\/p><p><strong>For the places constraint: places INT NULL CHECK (places &lt;= 6),<\/strong><\/p><p><strong>Please note, the database schema is not updated automatically. You need to refresh the window.<\/strong><\/p><p>The file <a href=\"http:\/\/complex-systems-ai.com\/wp-content\/uploads\/2024\/02\/peuplement-tables.txt\">stand tables<\/a> \u00a0allows the installation of a dataset in tables.<\/p><p>Use the MySQL menu command: File \/ Open SQL script to insert the table tuples.<\/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-ad7d63e elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ad7d63e\" 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-7a8ef82\" data-id=\"7a8ef82\" 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-ab060ed elementor-widget elementor-widget-toggle\" data-id=\"ab060ed\" 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-1791\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1791\" 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-1791\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1791\"><div>DROP DATABASE IF EXISTS lessor;\u00a0<\/div><div>CREATE DATABASE IF NOT EXISTS lessor;\u00a0<\/div><div>\u00a0<\/div><div>DROP TABLE IF EXISTS client;<\/div><div>CREATE TABLE IF NOT EXISTS client (<\/div><div>\u00a0 codeC VARCHAR(4) ,<\/div><div>\u00a0 name VARCHAR(20) NOT NULL,<\/div><div>\u00a0 first name VARCHAR(20),<\/div><div>\u00a0 age INT ,<\/div><div>\u00a0 permit VARCHAR(10) ,<\/div><div>\u00a0 address VARCHAR(50) ,<\/div><div>\u00a0 city VARCHAR(20),<\/div><div>\u00a0 PRIMARY KEY (Ccode) );<\/div><div>\u00a0<\/div><div>DROP TABLE IF EXISTS owner;<\/div><div>CREATE TABLE IF NOT EXISTS owner (<\/div><div>\u00a0 codeP VARCHAR(4) NOT NULL,<\/div><div>\u00a0 pseudo VARCHAR(20) NOT NULL,<\/div><div>\u00a0 email VARCHAR(20) NULL,<\/div><div>\u00a0 city VARCHAR(20) NULL,<\/div><div>\u00a0 yearI INT NULL,<\/div><div>\u00a0 PRIMARY KEY (Pcode) );<\/div><div>\u00a0\u00a0<\/div><div>DROP TABLE IF EXISTS car;<\/div><div>CREATE TABLE IF NOT EXISTS car (<\/div><div>\u00a0 immat VARCHAR(10) NOT NULL,<\/div><div>\u00a0 model VARCHAR(20) NULL,<\/div><div>\u00a0 mark VARCHAR(20) NULL,<\/div><div>\u00a0 category VARCHAR(20) NULL,<\/div><div>\u00a0 color ENUM(&#039;white&#039;,&#039;red&#039;,&#039;green&#039;,&#039;black&#039;,&#039;purple&#039;,&#039;blue&#039;),<\/div><div>\u00a0 places INT NULL CHECK (places &lt;= 6),<\/div><div>\u00a0 purchaseA VARCHAR(4) NULL,<\/div><div>\u00a0 counter INT NOT NULL,<\/div><div>\u00a0 price INT NULL,<\/div><div>\u00a0 codeP VARCHAR(4) NULL,<\/div><div>\u00a0 \u00a0PRIMARY KEY (immat),<\/div><div>\u00a0 \u00a0<\/div><div>\u00a0 \u00a0CONSTRAINT codePCar FOREIGN KEY (codeP)<\/div><div>Owner REFERENCES (codeP)<\/div><div>ON DELETE NO ACTION<\/div><div>ON UPDATE NO ACTION );<\/div><div>\u00a0<\/div><div>DROP TABLE IF EXISTS location;<\/div><div>CREATE TABLE IF NOT EXISTS location (<\/div><div>\u00a0 codeC VARCHAR(4) NOT NULL,<\/div><div>\u00a0 immat VARCHAR(10) NOT NULL,<\/div><div>\u00a0 year INT NOT NULL,<\/div><div>\u00a0 month INT NOT NULL,<\/div><div>\u00a0 numLoc VARCHAR(5) NULL,<\/div><div>\u00a0 km INT NOT NULL,<\/div><div>\u00a0 duration INT NULL,<\/div><div>\u00a0 cityD VARCHAR(20) NULL,<\/div><div>\u00a0 cityA VARCHAR(20) NULL,<\/div><div>\u00a0 dateD DATE NULL,<\/div><div>\u00a0 dateF DATE NULL,<\/div><div>\u00a0 PRIMARY KEY (Ccode, immat, year, month),<\/div><div>\u00a0<\/div><div>CONSTRAINT codeClocation FOREIGN KEY (codeC)<\/div><div>Customer REFERENCES (codeC)<\/div><div>ON DELETE NO ACTION<\/div><div>ON UPDATE NO ACTION,<\/div><div>\u00a0 CONSTRAINT immatRental FOREIGN KEY (immat)<\/div><div>CAR REFERENCES (immat)<\/div><div>ON DELETE NO ACTION<\/div><div>ON UPDATE NO ACTION );<\/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-0da3064 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"0da3064\" 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-f87f50f\" data-id=\"f87f50f\" 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-06a4bd1 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"06a4bd1\" 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-1c126e5 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1c126e5\" 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-29d4503\" data-id=\"29d4503\" 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-bf15919 elementor-widget elementor-widget-heading\" data-id=\"bf15919\" 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-25dcde7 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"25dcde7\" 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-12a9a62\" data-id=\"12a9a62\" 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-cbad695 elementor-widget elementor-widget-text-editor\" data-id=\"cbad695\" 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>Display the list (model, color, registration) of Peugeot vehicles.<\/li><li>View vehicle specifications 56AA46. (use *)<\/li><li>Where does Depardieu live?<\/li><li>Which customers have &#039;Jean&#039; for first name?<\/li><li>List of customers (last name, first name) with `jean&#039; for first name, in alphabetical order.<\/li><li>List of customers with a compound first name starting with jean (jean-pierre, jean-jacques etc.)<\/li><li>Who (last name, first name) lives in a street whose name contains the word &#039;bear&#039;?<\/li><li>What are 4-seater convertibles?<\/li><li>List (registration numbers) of blue colored cars.<\/li><li>List (registration numbers) of white-colored cars.<\/li><li>List (*) of vehicles registered in 62.<\/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-2b7f71f elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"2b7f71f\" 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-a2446d2\" data-id=\"a2446d2\" 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-983b8f0 elementor-widget elementor-widget-toggle\" data-id=\"983b8f0\" 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-1591\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-1591\" 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-1591\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-1591\"><p>\u2014 1 (5 rows returned)<br \/>SELECT model, color, immat FROM car WHERE brand = &#039;Peugeot&#039;;<\/p><p>\u2014 2 (1 rows returned)<br \/>SELECT * FROM car WHERE immat = &#039;56AA46\u2032;<\/p><p>\u2014 3 (3 rows returned)<br \/>SELECT first name, address, city FROM customer WHERE last name = &#039;Depardieu&#039;;<\/p><p>\u2014 4 (2 rows returned)<br \/>SELECT name FROM customer WHERE first name = &#039;John&#039;;<\/p><p>\u2014 5 (2 rows returned)<br \/>SELECT last name, first name FROM customer WHERE first name = &#039;Jean&#039; order by last name;<\/p><p>\u2014 6 (5 rows returned)<br \/>SELECT last name, first name FROM client WHERE first name like &#039;Jean%&#039; order by last name;<\/p><p>\u2014 7 (2 rows returned)<br \/>SELECT last name, first name, address FROM customer WHERE address like &#039;%ours%&#039; order by last name;<\/p><p>\u2014 8 (3 rows returned)<br \/>SELECT * FROM car WHERE category = &#039;convertible&#039;and seats = 2;<\/p><p>\u2014 9 (0 rows returned)<br \/>SELECT immat, brand, model FROM car WHERE color = &#039;blue&#039;;<\/p><p>\u2014 10 (8 rows returned)<br \/>SELECT immat, brand, model FROM car WHERE color = &#039;white&#039;;<\/p><p>\u2014 11 (4 rows returned)<br \/>SELECT * FROM car WHERE immat like &#039;&#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-ae9f90e elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ae9f90e\" 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-9ada273\" data-id=\"9ada273\" 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-78718d6 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"78718d6\" 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-3ac11b5 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"3ac11b5\" 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-51430f0\" data-id=\"51430f0\" 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-e1b4605 elementor-widget elementor-widget-heading\" data-id=\"e1b4605\" 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-2828061 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"2828061\" 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-7814512\" data-id=\"7814512\" 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-7e8c44e elementor-widget elementor-widget-text-editor\" data-id=\"7e8c44e\" 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>4-1 Study the Car table<\/p><ul><li>How many vehicles are there in the comic?<\/li><li>How many brands? (SELECT count(brands))<\/li><li>How many registrations? (SELECT count(immat))<\/li><li>Make the same queries adding distinct and explain the difference<\/li><\/ul><p>4-2. Insert tuples into a table<\/p><p>car (10AK37, Peugeot, 205, convertible, purple, 2 seats, 1980, meter: 65,000, \u20ac40\/day, owner: P99)<\/p><p>car (11BF37, Peugeot, 206, convertible, purple, 2 seats, 1980, meter: 21,000, \u20ac32\/day, owner: P99)<\/p><p>car (15AK37, Peugeot, 205, convertible, purple, 2 seats, 1985, meter: 27,000, \u20ac30\/day, owner: P99)<\/p><ul><li>Check the presence of these cars in the relevant table<\/li><\/ul><p>car (63GH94, Megane, Renault, sedan, pink, 4 seats, 2012, meter: 750, 40\u20ac\/day, owner: P99)<\/p><p>car (87AZ92, Clio, Renault, city car, green, 4 seats, 1999, meter: 61200, \u20ac40\/day, owner: P99)<\/p><ul><li>If problem =&gt; identify the problem, can we correct and redo the insertion?\u00a0 \u00a0\u00a0<\/li><\/ul><p>4-3. Miscellaneous<\/p><ul><li>How many vehicles are there in the comic?<\/li><li>How many purple vehicles are there?<\/li><li>Change color of vehicle registration 11BF37 to white<\/li><li>Remove the purple vehicles, how many vehicles are left in the comic?<\/li><li>Delete the car whose code P=&#039;P89&#039;, explain<\/li><\/ul><p>4.4 Insert columns in a class<\/p><ul><li>Add two new columns to the rental table intended to receive users&#039; opinions on the rental made:<ul><li>rating (from 0 to 5)<\/li><li>appreciation (text limited to 120 characters) and<\/li><\/ul><\/li><\/ul><p>Check the location table with the DESC command<\/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-89eacd2 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"89eacd2\" 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-bd0fdaa\" data-id=\"bd0fdaa\" 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-4d7ada7 elementor-widget elementor-widget-toggle\" data-id=\"4d7ada7\" 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-8121\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-8121\" 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-8121\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-8121\"><p>\u2014 1<\/p><p>SELECT count(model) FROM car;<\/p><p>SELECT count(distinct model) FROM car;<\/p><p>SELECT count(immat) FROM car;<\/p><p>SELECT count(distinct immat) FROM car;<\/p><p>\u2014 2<\/p><p>insert into car (immat, model, brand, category, color, seats, purchaseA, counter, priceJ, codeP)\u00a0<\/p><p>values (&#039;10AK37\u2032, &#039;205&#039;,&#039;Peugeot&#039;, &#039;cabriolet&#039;, &#039;purple&#039;,2,1980,65000,40,&#039;P99\u2032);<\/p><p>insert into car values (&#039;11BF37\u2032, &#039;206&#039;, &#039;Peugeot&#039;, &#039;cabriolet&#039;, &#039;purple&#039;,2,1980,21000,27,&#039;P99\u2032);<\/p><p>insert into car values (&#039;15AK37\u2032, &#039;205&#039;, &#039;Peugeot&#039;, &#039;cabriolet&#039;, &#039;purple&#039;,2,1980,27000,32,&#039;P99\u2032);<\/p><p>\u00a0<\/p><p>SELECT count(*) FROM car;<\/p><p>insert into car (immat, model, brand, category, color, seats, purchaseA, counter, priceJ, codeP)\u00a0<\/p><p>values (&#039;63GH94\u2032, &#039;megane&#039;,&#039;Renault&#039;,&#039;sedan&#039;,&#039;rouge&#039;,4,&#039;2012\u2032,750,40,&#039;P99\u2032);<\/p><p>insert into car (immat, model, brand, category, color, seats, purchaseA, counter, priceJ, codeP)\u00a0<\/p><p>values (&#039;87AZ93\u2032, &#039;Clio&#039;,&#039;Renault&#039;, &#039;citadine&#039;,&#039;vert&#039;,4,&#039;1999\u2032,61200,30,&#039;P99\u2032);<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p><p>\u2014 3<\/p><p>SELECT count(*) FROM car;<\/p><p>SELECT * FROM car WHERE color = &#039;purple&#039;;<\/p><p>SELECT * FROM car WHERE immat = &#039;11BF37\u2032;<\/p><p>update car set color = &#039;white&#039; WHERE immat = &#039;11BF37\u2032;\u00a0<\/p><p>SELECT count(*) FROM car WHERE color = &#039;purple&#039;;<\/p><p>\u00a0<\/p><p>SELECT count(*) FROM car;<\/p><p>delete FROM car WHERE color = &#039;purple&#039;;<\/p><p>SELECT count(*) FROM car;<\/p><p>\u00a0<\/p><p>SELECT count(*) FROM car WHERE codeP = &#039;P89&#039;;<\/p><p>delete FROM car WHERE codeP = &#039;P89&#039;;<\/p><p>SELECT count(*) FROM car WHERE codeP = &#039;P89&#039;;<\/p><p>\u2014 cannot delete P89 is referenced as a foreign key in another tuple<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p><p>\u2014 to modify the safe mode parameter (if there is a problem)<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p><p>\u2014 show variables like &#039;sql\\_safe\\_updates&#039;;<\/p><p>\u2014 set sql\\safe\\updates = 0;<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p><p>\u2014 4<\/p><p>alter table location add column note int;<\/p><p>alter table location add column appreciation varchar(20);<\/p><p>desc location;<\/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-7b784f5 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"7b784f5\" 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-0b3859f\" data-id=\"0b3859f\" 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-13aa0b0 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"13aa0b0\" 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-9879aaf elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"9879aaf\" 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-47d13c5\" data-id=\"47d13c5\" 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-8c5cdb3 elementor-widget elementor-widget-heading\" data-id=\"8c5cdb3\" 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-1379250 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1379250\" 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-e582e1d\" data-id=\"e582e1d\" 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-a290b07 elementor-widget elementor-widget-text-editor\" data-id=\"a290b07\" 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 vehicles are there in the database?<\/li><li>How many rentals are there in the bd<\/li><li>Delete the vehicle registered `11TR62\u2032<\/li><li>What error code and why?<\/li><li>In which table should delete on cascade be activated?<\/li><\/ol><p>\u00a0Two ALTER TABLEs will be necessary:<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0one for DROP FOREIGN KEY<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0then one for ADD CONSTRAINT \u2026 FOREIGN KEY<\/p><p><strong>Attention <\/strong>: the name chosen to designate the Foreign Key constraint must be unique, so use names like\u2026VariableNameTableName<\/p><ol start=\"6\"><li>Retry deletion of vehicle registered `11TR62\u2032<\/li><li>How many vehicles are there in the database (compare with the result previously noted)<\/li><li>How many rentals are there in the database (compare with the result previously noted)<\/li><li>Empty table (remove all tuples) from car table<\/li><li>Drop the customer table (tuple and table structure) using a DROP TABLE<\/li><li>Why doesn&#039;t this work?<\/li><li>Delete the location table (tuple and table structure) with a DROP TABLE Then delete the customer table. Why is the deletion of rental possible then customer and not customer before rental?<\/li><li>Delete in the correct order by DROP TABLE, car, maintenance and owner tables<\/li><li>Finally regenerate the entire DB to the initial state for the rest of the tutorial.<\/li><\/ol><p>Recreate the tables by running the table creation scripts;<\/p><p>Rerun the table population script<\/p><p>Then perform a bulk insert of a data file into the database (more precisely into the location table)<\/p><p><strong>LOAD DATA LOCAL INFILE &#039;\/temp\/locationComplements.csv&#039; 2 <\/strong><\/p><p><strong>INTO TABLE `location` <\/strong><\/p><p><strong>FIELDS TERMINATED BY &#039;;&#039; <\/strong><\/p><p><strong>LINES TERMINATED BY &#039;\\n&#039;;<\/strong><\/p><p>to modify the corresponding system variable:<\/p><p>SHOW GLOBAL VARIABLE LIKE &#039;local_infile&#039;;<\/p><p>SET GLOBAL LOCAL_INFILE = &#039;ON&#039;<\/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-1937df2 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"1937df2\" 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-4fdd2af\" data-id=\"4fdd2af\" 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-1d0e81b elementor-widget elementor-widget-toggle\" data-id=\"1d0e81b\" 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-3041\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-3041\" 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-3041\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-3041\"><p>\u2014 1,2,3<\/p><p>SELECT count(*) FROM car;<\/p><p>SELECT count(*) FROM location;<\/p><p>delete FROM car WHERE immat = &#039;11TR62\u2032;<\/p><p>\u2014 4<\/p><p>#\u2013Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`loueur`.`location`, CONSTRAINT `immat` FOREIGN KEY (`immat`) REFERENCES `car` (`immat`) ON DELETE NO ACTION ON UPDATE NO ACTION)<\/p><p>\u2014 5<\/p><p>alter table location drop foreign key immatLocation;<\/p><p>alter table location add CONSTRAINT immatLocation\u00a0<\/p><p>FOREIGN KEY (immat) REFERENCES car (immat)\u00a0<\/p><p>\u00a0 \u00a0 ON DELETE CASCADE ON UPDATE NO ACTION;<\/p><p>\u2014 6,7,8<\/p><p>delete FROM car WHERE immat = &#039;11TR62\u2032;<\/p><p>SELECT count(*) FROM car;<\/p><p>SELECT count(*) FROM location;<\/p><p>\u2014 9, 10, 11, 12<\/p><p>delete FROM car;<\/p><p>drop customer table;<\/p><p>#\u2013 Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails<\/p><p>drop table rental;<\/p><p>drop customer table;<\/p><p>\u2014 13<\/p><p>drop table maintenance;<\/p><p>drop table car;<\/p><p>drop table owner;<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p><p># LOAD DATA FILE<\/p><p>load data local infile &#039;\/temp\/locationComplements.csv&#039;<\/p><p>into table location<\/p><p>fields terminated by &#039;;&#039;<\/p><p>lines terminated by &#039;\\n&#039;;<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p><p>\u2014 to allow massive data loading<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p><p>\u2014 show global variables like &#039;local\\infile&#039;;<\/p><p>\u2014 set global local\\infile = &#039;ON&#039;;<\/p><p>\u2014 \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/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-f237539 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"f237539\" 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-87f05d2\" data-id=\"87f05d2\" 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-7e4c88c elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"7e4c88c\" 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-855d21c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"855d21c\" 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-71d9bfd\" data-id=\"71d9bfd\" 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-0cf1025 elementor-widget elementor-widget-heading\" data-id=\"0cf1025\" 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-912cdef elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"912cdef\" 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-e7b8947\" data-id=\"e7b8947\" 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-580b87a elementor-widget elementor-widget-text-editor\" data-id=\"580b87a\" 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>Creating a user and assigning access rights to the database<\/p><ol><li>Create a user with name &#039;superbozo&#039; (password &#039;user&#039;): CREATE USER &#039;superbozo&#039;@&#039;localhost&#039; IDENTIFIED BY &#039;123&#039;;<\/li><li>Give him all the rights to the comic GRANT ALL ON renter.* TO &#039;superbozo&#039;@&#039;localhost&#039;;<\/li><li>Create a user with name &#039;bozo&#039; (password &#039;user&#039;)<\/li><li>Give it only read access to the location table (SELECT)<\/li><li>Reconnect under the name bozo and try to insert a new owner (pseudo bozo, code P01, email bozo@gmail.com, city Paris, year 2017).<\/li><li>Log in again as root<\/li><li>Revoke for superbozo the rights allowing it to delete tuples (DELETE): REVOKE.<\/li><li>View the rights of `bozo&#039;, &#039;superbozo&#039; and the current user<\/li><li>Remove users bozo and superbozo: DROP USER \u2026<\/li><li>View the list of users (from the mysql.user table)<\/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-299a9d4 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"299a9d4\" 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-029953f\" data-id=\"029953f\" 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-2121f0e elementor-widget elementor-widget-toggle\" data-id=\"2121f0e\" 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-3471\" class=\"elementor-tab-title\" data-tab=\"1\" role=\"button\" aria-controls=\"elementor-tab-content-3471\" 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-3471\" class=\"elementor-tab-content elementor-clearfix\" data-tab=\"1\" role=\"region\" aria-labelledby=\"elementor-tab-title-3471\"><p>create user &#039;superbozo&#039;@&#039;localhost&#039; identified by &#039;123&#039;;<br \/>grant all on loueur.* to &#039;superbozo&#039;@&#039;localhost&#039;;<\/p><p>\u2014<br \/>create user &#039;bozo&#039;@&#039;localhost&#039; identified by &#039;user&#039;;<br \/>grant SELECT on loueur.location to &#039;bozo&#039;@&#039;localhost&#039;;<\/p><p>\u2014<br \/>revoke delete on loueur.location FROM &#039;superbozo&#039;@&#039;localhost&#039;;<br \/>show grants for &#039;superbozo&#039;@&#039;localhost&#039;;<br \/>show grants for &#039;bozo&#039;@&#039;localhost&#039;;<br \/>show grants for current_user;<br \/>show grants;<\/p><p>\u2014<br \/>drop user &#039;bozo&#039;@&#039;localhost&#039;;<br \/>drop user &#039;superbozo&#039;@&#039;localhost&#039;;<br \/>SELECT * FROM mysql.user;<br \/>SELECT user, host FROM mysql.user order by user;<\/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<\/div>","protected":false},"excerpt":{"rendered":"<p>Software analysis Home page Wiki Corrected SQL exercises for beginners These corrected SQL exercises for beginners are aimed, as the name suggests, at people who are not initiated into\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-19897","page","type-page","status-publish","hentry"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/19897","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=19897"}],"version-history":[{"count":10,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/19897\/revisions"}],"predecessor-version":[{"id":20500,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/19897\/revisions\/20500"}],"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=19897"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}