<jupyter_start><jupyter_text>SQL<jupyter_code># Import libraries
import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>Connect Python to RDBMS software<jupyter_code># Script for username and password
import db_config

# SQLite
import sqlite3
conn1 = sqlite3.connect('SQLite_db.db')

# MySQL
import mysql.connector
conn2 = mysql.connector.connect(host='localhost', database='SQLcook',
                                user=db_config.user, password=db_config.pwd)                           

# PostgreSQL
import psycopg2
conn3 = psycopg2.connect(host='localhost', database='SQLcook', 
                         user=db_config.user, password=db_config.pwd)<jupyter_output><empty_output><jupyter_text>SQL Queries<jupyter_code># 1.0 Retrieve Table Records - emp
## SQLite
df1 = pd.read_sql('''SELECT * 
                     FROM emp
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT * 
                     FROM emp
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT * 
                     FROM emp
                  ;''', conn3)                 

df1, df2, df3
# 1.0 Retrieve Table Records - dept
## SQLite
df1 = pd.read_sql('''SELECT * 
                     FROM dept
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT * 
                     FROM dept
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT * 
                     FROM dept
                  ;''', conn3)                 

df1, df2, df3
# 1.1 Subquery, Referencing Column Alias in WHERE Clause
## SQLite
df1 = pd.read_sql('''SELECT *
                     FROM (
                        SELECT sal AS salary, comm AS commission 
                        FROM emp
                          ) X
                     WHERE salary < 5000
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT *
                     FROM (
                        SELECT sal AS salary, comm AS commission 
                        FROM emp
                          ) X
                     WHERE salary < 5000;
                 ''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT *
                     FROM (
                        SELECT sal AS salary, comm AS commission 
                        FROM emp
                          ) X
                     WHERE salary < 5000;
                 ''', conn3)                

df1, df2, df3
# 1.2 Search for Text Patterns - 'I' at any position and beginnning with 'ER'
## SQLite
df1 = pd.read_sql('''SELECT ename, job
                     FROM emp
                     WHERE deptno in (10,20)
                        AND (ename like '%I%' OR job like '%ER')
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT ename, job
                     FROM emp
                     WHERE deptno in (10,20)
                        AND (ename like '%I%' OR job like '%ER')
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT ename, job
                     FROM emp
                     WHERE deptno in (10,20)
                        AND (ename like '%I%' OR job like '%ER')
                  ;''', conn3)                

df1, df2, df3
# 1.3 Sort Values with Nulls - Non-null comm sort asc, nulls last
## SQLite
df1 = pd.read_sql('''SELECT ename, sal, comm
                     FROM (
                         SELECT ename, sal, comm,
                            CASE WHEN comm IS NULL THEN 0 
                                 ELSE 1 
                            END AS is_null
                         FROM EMP
                          ) X
                     ORDER BY is_null desc, comm       
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT ename, sal, comm
                     FROM (
                         SELECT ename, sal, comm,
                            CASE WHEN comm IS NULL THEN 0 
                                 ELSE 1 
                            END AS is_null
                         FROM EMP
                          ) X
                     ORDER BY is_null desc, comm       
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT ename, sal, comm
                     FROM (
                         SELECT ename, sal, comm,
                            CASE WHEN comm IS NULL THEN 0 
                                 ELSE 1 
                            END AS is_null
                         FROM EMP
                          ) X
                     ORDER BY is_null desc, comm       
                  ;''', conn3)                

df1, df2, df3
# 1.4 Sort based on a Data-Dependent Key - Filter attribute not hidden
## SQLite
df1 = pd.read_sql('''SELECT ename, sal, job, comm,
                        CASE WHEN job = 'SALESMAN' THEN comm
                             ELSE sal
                        END AS ordered
                     FROM emp
                     ORDER BY ordered     
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT ename, sal, job, comm,
                        CASE WHEN job = 'SALESMAN' THEN comm
                             ELSE sal
                        END AS ordered
                     FROM emp
                     ORDER BY ordered     
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT ename, sal, job, comm,
                        CASE WHEN job = 'SALESMAN' THEN comm
                             ELSE sal
                        END AS ordered
                     FROM emp
                     ORDER BY ordered     
                  ;''', conn3)                

