Data Science — SQL

Basic queries

-- Select data
SELECT * FROM users;

-- Filter
SELECT * FROM users WHERE age > 25;

-- Order
SELECT * FROM users ORDER BY name;

-- Limit
SELECT * FROM users LIMIT 10;

Aggregations

-- Count
SELECT COUNT(*) FROM users;

-- Group by
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- Having
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

Joins

-- Inner join
SELECT * FROM users 
INNER JOIN orders ON users.id = orders.user_id;

-- Left join
SELECT * FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

Subqueries

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

Window functions

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

Python + SQL

import sqlite3
import pandas as pd

conn = sqlite3.connect('database.db')
df = pd.read_sql('SELECT * FROM users', conn)

Mini Practice

1. Write basic queries
2. Use aggregations
3. Join tables
4. Use window functions

Up Next

Continue with Python - Programming for data science.