Queries

import pymysql
pymysql.install_as_MySQLdb()

%load_ext sql
%config SqlMagic.autocommit=True

connection_string = "mysql://root:simple@127.0.0.1"

%sql $connection_string

%%sql
use app

%%sql
SHOW TABLES

%%sql

SELECT * FROM account LIMIT 10

%%sql
SELECT * FROM account ORDER BY last_name DESC, first_name DESC LIMIT 10 

%%sql
SELECT * FROM account ORDER BY dob LIMIT 10 OFFSET 100 

%%sql

SELECT * 
   FROM account 
   WHERE phone = 69235537483 

%%sql

SELECT * 
   FROM account 
   WHERE first_name = "Michael"
   ORDER BY dob
   LIMIT 10 OFFSET 20 

%%sql

SELECT * FROM account WHERE first_name="Anne" AND dob > "2001-01-01" LIMIT 10

%%sql
SELECT * 
FROM account 
WHERE DATEDIFF(now(), dob) < 300
LIMIT 10

%%sql
SELECT * 
FROM account 
WHERE NOT (DATEDIFF(now(), dob) < 300)  
LIMIT 10

%%sql
SELECT * 
FROM account 
WHERE dob is NULL  
LIMIT 10

%%sql
SELECT * FROM account WHERE dob BETWEEN "1978-01-01" AND "1997-01-01" AND first_name="Michael"
LIMIT 10

%%sql
SELECT * FROM account LIMIT 10

%%sql
SELECT DISTINCT last_name, first_name FROM account
ORDER BY last_name, first_name
LIMIT 5

%%sql
SELECT phone, first_name, last_name FROM account
ORDER BY last_name, first_name
LIMIT 5

%%sql
SELECT phone, concat(last_name, ", ", first_name) full_name FROM account
ORDER BY full_name
LIMIT 5

%%sql
SELECT * FROM (
    SELECT phone, first_name, last_name, floor(datediff(now(), dob)/365.25) age  
    FROM account) as q
WHERE age < 35
LIMIT 10

%%sql

SELECT *, phone id 
FROM account
LIMIT 10

%%sql
SELECT phone from account where last_name>"S" LIMIT 5

# Give me all the accounts that have a credit card

%%sql
-- accounts with a credit card
SELECT * 
FROM account 
WHERE phone IN (SELECT phone FROM credit_card)
LIMIT 5

%%sql
-- accounts with no credit card
SELECT * 
FROM account 
WHERE phone NOT IN (SELECT phone FROM credit_card)
LIMIT 5

%%sql
select * from purchase limit 5

%%sql
-- All the accounts that purchased AddOn #2.
SELECT * FROM account 
WHERE phone IN (SELECT phone FROM purchase WHERE addon_id=2)
LIMIT 5

%%sql
-- All the accounts that purchased AddOn #2.
SELECT DISTINCT phone FROM purchase WHERE addon_id=2
LIMIT 5

%%sql
-- Give me all accounts who have purchased both Addon 2 and 3
SELECT *
FROM account WHERE 
    phone in (SELECT phone FROM purchase WHERE addon_id=2) AND 
    phone in (SELECT phone FROM purchase WHERE addon_id=3)
LIMIT 5

%%sql
-- Give me all accounts who have purchased both Addon 2 or 3
SELECT *
FROM account WHERE 
    phone in (SELECT phone FROM purchase WHERE addon_id=2) or 
    phone in (SELECT phone FROM purchase WHERE addon_id=3)
LIMIT 5

%%sql
-- Give me all accounts who have purchased both Addon 2 or 3
SELECT *
FROM account WHERE 
    phone IN (SELECT phone FROM purchase WHERE addon_id in (2, 3))
LIMIT 5

%%sql
-- Give me all accounts who have purchased both Addon 2 but not 3
SELECT *
FROM account WHERE 
    phone IN (SELECT phone FROM purchase WHERE addon_id = 2) AND 
    phone NOT IN (SELECT phone FROM purchase WHERE addon_id = 3)
LIMIT 5

%%sql
-- Accounts with credit cards that have no purchases
SELECT * FROM account
WHERE phone in (SELECT phone from credit_card where card_number NOT IN (SELECT card_number FROM purchase))
LIMIT 5

%%sql
-- Accounts with credit cards but no purchases
SELECT * FROM account
WHERE phone in (SELECT phone from credit_card)
AND phone NOT IN (SELECT phone FROM purchase)
LIMIT 5