df1, df2, df3
# 1.5 Stack One Table atop Another - UNION ALL; SELECT lists must match in type
## SQLite
df1 = pd.read_sql('''SELECT ename as ename_and_dname, deptno
                     FROM emp
                     WHERE deptno = 10
                     UNION ALL
                     SELECT dname, deptno
                     FROM dept     
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT ename as ename_and_dname, deptno
                     FROM emp
                     WHERE deptno = 10
                     UNION ALL
                     SELECT dname, deptno
                     FROM dept     
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT ename as ename_and_dname, deptno
                     FROM emp
                     WHERE deptno = 10
                     UNION ALL
                     SELECT dname, deptno
                     FROM dept     
                  ;''', conn3)                

df1, df2, df3
# 1.6 Retrieve Values from One Table That Do Not Exist in Another - EXCEPT
## SQLite
df1 = pd.read_sql('''SELECT deptno FROM dept
                     EXCEPT
                     SELECT deptno FROM emp    
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT deptno FROM dept
                     WHERE deptno NOT IN (
                         SELECT deptno FROM emp)                      
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT deptno FROM dept
                     EXCEPT
                     SELECT deptno FROM emp             
                  ;''', conn3)                

df1, df2, df3
# 1.7 Combine Tables with Related Rows - INNER JOIN
## SQLite
df1 = pd.read_sql('''SELECT e.ename, d.loc
                     FROM emp e inner join dept d
                        ON (e.deptno = d.deptno)
                     WHERE e.deptno = 10    
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT e.ename, d.loc
                     FROM emp e inner join dept d
                        ON (e.deptno = d.deptno)
                     WHERE e.deptno = 10    
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT e.ename, d.loc
                     FROM emp e inner join dept d
                        ON (e.deptno = d.deptno)
                     WHERE e.deptno = 10              
                  ;''', conn3)                

df1, df2, df3
# 1.8 Retrieve Rows from One Table That Do Not Correspond to Rows in Another - LEFT OUTER JOIN
## SQLite
df1 = pd.read_sql('''SELECT d.* 
                     FROM dept d 
                        LEFT OUTER JOIN emp e
                            ON (d.deptno = e.deptno)
                     WHERE e.deptno IS NULL
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT d.* 
                     FROM dept d 
                        LEFT OUTER JOIN emp e
                            ON (d.deptno = e.deptno)
                     WHERE e.deptno IS NULL                   
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT d.* 
                     FROM dept d 
                        LEFT OUTER JOIN emp e
                            ON (d.deptno = e.deptno)
                     WHERE e.deptno IS NULL           
                  ;''', conn3)                

df1, df2, df3
# 1.9 Return Missing Data from Multiple Tables - FULL OUTER JOIN; SQLite only supports LEFT OUTER JOIN
## SQLite
df1 = pd.read_sql('''SELECT d.deptno, d.dname, e.ename
                     FROM dept d LEFT OUTER JOIN emp e
                        ON (d.deptno = e.deptno)
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT *
                     FROM (
                     SELECT d.deptno, d.dname, e.ename
                     FROM dept d RIGHT OUTER JOIN emp e
                        ON (d.deptno = e.deptno)
                     UNION
                     SELECT d.deptno, d.dname, e.ename
                     FROM dept d LEFT OUTER JOIN emp e
                        ON (d.deptno = e.deptno)   
                          ) x
                     ORDER BY 1            
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT d.deptno, d.dname, e.ename
                     FROM dept d FULL OUTER JOIN emp e
                        ON (d.deptno = e.deptno)       
                  ;''', conn3)                

