Skip to content

Instantly share code, notes, and snippets.

@cedias

cedias/sql-data-mining.ipynb

Last active October 1, 2020 13:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cedias/cfa468d9fcde747869967e34a3e27dea to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cedias/cfa468d9fcde747869967e34a3e27dea to your computer and use it in GitHub Desktop.
Download ZIP
SQL-Data Mining.ipynb
Display the source blob
Display the rendered blob
Raw
sql-data-mining.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "SQL-Data Mining.ipynb",
"provenance": [],
"collapsed_sections": [],
"authorship_tag": "ABX9TyOYwnE+Pv25jTGMPe34a8EN",
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/cedias/cfa468d9fcde747869967e34a3e27dea/sql-data-mining.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "s1eEFAzkUV3I"
},
"source": [
"# SQL Practical\n",
"\n",
"## Instructions:\n",
"\n",
"\n",
"1. Run the first code cell by clicking the little play button\n",
"2. Complete the SQL queries where asked\n",
"3. Run each query by simply clicking the play button\n",
"\n",
"\n",
"\n"
]
},
{
"cell_type": "code",
"metadata": {
"id": "oTYLewKYEN9r"
},
"source": [
"# <===== CLICK THE PLAY BUTTON HERE FIRST !!!!!\n",
"#\n",
"#\n",
"!wget -O chinook.zip https://cdn.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip\n",
"!unzip -o chinook.zip\n",
"!pip install pandas\n",
"import sqlite3\n",
"import pandas as pd\n",
"def make_sql_query(query):\n",
" # Create connection to database\n",
" connection = sqlite3.connect(\"chinook.db\") \n",
" pd.set_option('display.max_columns', 500)\n",
" result = pd.read_sql_query(query, connection)\n",
" print(\"=\"*20)\n",
" print(f\"Found {len(result)} rows\")\n",
" print(\"=\"*20)\n",
" print(result) \n",
"\n",
"make_sql_query(\"\"\"select TrackId, Name, AlbumId from Tracks \"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "nm-6SC_YUSdC"
},
"source": [
"![chinook.db](https://cdn.sqlitetutorial.net/wp-content/uploads/2015/11/sqlite-sample-database-color.jpg)\n",
" ### 1) In tracks Table: Select everything"
]
},
{
"cell_type": "code",
"metadata": {
"id": "sWGYM0vDINQJ"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "Ft3OI9VLQrHr"
},
"source": [
"\n",
"## 2) List tracks names where UnitPrice is above 0.99"
]
},
{
"cell_type": "code",
"metadata": {
"id": "8ukEyw3bQqFR"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "W5ycf9bWINQM"
},
"source": [
"### 3) For all customers, find their company names (without duplicates)"
]
},
{
"cell_type": "code",
"metadata": {
"id": "mlIO5Qq9INQM"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "uGKLGA5hINQP"
},
"source": [
" ### 4) For each tracks, get their name, their lenght in milliseconds, and their album id. Order them by album id in ascending order."
]
},
{
"cell_type": "code",
"metadata": {
"id": "_OS1Mn54INQQ"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "3sHA9sWVINQS"
},
"source": [
"### 5) Extract the trackid and name of the first 10 tracks (in the tracks table)"
]
},
{
"cell_type": "code",
"metadata": {
"id": "y3CkVWuTINQT"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "Bemk2jchINQV"
},
"source": [
" ### 6) extract the name, the lengh, the size in bytes and the album id of each track which lengh is greater than 250000 millisecond and which are from album number 1 (id=1).\n"
]
},
{
"cell_type": "code",
"metadata": {
"id": "baPXr3A-INQW"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "tvvbt3GIINQZ"
},
"source": [
" ### 7) extract the name, the album id and the composer of each track which composer's name contains \"smith\". Order the result by ascending album id"
]
},
{
"cell_type": "code",
"metadata": {
"id": "aJzJa8EUINQZ"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "2LnRvLa5INQc"
},
"source": [
"### 8) extract the trackid, the name of each track which name ends with the word \"Wild\""
]
},
{
"cell_type": "code",
"metadata": {
"id": "21zEMH-uINQd"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "ipZevuF5INQi"
},
"source": [
"### 9) extract the trackid, the name of each track which contains the word \"Wild\""
]
},
{
"cell_type": "code",
"metadata": {
"id": "iM8NZ0V-INQj"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "5ccmmu5wINQl"
},
"source": [
"### 10) extract the trackid, the name and genre id of each track that are NOT from genre 1 , 2 OR 3"
]
},
{
"cell_type": "code",
"metadata": {
"id": "CGj5MwPxINQm"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")\n"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "4EKleWN_INQo"
},
"source": [
"### 11) select the TrackId, Track, Album and Artist of artist whose id is 10"
]
},
{
"cell_type": "code",
"metadata": {
"id": "IP099kDmINQp"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")\n"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "EZBUCIcXINQr"
},
"source": [
"### 12) Count how much invoices has each customer"
]
},
{
"cell_type": "code",
"metadata": {
"id": "LS3KpyqsINQr"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {
"id": "2wF75zydINQu"
},
"source": [
"### 13) Which artists have written the most albums ? "
]
},
{
"cell_type": "code",
"metadata": {
"id": "qKB0r_y6INQu"
},
"source": [
"make_sql_query(\"\"\" \n",
"\n",
"-- Write the query here\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\"\"\")"
],
"execution_count": null,
"outputs": []
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment