{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "3JhHIQqNe4Qs" }, "source": [ "# Lecture 11 - Databases and SQL" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[![View notebook on Github](https://img.shields.io/static/v1.svg?logo=github&label=Repo&message=View%20On%20Github&color=lightgrey)](https://github.com/avakanski/Fall-2024-Applied-Data-Science-with-Python/blob/main/docs/Lectures/Theme_2-Data_Engineering/Lecture_11-SQL/Lecture_11-SQL.ipynb)\n", "[![Open In Collab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/avakanski/Fall-2024-Applied-Data-Science-with-Python/blob/main/docs/Lectures/Theme_2-Data_Engineering/Lecture_11-SQL/Lecture_11-SQL.ipynb) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "metadata": { "id": "iEkmemKte4Qv" }, "source": [ "- [11.1 Introduction to SQL](#11.1-introduction-to-sql)\n", "- [11.2 Using SQLite with Python](#11.2-using-sqlite-with-python)\n", "- [11.3 Create a New Table](#11.3-create-a-new-table)\n", "- [11.4 Database Example](#11.4-database-example) \n", "- [11.5 Querying Databases with SELECT](#11.5-querying-databases-with-select) \n", "- [11.6 Sorting Data with ORDER BY](#11.6-sorting-data-with-order-by) \n", "- [11.7 Filtering Data](#11.7-filtering-data) \n", "- [11.8 Conditional Expressions](#11.8-conditional-expressions) \n", "- [11.9 Joining Multiple Tables](#11.9-joining-multiple-tables) \n", "- [11.10 Return Data Statistics](#11.10-return-data-statistics) \n", "- [11.11 Grouping Data](#11.11-grouping-data) \n", "- [11.12 Modifying Data](#11.12-modifying-data) \n", "- [11.13 Working with Tables](#11.13-working-with-tables) \n", "- [11.14 Constraints](#11.14-constraints) \n", "- [11.15 Subqueries](#11.15-subqueries)\n", "- [11.16 Connect to an Existing Database](#11.16-connect-to-an-existing-database) \n", "- [References](#references)" ] }, { "cell_type": "markdown", "metadata": { "id": "gTFgm_bJe4Qw" }, "source": [ "\n", "\n", "## 11.1 Introduction to SQL" ] }, { "cell_type": "markdown", "metadata": { "id": "UFMFGCdwiw5k" }, "source": [ "**SQL (Structured Query Language)** is a programming language designed for managing data in Relational Data Base Management Systems (RDBMS), or for stream processing in Relational Data Stream Management Systems (RDSMS). A *relational database* is a database that stores related information across multiple tables, and allows to query information in more than one table at the same time. Within a table, the data is organized in a tabular format with rows and columns. \n", "\n", "SQL was initially developed in 1970, and since then different companies and vendors implemented SQL in their products with some variations. To bring greater conformity in the variants of SQL, the American National Standards Institute (ANSI) published the first SQL standard in 1986. The standard has been updated every few years since then. \n", "\n", "Today, there are several variants of SQL for database management systems available, some of which were developed by companies such as IBM and Oracle, as well as there are variants developed by communities, such as MySQL, PostgreSQL, MariaDB, etc. Although these variants of SQL have certain differences, they are based on the basic SQL syntax, and are quite similar.\n", "\n", "The main advantages of SQL include standardized syntax (since all relational database systems have an SQL query interpreter built-in), and is easy-to-understand due to using English-like commands and functions. " ] }, { "cell_type": "markdown", "metadata": { "id": "jzecVPPpneyL" }, "source": [ "### Relational Databases\n", "\n", "**Relational databases** store information in multiple tables, which allows to work with more complex data, and have flexibility in the way the data is organized. An example is shown in the next figure, where a database is shown that is used for managing the HR data of a small business. \n", "\n", "This database has seven tables:\n", "\n", "- Jobs table stores data related to job title and salary range.\n", "- Employees table stores the data of employees.\n", "- Dependents table stores the employee’s dependents.\n", "- Departments table stores department data.\n", "- Regions table stores the data of regions such as Asia, Europe, America, Middle East, and Africa. \n", "- Countries table stores the data of countries where the company is doing business.\n", "- Locations table stores the location of the departments of the company.\n", "\n", "Each table contains many records with rows and columns (similar to an Excel spreadsheet), and the records have relationships across the tables. Using multiple tables in a relational database allows us to avoid duplication of information, in comparison to using a single table to store all information. Also, it provides flexibility in how we work with the data. To establish relationships between the records in different tables we need to use an ID or identifier for each employee. The identifier for each employee, or in general for each record (row) in a relational database, is referred to as *primary key*. For instance, each employee can be assigned an ID value (such as employee 162), and each table would have an ID column (primary key column) to establish the relationship with the other tables in the database. \n", "\n", "\n", "\n", "Figure source: Reference [1]." ] }, { "cell_type": "markdown", "metadata": { "id": "_xVBgXO5uQkn" }, "source": [ "### SQL for Data Science\n", "\n", "SQL is a very important tool for data scientists, data analysts, developers, and database administrators. In particular, as many companies become data-driven, SQL becomes an essential tool for handling data stored in databases and performing various data analytics operations, such as calculating data statistics, updating records, removing duplicate columns, calculating correlations between records, and similar. \n", "\n", "### SQL versus Pandas\n", "\n", "SQL has similarities with the Pandas library, as it offers similar functionality to Pandas, which includes data manipulation over rows and columns, data merging, grouping, dealing with missing values, and similar. However, Pandas is not a relational database management library, but it is a data frame library. \n", "\n", "Still, Pandas offers additional functions and flexibility for handling and manipulating tabular data, and many users download databases to their local machine, and afterward use Pandas for data processing, rather than using SQL to process the data on the server.\n", "\n", "The benefits of using SQL over Pandas can depend on the task. Several considerations include:\n", "\n", "- In the case of a large database of information (e.g., GigaBytes of data), downloading the database to the local machine to be processed by Pandas may be slow or infeasible. Pandas is more suitable for processing small to medium size databases in Python. \n", "- Even if the user can download the data on the local machine for processing with Pandas, it may be required to apply some level of preprocessing or organizing the data on the server using SQL.\n", "- Some tasks can require that the data processing is done in the existing database. Also, when the tasks require fast data retrieval and processing, SQL can be more efficient than Pandas. \n", "\n", "\n", "### SQLite\n", "\n", "In this lecture, we will use **SQLite** which implements a self-contained, serverless SQL database engine. SQLite is lightweight in terms of setup and required recourses. Unlike most other SQL variants, SQLite does not have a separate server process, and it reads and writes directly to disk files. That is, it does not use the client/server model. Because it has no server managing access to it, SQLite is not suitable in multiuser environments where multiple people can simultaneously edit files." ] }, { "cell_type": "markdown", "metadata": { "id": "zvfWf2LnS5rS" }, "source": [ "## 11.2 Using SQLite with Python \n", "\n", "To demonstrate the use of SQLite with Python, in this lecture we will use *magic commands* in Jupyter Notebook. Magic commands are special commands which are not valid Python code, but perform certain actions in a Jupyter Notebook. They begin with the `%` symbol. \n", "\n", "The library `ipython-sql` offers the magic functions `%sql` and `%%sql`, which allow to connect to a database and use standard SQL commands in Jupyter Notebooks. To run `ipython-sql` on your computer, it needs to be installed (e.g., by `pip install ipython-sql`). If we run the notebooks on Google Colab, `ipython-sql` comes preinstalled. \n", "\n", "To load the `ipython-sql` library we will use `%load_ext sql` as in the next code. `%load_ext` is a magic command that loads an external package that can add new magic commands. " ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "id": "hMeJOyz1K1mA" }, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The magic command `%sql` is used to execute an SQL query that is contained in a single statement in a Jupyter notebook cell, and `%%sql` allows to execute an SQL query that is contained in multiple SQL statements in a single cell." ] }, { "cell_type": "markdown", "metadata": { "id": "weOcgGdx0PwM" }, "source": [ "## 11.3 Create a New Table \n", "\n", "To create a new table we will use the SQL command `CREATE TABLE`, as shown in the next cell. If the table already exists in the database, an error message will show up. \n", "\n", "In order to establish a connection to the newly created table, we used `%%sql sqlite://` in the cell below. If we wanted to create a new table in an existing database to which we have already established a connection, we could have used only the magic command `%%sql`.\n", "\n", "SQL has many commands or keywords that have special meaning, such as SELECT, INSERT, DELETE, and these keywords cannot be used as names of tables, columns, or other objects. \n", "\n", "To make SQL language more readable, it is a convention to write the SQL commands with uppercase letters, and the other variables and identifiers with lowercase letters. However, this is not required, as the SQL commands are not case-sensitive.\n", "\n", "Let's create a table called `cars` which has 3 columns: `id`, `name`, and `price`. In the cell below, we specify that the values of `id` and `price` columns are integers, denoted by the `INTEGER` keyword. The names in the `name` column have a `TEXT` type. We also specified with `NOT NULL` that the values should not be missing in the `id` and `name` columns, i.e., when we insert data into the table we have to specify the values for the `NON NULL` columns. \n", "\n", "Each table can have only one **PRIMARY KEY** column that uniquely identifies each row in the table, and prevents from inserting duplicate rows in the table. For the table below we set *PRIMARY KEY* to the `id` column. It is not required to define a primary key, however, it is a good practice to do it for every table. " ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 12, "status": "ok", "timestamp": 1668546497442, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "oHRu6dxfafde", "outputId": "371a0d07-a3da-4067-bf9b-5621dbf1c15e" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql sqlite://\n", "CREATE TABLE cars(\n", " id INTEGER NOT NULL PRIMARY KEY,\n", " name TEXT NOT NULL, \n", " price INTEGER);" ] }, { "cell_type": "markdown", "metadata": { "id": "MpiDVSoLdDIM" }, "source": [ "The table that we just created is empty, and to add data to the table we will use the `INSERT` statement. In each row we provide values for `id`, `name`, and `price`.\n", "\n", "When using multiple statements in SQL, the statements in each line need to be separated with a semicolon `;`. The last statement in a cell does not have to be followed by a semicolon. \n", "\n", "Inline comments can be inserted by using two consecutive hyphens `--` that comment the rest of the line, as shown in the second line of the next cell. \n", "\n", "And also, comments that span multiple lines can be inserted by using the multiline C-style notation `/* comment */` as in the last lines in the cell.\n", "\n", "Note also that we used just the magic command `%%sql` in this cell, and we didn't need to write `%%sql sqlite://` as in the above cell. The reason is that in the above cell we used `%%sql sqlite://` to establish a connection to the newly created table. Once a connection is established, we can use only `%sql` or `%%sql` to work with the table." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 9, "status": "ok", "timestamp": 1668546497442, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "mUgz3uE-cOer", "outputId": "2e2fb078-8087-441d-e886-e3c736a0d9fd" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "INSERT INTO cars VALUES(1,'Audi',52642); --Two consecutive hyphens (--) comment the rest of the line\n", "INSERT INTO cars VALUES(2,'Mercedes',57127);\n", "INSERT INTO cars VALUES(3,'Skoda',9000);\n", "INSERT INTO cars VALUES(4,'Volvo',29000);\n", "INSERT INTO cars VALUES(5,'Bentley',350000);\n", "INSERT INTO cars VALUES(6,'Citroen',21000);\n", "INSERT INTO cars VALUES(7,'Hummer',41400);\n", "INSERT INTO cars VALUES(8,'Volkswagen',21600);\n", "/* A comment that spans\n", "more than one line */" ] }, { "cell_type": "markdown", "metadata": { "id": "R3JOV7fMdfAY" }, "source": [ "We can display the table with the following code. Notice again that we used a single `%` in the magic command `%sql`, since we have only one line of code. " ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 242 }, "executionInfo": { "elapsed": 272, "status": "ok", "timestamp": 1668546497709, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "D5eyf0LwcOgs", "outputId": "d04ec49c-6e96-4d33-e32a-16812520a2ad" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idnameprice
1Audi52642
2Mercedes57127
3Skoda9000
4Volvo29000
5Bentley350000
6Citroen21000
7Hummer41400
8Volkswagen21600
" ], "text/plain": [ "[(1, 'Audi', 52642),\n", " (2, 'Mercedes', 57127),\n", " (3, 'Skoda', 9000),\n", " (4, 'Volvo', 29000),\n", " (5, 'Bentley', 350000),\n", " (6, 'Citroen', 21000),\n", " (7, 'Hummer', 41400),\n", " (8, 'Volkswagen', 21600)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * from cars" ] }, { "cell_type": "markdown", "metadata": { "id": "zn1-QPcUdnY5" }, "source": [ "### Another Example of Creating a Table\n", "\n", "In the next simple example, we will create another table called `writer`, with columns `FirstName`, `LastName`, and `Year`. " ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 40, "status": "ok", "timestamp": 1668546497710, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "M-CrMJ3w0PwT", "outputId": "9a4bbc66-0785-4f28-fae1-46da2384bd36" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql sqlite://\n", "CREATE TABLE writer(\n", " FirstName TEXT NOT NULL,\n", " LastName TEXT NOT NULL, \n", " Year INTEGER NOT NULL PRIMARY KEY);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the `INSERT` statement, we can also include the column names after the table name, as in `writer (FirstName,LastName,Year)`. This is not required, but it can reduce errors in entering the values for each column." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 32, "status": "ok", "timestamp": 1668546497711, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "OCNN60k90PwT", "outputId": "27303181-9606-4751-b5f8-9dfca42c96af" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "INSERT INTO writer (FirstName,LastName,Year) VALUES ('William', 'Shakespeare', 1616);\n", "INSERT INTO writer (FirstName,LastName,Year) VALUES ('Lin', 'Han', 1996);\n", "INSERT INTO writer (FirstName,LastName,Year) VALUES ('Peter', 'Brecht', 1978);" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 137 }, "executionInfo": { "elapsed": 28, "status": "ok", "timestamp": 1668546497711, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "qWFyb8PZ0PwU", "outputId": "93f9a888-a958-43e3-ace6-c5bb0003681f" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FirstNameLastNameYear
WilliamShakespeare1616
PeterBrecht1978
LinHan1996
" ], "text/plain": [ "[('William', 'Shakespeare', 1616),\n", " ('Peter', 'Brecht', 1978),\n", " ('Lin', 'Han', 1996)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * from writer" ] }, { "cell_type": "markdown", "metadata": { "id": "1QQ4GF4ZCppY" }, "source": [ "## 11.4 Database Example " ] }, { "cell_type": "markdown", "metadata": { "id": "E-5QpeTmCy_o" }, "source": [ "As an example of a database, let's create a database that was shown in the above section, related to managing the HR data of a small business. \n", "\n", "The cells below first create the tables (recall that the database has 7 tables), and afterward the information for each table is inserted." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 22, "status": "ok", "timestamp": 1668546497711, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "0MdRe3Q4DXx3", "outputId": "d01d5e76-77f4-47cb-b035-8189ef7ca657" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n", "Done.\n", "Done.\n", "Done.\n", "Done.\n", "Done.\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql sqlite://\n", "\n", "CREATE TABLE regions (\n", " region_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n", " region_name TEXT NOT NULL);\n", "\n", "CREATE TABLE countries (\n", " country_id TEXT NOT NULL,\n", " country_name TEXT NOT NULL,\n", " region_id INTEGER NOT NULL,\n", " PRIMARY KEY (country_id ASC),\n", " FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE);\n", "\n", "CREATE TABLE locations (\n", " location_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n", " street_address TEXT,\n", " postal_code TEXT,\n", " city text NOT NULL,\n", " state_province TEXT,\n", " country_id INTEGER NOT NULL,\n", " FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE);\n", "\n", "CREATE TABLE departments (\n", " department_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n", " department_name TEXT NOT NULL,\n", " location_id INTEGER NOT NULL,\n", " FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE);\n", "\n", "CREATE TABLE jobs (\n", " job_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n", " job_title TEXT NOT NULL,\n", " min_salary DOUBLE NOT NULL,\n", " max_salary DOUBLE NOT NULL);\n", "\n", "CREATE TABLE employees (\n", " employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n", " first_name TEXT,\n", " last_name TEXT NOT NULL,\n", " email TEXT NOT NULL,\n", " phone_number TEXT,\n", " hire_date TEXT NOT NULL,\n", " job_id INTEGER NOT NULL,\n", " salary DOUBLE NOT NULL,\n", " manager_id INTEGER,\n", " department_id INTEGER NOT NULL,\n", " FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,\n", " FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,\n", " FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE);\n", "\n", "CREATE TABLE dependents (\n", " dependent_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n", " first_name TEXT NOT NULL,\n", " last_name TEXT NOT NULL,\n", " relationship TEXT NOT NULL,\n", " employee_id INTEGER NOT NULL,\n", " FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE);" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 522, "status": "ok", "timestamp": 1668546498217, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "tHBt7diRDlCH", "outputId": "bc9dabb6-0d6b-4acd-a3a4-2a76ac40c1ff" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "/*Data for the table regions */\n", "INSERT INTO regions(region_id,region_name) VALUES (1,'Europe');\n", "INSERT INTO regions(region_id,region_name) VALUES (2,'Americas');\n", "INSERT INTO regions(region_id,region_name) VALUES (3,'Asia');\n", "INSERT INTO regions(region_id,region_name) VALUES (4,'Middle East and Africa');\n", "\n", "/*Data for the table countries */\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','Belgium',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','Brazil',2);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','Canada',2);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','Switzerland',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','China',3);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','Germany',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','Denmark',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','Egypt',4);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','France',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','HongKong',3);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','Israel',4);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','India',3);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','Italy',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','Japan',3);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('KW','Kuwait',4);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('MX','Mexico',2);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('NG','Nigeria',4);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('NL','Netherlands',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('SG','Singapore',3);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','United Kingdom',1);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','United States of America',2);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZM','Zambia',4);\n", "INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZW','Zimbabwe',4);\n", "\n", "/*Data for the table locations */\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK');\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');\n", "INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');\n", "\n", "/*Data for the table jobs */\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'Public Accountant',4200.00,9000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (2,'Accounting Manager',8200.00,16000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (3,'Administration Assistant',3000.00,6000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (4,'President',20000.00,40000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (5,'Administration Vice President',15000.00,30000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (6,'Accountant',4200.00,9000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (7,'Finance Manager',8200.00,16000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (8,'Human Resources Representative',4000.00,9000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (9,'Programmer',4000.00,10000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (10,'Marketing Manager',9000.00,15000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (11,'Marketing Representative',4000.00,9000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (12,'Public Relations Representative',4500.00,10500.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (13,'Purchasing Clerk',2500.00,5500.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (14,'Purchasing Manager',8000.00,15000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (15,'Sales Manager',10000.00,20000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (16,'Sales Representative',6000.00,12000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (17,'Shipping Clerk',2500.00,5500.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (18,'Stock Clerk',2000.00,5000.00);\n", "INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (19,'Stock Manager',5500.00,8500.00);\n", "\n", "/*Data for the table departments */\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'Administration',1700);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (2,'Marketing',1800);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (3,'Purchasing',1700);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (4,'Human Resources',2400);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (5,'Shipping',1500);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (6,'IT',1400);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (7,'Public Relations',2700);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (8,'Sales',2500);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (9,'Executive',1700);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (10,'Finance',1700);\n", "INSERT INTO departments(department_id,department_name,location_id) VALUES (11,'Accounting',1700);\n", "\n", "/*Data for the table employees */\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (100,'Steven','King','steven.king@sqltutorial.org','515.123.4567','1987-06-17',4,24000.00,NULL,9);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (101,'Neena','Kochhar','neena.kochhar@sqltutorial.org','515.123.4568','1989-09-21',5,17000.00,100,9);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (102,'Lex','De Haan','lex.de haan@sqltutorial.org','515.123.4569','1993-01-13',5,17000.00,100,9);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (103,'Alexander','Hunold','alexander.hunold@sqltutorial.org','590.423.4567','1990-01-03',9,9000.00,102,6);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (104,'Bruce','Ernst','bruce.ernst@sqltutorial.org','590.423.4568','1991-05-21',9,6000.00,103,6);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (105,'David','Austin','david.austin@sqltutorial.org','590.423.4569','1997-06-25',9,4800.00,103,6);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (106,'Valli','Pataballa','valli.pataballa@sqltutorial.org','590.423.4560','1998-02-05',9,4800.00,103,6);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (107,'Diana','Lorentz','diana.lorentz@sqltutorial.org','590.423.5567','1999-02-07',9,4200.00,103,6);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (108,'Nancy','Greenberg','nancy.greenberg@sqltutorial.org','515.124.4569','1994-08-17',7,12000.00,101,10);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (109,'Daniel','Faviet','daniel.faviet@sqltutorial.org','515.124.4169','1994-08-16',6,9000.00,108,10);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (110,'John','Chen','john.chen@sqltutorial.org','515.124.4269','1997-09-28',6,8200.00,108,10);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (111,'Ismael','Sciarra','ismael.sciarra@sqltutorial.org','515.124.4369','1997-09-30',6,7700.00,108,10);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (112,'Jose Manuel','Urman','jose manuel.urman@sqltutorial.org','515.124.4469','1998-03-07',6,7800.00,108,10);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (113,'Luis','Popp','luis.popp@sqltutorial.org','515.124.4567','1999-12-07',6,6900.00,108,10);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (114,'Den','Raphaely','den.raphaely@sqltutorial.org','515.127.4561','1994-12-07',14,11000.00,100,3);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (115,'Alexander','Khoo','alexander.khoo@sqltutorial.org','515.127.4562','1995-05-18',13,3100.00,114,3);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (116,'Shelli','Baida','shelli.baida@sqltutorial.org','515.127.4563','1997-12-24',13,2900.00,114,3);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (117,'Sigal','Tobias','sigal.tobias@sqltutorial.org','515.127.4564','1997-07-24',13,2800.00,114,3);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (118,'Guy','Himuro','guy.himuro@sqltutorial.org','515.127.4565','1998-11-15',13,2600.00,114,3);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (119,'Karen','Colmenares','karen.colmenares@sqltutorial.org','515.127.4566','1999-08-10',13,2500.00,114,3);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (120,'Matthew','Weiss','matthew.weiss@sqltutorial.org','650.123.1234','1996-07-18',19,8000.00,100,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (121,'Adam','Fripp','adam.fripp@sqltutorial.org','650.123.2234','1997-04-10',19,8200.00,100,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (122,'Payam','Kaufling','payam.kaufling@sqltutorial.org','650.123.3234','1995-05-01',19,7900.00,100,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (123,'Shanta','Vollman','shanta.vollman@sqltutorial.org','650.123.4234','1997-10-10',19,6500.00,100,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (126,'Irene','Mikkilineni','irene.mikkilineni@sqltutorial.org','650.124.1224','1998-09-28',18,2700.00,120,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (145,'John','Russell','john.russell@sqltutorial.org',NULL,'1996-10-01',15,14000.00,100,8);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (146,'Karen','Partners','karen.partners@sqltutorial.org',NULL,'1997-01-05',15,13500.00,100,8);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (176,'Jonathon','Taylor','jonathon.taylor@sqltutorial.org',NULL,'1998-03-24',16,8600.00,100,8);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (177,'Jack','Livingston','jack.livingston@sqltutorial.org',NULL,'1998-04-23',16,8400.00,100,8);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (178,'Kimberely','Grant','kimberely.grant@sqltutorial.org',NULL,'1999-05-24',16,7000.00,100,8);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (179,'Charles','Johnson','charles.johnson@sqltutorial.org',NULL,'2000-01-04',16,6200.00,100,8);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (192,'Sarah','Bell','sarah.bell@sqltutorial.org','650.501.1876','1996-02-04',17,4000.00,123,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (193,'Britney','Everett','britney.everett@sqltutorial.org','650.501.2876','1997-03-03',17,3900.00,123,5);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (200,'Jennifer','Whalen','jennifer.whalen@sqltutorial.org','515.123.4444','1987-09-17',3,4400.00,101,1);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (201,'Michael','Hartstein','michael.hartstein@sqltutorial.org','515.123.5555','1996-02-17',10,13000.00,100,2);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (202,'Pat','Fay','pat.fay@sqltutorial.org','603.123.6666','1997-08-17',11,6000.00,201,2);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (203,'Susan','Mavris','susan.mavris@sqltutorial.org','515.123.7777','1994-06-07',8,6500.00,101,4);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (204,'Hermann','Baer','hermann.baer@sqltutorial.org','515.123.8888','1994-06-07',12,10000.00,101,7);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (205,'Shelley','Higgins','shelley.higgins@sqltutorial.org','515.123.8080','1994-06-07',2,12000.00,101,11);\n", "INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (206,'William','Gietz','william.gietz@sqltutorial.org','515.123.8181','1994-06-07',1,8300.00,205,11);\n", "\n", "/*Data for the table dependents */\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Gietz','Child',206);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (2,'Nick','Higgins','Child',205);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (3,'Ed','Whalen','Child',200);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (4,'Jennifer','King','Child',100);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (5,'Johnny','Kochhar','Child',101);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (6,'Bette','De Haan','Child',102);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (7,'Grace','Faviet','Child',109);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (8,'Matthew','Chen','Child',110);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (9,'Joe','Sciarra','Child',111);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (10,'Christian','Urman','Child',112);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (11,'Zero','Popp','Child',113);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (12,'Karl','Greenberg','Child',108);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (13,'Uma','Mavris','Child',203);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (14,'Vivien','Hunold','Child',103);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (15,'Cuba','Ernst','Child',104);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (16,'Fred','Austin','Child',105);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (17,'Helen','Pataballa','Child',106);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (18,'Dan','Lorentz','Child',107);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (19,'Bob','Hartstein','Child',201);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (20,'Lucille','Fay','Child',202);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (21,'Kirsten','Baer','Child',204);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (22,'Elvis','Khoo','Child',115);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (23,'Sandra','Baida','Child',116);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (24,'Cameron','Tobias','Child',117);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (25,'Kevin','Himuro','Child',118);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (26,'Rip','Colmenares','Child',119);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (27,'Julia','Raphaely','Child',114);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (28,'Woody','Russell','Child',145);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (29,'Alec','Partners','Child',146);\n", "INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (30,'Sandra','Taylor','Child',176);" ] }, { "cell_type": "markdown", "metadata": { "id": "Qw0BNTykEFoU" }, "source": [ "## 11.5 Querying Databases with SELECT " ] }, { "cell_type": "markdown", "metadata": { "id": "VtbDRnddUVhA" }, "source": [ "The most common SQL task is to retrieve data from one or more tables. The data is returned in the form of a result table, called *result set*. This is accomplished with the `SELECT` statement, which has the following general syntax. \n", "\n", "```\n", "SELECT \n", " column1, column2, columnN \n", "FROM \n", " table_name\n", "```\n", "\n", "To run the code in this Jupyter Notebook, we will just insert the magic commands `%sql` or `%%sql` in front of each SQL code. \n", "\n", "For example, in the next cell we retrieved the columns `employee_id, first_name, last_name, hire_date` from the `employees` table. When the statement is evaluated, the database system first evaluates the `FROM` clause and the `SELECT` clause afterward. I.e., from the table named `employees` select the listed columns.\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 43, "status": "ok", "timestamp": 1668546498219, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "kE7T_NrIiALM", "outputId": "9b6ebb17-e4a4-4e55-e535-4f0a3d635a2e" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namehire_date
100StevenKing1987-06-17
101NeenaKochhar1989-09-21
102LexDe Haan1993-01-13
103AlexanderHunold1990-01-03
104BruceErnst1991-05-21
105DavidAustin1997-06-25
106ValliPataballa1998-02-05
107DianaLorentz1999-02-07
108NancyGreenberg1994-08-17
109DanielFaviet1994-08-16
110JohnChen1997-09-28
111IsmaelSciarra1997-09-30
112Jose ManuelUrman1998-03-07
113LuisPopp1999-12-07
114DenRaphaely1994-12-07
115AlexanderKhoo1995-05-18
116ShelliBaida1997-12-24
117SigalTobias1997-07-24
118GuyHimuro1998-11-15
119KarenColmenares1999-08-10
120MatthewWeiss1996-07-18
121AdamFripp1997-04-10
122PayamKaufling1995-05-01
123ShantaVollman1997-10-10
126IreneMikkilineni1998-09-28
145JohnRussell1996-10-01
146KarenPartners1997-01-05
176JonathonTaylor1998-03-24
177JackLivingston1998-04-23
178KimberelyGrant1999-05-24
179CharlesJohnson2000-01-04
192SarahBell1996-02-04
193BritneyEverett1997-03-03
200JenniferWhalen1987-09-17
201MichaelHartstein1996-02-17
202PatFay1997-08-17
203SusanMavris1994-06-07
204HermannBaer1994-06-07
205ShelleyHiggins1994-06-07
206WilliamGietz1994-06-07
" ], "text/plain": [ "[(100, 'Steven', 'King', '1987-06-17'),\n", " (101, 'Neena', 'Kochhar', '1989-09-21'),\n", " (102, 'Lex', 'De Haan', '1993-01-13'),\n", " (103, 'Alexander', 'Hunold', '1990-01-03'),\n", " (104, 'Bruce', 'Ernst', '1991-05-21'),\n", " (105, 'David', 'Austin', '1997-06-25'),\n", " (106, 'Valli', 'Pataballa', '1998-02-05'),\n", " (107, 'Diana', 'Lorentz', '1999-02-07'),\n", " (108, 'Nancy', 'Greenberg', '1994-08-17'),\n", " (109, 'Daniel', 'Faviet', '1994-08-16'),\n", " (110, 'John', 'Chen', '1997-09-28'),\n", " (111, 'Ismael', 'Sciarra', '1997-09-30'),\n", " (112, 'Jose Manuel', 'Urman', '1998-03-07'),\n", " (113, 'Luis', 'Popp', '1999-12-07'),\n", " (114, 'Den', 'Raphaely', '1994-12-07'),\n", " (115, 'Alexander', 'Khoo', '1995-05-18'),\n", " (116, 'Shelli', 'Baida', '1997-12-24'),\n", " (117, 'Sigal', 'Tobias', '1997-07-24'),\n", " (118, 'Guy', 'Himuro', '1998-11-15'),\n", " (119, 'Karen', 'Colmenares', '1999-08-10'),\n", " (120, 'Matthew', 'Weiss', '1996-07-18'),\n", " (121, 'Adam', 'Fripp', '1997-04-10'),\n", " (122, 'Payam', 'Kaufling', '1995-05-01'),\n", " (123, 'Shanta', 'Vollman', '1997-10-10'),\n", " (126, 'Irene', 'Mikkilineni', '1998-09-28'),\n", " (145, 'John', 'Russell', '1996-10-01'),\n", " (146, 'Karen', 'Partners', '1997-01-05'),\n", " (176, 'Jonathon', 'Taylor', '1998-03-24'),\n", " (177, 'Jack', 'Livingston', '1998-04-23'),\n", " (178, 'Kimberely', 'Grant', '1999-05-24'),\n", " (179, 'Charles', 'Johnson', '2000-01-04'),\n", " (192, 'Sarah', 'Bell', '1996-02-04'),\n", " (193, 'Britney', 'Everett', '1997-03-03'),\n", " (200, 'Jennifer', 'Whalen', '1987-09-17'),\n", " (201, 'Michael', 'Hartstein', '1996-02-17'),\n", " (202, 'Pat', 'Fay', '1997-08-17'),\n", " (203, 'Susan', 'Mavris', '1994-06-07'),\n", " (204, 'Hermann', 'Baer', '1994-06-07'),\n", " (205, 'Shelley', 'Higgins', '1994-06-07'),\n", " (206, 'William', 'Gietz', '1994-06-07')]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name, hire_date \n", "FROM \n", " employees" ] }, { "cell_type": "markdown", "metadata": { "id": "XVloOe3kh8S1" }, "source": [ "If we want to query all columns in a table we can use the asterisk operator `*` instead of the columns names. " ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 40, "status": "ok", "timestamp": 1668546498219, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "hvkAE1EzhTfG", "outputId": "30b008b8-9250-46aa-9386-4b8bb67e8a84" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_nameemailphone_numberhire_datejob_idsalarymanager_iddepartment_id
100StevenKingsteven.king@sqltutorial.org515.123.45671987-06-17424000.0None9
101NeenaKochharneena.kochhar@sqltutorial.org515.123.45681989-09-21517000.01009
102LexDe Haanlex.de haan@sqltutorial.org515.123.45691993-01-13517000.01009
103AlexanderHunoldalexander.hunold@sqltutorial.org590.423.45671990-01-0399000.01026
104BruceErnstbruce.ernst@sqltutorial.org590.423.45681991-05-2196000.01036
105DavidAustindavid.austin@sqltutorial.org590.423.45691997-06-2594800.01036
106ValliPataballavalli.pataballa@sqltutorial.org590.423.45601998-02-0594800.01036
107DianaLorentzdiana.lorentz@sqltutorial.org590.423.55671999-02-0794200.01036
108NancyGreenbergnancy.greenberg@sqltutorial.org515.124.45691994-08-17712000.010110
109DanielFavietdaniel.faviet@sqltutorial.org515.124.41691994-08-1669000.010810
110JohnChenjohn.chen@sqltutorial.org515.124.42691997-09-2868200.010810
111IsmaelSciarraismael.sciarra@sqltutorial.org515.124.43691997-09-3067700.010810
112Jose ManuelUrmanjose manuel.urman@sqltutorial.org515.124.44691998-03-0767800.010810
113LuisPoppluis.popp@sqltutorial.org515.124.45671999-12-0766900.010810
114DenRaphaelyden.raphaely@sqltutorial.org515.127.45611994-12-071411000.01003
115AlexanderKhooalexander.khoo@sqltutorial.org515.127.45621995-05-18133100.01143
116ShelliBaidashelli.baida@sqltutorial.org515.127.45631997-12-24132900.01143
117SigalTobiassigal.tobias@sqltutorial.org515.127.45641997-07-24132800.01143
118GuyHimuroguy.himuro@sqltutorial.org515.127.45651998-11-15132600.01143
119KarenColmenareskaren.colmenares@sqltutorial.org515.127.45661999-08-10132500.01143
120MatthewWeissmatthew.weiss@sqltutorial.org650.123.12341996-07-18198000.01005
121AdamFrippadam.fripp@sqltutorial.org650.123.22341997-04-10198200.01005
122PayamKauflingpayam.kaufling@sqltutorial.org650.123.32341995-05-01197900.01005
123ShantaVollmanshanta.vollman@sqltutorial.org650.123.42341997-10-10196500.01005
126IreneMikkilineniirene.mikkilineni@sqltutorial.org650.124.12241998-09-28182700.01205
145JohnRusselljohn.russell@sqltutorial.orgNone1996-10-011514000.01008
146KarenPartnerskaren.partners@sqltutorial.orgNone1997-01-051513500.01008
176JonathonTaylorjonathon.taylor@sqltutorial.orgNone1998-03-24168600.01008
177JackLivingstonjack.livingston@sqltutorial.orgNone1998-04-23168400.01008
178KimberelyGrantkimberely.grant@sqltutorial.orgNone1999-05-24167000.01008
179CharlesJohnsoncharles.johnson@sqltutorial.orgNone2000-01-04166200.01008
192SarahBellsarah.bell@sqltutorial.org650.501.18761996-02-04174000.01235
193BritneyEverettbritney.everett@sqltutorial.org650.501.28761997-03-03173900.01235
200JenniferWhalenjennifer.whalen@sqltutorial.org515.123.44441987-09-1734400.01011
201MichaelHartsteinmichael.hartstein@sqltutorial.org515.123.55551996-02-171013000.01002
202PatFaypat.fay@sqltutorial.org603.123.66661997-08-17116000.02012
203SusanMavrissusan.mavris@sqltutorial.org515.123.77771994-06-0786500.01014
204HermannBaerhermann.baer@sqltutorial.org515.123.88881994-06-071210000.01017
205ShelleyHigginsshelley.higgins@sqltutorial.org515.123.80801994-06-07212000.010111
206WilliamGietzwilliam.gietz@sqltutorial.org515.123.81811994-06-0718300.020511
" ], "text/plain": [ "[(100, 'Steven', 'King', 'steven.king@sqltutorial.org', '515.123.4567', '1987-06-17', 4, 24000.0, None, 9),\n", " (101, 'Neena', 'Kochhar', 'neena.kochhar@sqltutorial.org', '515.123.4568', '1989-09-21', 5, 17000.0, 100, 9),\n", " (102, 'Lex', 'De Haan', 'lex.de haan@sqltutorial.org', '515.123.4569', '1993-01-13', 5, 17000.0, 100, 9),\n", " (103, 'Alexander', 'Hunold', 'alexander.hunold@sqltutorial.org', '590.423.4567', '1990-01-03', 9, 9000.0, 102, 6),\n", " (104, 'Bruce', 'Ernst', 'bruce.ernst@sqltutorial.org', '590.423.4568', '1991-05-21', 9, 6000.0, 103, 6),\n", " (105, 'David', 'Austin', 'david.austin@sqltutorial.org', '590.423.4569', '1997-06-25', 9, 4800.0, 103, 6),\n", " (106, 'Valli', 'Pataballa', 'valli.pataballa@sqltutorial.org', '590.423.4560', '1998-02-05', 9, 4800.0, 103, 6),\n", " (107, 'Diana', 'Lorentz', 'diana.lorentz@sqltutorial.org', '590.423.5567', '1999-02-07', 9, 4200.0, 103, 6),\n", " (108, 'Nancy', 'Greenberg', 'nancy.greenberg@sqltutorial.org', '515.124.4569', '1994-08-17', 7, 12000.0, 101, 10),\n", " (109, 'Daniel', 'Faviet', 'daniel.faviet@sqltutorial.org', '515.124.4169', '1994-08-16', 6, 9000.0, 108, 10),\n", " (110, 'John', 'Chen', 'john.chen@sqltutorial.org', '515.124.4269', '1997-09-28', 6, 8200.0, 108, 10),\n", " (111, 'Ismael', 'Sciarra', 'ismael.sciarra@sqltutorial.org', '515.124.4369', '1997-09-30', 6, 7700.0, 108, 10),\n", " (112, 'Jose Manuel', 'Urman', 'jose manuel.urman@sqltutorial.org', '515.124.4469', '1998-03-07', 6, 7800.0, 108, 10),\n", " (113, 'Luis', 'Popp', 'luis.popp@sqltutorial.org', '515.124.4567', '1999-12-07', 6, 6900.0, 108, 10),\n", " (114, 'Den', 'Raphaely', 'den.raphaely@sqltutorial.org', '515.127.4561', '1994-12-07', 14, 11000.0, 100, 3),\n", " (115, 'Alexander', 'Khoo', 'alexander.khoo@sqltutorial.org', '515.127.4562', '1995-05-18', 13, 3100.0, 114, 3),\n", " (116, 'Shelli', 'Baida', 'shelli.baida@sqltutorial.org', '515.127.4563', '1997-12-24', 13, 2900.0, 114, 3),\n", " (117, 'Sigal', 'Tobias', 'sigal.tobias@sqltutorial.org', '515.127.4564', '1997-07-24', 13, 2800.0, 114, 3),\n", " (118, 'Guy', 'Himuro', 'guy.himuro@sqltutorial.org', '515.127.4565', '1998-11-15', 13, 2600.0, 114, 3),\n", " (119, 'Karen', 'Colmenares', 'karen.colmenares@sqltutorial.org', '515.127.4566', '1999-08-10', 13, 2500.0, 114, 3),\n", " (120, 'Matthew', 'Weiss', 'matthew.weiss@sqltutorial.org', '650.123.1234', '1996-07-18', 19, 8000.0, 100, 5),\n", " (121, 'Adam', 'Fripp', 'adam.fripp@sqltutorial.org', '650.123.2234', '1997-04-10', 19, 8200.0, 100, 5),\n", " (122, 'Payam', 'Kaufling', 'payam.kaufling@sqltutorial.org', '650.123.3234', '1995-05-01', 19, 7900.0, 100, 5),\n", " (123, 'Shanta', 'Vollman', 'shanta.vollman@sqltutorial.org', '650.123.4234', '1997-10-10', 19, 6500.0, 100, 5),\n", " (126, 'Irene', 'Mikkilineni', 'irene.mikkilineni@sqltutorial.org', '650.124.1224', '1998-09-28', 18, 2700.0, 120, 5),\n", " (145, 'John', 'Russell', 'john.russell@sqltutorial.org', None, '1996-10-01', 15, 14000.0, 100, 8),\n", " (146, 'Karen', 'Partners', 'karen.partners@sqltutorial.org', None, '1997-01-05', 15, 13500.0, 100, 8),\n", " (176, 'Jonathon', 'Taylor', 'jonathon.taylor@sqltutorial.org', None, '1998-03-24', 16, 8600.0, 100, 8),\n", " (177, 'Jack', 'Livingston', 'jack.livingston@sqltutorial.org', None, '1998-04-23', 16, 8400.0, 100, 8),\n", " (178, 'Kimberely', 'Grant', 'kimberely.grant@sqltutorial.org', None, '1999-05-24', 16, 7000.0, 100, 8),\n", " (179, 'Charles', 'Johnson', 'charles.johnson@sqltutorial.org', None, '2000-01-04', 16, 6200.0, 100, 8),\n", " (192, 'Sarah', 'Bell', 'sarah.bell@sqltutorial.org', '650.501.1876', '1996-02-04', 17, 4000.0, 123, 5),\n", " (193, 'Britney', 'Everett', 'britney.everett@sqltutorial.org', '650.501.2876', '1997-03-03', 17, 3900.0, 123, 5),\n", " (200, 'Jennifer', 'Whalen', 'jennifer.whalen@sqltutorial.org', '515.123.4444', '1987-09-17', 3, 4400.0, 101, 1),\n", " (201, 'Michael', 'Hartstein', 'michael.hartstein@sqltutorial.org', '515.123.5555', '1996-02-17', 10, 13000.0, 100, 2),\n", " (202, 'Pat', 'Fay', 'pat.fay@sqltutorial.org', '603.123.6666', '1997-08-17', 11, 6000.0, 201, 2),\n", " (203, 'Susan', 'Mavris', 'susan.mavris@sqltutorial.org', '515.123.7777', '1994-06-07', 8, 6500.0, 101, 4),\n", " (204, 'Hermann', 'Baer', 'hermann.baer@sqltutorial.org', '515.123.8888', '1994-06-07', 12, 10000.0, 101, 7),\n", " (205, 'Shelley', 'Higgins', 'shelley.higgins@sqltutorial.org', '515.123.8080', '1994-06-07', 2, 12000.0, 101, 11),\n", " (206, 'William', 'Gietz', 'william.gietz@sqltutorial.org', '515.123.8181', '1994-06-07', 1, 8300.0, 205, 11)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM employees" ] }, { "cell_type": "markdown", "metadata": { "id": "96c7vRn1_oCv" }, "source": [ "### List Tables in a Database\n", "\n", "We can also use the `SELECT` statement to display a list of all tables in the current database. Every SQLite database has an `sqlite_master` table that defines the schema for the database. The schema refers to the organization or structure of a database, and includes various elements such as tables and columns names, the relationships between tables, the types of data, and constraints (primary keys and foreign keys) used in the database. \n", "\n", "The following statement uses `sqlite_master` with the `type` field set to `'table'` to return the names of all tables in the current database." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 284 }, "executionInfo": { "elapsed": 37, "status": "ok", "timestamp": 1668546498219, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "7f0samvo86cs", "outputId": "01c0cc65-1afe-4389-b05b-cefad48eead5" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
cars
writer
regions
sqlite_sequence
countries
locations
departments
jobs
employees
dependents
" ], "text/plain": [ "[('cars',),\n", " ('writer',),\n", " ('regions',),\n", " ('sqlite_sequence',),\n", " ('countries',),\n", " ('locations',),\n", " ('departments',),\n", " ('jobs',),\n", " ('employees',),\n", " ('dependents',)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT name FROM sqlite_master WHERE type='table'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The next cell displays the entire `sqlite_master` table for the current database. It stores metadata about the tables and other objects in an SQLite database, and contains the columns `type`, `name`, `tbl_name` (the name of the table to which the object belongs), `rootpage` (the root page or page number of the object within the SQL database file), and `sql` (the SQL CREATE statement that was used to create the database). If the object is a table, the `tbl_name` is the same as the `name` in `sqlite_master`. Other types of objects in SQL include index, view, and trigger objects. \n", "\n", "The `sqlite_master` table is automatically created and maintained by SQLite, and it is typically queried to inspect the schema of a database or retrieve information about its tables and other objects." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
typenametbl_namerootpagesql
tablecarscars2CREATE TABLE cars(
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
price INTEGER)
tablewriterwriter3CREATE TABLE writer(
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
Year INTEGER NOT NULL PRIMARY KEY)
tableregionsregions4CREATE TABLE regions (
region_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
region_name TEXT NOT NULL)
tablesqlite_sequencesqlite_sequence5CREATE TABLE sqlite_sequence(name,seq)
tablecountriescountries6CREATE TABLE countries (
country_id TEXT NOT NULL,
country_name TEXT NOT NULL,
region_id INTEGER NOT NULL,
PRIMARY KEY (country_id ASC),
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE)
indexsqlite_autoindex_countries_1countries7None
tablelocationslocations8CREATE TABLE locations (
location_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
street_address TEXT,
postal_code TEXT,
city text NOT NULL,
state_province TEXT,
country_id INTEGER NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE)
tabledepartmentsdepartments9CREATE TABLE departments (
department_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
department_name TEXT NOT NULL,
location_id INTEGER NOT NULL,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE)
tablejobsjobs10CREATE TABLE jobs (
job_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
job_title TEXT NOT NULL,
min_salary DOUBLE NOT NULL,
max_salary DOUBLE NOT NULL)
tableemployeesemployees11CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
first_name TEXT,
last_name TEXT NOT NULL,
email TEXT NOT NULL,
phone_number TEXT,
hire_date TEXT NOT NULL,
job_id INTEGER NOT NULL,
salary DOUBLE NOT NULL,
manager_id INTEGER,
department_id INTEGER NOT NULL,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE)
tabledependentsdependents12CREATE TABLE dependents (
dependent_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
relationship TEXT NOT NULL,
employee_id INTEGER NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE)
" ], "text/plain": [ "[('table', 'cars', 'cars', 2, 'CREATE TABLE cars(\\n id INTEGER NOT NULL PRIMARY KEY,\\n name TEXT NOT NULL, \\n price INTEGER)'),\n", " ('table', 'writer', 'writer', 3, 'CREATE TABLE writer(\\n FirstName TEXT NOT NULL,\\n LastName TEXT NOT NULL, \\n Year INTEGER NOT NULL PRIMARY KEY)'),\n", " ('table', 'regions', 'regions', 4, 'CREATE TABLE regions (\\n region_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\\n region_name TEXT NOT NULL)'),\n", " ('table', 'sqlite_sequence', 'sqlite_sequence', 5, 'CREATE TABLE sqlite_sequence(name,seq)'),\n", " ('table', 'countries', 'countries', 6, 'CREATE TABLE countries (\\n country_id TEXT NOT NULL,\\n country_name TEXT NOT NULL,\\n region_id INTEGER NOT NULL,\\n PRIMARY KEY (country_id ASC),\\n FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE)'),\n", " ('index', 'sqlite_autoindex_countries_1', 'countries', 7, None),\n", " ('table', 'locations', 'locations', 8, 'CREATE TABLE locations (\\n location_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\\n street_address TEXT,\\n postal_code TEXT,\\n city text ... (22 characters truncated) ... province TEXT,\\n country_id INTEGER NOT NULL,\\n FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE)'),\n", " ('table', 'departments', 'departments', 9, 'CREATE TABLE departments (\\n department_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\\n department_name TEXT NOT NULL,\\n location_id INTEGER NOT NULL,\\n FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE)'),\n", " ('table', 'jobs', 'jobs', 10, 'CREATE TABLE jobs (\\n job_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\\n job_title TEXT NOT NULL,\\n min_salary DOUBLE NOT NULL,\\n max_salary DOUBLE NOT NULL)'),\n", " ('table', 'employees', 'employees', 11, 'CREATE TABLE employees (\\n employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\\n first_name TEXT,\\n last_name TEXT NOT NULL,\\n email ... (339 characters truncated) ... rtment_id) ON DELETE CASCADE ON UPDATE CASCADE,\\n FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE)'),\n", " ('table', 'dependents', 'dependents', 12, 'CREATE TABLE dependents (\\n dependent_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\\n first_name TEXT NOT NULL,\\n last_name TEXT NOT NULL,\\ ... (21 characters truncated) ... T NOT NULL,\\n employee_id INTEGER NOT NULL,\\n FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE)')]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM sqlite_master" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For instance, we can use the following code to retrieve the `CREATE` statement for the table `cars`." ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sql
CREATE TABLE cars(
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
price INTEGER)
" ], "text/plain": [ "[('CREATE TABLE cars(\\n id INTEGER NOT NULL PRIMARY KEY,\\n name TEXT NOT NULL, \\n price INTEGER)',)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT sql FROM sqlite_master WHERE type='table' AND name='cars';" ] }, { "cell_type": "markdown", "metadata": { "id": "BkKksHQ1l88_" }, "source": [ "### Perform Simple Calculations in SELECT Statements\n", "\n", "We can use standard math operators such as `+`, `*`, `/`, `%` in `SELECT` statements to perform simple mathematical calculations. The following expression creates a new calculated field `salary * 1.05` from the `salary` column and adds 5% to the salary of every employee. Specifically, the code does not create a new column in the table, but instead, it adds a new calculated field in the result set of the query." ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 35, "status": "ok", "timestamp": 1668546498220, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "svQGtukx86eo", "outputId": "2d0ffb9a-4500-468b-c4bd-172caee97ce0" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namesalarysalary*1.05
100Steven24000.025200.0
101Neena17000.017850.0
102Lex17000.017850.0
103Alexander9000.09450.0
104Bruce6000.06300.0
105David4800.05040.0
106Valli4800.05040.0
107Diana4200.04410.0
108Nancy12000.012600.0
109Daniel9000.09450.0
110John8200.08610.0
111Ismael7700.08085.0
112Jose Manuel7800.08190.0
113Luis6900.07245.0
114Den11000.011550.0
115Alexander3100.03255.0
116Shelli2900.03045.0
117Sigal2800.02940.0
118Guy2600.02730.0
119Karen2500.02625.0
120Matthew8000.08400.0
121Adam8200.08610.0
122Payam7900.08295.0
123Shanta6500.06825.0
126Irene2700.02835.0
145John14000.014700.0
146Karen13500.014175.0
176Jonathon8600.09030.0
177Jack8400.08820.0
178Kimberely7000.07350.0
179Charles6200.06510.0
192Sarah4000.04200.0
193Britney3900.04095.0
200Jennifer4400.04620.0
201Michael13000.013650.0
202Pat6000.06300.0
203Susan6500.06825.0
204Hermann10000.010500.0
205Shelley12000.012600.0
206William8300.08715.0
" ], "text/plain": [ "[(100, 'Steven', 24000.0, 25200.0),\n", " (101, 'Neena', 17000.0, 17850.0),\n", " (102, 'Lex', 17000.0, 17850.0),\n", " (103, 'Alexander', 9000.0, 9450.0),\n", " (104, 'Bruce', 6000.0, 6300.0),\n", " (105, 'David', 4800.0, 5040.0),\n", " (106, 'Valli', 4800.0, 5040.0),\n", " (107, 'Diana', 4200.0, 4410.0),\n", " (108, 'Nancy', 12000.0, 12600.0),\n", " (109, 'Daniel', 9000.0, 9450.0),\n", " (110, 'John', 8200.0, 8610.0),\n", " (111, 'Ismael', 7700.0, 8085.0),\n", " (112, 'Jose Manuel', 7800.0, 8190.0),\n", " (113, 'Luis', 6900.0, 7245.0),\n", " (114, 'Den', 11000.0, 11550.0),\n", " (115, 'Alexander', 3100.0, 3255.0),\n", " (116, 'Shelli', 2900.0, 3045.0),\n", " (117, 'Sigal', 2800.0, 2940.0),\n", " (118, 'Guy', 2600.0, 2730.0),\n", " (119, 'Karen', 2500.0, 2625.0),\n", " (120, 'Matthew', 8000.0, 8400.0),\n", " (121, 'Adam', 8200.0, 8610.0),\n", " (122, 'Payam', 7900.0, 8295.0),\n", " (123, 'Shanta', 6500.0, 6825.0),\n", " (126, 'Irene', 2700.0, 2835.0),\n", " (145, 'John', 14000.0, 14700.0),\n", " (146, 'Karen', 13500.0, 14175.0),\n", " (176, 'Jonathon', 8600.0, 9030.0),\n", " (177, 'Jack', 8400.0, 8820.0),\n", " (178, 'Kimberely', 7000.0, 7350.0),\n", " (179, 'Charles', 6200.0, 6510.0),\n", " (192, 'Sarah', 4000.0, 4200.0),\n", " (193, 'Britney', 3900.0, 4095.0),\n", " (200, 'Jennifer', 4400.0, 4620.0),\n", " (201, 'Michael', 13000.0, 13650.0),\n", " (202, 'Pat', 6000.0, 6300.0),\n", " (203, 'Susan', 6500.0, 6825.0),\n", " (204, 'Hermann', 10000.0, 10500.0),\n", " (205, 'Shelley', 12000.0, 12600.0),\n", " (206, 'William', 8300.0, 8715.0)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT\n", " employee_id, first_name, salary, salary*1.05 \n", "FROM \n", " employees;" ] }, { "cell_type": "markdown", "metadata": { "id": "4oTNY6Ftm2Rz" }, "source": [ "We can use `AS new_salary` to assign a different name `new_salary` to the calculated field `salary*1.05`." ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 32, "status": "ok", "timestamp": 1668546498220, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "RQ_oeGMa86g_", "outputId": "19101010-1769-4c60-8f30-58a78ec36dfc" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namesalarynew_salary
100Steven24000.025200.0
101Neena17000.017850.0
102Lex17000.017850.0
103Alexander9000.09450.0
104Bruce6000.06300.0
105David4800.05040.0
106Valli4800.05040.0
107Diana4200.04410.0
108Nancy12000.012600.0
109Daniel9000.09450.0
110John8200.08610.0
111Ismael7700.08085.0
112Jose Manuel7800.08190.0
113Luis6900.07245.0
114Den11000.011550.0
115Alexander3100.03255.0
116Shelli2900.03045.0
117Sigal2800.02940.0
118Guy2600.02730.0
119Karen2500.02625.0
120Matthew8000.08400.0
121Adam8200.08610.0
122Payam7900.08295.0
123Shanta6500.06825.0
126Irene2700.02835.0
145John14000.014700.0
146Karen13500.014175.0
176Jonathon8600.09030.0
177Jack8400.08820.0
178Kimberely7000.07350.0
179Charles6200.06510.0
192Sarah4000.04200.0
193Britney3900.04095.0
200Jennifer4400.04620.0
201Michael13000.013650.0
202Pat6000.06300.0
203Susan6500.06825.0
204Hermann10000.010500.0
205Shelley12000.012600.0
206William8300.08715.0
" ], "text/plain": [ "[(100, 'Steven', 24000.0, 25200.0),\n", " (101, 'Neena', 17000.0, 17850.0),\n", " (102, 'Lex', 17000.0, 17850.0),\n", " (103, 'Alexander', 9000.0, 9450.0),\n", " (104, 'Bruce', 6000.0, 6300.0),\n", " (105, 'David', 4800.0, 5040.0),\n", " (106, 'Valli', 4800.0, 5040.0),\n", " (107, 'Diana', 4200.0, 4410.0),\n", " (108, 'Nancy', 12000.0, 12600.0),\n", " (109, 'Daniel', 9000.0, 9450.0),\n", " (110, 'John', 8200.0, 8610.0),\n", " (111, 'Ismael', 7700.0, 8085.0),\n", " (112, 'Jose Manuel', 7800.0, 8190.0),\n", " (113, 'Luis', 6900.0, 7245.0),\n", " (114, 'Den', 11000.0, 11550.0),\n", " (115, 'Alexander', 3100.0, 3255.0),\n", " (116, 'Shelli', 2900.0, 3045.0),\n", " (117, 'Sigal', 2800.0, 2940.0),\n", " (118, 'Guy', 2600.0, 2730.0),\n", " (119, 'Karen', 2500.0, 2625.0),\n", " (120, 'Matthew', 8000.0, 8400.0),\n", " (121, 'Adam', 8200.0, 8610.0),\n", " (122, 'Payam', 7900.0, 8295.0),\n", " (123, 'Shanta', 6500.0, 6825.0),\n", " (126, 'Irene', 2700.0, 2835.0),\n", " (145, 'John', 14000.0, 14700.0),\n", " (146, 'Karen', 13500.0, 14175.0),\n", " (176, 'Jonathon', 8600.0, 9030.0),\n", " (177, 'Jack', 8400.0, 8820.0),\n", " (178, 'Kimberely', 7000.0, 7350.0),\n", " (179, 'Charles', 6200.0, 6510.0),\n", " (192, 'Sarah', 4000.0, 4200.0),\n", " (193, 'Britney', 3900.0, 4095.0),\n", " (200, 'Jennifer', 4400.0, 4620.0),\n", " (201, 'Michael', 13000.0, 13650.0),\n", " (202, 'Pat', 6000.0, 6300.0),\n", " (203, 'Susan', 6500.0, 6825.0),\n", " (204, 'Hermann', 10000.0, 10500.0),\n", " (205, 'Shelley', 12000.0, 12600.0),\n", " (206, 'William', 8300.0, 8715.0)]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, salary, salary*1.05 AS new_salary \n", "FROM\n", " employees;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 11.6 Sorting Data with ORDER BY " ] }, { "cell_type": "markdown", "metadata": { "id": "q3OhOiA-BBP-" }, "source": [ "The clause `ORDER BY` can be used within a `SELECT` statement to sort the returned rows.\n", "\n", "The general syntax is: \n", "\n", "```\n", "SELECT \n", " column1, column2, columnN \n", "FROM\n", " table_name\n", "ORDER BY sort_expression [ASC | DESC];\n", "```\n", "\n", "The `sort_expression` specifies the sort criteria, whereas `ASC` or `DESC` indicates whether to sort the result set into ascending (default) or descending order.\n", "\n", "The following example uses the clause `ORDER BY` to sort employees by first names in alphabetical order." ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 30, "status": "ok", "timestamp": 1668546498221, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "dumPr60JK_lY", "outputId": "cb104743-c7c3-423a-ae48-602b82cce936" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namehire_datesalary
121AdamFripp1997-04-108200.0
103AlexanderHunold1990-01-039000.0
115AlexanderKhoo1995-05-183100.0
193BritneyEverett1997-03-033900.0
104BruceErnst1991-05-216000.0
179CharlesJohnson2000-01-046200.0
109DanielFaviet1994-08-169000.0
105DavidAustin1997-06-254800.0
114DenRaphaely1994-12-0711000.0
107DianaLorentz1999-02-074200.0
118GuyHimuro1998-11-152600.0
204HermannBaer1994-06-0710000.0
126IreneMikkilineni1998-09-282700.0
111IsmaelSciarra1997-09-307700.0
177JackLivingston1998-04-238400.0
200JenniferWhalen1987-09-174400.0
110JohnChen1997-09-288200.0
145JohnRussell1996-10-0114000.0
176JonathonTaylor1998-03-248600.0
112Jose ManuelUrman1998-03-077800.0
119KarenColmenares1999-08-102500.0
146KarenPartners1997-01-0513500.0
178KimberelyGrant1999-05-247000.0
102LexDe Haan1993-01-1317000.0
113LuisPopp1999-12-076900.0
120MatthewWeiss1996-07-188000.0
201MichaelHartstein1996-02-1713000.0
108NancyGreenberg1994-08-1712000.0
101NeenaKochhar1989-09-2117000.0
202PatFay1997-08-176000.0
122PayamKaufling1995-05-017900.0
192SarahBell1996-02-044000.0
123ShantaVollman1997-10-106500.0
205ShelleyHiggins1994-06-0712000.0
116ShelliBaida1997-12-242900.0
117SigalTobias1997-07-242800.0
100StevenKing1987-06-1724000.0
203SusanMavris1994-06-076500.0
106ValliPataballa1998-02-054800.0
206WilliamGietz1994-06-078300.0
" ], "text/plain": [ "[(121, 'Adam', 'Fripp', '1997-04-10', 8200.0),\n", " (103, 'Alexander', 'Hunold', '1990-01-03', 9000.0),\n", " (115, 'Alexander', 'Khoo', '1995-05-18', 3100.0),\n", " (193, 'Britney', 'Everett', '1997-03-03', 3900.0),\n", " (104, 'Bruce', 'Ernst', '1991-05-21', 6000.0),\n", " (179, 'Charles', 'Johnson', '2000-01-04', 6200.0),\n", " (109, 'Daniel', 'Faviet', '1994-08-16', 9000.0),\n", " (105, 'David', 'Austin', '1997-06-25', 4800.0),\n", " (114, 'Den', 'Raphaely', '1994-12-07', 11000.0),\n", " (107, 'Diana', 'Lorentz', '1999-02-07', 4200.0),\n", " (118, 'Guy', 'Himuro', '1998-11-15', 2600.0),\n", " (204, 'Hermann', 'Baer', '1994-06-07', 10000.0),\n", " (126, 'Irene', 'Mikkilineni', '1998-09-28', 2700.0),\n", " (111, 'Ismael', 'Sciarra', '1997-09-30', 7700.0),\n", " (177, 'Jack', 'Livingston', '1998-04-23', 8400.0),\n", " (200, 'Jennifer', 'Whalen', '1987-09-17', 4400.0),\n", " (110, 'John', 'Chen', '1997-09-28', 8200.0),\n", " (145, 'John', 'Russell', '1996-10-01', 14000.0),\n", " (176, 'Jonathon', 'Taylor', '1998-03-24', 8600.0),\n", " (112, 'Jose Manuel', 'Urman', '1998-03-07', 7800.0),\n", " (119, 'Karen', 'Colmenares', '1999-08-10', 2500.0),\n", " (146, 'Karen', 'Partners', '1997-01-05', 13500.0),\n", " (178, 'Kimberely', 'Grant', '1999-05-24', 7000.0),\n", " (102, 'Lex', 'De Haan', '1993-01-13', 17000.0),\n", " (113, 'Luis', 'Popp', '1999-12-07', 6900.0),\n", " (120, 'Matthew', 'Weiss', '1996-07-18', 8000.0),\n", " (201, 'Michael', 'Hartstein', '1996-02-17', 13000.0),\n", " (108, 'Nancy', 'Greenberg', '1994-08-17', 12000.0),\n", " (101, 'Neena', 'Kochhar', '1989-09-21', 17000.0),\n", " (202, 'Pat', 'Fay', '1997-08-17', 6000.0),\n", " (122, 'Payam', 'Kaufling', '1995-05-01', 7900.0),\n", " (192, 'Sarah', 'Bell', '1996-02-04', 4000.0),\n", " (123, 'Shanta', 'Vollman', '1997-10-10', 6500.0),\n", " (205, 'Shelley', 'Higgins', '1994-06-07', 12000.0),\n", " (116, 'Shelli', 'Baida', '1997-12-24', 2900.0),\n", " (117, 'Sigal', 'Tobias', '1997-07-24', 2800.0),\n", " (100, 'Steven', 'King', '1987-06-17', 24000.0),\n", " (203, 'Susan', 'Mavris', '1994-06-07', 6500.0),\n", " (106, 'Valli', 'Pataballa', '1998-02-05', 4800.0),\n", " (206, 'William', 'Gietz', '1994-06-07', 8300.0)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name, hire_date, salary \n", "FROM \n", " employees \n", "ORDER BY first_name;" ] }, { "cell_type": "markdown", "metadata": { "id": "qfpTwQ-QDgiI" }, "source": [ "The `ORDER BY` clause also allows using multiple expressions for sorting, separated by commas. In the following example `ORDER BY` is used to sort the employees by their first name in ascending order, and the employees who have the same first name are further sorted by their last name in descending order. E.g., check the sorting for the two employees with the name Alexander. " ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 315, "status": "ok", "timestamp": 1668546498511, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "5sYoF7im86i5", "outputId": "0fa50803-1c34-4562-f215-e4538520f06b" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namehire_datesalary
121AdamFripp1997-04-108200.0
115AlexanderKhoo1995-05-183100.0
103AlexanderHunold1990-01-039000.0
193BritneyEverett1997-03-033900.0
104BruceErnst1991-05-216000.0
179CharlesJohnson2000-01-046200.0
109DanielFaviet1994-08-169000.0
105DavidAustin1997-06-254800.0
114DenRaphaely1994-12-0711000.0
107DianaLorentz1999-02-074200.0
118GuyHimuro1998-11-152600.0
204HermannBaer1994-06-0710000.0
126IreneMikkilineni1998-09-282700.0
111IsmaelSciarra1997-09-307700.0
177JackLivingston1998-04-238400.0
200JenniferWhalen1987-09-174400.0
145JohnRussell1996-10-0114000.0
110JohnChen1997-09-288200.0
176JonathonTaylor1998-03-248600.0
112Jose ManuelUrman1998-03-077800.0
146KarenPartners1997-01-0513500.0
119KarenColmenares1999-08-102500.0
178KimberelyGrant1999-05-247000.0
102LexDe Haan1993-01-1317000.0
113LuisPopp1999-12-076900.0
120MatthewWeiss1996-07-188000.0
201MichaelHartstein1996-02-1713000.0
108NancyGreenberg1994-08-1712000.0
101NeenaKochhar1989-09-2117000.0
202PatFay1997-08-176000.0
122PayamKaufling1995-05-017900.0
192SarahBell1996-02-044000.0
123ShantaVollman1997-10-106500.0
205ShelleyHiggins1994-06-0712000.0
116ShelliBaida1997-12-242900.0
117SigalTobias1997-07-242800.0
100StevenKing1987-06-1724000.0
203SusanMavris1994-06-076500.0
106ValliPataballa1998-02-054800.0
206WilliamGietz1994-06-078300.0
" ], "text/plain": [ "[(121, 'Adam', 'Fripp', '1997-04-10', 8200.0),\n", " (115, 'Alexander', 'Khoo', '1995-05-18', 3100.0),\n", " (103, 'Alexander', 'Hunold', '1990-01-03', 9000.0),\n", " (193, 'Britney', 'Everett', '1997-03-03', 3900.0),\n", " (104, 'Bruce', 'Ernst', '1991-05-21', 6000.0),\n", " (179, 'Charles', 'Johnson', '2000-01-04', 6200.0),\n", " (109, 'Daniel', 'Faviet', '1994-08-16', 9000.0),\n", " (105, 'David', 'Austin', '1997-06-25', 4800.0),\n", " (114, 'Den', 'Raphaely', '1994-12-07', 11000.0),\n", " (107, 'Diana', 'Lorentz', '1999-02-07', 4200.0),\n", " (118, 'Guy', 'Himuro', '1998-11-15', 2600.0),\n", " (204, 'Hermann', 'Baer', '1994-06-07', 10000.0),\n", " (126, 'Irene', 'Mikkilineni', '1998-09-28', 2700.0),\n", " (111, 'Ismael', 'Sciarra', '1997-09-30', 7700.0),\n", " (177, 'Jack', 'Livingston', '1998-04-23', 8400.0),\n", " (200, 'Jennifer', 'Whalen', '1987-09-17', 4400.0),\n", " (145, 'John', 'Russell', '1996-10-01', 14000.0),\n", " (110, 'John', 'Chen', '1997-09-28', 8200.0),\n", " (176, 'Jonathon', 'Taylor', '1998-03-24', 8600.0),\n", " (112, 'Jose Manuel', 'Urman', '1998-03-07', 7800.0),\n", " (146, 'Karen', 'Partners', '1997-01-05', 13500.0),\n", " (119, 'Karen', 'Colmenares', '1999-08-10', 2500.0),\n", " (178, 'Kimberely', 'Grant', '1999-05-24', 7000.0),\n", " (102, 'Lex', 'De Haan', '1993-01-13', 17000.0),\n", " (113, 'Luis', 'Popp', '1999-12-07', 6900.0),\n", " (120, 'Matthew', 'Weiss', '1996-07-18', 8000.0),\n", " (201, 'Michael', 'Hartstein', '1996-02-17', 13000.0),\n", " (108, 'Nancy', 'Greenberg', '1994-08-17', 12000.0),\n", " (101, 'Neena', 'Kochhar', '1989-09-21', 17000.0),\n", " (202, 'Pat', 'Fay', '1997-08-17', 6000.0),\n", " (122, 'Payam', 'Kaufling', '1995-05-01', 7900.0),\n", " (192, 'Sarah', 'Bell', '1996-02-04', 4000.0),\n", " (123, 'Shanta', 'Vollman', '1997-10-10', 6500.0),\n", " (205, 'Shelley', 'Higgins', '1994-06-07', 12000.0),\n", " (116, 'Shelli', 'Baida', '1997-12-24', 2900.0),\n", " (117, 'Sigal', 'Tobias', '1997-07-24', 2800.0),\n", " (100, 'Steven', 'King', '1987-06-17', 24000.0),\n", " (203, 'Susan', 'Mavris', '1994-06-07', 6500.0),\n", " (106, 'Valli', 'Pataballa', '1998-02-05', 4800.0),\n", " (206, 'William', 'Gietz', '1994-06-07', 8300.0)]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name, hire_date, salary \n", "FROM \n", " employees \n", "ORDER BY first_name, last_name DESC;" ] }, { "cell_type": "markdown", "metadata": { "id": "i0tX2jFdEumx" }, "source": [ "Similarly, we can use `ORDER BY` to sort columns with numerical data, or to sort by date as in the following cell. " ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 912 }, "executionInfo": { "elapsed": 42, "status": "ok", "timestamp": 1668546498512, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "0geN3ekW2wAZ", "outputId": "08323f24-a25b-491a-952d-b604a756a99f" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namehire_datesalary
100StevenKing1987-06-1724000.0
200JenniferWhalen1987-09-174400.0
101NeenaKochhar1989-09-2117000.0
103AlexanderHunold1990-01-039000.0
104BruceErnst1991-05-216000.0
102LexDe Haan1993-01-1317000.0
203SusanMavris1994-06-076500.0
204HermannBaer1994-06-0710000.0
205ShelleyHiggins1994-06-0712000.0
206WilliamGietz1994-06-078300.0
109DanielFaviet1994-08-169000.0
108NancyGreenberg1994-08-1712000.0
114DenRaphaely1994-12-0711000.0
122PayamKaufling1995-05-017900.0
115AlexanderKhoo1995-05-183100.0
192SarahBell1996-02-044000.0
201MichaelHartstein1996-02-1713000.0
120MatthewWeiss1996-07-188000.0
145JohnRussell1996-10-0114000.0
146KarenPartners1997-01-0513500.0
193BritneyEverett1997-03-033900.0
121AdamFripp1997-04-108200.0
105DavidAustin1997-06-254800.0
117SigalTobias1997-07-242800.0
202PatFay1997-08-176000.0
110JohnChen1997-09-288200.0
111IsmaelSciarra1997-09-307700.0
123ShantaVollman1997-10-106500.0
116ShelliBaida1997-12-242900.0
106ValliPataballa1998-02-054800.0
112Jose ManuelUrman1998-03-077800.0
176JonathonTaylor1998-03-248600.0
177JackLivingston1998-04-238400.0
126IreneMikkilineni1998-09-282700.0
118GuyHimuro1998-11-152600.0
107DianaLorentz1999-02-074200.0
178KimberelyGrant1999-05-247000.0
119KarenColmenares1999-08-102500.0
113LuisPopp1999-12-076900.0
179CharlesJohnson2000-01-046200.0
" ], "text/plain": [ "[(100, 'Steven', 'King', '1987-06-17', 24000.0),\n", " (200, 'Jennifer', 'Whalen', '1987-09-17', 4400.0),\n", " (101, 'Neena', 'Kochhar', '1989-09-21', 17000.0),\n", " (103, 'Alexander', 'Hunold', '1990-01-03', 9000.0),\n", " (104, 'Bruce', 'Ernst', '1991-05-21', 6000.0),\n", " (102, 'Lex', 'De Haan', '1993-01-13', 17000.0),\n", " (203, 'Susan', 'Mavris', '1994-06-07', 6500.0),\n", " (204, 'Hermann', 'Baer', '1994-06-07', 10000.0),\n", " (205, 'Shelley', 'Higgins', '1994-06-07', 12000.0),\n", " (206, 'William', 'Gietz', '1994-06-07', 8300.0),\n", " (109, 'Daniel', 'Faviet', '1994-08-16', 9000.0),\n", " (108, 'Nancy', 'Greenberg', '1994-08-17', 12000.0),\n", " (114, 'Den', 'Raphaely', '1994-12-07', 11000.0),\n", " (122, 'Payam', 'Kaufling', '1995-05-01', 7900.0),\n", " (115, 'Alexander', 'Khoo', '1995-05-18', 3100.0),\n", " (192, 'Sarah', 'Bell', '1996-02-04', 4000.0),\n", " (201, 'Michael', 'Hartstein', '1996-02-17', 13000.0),\n", " (120, 'Matthew', 'Weiss', '1996-07-18', 8000.0),\n", " (145, 'John', 'Russell', '1996-10-01', 14000.0),\n", " (146, 'Karen', 'Partners', '1997-01-05', 13500.0),\n", " (193, 'Britney', 'Everett', '1997-03-03', 3900.0),\n", " (121, 'Adam', 'Fripp', '1997-04-10', 8200.0),\n", " (105, 'David', 'Austin', '1997-06-25', 4800.0),\n", " (117, 'Sigal', 'Tobias', '1997-07-24', 2800.0),\n", " (202, 'Pat', 'Fay', '1997-08-17', 6000.0),\n", " (110, 'John', 'Chen', '1997-09-28', 8200.0),\n", " (111, 'Ismael', 'Sciarra', '1997-09-30', 7700.0),\n", " (123, 'Shanta', 'Vollman', '1997-10-10', 6500.0),\n", " (116, 'Shelli', 'Baida', '1997-12-24', 2900.0),\n", " (106, 'Valli', 'Pataballa', '1998-02-05', 4800.0),\n", " (112, 'Jose Manuel', 'Urman', '1998-03-07', 7800.0),\n", " (176, 'Jonathon', 'Taylor', '1998-03-24', 8600.0),\n", " (177, 'Jack', 'Livingston', '1998-04-23', 8400.0),\n", " (126, 'Irene', 'Mikkilineni', '1998-09-28', 2700.0),\n", " (118, 'Guy', 'Himuro', '1998-11-15', 2600.0),\n", " (107, 'Diana', 'Lorentz', '1999-02-07', 4200.0),\n", " (178, 'Kimberely', 'Grant', '1999-05-24', 7000.0),\n", " (119, 'Karen', 'Colmenares', '1999-08-10', 2500.0),\n", " (113, 'Luis', 'Popp', '1999-12-07', 6900.0),\n", " (179, 'Charles', 'Johnson', '2000-01-04', 6200.0)]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " employee_id, first_name, last_name, hire_date, salary \n", "FROM \n", " employees \n", "ORDER BY hire_date;" ] }, { "cell_type": "markdown", "metadata": { "id": "1tftp-AjGK9Q" }, "source": [ "## 11.7 Filtering Data " ] }, { "cell_type": "markdown", "metadata": { "id": "3-vca0nPGqVB" }, "source": [ "### LIMIT\n", "\n", "`LIMIT` is used to constrain the number of rows returned by a query, similar to the `head()` method in Pandas.\n" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 179 }, "executionInfo": { "elapsed": 41, "status": "ok", "timestamp": 1668546498513, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "dsNuYSusK_jX", "outputId": "788a2b42-38cf-4b4f-a7a0-bb18a627a5fc" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namehire_datesalary
121AdamFripp1997-04-108200.0
103AlexanderHunold1990-01-039000.0
115AlexanderKhoo1995-05-183100.0
193BritneyEverett1997-03-033900.0
104BruceErnst1991-05-216000.0
" ], "text/plain": [ "[(121, 'Adam', 'Fripp', '1997-04-10', 8200.0),\n", " (103, 'Alexander', 'Hunold', '1990-01-03', 9000.0),\n", " (115, 'Alexander', 'Khoo', '1995-05-18', 3100.0),\n", " (193, 'Britney', 'Everett', '1997-03-03', 3900.0),\n", " (104, 'Bruce', 'Ernst', '1991-05-21', 6000.0)]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " employee_id, first_name, last_name, hire_date, salary \n", "FROM \n", " employees \n", "ORDER BY first_name \n", "LIMIT 5;" ] }, { "cell_type": "markdown", "metadata": { "id": "9Y97Q-sPIrQm" }, "source": [ "It is also possible to include an `OFFSET` clause, which will skip rows before retrieving the data. E.g., in the next cell, the first 3 rows are skipped, and rows 4-8 are returned. " ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 179 }, "executionInfo": { "elapsed": 37, "status": "ok", "timestamp": 1668546498513, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "4Gp6pN85GLEt", "outputId": "83ca8090-2c02-4f12-fe64-f17ed36021b8" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namehire_datesalary
193BritneyEverett1997-03-033900.0
104BruceErnst1991-05-216000.0
179CharlesJohnson2000-01-046200.0
109DanielFaviet1994-08-169000.0
105DavidAustin1997-06-254800.0
" ], "text/plain": [ "[(193, 'Britney', 'Everett', '1997-03-03', 3900.0),\n", " (104, 'Bruce', 'Ernst', '1991-05-21', 6000.0),\n", " (179, 'Charles', 'Johnson', '2000-01-04', 6200.0),\n", " (109, 'Daniel', 'Faviet', '1994-08-16', 9000.0),\n", " (105, 'David', 'Austin', '1997-06-25', 4800.0)]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT\n", " employee_id, first_name, last_name, hire_date, salary \n", "FROM \n", " employees \n", "ORDER BY first_name\n", "LIMIT 5 \n", "OFFSET 3;" ] }, { "cell_type": "markdown", "metadata": { "id": "scbrAeEPJZLx" }, "source": [ "### DISTINCT \n", "\n", "The `DISTINCT` clause allows to remove duplicate rows from the result set.\n", "\n", "E.g., the next cell shows the first 15 rows of the salary columns, where some rows have the same value. In the cell afterward, `DISTINCT` is used to remove the rows with the same values for salary." ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 389 }, "executionInfo": { "elapsed": 33, "status": "ok", "timestamp": 1668546498513, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "smgAPe0SGLGi", "outputId": "8610af08-bdfe-4861-a2c1-709bd47fd794" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
salary
24000.0
17000.0
17000.0
14000.0
13500.0
13000.0
12000.0
12000.0
11000.0
10000.0
9000.0
9000.0
8600.0
8400.0
8300.0
" ], "text/plain": [ "[(24000.0,),\n", " (17000.0,),\n", " (17000.0,),\n", " (14000.0,),\n", " (13500.0,),\n", " (13000.0,),\n", " (12000.0,),\n", " (12000.0,),\n", " (11000.0,),\n", " (10000.0,),\n", " (9000.0,),\n", " (9000.0,),\n", " (8600.0,),\n", " (8400.0,),\n", " (8300.0,)]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " salary \n", "FROM \n", " employees \n", "ORDER BY salary DESC \n", "LIMIT 15;" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 389 }, "executionInfo": { "elapsed": 31, "status": "ok", "timestamp": 1668546498514, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "Jk867nh7GLIf", "outputId": "9f901a8b-1c6d-4353-c7f0-c5a066344b86" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
salary
24000.0
17000.0
14000.0
13500.0
13000.0
12000.0
11000.0
10000.0
9000.0
8600.0
8400.0
8300.0
8200.0
8000.0
7900.0
" ], "text/plain": [ "[(24000.0,),\n", " (17000.0,),\n", " (14000.0,),\n", " (13500.0,),\n", " (13000.0,),\n", " (12000.0,),\n", " (11000.0,),\n", " (10000.0,),\n", " (9000.0,),\n", " (8600.0,),\n", " (8400.0,),\n", " (8300.0,),\n", " (8200.0,),\n", " (8000.0,),\n", " (7900.0,)]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT DISTINCT \n", " salary \n", "FROM \n", " employees \n", "ORDER BY salary DESC\n", "LIMIT 15;" ] }, { "cell_type": "markdown", "metadata": { "id": "T9JRPTQ9MGJI" }, "source": [ "### WHERE\n", "\n", "The `WHERE` clause filters data based on specified conditions. For instance, return only the employees that have a salary greater than a certain value." ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 284 }, "executionInfo": { "elapsed": 28, "status": "ok", "timestamp": 1668546498514, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "F3nnMfBxGLLY", "outputId": "00a3e730-cc2c-4fae-d3db-bd6e0e2a5730" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namesalary
100StevenKing24000.0
101NeenaKochhar17000.0
102LexDe Haan17000.0
145JohnRussell14000.0
146KarenPartners13500.0
201MichaelHartstein13000.0
108NancyGreenberg12000.0
205ShelleyHiggins12000.0
114DenRaphaely11000.0
204HermannBaer10000.0
" ], "text/plain": [ "[(100, 'Steven', 'King', 24000.0),\n", " (101, 'Neena', 'Kochhar', 17000.0),\n", " (102, 'Lex', 'De Haan', 17000.0),\n", " (145, 'John', 'Russell', 14000.0),\n", " (146, 'Karen', 'Partners', 13500.0),\n", " (201, 'Michael', 'Hartstein', 13000.0),\n", " (108, 'Nancy', 'Greenberg', 12000.0),\n", " (205, 'Shelley', 'Higgins', 12000.0),\n", " (114, 'Den', 'Raphaely', 11000.0),\n", " (204, 'Hermann', 'Baer', 10000.0)]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name,salary \n", "FROM \n", " employees \n", "WHERE salary > 9000 \n", "ORDER BY salary DESC;" ] }, { "cell_type": "markdown", "metadata": { "id": "ybtWARO0M9AA" }, "source": [ "Or, return the employees who work in the department 5. " ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 221 }, "executionInfo": { "elapsed": 26, "status": "ok", "timestamp": 1668546498515, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "neKdj-UhM6gr", "outputId": "1184d94d-5aa7-4b6c-f0e9-3a16a44a5310" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namesalarydepartment_id
121AdamFripp8200.05
193BritneyEverett3900.05
126IreneMikkilineni2700.05
120MatthewWeiss8000.05
122PayamKaufling7900.05
192SarahBell4000.05
123ShantaVollman6500.05
" ], "text/plain": [ "[(121, 'Adam', 'Fripp', 8200.0, 5),\n", " (193, 'Britney', 'Everett', 3900.0, 5),\n", " (126, 'Irene', 'Mikkilineni', 2700.0, 5),\n", " (120, 'Matthew', 'Weiss', 8000.0, 5),\n", " (122, 'Payam', 'Kaufling', 7900.0, 5),\n", " (192, 'Sarah', 'Bell', 4000.0, 5),\n", " (123, 'Shanta', 'Vollman', 6500.0, 5)]" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name, salary, department_id \n", "FROM \n", " employees \n", "WHERE department_id = 5 \n", "ORDER BY first_name;" ] }, { "cell_type": "markdown", "metadata": { "id": "wNGiqy3_Ob3x" }, "source": [ "### Comparison Operators" ] }, { "cell_type": "markdown", "metadata": { "id": "LbcqYkoUNufB" }, "source": [ "To specify a condition, we can use the standard comparison operators, such as `>`, `<`, `>=`, `<=`, `=`, and note that `<>` can be used for `'not equal to'`." ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 766 }, "executionInfo": { "elapsed": 22, "status": "ok", "timestamp": 1668546498515, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "VJ4J1gf8M6kp", "outputId": "12e9a251-9406-4b49-feda-c36af7680673" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namesalarydepartment_id
103AlexanderHunold9000.06
115AlexanderKhoo3100.03
104BruceErnst6000.06
179CharlesJohnson6200.08
109DanielFaviet9000.010
105DavidAustin4800.06
114DenRaphaely11000.03
107DianaLorentz4200.06
118GuyHimuro2600.03
204HermannBaer10000.07
111IsmaelSciarra7700.010
177JackLivingston8400.08
200JenniferWhalen4400.01
110JohnChen8200.010
145JohnRussell14000.08
176JonathonTaylor8600.08
112Jose ManuelUrman7800.010
119KarenColmenares2500.03
146KarenPartners13500.08
178KimberelyGrant7000.08
102LexDe Haan17000.09
113LuisPopp6900.010
201MichaelHartstein13000.02
108NancyGreenberg12000.010
101NeenaKochhar17000.09
202PatFay6000.02
205ShelleyHiggins12000.011
116ShelliBaida2900.03
117SigalTobias2800.03
100StevenKing24000.09
203SusanMavris6500.04
106ValliPataballa4800.06
206WilliamGietz8300.011
" ], "text/plain": [ "[(103, 'Alexander', 'Hunold', 9000.0, 6),\n", " (115, 'Alexander', 'Khoo', 3100.0, 3),\n", " (104, 'Bruce', 'Ernst', 6000.0, 6),\n", " (179, 'Charles', 'Johnson', 6200.0, 8),\n", " (109, 'Daniel', 'Faviet', 9000.0, 10),\n", " (105, 'David', 'Austin', 4800.0, 6),\n", " (114, 'Den', 'Raphaely', 11000.0, 3),\n", " (107, 'Diana', 'Lorentz', 4200.0, 6),\n", " (118, 'Guy', 'Himuro', 2600.0, 3),\n", " (204, 'Hermann', 'Baer', 10000.0, 7),\n", " (111, 'Ismael', 'Sciarra', 7700.0, 10),\n", " (177, 'Jack', 'Livingston', 8400.0, 8),\n", " (200, 'Jennifer', 'Whalen', 4400.0, 1),\n", " (110, 'John', 'Chen', 8200.0, 10),\n", " (145, 'John', 'Russell', 14000.0, 8),\n", " (176, 'Jonathon', 'Taylor', 8600.0, 8),\n", " (112, 'Jose Manuel', 'Urman', 7800.0, 10),\n", " (119, 'Karen', 'Colmenares', 2500.0, 3),\n", " (146, 'Karen', 'Partners', 13500.0, 8),\n", " (178, 'Kimberely', 'Grant', 7000.0, 8),\n", " (102, 'Lex', 'De Haan', 17000.0, 9),\n", " (113, 'Luis', 'Popp', 6900.0, 10),\n", " (201, 'Michael', 'Hartstein', 13000.0, 2),\n", " (108, 'Nancy', 'Greenberg', 12000.0, 10),\n", " (101, 'Neena', 'Kochhar', 17000.0, 9),\n", " (202, 'Pat', 'Fay', 6000.0, 2),\n", " (205, 'Shelley', 'Higgins', 12000.0, 11),\n", " (116, 'Shelli', 'Baida', 2900.0, 3),\n", " (117, 'Sigal', 'Tobias', 2800.0, 3),\n", " (100, 'Steven', 'King', 24000.0, 9),\n", " (203, 'Susan', 'Mavris', 6500.0, 4),\n", " (106, 'Valli', 'Pataballa', 4800.0, 6),\n", " (206, 'William', 'Gietz', 8300.0, 11)]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name, salary, department_id\n", "FROM \n", " employees \n", "WHERE department_id <> 5 \n", "ORDER BY first_name;" ] }, { "cell_type": "markdown", "metadata": { "id": "VlSVddSKOe84" }, "source": [ "### Logical Operators\n", "\n", "We can also use logical operators to combine multiple conditions in the `WHERE` clause of an SQL statement. The following table contains the SQL logical operators.\n", "\n", "| **Operator** | **Meaning** |\n", "|--------------|---------------------------------------------------------------|\n", "| **AND** | Returns true if **both** expressions are true. |\n", "| **BETWEEN** | Returns true if the operand is within a specified **range**. |\n", "| **OR** | Returns true if **either** expression is true. |\n", "| **LIKE** | Returns true if the operand **matches a pattern**. |\n", "| **IN** | Returns true if the operand is **equal** to one of the values in a list. |\n", "| **NOT** | Reverses the result of any other Boolean operator. |\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Following are several examples of using logical operators. " ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 200 }, "executionInfo": { "elapsed": 369, "status": "ok", "timestamp": 1668546498866, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "nUBYaYsiM6ma", "outputId": "373af86f-4cde-4b5e-946d-148e34dbf8e1" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namesalary
BruceErnst6000.0
PatFay6000.0
CharlesJohnson6200.0
ShantaVollman6500.0
SusanMavris6500.0
LuisPopp6900.0
" ], "text/plain": [ "[('Bruce', 'Ernst', 6000.0),\n", " ('Pat', 'Fay', 6000.0),\n", " ('Charles', 'Johnson', 6200.0),\n", " ('Shanta', 'Vollman', 6500.0),\n", " ('Susan', 'Mavris', 6500.0),\n", " ('Luis', 'Popp', 6900.0)]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " first_name, last_name, salary \n", "FROM \n", " employees \n", "WHERE salary > 5000 AND salary < 7000 \n", "ORDER BY salary;" ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 221 }, "executionInfo": { "elapsed": 59, "status": "ok", "timestamp": 1668546498866, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "rWRZdEJNRpZo", "outputId": "d3a8007a-2394-4536-9890-623b785c7e42" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namesalary
BruceErnst6000.0
PatFay6000.0
CharlesJohnson6200.0
ShantaVollman6500.0
SusanMavris6500.0
LuisPopp6900.0
KimberelyGrant7000.0
" ], "text/plain": [ "[('Bruce', 'Ernst', 6000.0),\n", " ('Pat', 'Fay', 6000.0),\n", " ('Charles', 'Johnson', 6200.0),\n", " ('Shanta', 'Vollman', 6500.0),\n", " ('Susan', 'Mavris', 6500.0),\n", " ('Luis', 'Popp', 6900.0),\n", " ('Kimberely', 'Grant', 7000.0)]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " first_name, last_name, salary \n", "FROM \n", " employees \n", "WHERE salary BETWEEN 5000 AND 7000 \n", "ORDER BY salary;" ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 137 }, "executionInfo": { "elapsed": 57, "status": "ok", "timestamp": 1668546498867, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "2WHcM351M6ox", "outputId": "73e68f06-edb1-4e35-f739-41a15659cdf8" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namesalary
BruceErnst6000.0
PatFay6000.0
KimberelyGrant7000.0
" ], "text/plain": [ "[('Bruce', 'Ernst', 6000.0),\n", " ('Pat', 'Fay', 6000.0),\n", " ('Kimberely', 'Grant', 7000.0)]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " first_name, last_name, salary \n", "FROM \n", " employees \n", "WHERE salary = 6000 OR salary = 7000 \n", "ORDER BY salary;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Select employees with first names that start with `jo` by using the `LIKE` logical operator. The wildcard character `%` matches zero of more characters." ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 158 }, "executionInfo": { "elapsed": 54, "status": "ok", "timestamp": 1668546498867, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "jkR-H_s2GLM_", "outputId": "9bbb7833-2ac6-43ca-ae65-5e7c0dee6feb" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_name
110JohnChen
145JohnRussell
176JonathonTaylor
112Jose ManuelUrman
" ], "text/plain": [ "[(110, 'John', 'Chen'),\n", " (145, 'John', 'Russell'),\n", " (176, 'Jonathon', 'Taylor'),\n", " (112, 'Jose Manuel', 'Urman')]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name \n", "FROM \n", " employees \n", "WHERE first_name LIKE 'jo%' \n", "ORDER BY first_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Select employees with first names whose second character is `h`. The wildcard character `_` matches exactly one character. Therefore, the first character in `first_name` can be any character." ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_name
179CharlesJohnson
123ShantaVollman
205ShelleyHiggins
116ShelliBaida
" ], "text/plain": [ "[(179, 'Charles', 'Johnson'),\n", " (123, 'Shanta', 'Vollman'),\n", " (205, 'Shelley', 'Higgins'),\n", " (116, 'Shelli', 'Baida')]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name\n", "FROM\n", " employees\n", "WHERE\n", " first_name LIKE '_h%'\n", "ORDER BY first_name;" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namedepartment_id
JohnRussell8
KarenPartners8
JonathonTaylor8
JackLivingston8
KimberelyGrant8
CharlesJohnson8
StevenKing9
NeenaKochhar9
LexDe Haan9
" ], "text/plain": [ "[('John', 'Russell', 8),\n", " ('Karen', 'Partners', 8),\n", " ('Jonathon', 'Taylor', 8),\n", " ('Jack', 'Livingston', 8),\n", " ('Kimberely', 'Grant', 8),\n", " ('Charles', 'Johnson', 8),\n", " ('Steven', 'King', 9),\n", " ('Neena', 'Kochhar', 9),\n", " ('Lex', 'De Haan', 9)]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " first_name, last_name, department_id\n", "FROM\n", " employees\n", "WHERE department_id IN (8, 9)\n", "ORDER BY department_id;" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### IS NULL Operator\n", "\n", "To determine whether a row or column has missing or non-defined values, we can use the `IS NULL` operator. \n", "\n", "For instance, to find all employees who do not have phone numbers, we use the `IS NUL` operator as follows." ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namephone_number
145JohnRussellNone
146KarenPartnersNone
176JonathonTaylorNone
177JackLivingstonNone
178KimberelyGrantNone
179CharlesJohnsonNone
" ], "text/plain": [ "[(145, 'John', 'Russell', None),\n", " (146, 'Karen', 'Partners', None),\n", " (176, 'Jonathon', 'Taylor', None),\n", " (177, 'Jack', 'Livingston', None),\n", " (178, 'Kimberely', 'Grant', None),\n", " (179, 'Charles', 'Johnson', None)]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " employee_id, first_name, last_name, phone_number\n", "FROM\n", " employees\n", "WHERE phone_number IS NULL" ] }, { "cell_type": "markdown", "metadata": { "id": "GaIG3J4cbJda" }, "source": [ "## 11.8 Conditional Expressions " ] }, { "cell_type": "markdown", "metadata": { "id": "FLrXfGRublAp" }, "source": [ "The `CASE` expression is used to add if-then-else logic to SQL statements, which allows to evaluate a list of conditions and returns one of the possible results.\n", "\n", "In the next cell, the `CASE` expression returns the results `Low`, `Average`, or `High` based on the conditions regarding the salary. The results are collected in the `evaluation` column. " ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 389 }, "executionInfo": { "elapsed": 52, "status": "ok", "timestamp": 1668546498867, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "nUsDyB9qGLRR", "outputId": "3139b973-92c1-486a-b96d-80ccad5b83a5" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_nameevaluation
StevenKingHigh
NeenaKochharHigh
LexDe HaanHigh
AlexanderHunoldHigh
BruceErnstHigh
DavidAustinAverage
ValliPataballaAverage
DianaLorentzAverage
NancyGreenbergHigh
DanielFavietHigh
JohnChenHigh
IsmaelSciarraHigh
Jose ManuelUrmanHigh
LuisPoppHigh
DenRaphaelyHigh
" ], "text/plain": [ "[('Steven', 'King', 'High'),\n", " ('Neena', 'Kochhar', 'High'),\n", " ('Lex', 'De Haan', 'High'),\n", " ('Alexander', 'Hunold', 'High'),\n", " ('Bruce', 'Ernst', 'High'),\n", " ('David', 'Austin', 'Average'),\n", " ('Valli', 'Pataballa', 'Average'),\n", " ('Diana', 'Lorentz', 'Average'),\n", " ('Nancy', 'Greenberg', 'High'),\n", " ('Daniel', 'Faviet', 'High'),\n", " ('John', 'Chen', 'High'),\n", " ('Ismael', 'Sciarra', 'High'),\n", " ('Jose Manuel', 'Urman', 'High'),\n", " ('Luis', 'Popp', 'High'),\n", " ('Den', 'Raphaely', 'High')]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " first_name, last_name,\n", "CASE WHEN salary < 3000 THEN 'Low' \n", " WHEN salary >= 3000 AND salary <= 5000 THEN 'Average' \n", " WHEN salary > 5000 THEN 'High' \n", "END evaluation \n", "FROM \n", " employees\n", "LIMIT 15;" ] }, { "cell_type": "markdown", "metadata": { "id": "7eB9fXgqc9gy" }, "source": [ "## 11.9 Joining Multiple Tables " ] }, { "cell_type": "markdown", "metadata": { "id": "YPlF-mKndx3B" }, "source": [ "SQL provides several ways to join tables, such as `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL OUTER JOIN`, and others. \n", "\n", "### INNER JOIN\n", "\n", "`INNER JOIN` combines rows from two or more tables based on a related column between them. It retrieves only the rows that have matching values in both tables.\n", "\n", "The syntax is:\n", "\n", " SELECT \n", " column1, column2, ...\n", " FROM \n", " table1\n", " INNER JOIN table2 ON table1.columnX = table2.columnX;\n", "\n", "It specifies to join `table1` and `table2` by matching values in `columnX` from `table1` and `columnX` from `table2`, and returns `column1, column2, ...` from the joined tables.\n", "\n", "Let's show how we can use `INNER JOIN`, where for instance, we want to retrieve the list of employees who work in departments 1, 2, and 3, and we would like to list the names of the departments. \n", "\n", "To do that, first we can notice that both the `employees` table and the `departments` table have a column `department_id`. Therefore, we can use the `department_id` column in the `employees` table as the foreign key column to link the `employees` to the `departments` table. \n", "\n", "Let's first display the employees who work in departments 1, 2, and 3 in the `employees` table to understand the data." ] }, { "cell_type": "code", "execution_count": 35, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 263 }, "executionInfo": { "elapsed": 50, "status": "ok", "timestamp": 1668546498868, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "83UlDh2-yUlQ", "outputId": "df1bb096-835c-4ca1-fd57-6ebca6c25ab6" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namedepartment_id
JenniferWhalen1
MichaelHartstein2
PatFay2
DenRaphaely3
AlexanderKhoo3
ShelliBaida3
SigalTobias3
GuyHimuro3
KarenColmenares3
" ], "text/plain": [ "[('Jennifer', 'Whalen', 1),\n", " ('Michael', 'Hartstein', 2),\n", " ('Pat', 'Fay', 2),\n", " ('Den', 'Raphaely', 3),\n", " ('Alexander', 'Khoo', 3),\n", " ('Shelli', 'Baida', 3),\n", " ('Sigal', 'Tobias', 3),\n", " ('Guy', 'Himuro', 3),\n", " ('Karen', 'Colmenares', 3)]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " first_name, last_name, department_id \n", "FROM \n", " employees \n", "WHERE department_id IN (1, 2, 3) \n", "ORDER BY department_id;" ] }, { "cell_type": "markdown", "metadata": { "id": "DOHaywdZzKVI" }, "source": [ "We can also find the names of the departments in the `departments` table that have a `department_id` of 1, 2, and 3. " ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 137 }, "executionInfo": { "elapsed": 47, "status": "ok", "timestamp": 1668546498868, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "EVESoBuzzLOQ", "outputId": "9eeb7184-33eb-42d1-d6ba-57d5407c15f2" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
department_iddepartment_name
1Administration
2Marketing
3Purchasing
" ], "text/plain": [ "[(1, 'Administration'), (2, 'Marketing'), (3, 'Purchasing')]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " department_id, department_name \n", "FROM \n", " departments\n", "WHERE department_id IN (1, 2, 3);" ] }, { "cell_type": "markdown", "metadata": { "id": "DXtqPD2sznss" }, "source": [ "Next, we join the `employees` and `departments` tables, and use `INNER JOIN` to match the `department_id` column in the two tables. For each row, if the condition `departments.department_id = employees.department_id` is satisfied, the combined row will include data from rows in both `employees` and `departments` tables in the result set." ] }, { "cell_type": "code", "execution_count": 37, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 263 }, "executionInfo": { "elapsed": 46, "status": "ok", "timestamp": 1668546498869, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "VsodnK_kbIKt", "outputId": "39470e2f-0551-48a1-f8de-a56c7c63eae1" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namedepartment_iddepartment_id_1department_name
JenniferWhalen11Administration
MichaelHartstein22Marketing
PatFay22Marketing
DenRaphaely33Purchasing
AlexanderKhoo33Purchasing
ShelliBaida33Purchasing
SigalTobias33Purchasing
GuyHimuro33Purchasing
KarenColmenares33Purchasing
" ], "text/plain": [ "[('Jennifer', 'Whalen', 1, 1, 'Administration'),\n", " ('Michael', 'Hartstein', 2, 2, 'Marketing'),\n", " ('Pat', 'Fay', 2, 2, 'Marketing'),\n", " ('Den', 'Raphaely', 3, 3, 'Purchasing'),\n", " ('Alexander', 'Khoo', 3, 3, 'Purchasing'),\n", " ('Shelli', 'Baida', 3, 3, 'Purchasing'),\n", " ('Sigal', 'Tobias', 3, 3, 'Purchasing'),\n", " ('Guy', 'Himuro', 3, 3, 'Purchasing'),\n", " ('Karen', 'Colmenares', 3, 3, 'Purchasing')]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " first_name, last_name, employees.department_id, departments.department_id, department_name \n", "FROM \n", " employees \n", "INNER JOIN departments ON departments.department_id = employees.department_id \n", "WHERE employees.department_id IN (1, 2, 3);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's look at one more example, where we used `INNER JOIN` to join three tables. Specifically, in the next cell we use one more `INNER JOIN` clause to join the tables `employees` and `jobs` using the `job_id` column, in order to retrieve the information about the `job_title` column from the `jobs` table." ] }, { "cell_type": "code", "execution_count": 38, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namelast_namedepartment_iddepartment_id_1department_namejob_idjob_id_1job_title
DenRaphaely33Purchasing1414Purchasing Manager
AlexanderKhoo33Purchasing1313Purchasing Clerk
ShelliBaida33Purchasing1313Purchasing Clerk
SigalTobias33Purchasing1313Purchasing Clerk
GuyHimuro33Purchasing1313Purchasing Clerk
KarenColmenares33Purchasing1313Purchasing Clerk
JenniferWhalen11Administration33Administration Assistant
MichaelHartstein22Marketing1010Marketing Manager
PatFay22Marketing1111Marketing Representative
" ], "text/plain": [ "[('Den', 'Raphaely', 3, 3, 'Purchasing', 14, 14, 'Purchasing Manager'),\n", " ('Alexander', 'Khoo', 3, 3, 'Purchasing', 13, 13, 'Purchasing Clerk'),\n", " ('Shelli', 'Baida', 3, 3, 'Purchasing', 13, 13, 'Purchasing Clerk'),\n", " ('Sigal', 'Tobias', 3, 3, 'Purchasing', 13, 13, 'Purchasing Clerk'),\n", " ('Guy', 'Himuro', 3, 3, 'Purchasing', 13, 13, 'Purchasing Clerk'),\n", " ('Karen', 'Colmenares', 3, 3, 'Purchasing', 13, 13, 'Purchasing Clerk'),\n", " ('Jennifer', 'Whalen', 1, 1, 'Administration', 3, 3, 'Administration Assistant'),\n", " ('Michael', 'Hartstein', 2, 2, 'Marketing', 10, 10, 'Marketing Manager'),\n", " ('Pat', 'Fay', 2, 2, 'Marketing', 11, 11, 'Marketing Representative')]" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " first_name, last_name, employees.department_id, departments.department_id, \n", " department_name, employees.job_id, jobs.job_id, job_title\n", "FROM\n", " employees\n", "INNER JOIN departments ON departments.department_id = employees.department_id\n", "INNER JOIN jobs ON jobs.job_id = employees.job_id\n", "WHERE employees.department_id IN (1, 2, 3);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "As we mentioned above, `INNER JOIN` retrieves only the rows that have matching values in both tables. This is shown in the figure below, which we already saw in the `Pandas` lecture. The table also shows other join operations.\n", "\n", "\n", "Figure: Join Operations." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### LEFT JOIN\n", "\n", "`LEFT JOIN` (also referred to as `LEFT OUTER JOIN`) is similar to `INNER JOIN`, only it retrieves all rows from the left table in the `JOIN` clause and the matched rows from the right table. \n", "\n", "The \"left table\" is the table that appears on the left side of the `JOIN` clause. It is considered the primary table, and its rows are the ones that we want to retrieve or work with primarily.\n", "\n", "The \"right table\" is the table that appears on the right side of the `JOIN` clause. It is the secondary table, and we are typically interested in its data to complement or match with the data from the left table.\n", "\n", "For instance, the `countries` table is shown below, with `country_id` column being the primary key. The following query returns columns for the countries US, UK, and China." ] }, { "cell_type": "code", "execution_count": 39, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_idcountry_name
CNChina
UKUnited Kingdom
USUnited States of America
" ], "text/plain": [ "[('CN', 'China'), ('UK', 'United Kingdom'), ('US', 'United States of America')]" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_id, country_name\n", "FROM\n", " countries\n", "WHERE country_id IN ('US', 'UK', 'CN');" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Similarly, the `locations` table shown below has a `country_id` column as the foreign key. The query returns the locations located in the US, UK, and China." ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_idstreet_addresscity
US2014 Jabberwocky RdSouthlake
US2011 Interiors BlvdSouth San Francisco
US2004 Charade RdSeattle
UK8204 Arthur StLondon
UKMagdalen Centre, The Oxford Science ParkOxford
" ], "text/plain": [ "[('US', '2014 Jabberwocky Rd', 'Southlake'),\n", " ('US', '2011 Interiors Blvd', 'South San Francisco'),\n", " ('US', '2004 Charade Rd', 'Seattle'),\n", " ('UK', '8204 Arthur St', 'London'),\n", " ('UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')]" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_id, street_address, city\n", "FROM\n", " locations\n", "WHERE country_id IN ('US', 'UK', 'CN');" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the next cell, because we use the `LEFT JOIN` clause, all rows that satisfy the condition in the `WHERE` clause of the `countries` table are included in the result set. \n", "\n", "For each row in the `countries` table, the `LEFT JOIN` clause finds the matching rows in the `locations` table. If at least one matching row is found, the database engine combines the data from columns of the matching rows in both tables.\n", "\n", "In this case, there is no matching row found for the country `China` with the `country_id` of CN, and therefore, the row in the `countries` table is included in the result set and the rows in the `locations` table for `China` are filled with `None` values. " ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_namecountry_idcountry_id_1street_addresscity
ChinaCNNoneNoneNone
United KingdomUKUK8204 Arthur StLondon
United KingdomUKUKMagdalen Centre, The Oxford Science ParkOxford
United States of AmericaUSUS2014 Jabberwocky RdSouthlake
United States of AmericaUSUS2011 Interiors BlvdSouth San Francisco
United States of AmericaUSUS2004 Charade RdSeattle
" ], "text/plain": [ "[('China', 'CN', None, None, None),\n", " ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London'),\n", " ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford'),\n", " ('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake'),\n", " ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco'),\n", " ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')]" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_name, countries.country_id, locations.country_id, street_address, city\n", "FROM\n", " countries\n", "LEFT JOIN locations ON locations.country_id = countries.country_id\n", "WHERE countries.country_id IN ('US', 'UK', 'CN')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we used `LEFT JOIN` for all rows in the `countries` table, the results would have been as in the following cell." ] }, { "cell_type": "code", "execution_count": 42, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_namecountry_idcountry_id_1street_addresscity
ArgentinaARNoneNoneNone
AustraliaAUNoneNoneNone
BelgiumBENoneNoneNone
BrazilBRNoneNoneNone
CanadaCACA147 Spadina AveToronto
SwitzerlandCHNoneNoneNone
ChinaCNNoneNoneNone
GermanyDEDESchwanthalerstr. 7031Munich
DenmarkDKNoneNoneNone
EgyptEGNoneNoneNone
FranceFRNoneNoneNone
HongKongHKNoneNoneNone
IsraelILNoneNoneNone
IndiaINNoneNoneNone
ItalyITNoneNoneNone
JapanJPNoneNoneNone
KuwaitKWNoneNoneNone
MexicoMXNoneNoneNone
NigeriaNGNoneNoneNone
NetherlandsNLNoneNoneNone
SingaporeSGNoneNoneNone
United KingdomUKUK8204 Arthur StLondon
United KingdomUKUKMagdalen Centre, The Oxford Science ParkOxford
United States of AmericaUSUS2004 Charade RdSeattle
United States of AmericaUSUS2011 Interiors BlvdSouth San Francisco
United States of AmericaUSUS2014 Jabberwocky RdSouthlake
ZambiaZMNoneNoneNone
ZimbabweZWNoneNoneNone
" ], "text/plain": [ "[('Argentina', 'AR', None, None, None),\n", " ('Australia', 'AU', None, None, None),\n", " ('Belgium', 'BE', None, None, None),\n", " ('Brazil', 'BR', None, None, None),\n", " ('Canada', 'CA', 'CA', '147 Spadina Ave', 'Toronto'),\n", " ('Switzerland', 'CH', None, None, None),\n", " ('China', 'CN', None, None, None),\n", " ('Germany', 'DE', 'DE', 'Schwanthalerstr. 7031', 'Munich'),\n", " ('Denmark', 'DK', None, None, None),\n", " ('Egypt', 'EG', None, None, None),\n", " ('France', 'FR', None, None, None),\n", " ('HongKong', 'HK', None, None, None),\n", " ('Israel', 'IL', None, None, None),\n", " ('India', 'IN', None, None, None),\n", " ('Italy', 'IT', None, None, None),\n", " ('Japan', 'JP', None, None, None),\n", " ('Kuwait', 'KW', None, None, None),\n", " ('Mexico', 'MX', None, None, None),\n", " ('Nigeria', 'NG', None, None, None),\n", " ('Netherlands', 'NL', None, None, None),\n", " ('Singapore', 'SG', None, None, None),\n", " ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London'),\n", " ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford'),\n", " ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle'),\n", " ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco'),\n", " ('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake'),\n", " ('Zambia', 'ZM', None, None, None),\n", " ('Zimbabwe', 'ZW', None, None, None)]" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_name, countries.country_id, locations.country_id, street_address, city\n", "FROM\n", " countries\n", "LEFT JOIN locations ON locations.country_id = countries.country_id" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`LEFT JOIN` is useful when we want to retrieve data from a primary table and include related information from a secondary table, but we don't want to exclude records from the primary table if there are no matching rows in the secondary table." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And, because non-matching rows in the `locations` table are filled with the `None` values, we can apply the `LEFT JOIN` clause to find mismatched rows between the tables.\n", "\n", "For example, to find the countries in the `countries` table that do not have any locations in the `locations` table, we can use the following query." ] }, { "cell_type": "code", "execution_count": 43, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_name
Argentina
Australia
Belgium
Brazil
China
Denmark
Egypt
France
HongKong
India
Israel
Italy
Japan
Kuwait
Mexico
Netherlands
Nigeria
Singapore
Switzerland
Zambia
Zimbabwe
" ], "text/plain": [ "[('Argentina',),\n", " ('Australia',),\n", " ('Belgium',),\n", " ('Brazil',),\n", " ('China',),\n", " ('Denmark',),\n", " ('Egypt',),\n", " ('France',),\n", " ('HongKong',),\n", " ('India',),\n", " ('Israel',),\n", " ('Italy',),\n", " ('Japan',),\n", " ('Kuwait',),\n", " ('Mexico',),\n", " ('Netherlands',),\n", " ('Nigeria',),\n", " ('Singapore',),\n", " ('Switzerland',),\n", " ('Zambia',),\n", " ('Zimbabwe',)]" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_name\n", "FROM\n", " countries\n", "LEFT JOIN locations ON locations.country_id = countries.country_id\n", "WHERE locations.country_id IS NULL\n", "ORDER BY country_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### RIGHT JOIN\n", "\n", "By analogy, `RIGHT JOIN` (or `RIGHT OUTER JOIN`) returns all rows from the right table and the matched rows from the left table. If there are no matches in the left table, the result will contain `None` values for those columns from the left table.\n", "\n", "In the example below, the entries in the `locations` table are returned, and they all have matched rows in the `countries` table." ] }, { "cell_type": "code", "execution_count": 44, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_namecountry_idcountry_id_1street_addresscity
CanadaCACA147 Spadina AveToronto
GermanyDEDESchwanthalerstr. 7031Munich
United KingdomUKUK8204 Arthur StLondon
United KingdomUKUKMagdalen Centre, The Oxford Science ParkOxford
United States of AmericaUSUS2014 Jabberwocky RdSouthlake
United States of AmericaUSUS2011 Interiors BlvdSouth San Francisco
United States of AmericaUSUS2004 Charade RdSeattle
" ], "text/plain": [ "[('Canada', 'CA', 'CA', '147 Spadina Ave', 'Toronto'),\n", " ('Germany', 'DE', 'DE', 'Schwanthalerstr. 7031', 'Munich'),\n", " ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London'),\n", " ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford'),\n", " ('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake'),\n", " ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco'),\n", " ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')]" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_name, countries.country_id, locations.country_id, street_address, city\n", "FROM\n", " countries\n", "RIGHT JOIN locations ON locations.country_id = countries.country_id;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### FULL JOIN" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`FULL JOIN` (also known as a `FULL OUTER JOIN`) combines the result sets of both a `LEFT JOIN` and a `RIGHT JOIN`, it returns all rows from both tables and includes rows from the left table that have no match in the right table (with `None` values for right table columns) and rows from the right table that have no match in the left table (with `None` values for left table columns)." ] }, { "cell_type": "code", "execution_count": 45, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_namecountry_idcountry_id_1street_addresscity
ArgentinaARNoneNoneNone
AustraliaAUNoneNoneNone
BelgiumBENoneNoneNone
BrazilBRNoneNoneNone
CanadaCACA147 Spadina AveToronto
SwitzerlandCHNoneNoneNone
ChinaCNNoneNoneNone
GermanyDEDESchwanthalerstr. 7031Munich
DenmarkDKNoneNoneNone
EgyptEGNoneNoneNone
FranceFRNoneNoneNone
HongKongHKNoneNoneNone
IsraelILNoneNoneNone
IndiaINNoneNoneNone
ItalyITNoneNoneNone
JapanJPNoneNoneNone
KuwaitKWNoneNoneNone
MexicoMXNoneNoneNone
NigeriaNGNoneNoneNone
NetherlandsNLNoneNoneNone
SingaporeSGNoneNoneNone
United KingdomUKUK8204 Arthur StLondon
United KingdomUKUKMagdalen Centre, The Oxford Science ParkOxford
United States of AmericaUSUS2014 Jabberwocky RdSouthlake
United States of AmericaUSUS2011 Interiors BlvdSouth San Francisco
United States of AmericaUSUS2004 Charade RdSeattle
ZambiaZMNoneNoneNone
ZimbabweZWNoneNoneNone
" ], "text/plain": [ "[('Argentina', 'AR', None, None, None),\n", " ('Australia', 'AU', None, None, None),\n", " ('Belgium', 'BE', None, None, None),\n", " ('Brazil', 'BR', None, None, None),\n", " ('Canada', 'CA', 'CA', '147 Spadina Ave', 'Toronto'),\n", " ('Switzerland', 'CH', None, None, None),\n", " ('China', 'CN', None, None, None),\n", " ('Germany', 'DE', 'DE', 'Schwanthalerstr. 7031', 'Munich'),\n", " ('Denmark', 'DK', None, None, None),\n", " ('Egypt', 'EG', None, None, None),\n", " ('France', 'FR', None, None, None),\n", " ('HongKong', 'HK', None, None, None),\n", " ('Israel', 'IL', None, None, None),\n", " ('India', 'IN', None, None, None),\n", " ('Italy', 'IT', None, None, None),\n", " ('Japan', 'JP', None, None, None),\n", " ('Kuwait', 'KW', None, None, None),\n", " ('Mexico', 'MX', None, None, None),\n", " ('Nigeria', 'NG', None, None, None),\n", " ('Netherlands', 'NL', None, None, None),\n", " ('Singapore', 'SG', None, None, None),\n", " ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London'),\n", " ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford'),\n", " ('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake'),\n", " ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco'),\n", " ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle'),\n", " ('Zambia', 'ZM', None, None, None),\n", " ('Zimbabwe', 'ZW', None, None, None)]" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_name, countries.country_id, locations.country_id, street_address, city\n", "FROM\n", " countries\n", "FULL JOIN locations ON locations.country_id = countries.country_id;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And the next cell shows the `OUTER JOIN` when the `locations` table is listed first, i.e., to the left side of the join clause." ] }, { "cell_type": "code", "execution_count": 46, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_namecountry_idcountry_id_1street_addresscity
United States of AmericaUSUS2014 Jabberwocky RdSouthlake
United States of AmericaUSUS2011 Interiors BlvdSouth San Francisco
United States of AmericaUSUS2004 Charade RdSeattle
CanadaCACA147 Spadina AveToronto
United KingdomUKUK8204 Arthur StLondon
United KingdomUKUKMagdalen Centre, The Oxford Science ParkOxford
GermanyDEDESchwanthalerstr. 7031Munich
ArgentinaARNoneNoneNone
AustraliaAUNoneNoneNone
BelgiumBENoneNoneNone
BrazilBRNoneNoneNone
SwitzerlandCHNoneNoneNone
ChinaCNNoneNoneNone
DenmarkDKNoneNoneNone
EgyptEGNoneNoneNone
FranceFRNoneNoneNone
HongKongHKNoneNoneNone
IsraelILNoneNoneNone
IndiaINNoneNoneNone
ItalyITNoneNoneNone
JapanJPNoneNoneNone
KuwaitKWNoneNoneNone
MexicoMXNoneNoneNone
NigeriaNGNoneNoneNone
NetherlandsNLNoneNoneNone
SingaporeSGNoneNoneNone
ZambiaZMNoneNoneNone
ZimbabweZWNoneNoneNone
" ], "text/plain": [ "[('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake'),\n", " ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco'),\n", " ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle'),\n", " ('Canada', 'CA', 'CA', '147 Spadina Ave', 'Toronto'),\n", " ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London'),\n", " ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford'),\n", " ('Germany', 'DE', 'DE', 'Schwanthalerstr. 7031', 'Munich'),\n", " ('Argentina', 'AR', None, None, None),\n", " ('Australia', 'AU', None, None, None),\n", " ('Belgium', 'BE', None, None, None),\n", " ('Brazil', 'BR', None, None, None),\n", " ('Switzerland', 'CH', None, None, None),\n", " ('China', 'CN', None, None, None),\n", " ('Denmark', 'DK', None, None, None),\n", " ('Egypt', 'EG', None, None, None),\n", " ('France', 'FR', None, None, None),\n", " ('HongKong', 'HK', None, None, None),\n", " ('Israel', 'IL', None, None, None),\n", " ('India', 'IN', None, None, None),\n", " ('Italy', 'IT', None, None, None),\n", " ('Japan', 'JP', None, None, None),\n", " ('Kuwait', 'KW', None, None, None),\n", " ('Mexico', 'MX', None, None, None),\n", " ('Nigeria', 'NG', None, None, None),\n", " ('Netherlands', 'NL', None, None, None),\n", " ('Singapore', 'SG', None, None, None),\n", " ('Zambia', 'ZM', None, None, None),\n", " ('Zimbabwe', 'ZW', None, None, None)]" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " country_name, countries.country_id, locations.country_id, street_address, city\n", "FROM\n", " locations\n", "FULL JOIN countries ON locations.country_id = countries.country_id;" ] }, { "cell_type": "markdown", "metadata": { "id": "O5hYRZFW0wLx" }, "source": [ "## 11.10 Return Data Statistics " ] }, { "cell_type": "markdown", "metadata": { "id": "kZFwPAMy02ry" }, "source": [ "*Aggregate functions* in SQL take a list of values and return a single value, such as the average `AVG()`, `MIN()`, `MAX()`, `SUM()`, or `COUNT()`." ] }, { "cell_type": "code", "execution_count": 47, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 95 }, "executionInfo": { "elapsed": 42, "status": "ok", "timestamp": 1668546498869, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "rddarNqh2_7f", "outputId": "78c221a1-abdf-438b-8318-50633df0f154" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AVG(salary)
8060.0
" ], "text/plain": [ "[(8060.0,)]" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " AVG(salary) \n", "FROM \n", " employees;" ] }, { "cell_type": "code", "execution_count": 48, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 95 }, "executionInfo": { "elapsed": 39, "status": "ok", "timestamp": 1668546498869, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "CQc0D-KvlSqH", "outputId": "aff04c02-b4c5-4727-d474-40013f61786f" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
MAX(salary)
24000.0
" ], "text/plain": [ "[(24000.0,)]" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " MAX(salary) \n", "FROM \n", " employees;" ] }, { "cell_type": "code", "execution_count": 49, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 95 }, "executionInfo": { "elapsed": 38, "status": "ok", "timestamp": 1668546498870, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "X25mN5iXlngk", "outputId": "67ba6402-7c71-42d2-8fc5-c947c494d461" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SUM(salary)
41200.0
" ], "text/plain": [ "[(41200.0,)]" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " SUM(salary) \n", "FROM \n", " employees \n", "WHERE department_id = 5;" ] }, { "cell_type": "markdown", "metadata": { "id": "BdaOzPbimOv4" }, "source": [ "`COUNT` returns the number of rows in a table. It can be used by providing the name of a column, or it can also be used with an asterisk `*` as in the following cell." ] }, { "cell_type": "code", "execution_count": 50, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 95 }, "executionInfo": { "elapsed": 36, "status": "ok", "timestamp": 1668546498870, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "toQh7LgKl9dv", "outputId": "77943f0a-0f96-4017-83ee-594de81c78f1" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(employee_id)
40
" ], "text/plain": [ "[(40,)]" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " COUNT(employee_id) \n", "FROM \n", " employees;" ] }, { "cell_type": "code", "execution_count": 51, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 95 }, "executionInfo": { "elapsed": 34, "status": "ok", "timestamp": 1668546498871, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "C2CEZSXmmf5p", "outputId": "20eb0696-cf9a-42ae-810f-48a72d61a9f1" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(*)
40
" ], "text/plain": [ "[(40,)]" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " COUNT(*) \n", "FROM \n", " employees;" ] }, { "cell_type": "markdown", "metadata": { "id": "o3983LmT1mrQ" }, "source": [ "## 11.11 Grouping Data " ] }, { "cell_type": "markdown", "metadata": { "id": "yGzk2k0R1wYl" }, "source": [ "`GROUP BY` allows to group rows based on values from more than one column. It is typically combined with aggregate functions, like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.\n", "\n", "For instance, let's assume that we would like to retrieve information about the average salary in each department. In the next cell we will first display the salaries of all employees per department, and in the following cell we can see how `GROUP BY` is used to calculate the average salary for each department. " ] }, { "cell_type": "code", "execution_count": 52, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
department_idsalary
924000.0
917000.0
917000.0
69000.0
66000.0
64800.0
64800.0
64200.0
1012000.0
109000.0
108200.0
107700.0
107800.0
106900.0
311000.0
33100.0
32900.0
32800.0
32600.0
32500.0
58000.0
58200.0
57900.0
56500.0
52700.0
814000.0
813500.0
88600.0
88400.0
87000.0
86200.0
54000.0
53900.0
14400.0
213000.0
26000.0
46500.0
710000.0
1112000.0
118300.0
" ], "text/plain": [ "[(9, 24000.0),\n", " (9, 17000.0),\n", " (9, 17000.0),\n", " (6, 9000.0),\n", " (6, 6000.0),\n", " (6, 4800.0),\n", " (6, 4800.0),\n", " (6, 4200.0),\n", " (10, 12000.0),\n", " (10, 9000.0),\n", " (10, 8200.0),\n", " (10, 7700.0),\n", " (10, 7800.0),\n", " (10, 6900.0),\n", " (3, 11000.0),\n", " (3, 3100.0),\n", " (3, 2900.0),\n", " (3, 2800.0),\n", " (3, 2600.0),\n", " (3, 2500.0),\n", " (5, 8000.0),\n", " (5, 8200.0),\n", " (5, 7900.0),\n", " (5, 6500.0),\n", " (5, 2700.0),\n", " (8, 14000.0),\n", " (8, 13500.0),\n", " (8, 8600.0),\n", " (8, 8400.0),\n", " (8, 7000.0),\n", " (8, 6200.0),\n", " (5, 4000.0),\n", " (5, 3900.0),\n", " (1, 4400.0),\n", " (2, 13000.0),\n", " (2, 6000.0),\n", " (4, 6500.0),\n", " (7, 10000.0),\n", " (11, 12000.0),\n", " (11, 8300.0)]" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " department_id, salary\n", "FROM \n", " employees " ] }, { "cell_type": "code", "execution_count": 53, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 305 }, "executionInfo": { "elapsed": 307, "status": "ok", "timestamp": 1668546499147, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "VoHmAANV3X2s", "outputId": "2b2f52a3-bf57-4c24-a8c0-5401f1c3569a" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
department_idAVG(salary)
14400.0
29500.0
34150.0
46500.0
55885.714285714285
65760.0
710000.0
89616.666666666666
919333.333333333332
108600.0
1110150.0
" ], "text/plain": [ "[(1, 4400.0),\n", " (2, 9500.0),\n", " (3, 4150.0),\n", " (4, 6500.0),\n", " (5, 5885.714285714285),\n", " (6, 5760.0),\n", " (7, 10000.0),\n", " (8, 9616.666666666666),\n", " (9, 19333.333333333332),\n", " (10, 8600.0),\n", " (11, 10150.0)]" ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " department_id, AVG(salary) \n", "FROM \n", " employees \n", "GROUP BY department_id;" ] }, { "cell_type": "markdown", "metadata": { "id": "06iZQVKIpqp3" }, "source": [ "Similarly, the following cell displays the minimum, maximum, and average salary for each department. And, instead of using the `department_id` column, it will be more convenient to display the department names. Since the `employees` table does not have a column with the department names, we will use `INNER JOIN` to retrieve the `department_name` column from the `departments` table." ] }, { "cell_type": "code", "execution_count": 54, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 305 }, "executionInfo": { "elapsed": 59, "status": "ok", "timestamp": 1668546499148, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "Z3dtqQf73icA", "outputId": "fb903209-ad01-4b5f-ea62-5a55b7692d1c" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
department_namemin_salarymax_salaryaverage_salary
Accounting8300.012000.010150.0
Administration4400.04400.04400.0
Executive17000.024000.019333.0
Finance6900.012000.08600.0
Human Resources6500.06500.06500.0
IT4200.09000.05760.0
Marketing6000.013000.09500.0
Public Relations10000.010000.010000.0
Purchasing2500.011000.04150.0
Sales6200.014000.09617.0
Shipping2700.08200.05886.0
" ], "text/plain": [ "[('Accounting', 8300.0, 12000.0, 10150.0),\n", " ('Administration', 4400.0, 4400.0, 4400.0),\n", " ('Executive', 17000.0, 24000.0, 19333.0),\n", " ('Finance', 6900.0, 12000.0, 8600.0),\n", " ('Human Resources', 6500.0, 6500.0, 6500.0),\n", " ('IT', 4200.0, 9000.0, 5760.0),\n", " ('Marketing', 6000.0, 13000.0, 9500.0),\n", " ('Public Relations', 10000.0, 10000.0, 10000.0),\n", " ('Purchasing', 2500.0, 11000.0, 4150.0),\n", " ('Sales', 6200.0, 14000.0, 9617.0),\n", " ('Shipping', 2700.0, 8200.0, 5886.0)]" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT \n", " department_name, MIN(salary) AS min_salary, MAX(salary) AS max_salary, ROUND(AVG(salary)) AS average_salary \n", "FROM \n", " employees \n", "INNER JOIN departments ON departments.department_id = employees.department_id \n", "GROUP BY department_name;" ] }, { "cell_type": "markdown", "metadata": { "id": "qj3ipXnJ4YXD" }, "source": [ "## 11.12 Modifying Data " ] }, { "cell_type": "markdown", "metadata": { "id": "g8cb7h4H4kMA" }, "source": [ "### INSERT\n", "\n", "`INSERT` is used to insert one or more rows into a table, and we already used it in subsection 11.3 when we created new tables. The general syntax is:\n", "\n", "```\n", "INSERT INTO table (column1, column2,...)\n", "VALUES (value1, value2, ...);\n", "```\n", "\n", "It is important to ensure that the number of values matches the number of columns, and that the value type corresponds to the data type for that column.\n", "\n", "It is also possible to insert value without specifying the columns, as in:\n", "\n", "```\n", "INSERT INTO table \n", "VALUES (value1, value2, ...);\n", "```\n", "\n", "The primary data types supported by SQLite include INTEGER (integer numbers), REAL (floating-point numbers), TEXT (string of characters), VARCHAR (variable-length string with a defined maximum length), CHAR (string with a fixed length), BOOLEAN (True or False), NULL (absence of a value), and BLOB (binary large object, for storing binary data like images of files). \n", "\n", "For instance, to insert a new row into the `dependents` table, we can use the following code. The new row will be added to the bottom of the table `dependents`." ] }, { "cell_type": "code", "execution_count": 55, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 56, "status": "ok", "timestamp": 1668546499148, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "x9Psimq42__0", "outputId": "ef9ceee6-74eb-4571-f2a7-2f0c65765ae8" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "INSERT INTO dependents (first_name, last_name, relationship, employee_id) \n", "VALUES ('Dustin', 'Johnson', 'Child', 178);" ] }, { "cell_type": "code", "execution_count": 56, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 724 }, "executionInfo": { "elapsed": 51, "status": "ok", "timestamp": 1668546499148, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "Xf3a3Fyi3AEE", "outputId": "f7b03e0b-7ab4-44a3-d356-2fea09943d68" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dependent_idfirst_namelast_namerelationshipemployee_id
1PenelopeGietzChild206
2NickHigginsChild205
3EdWhalenChild200
4JenniferKingChild100
5JohnnyKochharChild101
6BetteDe HaanChild102
7GraceFavietChild109
8MatthewChenChild110
9JoeSciarraChild111
10ChristianUrmanChild112
11ZeroPoppChild113
12KarlGreenbergChild108
13UmaMavrisChild203
14VivienHunoldChild103
15CubaErnstChild104
16FredAustinChild105
17HelenPataballaChild106
18DanLorentzChild107
19BobHartsteinChild201
20LucilleFayChild202
21KirstenBaerChild204
22ElvisKhooChild115
23SandraBaidaChild116
24CameronTobiasChild117
25KevinHimuroChild118
26RipColmenaresChild119
27JuliaRaphaelyChild114
28WoodyRussellChild145
29AlecPartnersChild146
30SandraTaylorChild176
31DustinJohnsonChild178
" ], "text/plain": [ "[(1, 'Penelope', 'Gietz', 'Child', 206),\n", " (2, 'Nick', 'Higgins', 'Child', 205),\n", " (3, 'Ed', 'Whalen', 'Child', 200),\n", " (4, 'Jennifer', 'King', 'Child', 100),\n", " (5, 'Johnny', 'Kochhar', 'Child', 101),\n", " (6, 'Bette', 'De Haan', 'Child', 102),\n", " (7, 'Grace', 'Faviet', 'Child', 109),\n", " (8, 'Matthew', 'Chen', 'Child', 110),\n", " (9, 'Joe', 'Sciarra', 'Child', 111),\n", " (10, 'Christian', 'Urman', 'Child', 112),\n", " (11, 'Zero', 'Popp', 'Child', 113),\n", " (12, 'Karl', 'Greenberg', 'Child', 108),\n", " (13, 'Uma', 'Mavris', 'Child', 203),\n", " (14, 'Vivien', 'Hunold', 'Child', 103),\n", " (15, 'Cuba', 'Ernst', 'Child', 104),\n", " (16, 'Fred', 'Austin', 'Child', 105),\n", " (17, 'Helen', 'Pataballa', 'Child', 106),\n", " (18, 'Dan', 'Lorentz', 'Child', 107),\n", " (19, 'Bob', 'Hartstein', 'Child', 201),\n", " (20, 'Lucille', 'Fay', 'Child', 202),\n", " (21, 'Kirsten', 'Baer', 'Child', 204),\n", " (22, 'Elvis', 'Khoo', 'Child', 115),\n", " (23, 'Sandra', 'Baida', 'Child', 116),\n", " (24, 'Cameron', 'Tobias', 'Child', 117),\n", " (25, 'Kevin', 'Himuro', 'Child', 118),\n", " (26, 'Rip', 'Colmenares', 'Child', 119),\n", " (27, 'Julia', 'Raphaely', 'Child', 114),\n", " (28, 'Woody', 'Russell', 'Child', 145),\n", " (29, 'Alec', 'Partners', 'Child', 146),\n", " (30, 'Sandra', 'Taylor', 'Child', 176),\n", " (31, 'Dustin', 'Johnson', 'Child', 178)]" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# verify the changes\n", "%sql SELECT * from dependents" ] }, { "cell_type": "markdown", "metadata": { "id": "N9xUi0n6R9bD" }, "source": [ "### UPDATE\n", "\n", "`UPDATE` is used to update existing data in a table.\n", "\n", "For instance, to update the last name of the employee with `id=192`, we can write:" ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 47, "status": "ok", "timestamp": 1668546499149, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "LhFKoRKT3AHq", "outputId": "1bf0116b-ebde-4350-c5b8-e541f45f5740" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "UPDATE employees \n", "SET last_name = 'Lopez' \n", "WHERE employee_id = 192;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And we can verify the changes." ] }, { "cell_type": "code", "execution_count": 58, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 95 }, "executionInfo": { "elapsed": 44, "status": "ok", "timestamp": 1668546499149, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "XOsGIEm4R8fK", "outputId": "b869f4cb-5424-4867-c6ed-2eebeacbadc0" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_name
192SarahLopez
" ], "text/plain": [ "[(192, 'Sarah', 'Lopez')]" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT\n", " employee_id, first_name, last_name\n", "FROM \n", " employees\n", "WHERE employee_id = 192;" ] }, { "cell_type": "markdown", "metadata": { "id": "jTGvcEMqVVvH" }, "source": [ "### DELETE\n", "\n", "`DELETE` is used to delete one or more rows from a table permanently." ] }, { "cell_type": "code", "execution_count": 59, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 39, "status": "ok", "timestamp": 1668546499149, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "qn4BtawLR8hW", "outputId": "d69632aa-83e5-4fa7-9a8b-1766b2a65e81" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "DELETE FROM dependents \n", "WHERE dependent_id = 16;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Verify the change." ] }, { "cell_type": "code", "execution_count": 60, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 703 }, "executionInfo": { "elapsed": 37, "status": "ok", "timestamp": 1668546499150, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "Q9-VzVVnR8jF", "outputId": "f4d7e8b3-7717-4a28-96da-888c6100092b" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dependent_idfirst_namelast_namerelationshipemployee_id
1PenelopeGietzChild206
2NickHigginsChild205
3EdWhalenChild200
4JenniferKingChild100
5JohnnyKochharChild101
6BetteDe HaanChild102
7GraceFavietChild109
8MatthewChenChild110
9JoeSciarraChild111
10ChristianUrmanChild112
11ZeroPoppChild113
12KarlGreenbergChild108
13UmaMavrisChild203
14VivienHunoldChild103
15CubaErnstChild104
17HelenPataballaChild106
18DanLorentzChild107
19BobHartsteinChild201
20LucilleFayChild202
21KirstenBaerChild204
22ElvisKhooChild115
23SandraBaidaChild116
24CameronTobiasChild117
25KevinHimuroChild118
26RipColmenaresChild119
27JuliaRaphaelyChild114
28WoodyRussellChild145
29AlecPartnersChild146
30SandraTaylorChild176
31DustinJohnsonChild178
" ], "text/plain": [ "[(1, 'Penelope', 'Gietz', 'Child', 206),\n", " (2, 'Nick', 'Higgins', 'Child', 205),\n", " (3, 'Ed', 'Whalen', 'Child', 200),\n", " (4, 'Jennifer', 'King', 'Child', 100),\n", " (5, 'Johnny', 'Kochhar', 'Child', 101),\n", " (6, 'Bette', 'De Haan', 'Child', 102),\n", " (7, 'Grace', 'Faviet', 'Child', 109),\n", " (8, 'Matthew', 'Chen', 'Child', 110),\n", " (9, 'Joe', 'Sciarra', 'Child', 111),\n", " (10, 'Christian', 'Urman', 'Child', 112),\n", " (11, 'Zero', 'Popp', 'Child', 113),\n", " (12, 'Karl', 'Greenberg', 'Child', 108),\n", " (13, 'Uma', 'Mavris', 'Child', 203),\n", " (14, 'Vivien', 'Hunold', 'Child', 103),\n", " (15, 'Cuba', 'Ernst', 'Child', 104),\n", " (17, 'Helen', 'Pataballa', 'Child', 106),\n", " (18, 'Dan', 'Lorentz', 'Child', 107),\n", " (19, 'Bob', 'Hartstein', 'Child', 201),\n", " (20, 'Lucille', 'Fay', 'Child', 202),\n", " (21, 'Kirsten', 'Baer', 'Child', 204),\n", " (22, 'Elvis', 'Khoo', 'Child', 115),\n", " (23, 'Sandra', 'Baida', 'Child', 116),\n", " (24, 'Cameron', 'Tobias', 'Child', 117),\n", " (25, 'Kevin', 'Himuro', 'Child', 118),\n", " (26, 'Rip', 'Colmenares', 'Child', 119),\n", " (27, 'Julia', 'Raphaely', 'Child', 114),\n", " (28, 'Woody', 'Russell', 'Child', 145),\n", " (29, 'Alec', 'Partners', 'Child', 146),\n", " (30, 'Sandra', 'Taylor', 'Child', 176),\n", " (31, 'Dustin', 'Johnson', 'Child', 178)]" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM dependents" ] }, { "cell_type": "markdown", "metadata": { "id": "OODNmYu4WuW5" }, "source": [ "## 11.13 Working with Tables " ] }, { "cell_type": "markdown", "metadata": { "id": "pS0QOM6wWzZv" }, "source": [ "In subsection 11.3 we learned how to use the `CREATE TABLE` statement. Several other related SQL statements for working with tables are described next." ] }, { "cell_type": "markdown", "metadata": { "id": "6pfjgZjccVav" }, "source": [ "### ALTER TABLE\n", "\n", "`ALTER TABLE` allows to add new columns in an existing table using `ADD`, remove columns in a table with `DROP`, rename columns with `RENAME`, or modify attributes of a column such as primary key, default value, etc. with `ADD CONSTRAINT` or `DROP CONSTRAINT` and related commands." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Add a Column**" ] }, { "cell_type": "code", "execution_count": 61, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 33, "status": "ok", "timestamp": 1668546499150, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "dffE_UQGR8ld", "outputId": "f172bab9-4605-45cb-8ee2-706dc3993eab" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "ALTER TABLE cars \n", "ADD mileage INT;" ] }, { "cell_type": "code", "execution_count": 62, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 242 }, "executionInfo": { "elapsed": 29, "status": "ok", "timestamp": 1668546499150, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "2pnoaLoydBHu", "outputId": "f252596e-12d9-466c-b633-aaae79d04f92" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idnamepricemileage
1Audi52642None
2Mercedes57127None
3Skoda9000None
4Volvo29000None
5Bentley350000None
6Citroen21000None
7Hummer41400None
8Volkswagen21600None
" ], "text/plain": [ "[(1, 'Audi', 52642, None),\n", " (2, 'Mercedes', 57127, None),\n", " (3, 'Skoda', 9000, None),\n", " (4, 'Volvo', 29000, None),\n", " (5, 'Bentley', 350000, None),\n", " (6, 'Citroen', 21000, None),\n", " (7, 'Hummer', 41400, None),\n", " (8, 'Volkswagen', 21600, None)]" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM cars" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Remove a Column**" ] }, { "cell_type": "code", "execution_count": 63, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "ALTER TABLE cars \n", "DROP price;" ] }, { "cell_type": "code", "execution_count": 64, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idnamemileage
1AudiNone
2MercedesNone
3SkodaNone
4VolvoNone
5BentleyNone
6CitroenNone
7HummerNone
8VolkswagenNone
" ], "text/plain": [ "[(1, 'Audi', None),\n", " (2, 'Mercedes', None),\n", " (3, 'Skoda', None),\n", " (4, 'Volvo', None),\n", " (5, 'Bentley', None),\n", " (6, 'Citroen', None),\n", " (7, 'Hummer', None),\n", " (8, 'Volkswagen', None)]" ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# verfiy the change\n", "%sql SELECT * FROM cars" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "**Rename a Column**" ] }, { "cell_type": "code", "execution_count": 65, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "ALTER TABLE cars \n", "RENAME mileage TO miles;" ] }, { "cell_type": "code", "execution_count": 66, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idnamemiles
1AudiNone
2MercedesNone
3SkodaNone
4VolvoNone
5BentleyNone
6CitroenNone
7HummerNone
8VolkswagenNone
" ], "text/plain": [ "[(1, 'Audi', None),\n", " (2, 'Mercedes', None),\n", " (3, 'Skoda', None),\n", " (4, 'Volvo', None),\n", " (5, 'Bentley', None),\n", " (6, 'Citroen', None),\n", " (7, 'Hummer', None),\n", " (8, 'Volkswagen', None)]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# verfiy the change\n", "%sql SELECT * FROM cars" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Rename a Table**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Similarly, `ALTER TABLE` can be used to rename a table, as in the following example. " ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "ALTER TABLE writer\n", "RENAME TO authors;" ] }, { "cell_type": "code", "execution_count": 68, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
cars
authors
regions
sqlite_sequence
countries
locations
departments
jobs
employees
dependents
" ], "text/plain": [ "[('cars',),\n", " ('authors',),\n", " ('regions',),\n", " ('sqlite_sequence',),\n", " ('countries',),\n", " ('locations',),\n", " ('departments',),\n", " ('jobs',),\n", " ('employees',),\n", " ('dependents',)]" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# verfiy the change\n", "%sql SELECT name FROM sqlite_master WHERE type='table'" ] }, { "cell_type": "markdown", "metadata": { "id": "VcUkklqeeVaO" }, "source": [ "### DROP TABLE\n", "\n", "`DROP TABLE` removes a table from a database. " ] }, { "cell_type": "code", "execution_count": 69, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 25, "status": "ok", "timestamp": 1668546499151, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "9LB_u2XyR8o2", "outputId": "d840cf0f-2965-410d-85b2-7dbb4ea6a5ac" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql DROP TABLE cars;" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 263 }, "executionInfo": { "elapsed": 256, "status": "ok", "timestamp": 1668546499386, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "y3n8EHhwbIXI", "outputId": "1e7d29f0-01eb-4661-e7e9-a634be774cc9" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
authors
regions
sqlite_sequence
countries
locations
departments
jobs
employees
dependents
" ], "text/plain": [ "[('authors',),\n", " ('regions',),\n", " ('sqlite_sequence',),\n", " ('countries',),\n", " ('locations',),\n", " ('departments',),\n", " ('jobs',),\n", " ('employees',),\n", " ('dependents',)]" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# verfiy the change\n", "%sql SELECT name FROM sqlite_master WHERE type='table'" ] }, { "cell_type": "markdown", "metadata": { "id": "GiYZE-B7j3Ap" }, "source": [ "## 11.14 Constraints \n", "\n", "Constraints impose rules that restrict the type of data a column can hold or establish other restrictions. So far, we described the following constraints used in SQL databases:\n", "\n", "- `PRIMARY KEY`, defines a primary key for a table.\n", "- `NOT NULL`, ensures that values are inserted for all rows in a column.\n", "- `UNIQUE`, ensures that each row in a column has unique values. \n", "\n", "### FOREIGN KEY\n", "\n", "Beside the primary key, some tables also define a **foreign key**. This is a column or a group of columns that enforces a link between the data in two tables. In a foreign key reference, the primary key column of the first table is referenced by the column of the second table, and the column of the second table becomes the foreign key. \n", "\n", "For instance, when we created the `countries` table (see the inserted code below), we specified that it has `country_id` column as a primary key, and `region_id` column as a foreign key. Therefore, the `region_id` column in `countries` will point to the `region_id` column in the `regions` table. This is indicated in the last line below: `FOREIGN KEY (region_id) REFERENCES regions (region_id)`.\n", "\n", " CREATE TABLE countries (\n", " country_id TEXT NOT NULL,\n", " country_name TEXT NOT NULL,\n", " region_id INTEGER NOT NULL,\n", " PRIMARY KEY (country_id ASC),\n", " FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE);\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note also that in the last line `ON DELETE CASCADE` specifies that if a row in the `regions` table is deleted, all rows in the `countries` table that have a matching `region_id` will also be deleted automatically. Analogously, `ON UPDATE CASCADE` specifies that if a `region_id` value in the `regions` table is updated, all corresponding rows in the `countries` table will be updated accordingly. This is useful for maintaining referential integrity between the rows in these two tables.\n", "\n", "Let's inspect the `countries` table." ] }, { "cell_type": "code", "execution_count": 71, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 598 }, "executionInfo": { "elapsed": 19, "status": "ok", "timestamp": 1668546499386, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "7CTqIambeqrU", "outputId": "ee6a2359-ed15-4107-b04a-ef7371fbba15" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country_idcountry_nameregion_id
ARArgentina2
AUAustralia3
BEBelgium1
BRBrazil2
CACanada2
CHSwitzerland1
CNChina3
DEGermany1
DKDenmark1
EGEgypt4
FRFrance1
HKHongKong3
ILIsrael4
INIndia3
ITItaly1
JPJapan3
KWKuwait4
MXMexico2
NGNigeria4
NLNetherlands1
SGSingapore3
UKUnited Kingdom1
USUnited States of America2
ZMZambia4
ZWZimbabwe4
" ], "text/plain": [ "[('AR', 'Argentina', 2),\n", " ('AU', 'Australia', 3),\n", " ('BE', 'Belgium', 1),\n", " ('BR', 'Brazil', 2),\n", " ('CA', 'Canada', 2),\n", " ('CH', 'Switzerland', 1),\n", " ('CN', 'China', 3),\n", " ('DE', 'Germany', 1),\n", " ('DK', 'Denmark', 1),\n", " ('EG', 'Egypt', 4),\n", " ('FR', 'France', 1),\n", " ('HK', 'HongKong', 3),\n", " ('IL', 'Israel', 4),\n", " ('IN', 'India', 3),\n", " ('IT', 'Italy', 1),\n", " ('JP', 'Japan', 3),\n", " ('KW', 'Kuwait', 4),\n", " ('MX', 'Mexico', 2),\n", " ('NG', 'Nigeria', 4),\n", " ('NL', 'Netherlands', 1),\n", " ('SG', 'Singapore', 3),\n", " ('UK', 'United Kingdom', 1),\n", " ('US', 'United States of America', 2),\n", " ('ZM', 'Zambia', 4),\n", " ('ZW', 'Zimbabwe', 4)]" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM countries" ] }, { "cell_type": "code", "execution_count": 72, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 158 }, "executionInfo": { "elapsed": 18, "status": "ok", "timestamp": 1668546499387, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "-2HzN3dGeqvp", "outputId": "3de03f86-3b30-474b-b426-104504091b87" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_idregion_name
1Europe
2Americas
3Asia
4Middle East and Africa
" ], "text/plain": [ "[(1, 'Europe'), (2, 'Americas'), (3, 'Asia'), (4, 'Middle East and Africa')]" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM regions" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "As we mentioned in the section on joining tables, we can use the foreign key column in a table to link to other tables. In the above example, the `region_id` column establishes a relationship between the `countries` table and the `regions` table, where` countries.region_id` must match an existing value in `regions.region_id`." ] }, { "cell_type": "markdown", "metadata": { "id": "1fMWL_AqqZeV" }, "source": [ "## 11.15 Subqueries \n", "\n", "A *subquery* is a query that is nested inside another query, such as `SELECT`. \n", "\n", "For instance, the following cell retrieves employees in the departments that have a `location_id=1700`. \n", "\n", "The query placed inside the parentheses is a subquery. It is also known as an *inner query* or *inner select*. The query that contains the subquery is called an *outer query* or an *outer select*.\n", "\n", "To execute the query, the database system first has to execute the subquery and retrieve the departments with a `location_id` of 1700, and afterward it has to execute the outer query." ] }, { "cell_type": "code", "execution_count": 73, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 451 }, "executionInfo": { "elapsed": 16, "status": "ok", "timestamp": 1668546499388, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "e3I_hOSPeqxU", "outputId": "8f810ab5-fbdf-465d-bf5d-118b80ef6034" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namedepartment_id
115AlexanderKhoo3
109DanielFaviet10
114DenRaphaely3
118GuyHimuro3
111IsmaelSciarra10
200JenniferWhalen1
110JohnChen10
112Jose ManuelUrman10
119KarenColmenares3
102LexDe Haan9
113LuisPopp10
108NancyGreenberg10
101NeenaKochhar9
205ShelleyHiggins11
116ShelliBaida3
117SigalTobias3
100StevenKing9
206WilliamGietz11
" ], "text/plain": [ "[(115, 'Alexander', 'Khoo', 3),\n", " (109, 'Daniel', 'Faviet', 10),\n", " (114, 'Den', 'Raphaely', 3),\n", " (118, 'Guy', 'Himuro', 3),\n", " (111, 'Ismael', 'Sciarra', 10),\n", " (200, 'Jennifer', 'Whalen', 1),\n", " (110, 'John', 'Chen', 10),\n", " (112, 'Jose Manuel', 'Urman', 10),\n", " (119, 'Karen', 'Colmenares', 3),\n", " (102, 'Lex', 'De Haan', 9),\n", " (113, 'Luis', 'Popp', 10),\n", " (108, 'Nancy', 'Greenberg', 10),\n", " (101, 'Neena', 'Kochhar', 9),\n", " (205, 'Shelley', 'Higgins', 11),\n", " (116, 'Shelli', 'Baida', 3),\n", " (117, 'Sigal', 'Tobias', 3),\n", " (100, 'Steven', 'King', 9),\n", " (206, 'William', 'Gietz', 11)]" ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " employee_id, first_name, last_name, department_id\n", "FROM \n", " employees \n", "WHERE department_id IN \n", " (SELECT department_id FROM departments \n", " WHERE location_id = 1700) \n", "ORDER BY first_name , last_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The next code finds the employee who has the highest salary." ] }, { "cell_type": "code", "execution_count": 74, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namesalary
100StevenKing24000.0
" ], "text/plain": [ "[(100, 'Steven', 'King', 24000.0)]" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " employee_id, first_name, last_name, salary\n", "FROM\n", " employees\n", "WHERE salary = \n", " (SELECT MAX(salary) FROM employees)\n", "ORDER BY first_name , last_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "E.g., find all employees who salaries are greater than the average salary of all employees." ] }, { "cell_type": "code", "execution_count": 75, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_namesalary
100StevenKing24000.0
101NeenaKochhar17000.0
102LexDe Haan17000.0
103AlexanderHunold9000.0
108NancyGreenberg12000.0
109DanielFaviet9000.0
110JohnChen8200.0
114DenRaphaely11000.0
121AdamFripp8200.0
145JohnRussell14000.0
146KarenPartners13500.0
176JonathonTaylor8600.0
177JackLivingston8400.0
201MichaelHartstein13000.0
204HermannBaer10000.0
205ShelleyHiggins12000.0
206WilliamGietz8300.0
" ], "text/plain": [ "[(100, 'Steven', 'King', 24000.0),\n", " (101, 'Neena', 'Kochhar', 17000.0),\n", " (102, 'Lex', 'De Haan', 17000.0),\n", " (103, 'Alexander', 'Hunold', 9000.0),\n", " (108, 'Nancy', 'Greenberg', 12000.0),\n", " (109, 'Daniel', 'Faviet', 9000.0),\n", " (110, 'John', 'Chen', 8200.0),\n", " (114, 'Den', 'Raphaely', 11000.0),\n", " (121, 'Adam', 'Fripp', 8200.0),\n", " (145, 'John', 'Russell', 14000.0),\n", " (146, 'Karen', 'Partners', 13500.0),\n", " (176, 'Jonathon', 'Taylor', 8600.0),\n", " (177, 'Jack', 'Livingston', 8400.0),\n", " (201, 'Michael', 'Hartstein', 13000.0),\n", " (204, 'Hermann', 'Baer', 10000.0),\n", " (205, 'Shelley', 'Higgins', 12000.0),\n", " (206, 'William', 'Gietz', 8300.0)]" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " employee_id, first_name, last_name, salary\n", "FROM\n", " employees\n", "WHERE\n", " salary > \n", " (SELECT AVG(salary) FROM employees); " ] }, { "cell_type": "markdown", "metadata": { "id": "74DCdk9oeZWU" }, "source": [ "## 11.16 Connect to an Existing Database " ] }, { "cell_type": "markdown", "metadata": { "id": "90R-3ze3ecuY" }, "source": [ "Recall again that when we created new tables in section 11.3 or a new database in section 11.4, we used `sqlite://` to connect to the tables or to the database. This syntax is used to specify an **in-memory** table or database, that is, it indicates that we want to work with tables or databases that exist only in the memory of our local machine. This syntax uses two forward slashes `//` after `sqlite:`.\n", "\n", "To connect to an existing database that is stored in a local directory of our computer, we will use the syntax `sqlite:///relative_database_path`, where `relative_database_path` specifies the **relative path** to the local directory where the SQL database is stored. This syntax uses three forward slashes `///` after `sqlite:`.\n", "\n", "Similarly, we can connect to an existing database that is stored in a directory of our computer by using the **absolute path** to the database with the syntax `sqlite:////absolute_database_path`. This syntax uses four forward slashes `////` after `sqlite:`.\n", "\n", "In the following cell, we use `sqlite:///data/EssentialSQL.db` to initialize the connection to the database `EssentialSQL.db` which is located in the `data` subdirectory in the current working directory. " ] }, { "cell_type": "code", "execution_count": 76, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 36 }, "executionInfo": { "elapsed": 459, "status": "ok", "timestamp": 1668546516311, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "PjvC53LzKAA7", "outputId": "99fd3213-304a-4abf-db31-a2ac31ed130c" }, "outputs": [], "source": [ "%sql sqlite:///data/EssentialSQL.db" ] }, { "cell_type": "markdown", "metadata": { "id": "By0Hflsnig19" }, "source": [ "Note again that there are three forward slashes after `sqlite:`, since we provided a relative path to the database.\n" ] }, { "cell_type": "markdown", "metadata": { "id": "zeCNSm3L5tWs" }, "source": [ "We can inspect the tables in the database `EssentialSQL.db` in the following cell." ] }, { "cell_type": "code", "execution_count": 77, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 657 }, "executionInfo": { "elapsed": 639, "status": "ok", "timestamp": 1668546516947, "user": { "displayName": "Aleksandar Vakanski", "userId": "07675307153279708378" }, "user_tz": 420 }, "id": "i13EiAJ95mhL", "outputId": "e0981d5f-79cd-4e7a-e02e-07e49c9c4be2" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " sqlite://\n", " * sqlite:///data/EssentialSQL.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
Customers
Shippers
Employees
Orders
OrderDetails
" ], "text/plain": [ "[('Customers',), ('Shippers',), ('Employees',), ('Orders',), ('OrderDetails',)]" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT name FROM sqlite_master WHERE type='table'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And we can inspect the data in the table `Customers`." ] }, { "cell_type": "code", "execution_count": 78, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " sqlite://\n", " * sqlite:///data/EssentialSQL.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CustomerIDCompanyNameContactNameContactTitleAddressCityState
1Deerfield TileDick TerrcottaOwner450 Village StreetDeerfieldIL
2Sagebrush CarpetBarbara BerberDirector of Installations10 Industrial DriveEl PasoTX
3Floor Co.Jim WoodInstaller34218 Private LaneMonclairNJ
4Main Tile and BathToni FaucetOwnerSuite 23, Henry BuildingOrlandoFL
5Slots CarpetJack Diamond IIIPurchaser3024 Jackpot DriveLas VegasNV
" ], "text/plain": [ "[(1, 'Deerfield Tile', 'Dick Terrcotta', 'Owner', '450 Village Street', 'Deerfield', 'IL'),\n", " (2, 'Sagebrush Carpet', 'Barbara Berber', 'Director of Installations', '10 Industrial Drive', 'El Paso', 'TX'),\n", " (3, 'Floor Co.', 'Jim Wood', 'Installer', '34218 Private Lane', 'Monclair', 'NJ'),\n", " (4, 'Main Tile and Bath', 'Toni Faucet', 'Owner', 'Suite 23, Henry Building', 'Orlando', 'FL'),\n", " (5, 'Slots Carpet', 'Jack Diamond III', 'Purchaser', '3024 Jackpot Drive', 'Las Vegas', 'NV')]" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM Customers" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If the database does not exist, SQLite will create a new database with the provided name in the home directory. The following cell will create a new database named `test.db` in the current working directory. " ] }, { "cell_type": "code", "execution_count": 79, "metadata": { "tags": [] }, "outputs": [], "source": [ "%sql sqlite:///test.db" ] }, { "cell_type": "markdown", "metadata": { "id": "vweobvFVe4RB" }, "source": [ "\n", "\n", "## References\n", "\n", "1. SQL Tutorial, available at [https://www.sqltutorial.org/](https://www.sqltutorial.org/).\n", "2. Practice SQL with SQLite and Jupyter Notebook, by Chonghua Yin, available at [https://github.com/royalosyin/Practice-SQL-with-SQLite-and-Jupyter-Notebook](https://github.com/royalosyin/Practice-SQL-with-SQLite-and-Jupyter-Notebook).\n" ] }, { "cell_type": "markdown", "metadata": { "id": "bDbzcXmWe4RB" }, "source": [ "[BACK TO TOP](#top)" ] } ], "metadata": { "colab": { "collapsed_sections": [ "GiYZE-B7j3Ap" ], "provenance": [ { "file_id": "1_wSiz1ALZ1l9vda7yvvdLgEHHE8M0F66", "timestamp": 1666471615676 } ] }, "gpuClass": "premium", "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.11.5" } }, "nbformat": 4, "nbformat_minor": 4 }