df1, df2, df3
# 1.10 Perform Outer Joins When Using Aggregates
## SQLite
cursor1 = conn1.cursor()
cursor1.execute('DROP TABLE IF EXISTS emp_bonus;')
cursor1.execute("CREATE TABLE emp_bonus ( \
	                empno decimal(4,0) NOT NULL, \
	                received date default NULL, \
	                type decimal(1,0) default NULL);")
cursor1.execute("INSERT INTO emp_bonus VALUES ('7934','2005-03-17','1');")
cursor1.execute("INSERT INTO emp_bonus VALUES ('7934','2005-02-15','2');")
df1 = pd.read_sql('''SELECT deptno,
                        SUM(DISTINCT sal) AS total_sal,
                        SUM(bonus) AS total_bonus
                     FROM (
                        SELECT e.empno, e.ename, e.sal, e.deptno,
                               e.sal * CASE WHEN eb.type IS NULL THEN 0
                                            WHEN eb.type = 1 THEN .1
                                            WHEN eb.type = 2 THEN .2
                                            ELSE .3
                                       END AS bonus
                        FROM emp e LEFT OUTER JOIN emp_bonus eb
                            ON (e.empno = eb.empno)
                        WHERE e.deptno = 10 
                          ) x
                     GROUP BY deptno
                  ;''', conn1)
cursor1.execute('DROP TABLE IF EXISTS emp_bonus;')                  
cursor1.close()

## MySQL
cursor2 = conn2.cursor()
cursor2.execute('DROP TABLE IF EXISTS emp_bonus;')
cursor2.execute("CREATE TABLE emp_bonus ( \
	                empno decimal(4,0) NOT NULL, \
	                received date default NULL, \
	                type decimal(1,0) default NULL);")
cursor2.execute("INSERT INTO emp_bonus VALUES ('7934','2005-03-17','1');")
cursor2.execute("INSERT INTO emp_bonus VALUES ('7934','2005-02-15','2');")
df2 = pd.read_sql('''SELECT deptno,
                        SUM(DISTINCT sal) AS total_sal,
                        SUM(bonus) AS total_bonus
                     FROM (
                        SELECT e.empno, e.ename, e.sal, e.deptno,
                               e.sal * CASE WHEN eb.type IS NULL THEN 0
                                            WHEN eb.type = 1 THEN .1
                                            WHEN eb.type = 2 THEN .2
                                            ELSE .3
                                       END AS bonus
                        FROM emp e LEFT OUTER JOIN emp_bonus eb
                            ON (e.empno = eb.empno)
                        WHERE e.deptno = 10 
                          ) x
                     GROUP BY deptno
                  ;''', conn2)
cursor2.execute('DROP TABLE IF EXISTS emp_bonus;')                  
cursor2.close()

## PostgreSQL
cursor3 = conn3.cursor()
cursor3.execute('DROP TABLE IF EXISTS emp_bonus;')
cursor3.execute("CREATE TABLE emp_bonus ( \
	                empno decimal(4,0) NOT NULL, \
	                received date default NULL, \
	                type decimal(1,0) default NULL);")
cursor3.execute("INSERT INTO emp_bonus VALUES ('7934','2005-03-17','1');")
cursor3.execute("INSERT INTO emp_bonus VALUES ('7934','2005-02-15','2');")
df3 = pd.read_sql('''SELECT deptno,
                        SUM(DISTINCT sal) AS total_sal,
                        SUM(bonus) AS total_bonus
                     FROM (
                        SELECT e.empno, e.ename, e.sal, e.deptno,
                               e.sal * CASE WHEN eb.type IS NULL THEN 0
                                            WHEN eb.type = 1 THEN .1
                                            WHEN eb.type = 2 THEN .2
                                            ELSE .3
                                       END AS bonus
                        FROM emp e LEFT OUTER JOIN emp_bonus eb
                            ON (e.empno = eb.empno)
                        WHERE e.deptno = 10 
                          ) x
                     GROUP BY deptno
                  ;''', conn3)
