# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # SQL

# Before you start, download the SQLite version of the [Chinook database](https://github.com/lerocha/chinook-database) from [GitHub](https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite).

# +
import numpy as np
import pandas as pd
import sqlite3

# Open connection to database
db_connection = sqlite3.connect('Chinook_Sqlite.sqlite')
# -

# ## Example
#
# Select the first 10 customers.

pd.read_sql(
    '''SELECT *
       FROM customer
       LIMIT 10''', con=db_connection)

# ## Exercise 1
#
# Select the first name of all customers from the UK.

pd.read_sql(
    '''SELECT FirstName
       FROM customer
       WHERE Country == \'United Kingdom\'''', con=db_connection)

# ## Exercise 2
#
# Select the city and country of all customers from the UK or Portugal.

pd.read_sql(
    '''SELECT City, Country
       FROM customer
       WHERE Country == \'United Kingdom\'
          OR Country == \'Portugal\'''', con=db_connection)

# ## Exercise 3
#
# Select the first 10 invoices.

pd.read_sql(
    '''SELECT *
       FROM invoice
       LIMIT 10''', con=db_connection)

# ## Exercise 4
#
# Join the tables `customer` and `invoice`, and retrieve customer ID and invoice amount.

pd.read_sql(
    '''SELECT c.CustomerId, i.Total
       FROM customer AS c
       JOIN invoice AS i
       ON c.CustomerId == i.CustomerId''', con=db_connection)

# Now compute the total of all invoices by customer.

pd.read_sql(
    '''SELECT c.CustomerId, SUM(i.Total)
       FROM customer AS c
       JOIN invoice AS i
       ON c.CustomerId == i.CustomerId
       GROUP BY c.CustomerId''', con=db_connection)

# Now aggregate only invoices from 2013.
#
# Hint: use the SQLite function `STRFTIME` on `InvoiceDate`.

pd.read_sql(
    '''SELECT c.CustomerId, SUM(i.Total)
       FROM customer AS c
       JOIN invoice AS i
       ON c.CustomerId == i.CustomerId
       WHERE STRFTIME(\'%Y\', i.InvoiceDate) == \'2013\'
       GROUP BY c.CustomerId''', con=db_connection)

# Now order by total amount in descending order.

pd.read_sql(
    '''SELECT c.CustomerId, SUM(i.Total) AS total
       FROM customer AS c
       JOIN invoice AS i
       ON c.CustomerId == i.CustomerId
       WHERE STRFTIME(\'%Y\', i.InvoiceDate) == \'2013\'
       GROUP BY c.CustomerId
       ORDER BY total DESC''', con=db_connection)

# Finally, add the first name of the support rep from table `employee`.

pd.read_sql(
    '''SELECT c.CustomerId, e.FirstName, SUM(i.Total) AS total
       FROM customer AS c
       JOIN invoice AS i
       ON c.CustomerId == i.CustomerId
       JOIN employee AS e
       ON c.SupportRepId == e.EmployeeId
       WHERE STRFTIME(\'%Y\', i.InvoiceDate) == \'2013\'
       GROUP BY c.CustomerId
       ORDER BY total DESC''', con=db_connection)

StarcoderdataJupyterScriptsDedupFiltered