{ "cells": [ { "cell_type": "markdown", "id": "88f7253c-9f21-4600-916b-0cf95eb2bc9a", "metadata": { "tags": [] }, "source": [ "# SQL exercise – Conditional queries\n", "\n", "The questions in this exercise are about the syntax of SQL queries.\n", "\n", "We will use:\n", "* the QSpatiaLite stork database that we used earlier;\n", "* SQL to write queries on this database;\n", "* the programming language Python to query this database (execute the queries).\n", "\n", "
\n", "
\n", " How to use this notebook?
\n", " This notebook consists of text cells and code cells (Python). The code cells must be executed in order to see the output of the program.
To execute a cell, select it and then simply click on the \"play\" button (►) in the toolbar at the top of the notebook, or type shift + enter.
It is important to run the code cells in the same order as they appear in the notebook.\n", "
\n" ] }, { "cell_type": "markdown", "id": "329266d4-d6a6-4288-afa5-f09f1b49b3b6", "metadata": { "tags": [] }, "source": [ "---\n", "---" ] }, { "cell_type": "markdown", "id": "f729f7c4-8f0d-4257-b630-f2196d029404", "metadata": { "tags": [] }, "source": [ "# Accessing the database with Python\n", "\n", "In this first part, we show you with an example how to write and execute your queries on the stork database with Python." ] }, { "cell_type": "markdown", "id": "2618b301-9b25-424a-85be-b5926f4c1e59", "metadata": { "tags": [] }, "source": [ "## Importing Python libraries\n", "To manipulate a database with Python we need the `sqlite3` and `pandas` libraries:" ] }, { "cell_type": "code", "execution_count": null, "id": "471250f8-7def-4004-8b0f-87d62cd225f7", "metadata": {}, "outputs": [], "source": [ "# Importing libraries\n", "import sqlite3\n", "import pandas" ] }, { "cell_type": "markdown", "id": "cd0f126e-7ee4-4679-af77-c6d7c1500731", "metadata": {}, "source": [ "## Connection to the database \n", "First, a connection to the database is established:" ] }, { "cell_type": "code", "execution_count": null, "id": "5fa025fa-c7ef-416c-8949-09c4c99166a7", "metadata": {}, "outputs": [], "source": [ "# Establish the connection to the database:\n", "con = sqlite3.connect(\"stork-mooc.sqlite\")" ] }, { "cell_type": "markdown", "id": "256ac196-c886-4fac-a85d-312a5e8cea7e", "metadata": {}, "source": [ "## Executing SQL queries\n", "\n", "To execute a SQL query on our database with Python, we use the `read_sql_query` function from the `pandas` library which :\n", "* executes the query\n", "* returns the result table\n", "To display this table, you just have to do a `display`.\n", "\n", "Here is an example, which executes the query `select * from migration` and displays the result:" ] }, { "cell_type": "code", "execution_count": null, "id": "3712f3c8-027f-4492-bffc-7d1d70eb8400", "metadata": {}, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\"\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "a8110b94-caae-442e-9f72-6aca5fe084c7", "metadata": {}, "source": [ "
\n", "\n", "**Question** : modify the above query to retrieve the contents of the `stork` table." ] }, { "cell_type": "markdown", "id": "92a6c39d-b5a6-4510-8043-e6cea497bcae", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "b91caccd-0777-4dca-8dba-a73e9a11f807", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`select * from stork`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "5403e1d0-7cf3-4362-8d9c-6947dbaf3f45", "metadata": {}, "source": [ "---\n", "---" ] }, { "cell_type": "markdown", "id": "277ba881-3bc5-4f0c-87e3-95e9ad56e7db", "metadata": {}, "source": [ "# Exercises with SQL\n", "\n", "For each of the following questions, **give the SQL syntax for the requested queries** and **test your queries.** \n", "\n", "As a reminder, here is the UML diagram of our stork database. \n", "\n", "\"UML" ] }, { "cell_type": "markdown", "id": "4aa2d382-244c-456d-a829-ef66f5b31d29", "metadata": {}, "source": [ "---\n", "\n", "## Question 1\n", "**Question** : Give the names of all storks who were born in 2001.\n", "\n", "Here is how to test your query: \n", "1. Edit the query in the cell below and then execute the cell to see the result. \n", "You can edit and re-execute the cell as many times as necessary. \n", "2. Once you are satisfied with your query, you can execute the validation cell below to check if your query is correct or not. \n", "3. Finally, you can compare your query with the solution." ] }, { "cell_type": "code", "execution_count": null, "id": "4ce7da44-0f13-431e-8e4a-0135783dec0c", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "72a6b65e-0da5-4ed0-a5bd-54d3f9a9515d", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "839e19f7-bdc8-494d-9fae-b41e3035e4a1", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question1_validation(query)" ] }, { "cell_type": "markdown", "id": "32614c72-109d-4e7c-9b6c-0d4ea6e9edfd", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "3248b00b-42fc-4043-849d-ce206ef3d94e", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`Select stork.name from stork where Stork.yearofbirth =2001`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "5ded8691-5cc7-4ffd-8e7a-8b9e68ec9b28", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 2\n", "\n", "**Question** : Give the names of all storks who were born between 1999 and 2001.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "1a628b1f-3039-4e1a-b6b2-f7b1fe8f608b", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "ce865e3f-7d86-448e-a4b4-d48ec87cc90d", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "b60cc09c-cbe5-4055-bdbf-dff6ef205faa", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question2_validation(query)" ] }, { "cell_type": "markdown", "id": "9836d9ff-5de5-4c03-ade5-499dd095dc45", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "74758991-7c8a-49be-8c23-d99f3505b03b", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`Select stork.name from stork where Stork.yearofbirth between 1999 and 2001`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "baf8ea9d-52c7-4f09-b2da-f7a2350edb84", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 3\n", "\n", "**Question** : Give the names of all storks whose names finish with an \"a\"." ] }, { "cell_type": "code", "execution_count": null, "id": "a692c23c-2930-4382-8fcf-e9ddf20598b4", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "4ea4d75e-15f8-41c5-82a0-bae894f20cee", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "edd89993-4524-4260-ae45-c03dcef23817", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question3_validation(query)" ] }, { "cell_type": "markdown", "id": "8fe88b53-9c96-4d53-b3f0-b364d048255b", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "e4b270be-9464-4b53-8dc9-ae0b6d45f30f", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`Select stork.name from stork where Stork.name like '%a'`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "2181501d-9609-45db-add3-11fef5627712", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 4\n", "\n", "**Question** : Give the names of all storks whose names contain either a \"p\" or an \"s\"." ] }, { "cell_type": "code", "execution_count": null, "id": "21138ba1-2cec-447a-a76a-88806e636c22", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "0c092ac8-da93-434e-b3d3-9229f6a6f8bd", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "6f6fa35d-2308-4d9f-91ad-3b849f122479", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question4_validation(query)" ] }, { "cell_type": "markdown", "id": "dd4c5fb5-69a0-4395-a9ad-c4fa7e5dc483", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "4b0e5608-1b34-47ca-9e4e-eac6f9fb944b", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`Select stork.name from stork where Stork.name like '%p%' or Stork.name like '%s%'`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "43863949-1e0d-4dee-9f08-f560700677a5", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 5\n", "\n", "**Question** : Give the names of all storks whose names contain either an \"a\" or an \"i\", but do not contain an \"e\"." ] }, { "cell_type": "code", "execution_count": null, "id": "f288bc46-692b-46b6-a424-19d90e3476bd", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "90ccae8d-2b3b-4e44-b443-6cc556df1179", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "4aaf1326-f94a-4527-b334-266e5989238f", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question5_validation(query)" ] }, { "cell_type": "markdown", "id": "3a38f8f3-c3a7-4c17-9ee2-5bd61df4f3b6", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "e3030f35-7b38-4be0-852b-b1dff7635117", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`Select stork.name from stork where (Stork.name like '%a%' or Stork.name like '%i%') and Stork.name not like '%e%'`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "dbe64f35-bfbc-46df-8ee3-439e94c17738", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 6\n", "\n", "**Question** : List the storks who have a sponsor along with the name of their sponsor (use only the WHERE clause, do not use JOIN yet!)" ] }, { "cell_type": "code", "execution_count": null, "id": "f510aa9b-259d-44a8-b7dc-1a3b0513f4d0", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "ae1bd125-f379-4720-9e72-e50e959da3cc", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "524ed0f6-8363-4491-9d12-929eb3bcf9e4", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question6_validation(query)" ] }, { "cell_type": "markdown", "id": "86da7b5c-9810-483e-962c-0b4cea076e8d", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "00228895-ea26-4dab-949a-409057b9a8e2", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`Select MemberStork.name , Member.name from MemberStork, Member where MemberStork.memberid = Member.id`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "3ec79f57-622d-4d8c-96cc-99d99b198656", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 7\n", "\n", "**Question** : List all of the storks and the name of their sponsor (this time, use the JOIN clause). " ] }, { "cell_type": "code", "execution_count": null, "id": "de57ea19-6d96-483e-b154-589c08e94ebf", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "f5b758ef-eab2-4f5b-bebf-5f6deca42053", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "92ed5890-138a-43c8-9a9e-d13d10de731c", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question7_validation(query)" ] }, { "cell_type": "markdown", "id": "d6f8edcd-befc-4bb5-b91e-7f98901a2fc5", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "8c21b02a-1318-4d62-9662-ce51158a0edf", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`select MemberStork.name, Member.name from MemberStork JOIN Member ON MemberStork.memberid = Member.id`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "af3a4574-75c2-468d-9981-4f55321fc320", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 8\n", "\n", "**Question** : Provide the query that returns the name of the stork that does not have a sponsor. " ] }, { "cell_type": "code", "execution_count": null, "id": "66b863b8-4df6-4434-973d-444223160e93", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Give the query to execute\n", "query = \"select * from migration\" # TODO: MODIFY THE QUERY HERE\n", "\n", "# Execute the query and get the result table\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Display the result table\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "7c119b3e-1c54-4510-b475-9a27ab9277f6", "metadata": {}, "source": [ "**Validation** : Execute the following cell to see if your query is correct or not. \n", "Read carefully the error messages to correct your query." ] }, { "cell_type": "code", "execution_count": null, "id": "ab671537-d049-46a0-9285-b1b5e6f2207c", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_conditions import *\n", "question8_validation(query)" ] }, { "cell_type": "markdown", "id": "2cc5e261-d094-4989-a3d9-b134ac7a9e40", "metadata": { "tags": [] }, "source": [ "**Solution** : Click on the three small dots \"...\" below." ] }, { "cell_type": "markdown", "id": "c42af5c5-d028-48ba-bbf8-86445795da9b", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Here is the correct query:\n", " \n", "`SELECT Stork.name FROM Stork LEFT JOIN MemberStork ON Stork.name = MemberStork.name where MemberStork.name is null`\n", " \n", "
\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.10" } }, "nbformat": 4, "nbformat_minor": 5 }