cursor3.execute('DROP TABLE IF EXISTS emp_bonus;')                  
cursor3.close()            

df1, df2, df3
# 1.11 Separate Numeric and Character Data
## SQLite - does not support translate

## MySQL - does not support translate

## PostgreSQL
df3 = pd.read_sql('''SELECT replace(translate(data,'0123456789','0000000000'),'0','') AS ename,
                            replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',
                                              rpad('z',26,'z')),'z','') AS sal                             
                     FROM (
                         SELECT ename||sal AS data
                         FROM emp
                          ) x
                  ;''', conn3)             

df3
# 1.12 Average Value in a Column - ignore nulls; with group by
## SQLite
df1 = pd.read_sql('''SELECT deptno, AVG(sal) as avg_sal
                     FROM emp
                     GROUP BY deptno
                     ORDER BY deptno
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT deptno, AVG(sal) as avg_sal
                     FROM emp
                     GROUP BY deptno
                     ORDER BY deptno
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT deptno, AVG(sal) as avg_sal
                     FROM emp
                     GROUP BY deptno
                     ORDER BY deptno
                  ;''', conn3)               

df1, df2, df3
# 1.13 Min/Max Value in a Column
## SQLite
df1 = pd.read_sql('''SELECT MIN(sal) as min_sal, MAX(sal) as max_sal
                     FROM emp
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT MIN(sal) as min_sal, MAX(sal) as max_sal
                     FROM emp
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT MIN(sal) as min_sal, MAX(sal) as max_sal
                     FROM emp
                  ;''', conn3)               

df1, df2, df3
# 1.14 Sum Values in a Column
## SQLite
df1 = pd.read_sql('''SELECT SUM(sal) as sum_sal
                     FROM emp
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT SUM(sal) as sum_sal
                     FROM emp
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT SUM(sal) as sum_sal
                     FROM emp
                  ;''', conn3)               

df1, df2, df3
# 1.15 Count Rows in a Table
## SQLite
df1 = pd.read_sql('''SELECT COUNT(*) as cnt
                     FROM emp
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT COUNT(*) as cnt
                     FROM emp
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT COUNT(*) as cnt
                     FROM emp
                  ;''', conn3)               

df1, df2, df3
# 1.16 Count Values in a Column - ignore nulls if column name is passed as argument
## SQLite
df1 = pd.read_sql('''SELECT COUNT(comm) AS cnt_comm
                     FROM emp
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT COUNT(comm) AS cnt_comm
                     FROM emp
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT COUNT(comm) AS cnt_comm
                     FROM emp
                  ;''', conn3)               

df1, df2, df3
# 1.17 Running Total - numpy cumsum
## SQLite
df1 = pd.read_sql('''SELECT ename, sal,
                            SUM(sal) OVER (
                                ORDER BY sal, empno) AS running_total
                     FROM emp
                     ORDER BY 2
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT ename, sal,
                            SUM(sal) OVER (
                                ORDER BY sal, empno) AS running_total
                     FROM emp
                     ORDER BY 2
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT ename, sal,
                            SUM(sal) OVER (
                                ORDER BY sal, empno) AS running_total
                     FROM emp
                     ORDER BY 2
                  ;''', conn3)               

df1, df2, df3
# 1.18 Running Product - numpy cumprod
## SQLite - no logarithmic function

## MySQL
df2 = pd.read_sql('''SELECT empno, ename, sal,
                            EXP(SUM(LN(sal)) OVER (
                                ORDER BY sal, empno)) AS running_prod
                     FROM emp
                     WHERE deptno = 10
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT empno, ename, sal,
                            EXP(SUM(LN(sal)) OVER (
                                ORDER BY sal, empno)) AS running_prod
                     FROM emp
                     WHERE deptno = 10
                  ;''', conn3)               

