# ---
# 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 Basics
#
# **Setting up the conda env:**
#
# ```
# conda create -n sql python
# conda activate sql
# conda install ipython-sql sqlalchemy psycopg2 notebook pandas -c conda-forge
# ```
#
#
# **Sample dataset:**
# - [cities.csv](https://github.com/giswqs/postgis/blob/master/data/cities.csv)
# - [countries.csv](https://raw.githubusercontent.com/giswqs/postgis/master/data/countries.csv)
#

# ## Connecting to the database

# %load_ext sql

import os

host = "localhost"
database = "sdb"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')

connection_string = f"postgresql://{user}:{password}@{host}/{database}"

# %sql $connection_string

# + language="sql"
#
# SELECT * FROM cities LIMIT 10
# -

# ## The SQL SELECT statement

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities

# + language="sql"
#
# SELECT * FROM cities LIMIT 10

# + language="sql"
#
# SELECT name, country FROM cities LIMIT 10

# + language="sql"
#
# SELECT DISTINCT country FROM cities LIMIT 10

# + language="sql"
#
# SELECT COUNT(DISTINCT country) FROM cities

# + language="sql"
#
# SELECT MAX(population) FROM cities

# + language="sql"
#
# SELECT SUM(population) FROM cities

# + language="sql"
#
# SELECT AVG(population) FROM cities

# + language="sql"
#
# SELECT * FROM cities ORDER BY country LIMIT 10

# + language="sql"
#
# SELECT * FROM cities ORDER BY country ASC, population DESC LIMIT 10
# -

# ## The WHERE Clause

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country='USA'

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country='USA' OR country='CAN'

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country='USA' AND population>1000000

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country LIKE 'U%'

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country LIKE '%A'

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country LIKE '_S_'

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE country IN ('USA', 'CAN', 'CHN')

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities WHERE population BETWEEN 1000000 AND 10000000
# -

# ## SQL Joins
#
# Reference: https://www.w3schools.com/sql/sql_join.asp
#
# Here are the different types of the JOINs in SQL:
#
# - `(INNER) JOIN`: Returns records that have matching values in both tables
# - `LEFT (OUTER) JOIN`: Returns all records from the left table, and the matched records from the right table
# - `RIGHT (OUTER) JOIN`: Returns all records from the right table, and the matched records from the left table
# - `FULL (OUTER) JOIN`: Returns all records when there is a match in either left or right table
#
# ![](https://i.imgur.com/mITYzuS.png)

# + language="sql"
#
# SELECT COUNT(*) FROM cities

# + language="sql"
#
# SELECT * FROM cities LIMIT 10

# + language="sql"
#
# SELECT COUNT(*) FROM countries

# + language="sql"
#
# SELECT * FROM countries LIMIT 10
# -

# ### SQL Inner Join

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities INNER JOIN countries ON cities.country = countries."Alpha3_code"

# + tags=["hide-output"] language="sql"
#
# SELECT name, country, countries."Country" FROM cities INNER JOIN countries ON cities.country = countries."Alpha3_code"
# -

# ### SQL Left Join

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities LEFT JOIN countries ON cities.country = countries."Alpha3_code"
# -

# ### SQL Right Join

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities RIGHT JOIN countries ON cities.country = countries."Alpha3_code"
# -

# ### SQL Full Join

# + tags=["hide-output"] language="sql"
#
# SELECT * FROM cities FULL JOIN countries ON cities.country = countries."Alpha3_code"
# -

# ### SQL Union

# + tags=["hide-output"] language="sql"
#
# SELECT country FROM cities
# UNION 
# SELECT "Alpha3_code" FROM countries
# -

# ## Aggregation
#
# ### Group By

# + tags=["hide-output"] language="sql"
#
# SELECT COUNT(name), country 
# FROM cities 
# GROUP BY country 
# ORDER BY COUNT(name) DESC

# + tags=["hide-output"] language="sql"
#
# SELECT countries."Country", COUNT(name)
# FROM cities
# LEFT JOIN countries ON cities.country = countries."Alpha3_code"
# GROUP BY countries."Country"
# ORDER BY COUNT(name) DESC
# -

# ### Having

# + language="sql"
#
# SELECT COUNT(name), country
# FROM cities
# GROUP BY country
# HAVING COUNT(name) > 40
# ORDER BY COUNT(name) DESC

# + language="sql"
#
# SELECT countries."Country", COUNT(name)
# FROM cities
# LEFT JOIN countries ON cities.country = countries."Alpha3_code"
# GROUP BY countries."Country"
# HAVING COUNT(name) > 40
# ORDER BY COUNT(name) DESC
# -

# ## Conditional statements

# + tags=["hide-output"] language="sql"
#
# SELECT name, population,
# CASE
#     WHEN population > 10000000 THEN 'Megacity'
#     WHEN population > 1000000 THEN 'Large city'
#     ELSE 'Small city'
# END AS category
# FROM cities
# -

# ## Saving results

# + language="sql"
#
# SELECT *
# INTO cities_new
# FROM cities

# + language="sql"
#
# DROP TABLE IF EXISTS cities_usa;
#
# SELECT *
# INTO cities_usa
# FROM cities
# WHERE country = 'USA'

# + language="sql"
#
# INSERT INTO cities_usa
# SELECT *
# FROM cities
# WHERE country = 'CAN'
# -

# ## SQL Comments
#
# ### Single line coments

# + language="sql"
#
# SELECT * FROM cities LIMIT 10 -- This is a comment;
# -

# ### Multi-line comments

# + language="sql"
#
# SELECT COUNT(name), country 
# FROM cities 
# /*
#  * Adding Group by
#  * Adding Order by
#  */
# GROUP BY country 
# ORDER BY COUNT(name) DESC
# LIMIT 10
# -



StarcoderdataJupyterScriptsDedupFiltered