df2, df3
# 1.19 Smooth a Series of Values - pandas shift and rolling (Moving Average)
## SQLite
df1 = pd.read_sql('''SELECT ename, sal, LagOne, LagTwo,
                            (sal + LagOne + LagTwo)/3 AS MovingAverage
                     FROM (SELECT ename, sal,
                                LAG(sal, 1) OVER (ORDER BY sal, empno) as LagOne,
                                LAG(sal, 2) OVER (ORDER BY sal, empno) as LagTwo
                           FROM emp) x
                  ;''', conn1)

## MySQL
df2 = pd.read_sql('''SELECT ename, sal, LagOne, LagTwo,
                            (sal + LagOne + LagTwo)/3 AS MovingAverage
                     FROM (SELECT ename, sal,
                                LAG(sal, 1) OVER (ORDER BY sal, empno) as LagOne,
                                LAG(sal, 2) OVER (ORDER BY sal, empno) as LagTwo
                           FROM emp) x
                  ;''', conn2)

## PostgreSQL
df3 = pd.read_sql('''SELECT ename, sal, LagOne, LagTwo,
                            (sal + LagOne + LagTwo)/3 AS MovingAverage
                     FROM (SELECT ename, sal,
                                LAG(sal, 1) OVER (ORDER BY sal, empno) as LagOne,
                                LAG(sal, 2) OVER (ORDER BY sal, empno) as LagTwo
                           FROM emp) x
                  ;''', conn3)               

df1, df2, df3
# 1.20 Running Total with Changing Values
## SQLite
cursor1 = conn1.cursor()
cursor1.execute('DROP VIEW IF EXISTS V;')
cursor1.execute('DROP TABLE IF EXISTS t1;')
cursor1.execute("CREATE TABLE t1 ( \
	                id integer NOT NULL);")
cursor1.execute("INSERT INTO t1 VALUES (1);")  
cursor1.execute("CREATE VIEW V (id, amt, trx) AS \
                 SELECT 1, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 2, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 3, 50,  'PY' FROM t1 UNION ALL \
                 SELECT 4, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 5, 200, 'PY' FROM t1 UNION ALL \
                 SELECT 6, 50, 'PY' FROM t1;")
df1 = pd.read_sql('''SELECT CASE WHEN trx = 'PY' THEN 'PAYMENT'
                                 ELSE 'PURCHASE'
                            END AS trx_type,
                            amt,
                            SUM(CASE WHEN trx = 'PY' THEN -amt
                                     ELSE amt
                                END) OVER (ORDER BY id) AS balance
                     FROM V
                  ;''', conn1)  
cursor1.execute('DROP VIEW IF EXISTS V;')
cursor1.execute('DROP TABLE IF EXISTS t1;') 
cursor1.close()  

## MySQL
cursor2 = conn2.cursor()
cursor2.execute('DROP VIEW IF EXISTS V;')
cursor2.execute('DROP TABLE IF EXISTS t1;')
cursor2.execute("CREATE TABLE t1 ( \
	                id integer NOT NULL);")
cursor2.execute("INSERT INTO t1 VALUES (1);")  
cursor2.execute("CREATE VIEW V (id, amt, trx) AS \
                 SELECT 1, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 2, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 3, 50,  'PY' FROM t1 UNION ALL \
                 SELECT 4, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 5, 200, 'PY' FROM t1 UNION ALL \
                 SELECT 6, 50, 'PY' FROM t1;")
df2 = pd.read_sql('''SELECT CASE WHEN trx = 'PY' THEN 'PAYMENT'
                                 ELSE 'PURCHASE'
                            END AS trx_type,
                            amt,
                            SUM(CASE WHEN trx = 'PY' THEN -amt
                                     ELSE amt
                                END) OVER (ORDER BY id) AS balance
                     FROM V
                  ;''', conn2)  
cursor2.execute('DROP VIEW IF EXISTS V;')
cursor2.execute('DROP TABLE IF EXISTS t1;')
cursor2.close()  

## PostgreSQL
cursor3 = conn3.cursor()
cursor3.execute('DROP VIEW IF EXISTS V;')
cursor3.execute('DROP TABLE IF EXISTS t1;')
cursor3.execute("CREATE TABLE t1 ( \
	                id integer NOT NULL);")
cursor3.execute("INSERT INTO t1 VALUES (1);")  
cursor3.execute("CREATE VIEW V (id, amt, trx) AS \
                 SELECT 1, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 2, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 3, 50,  'PY' FROM t1 UNION ALL \
                 SELECT 4, 100, 'PR' FROM t1 UNION ALL \
                 SELECT 5, 200, 'PY' FROM t1 UNION ALL \
                 SELECT 6, 50, 'PY' FROM t1;")
df3 = pd.read_sql('''SELECT CASE WHEN trx = 'PY' THEN 'PAYMENT'
                                 ELSE 'PURCHASE'
                            END AS trx_type,
                            amt,
                            SUM(CASE WHEN trx = 'PY' THEN -amt
                                     ELSE amt
                                END) OVER (ORDER BY id) AS balance
                     FROM V
                  ;''', conn3)  
cursor3.execute('DROP VIEW IF EXISTS V;')  
cursor3.execute('DROP TABLE IF EXISTS t1;')  
cursor3.close()                   

df1, df2, df3
# 1.21 Find Outliers Using the Median Absolute Deviation - demonstrate WITH clause
## SQLite - too complicated workaround; use python numpy and/or pandas

## MySQL - too complicated workaround; use python numpy and/or pandas
   
## PostgreSQL
df3 = pd.read_sql('''WITH median(median)        AS (SELECT PERCENTILE_CONT(0.5)
                                                            WITHIN GROUP(ORDER BY sal)
                                                    FROM emp),
                          devtab(deviation)     AS (SELECT ABS(sal - median)
                                                    FROM emp INNER JOIN median ON 1=1),
                          MedAbsDeviation(MAD)  AS (SELECT PERCENTILE_CONT(0.5)
                                                            WITHIN GROUP(ORDER BY deviation)
                                                    FROM devtab)
                     SELECT ABS(sal - MAD)/MAD AS normalize, sal, ename, job
                     FROM MedAbsDeviation INNER JOIN emp ON 1=1                                           
                  ;''', conn3)               

df3<jupyter_output><empty_output><jupyter_text>Closing - Reset Databases and Close Connections<jupyter_code># Clean up databases before closing
cursor1 = conn1.cursor()
cursor1.execute('DROP VIEW IF EXISTS V;')
cursor1.execute('DROP TABLE IF EXISTS emp_bonus;')
cursor1.execute('DROP TABLE IF EXISTS t1;')
cursor1.execute('DROP TABLE IF EXISTS t10;')
cursor1.close()

cursor2 = conn2.cursor()
cursor2.execute('DROP VIEW IF EXISTS V;')
cursor2.execute('DROP TABLE IF EXISTS emp_bonus;')
cursor2.execute('DROP TABLE IF EXISTS t1;')
cursor2.execute('DROP TABLE IF EXISTS t10;')
cursor2.close()

cursor3 = conn3.cursor()
cursor3.execute('DROP VIEW IF EXISTS V;')
cursor3.execute('DROP TABLE IF EXISTS emp_bonus;')
cursor3.execute('DROP TABLE IF EXISTS t1;')
cursor3.execute('DROP TABLE IF EXISTS t10;')
cursor3.close()
# Closing connections
conn1.close()
conn2.close()
conn3.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0907099550166243, 'fraction_numerical': 0.0253080383336593, 'mean_word_length': 1.11779324055666, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 53, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '384620', 'license': 'MIT', 'n_tokens_mistral': 7740, 'n_tokens_neox': 6883, 'n_words': 2151, 'path': 'SQL.ipynb', 'repo_name': 'gwong8/SQL', 'size': '25565'}