<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 SQL
SQL is a language for getting data from databases. It is also becoming essential to have some basic familiarity with SQL because it is universally used to slice and dice data across many different data storage formats and technologies.
We will only show the use of SQL as a query language here, since that is probably all that a statistician will use SQL for. However, SQL can also be used to create and modify tables, as well as manage database permissions.
SQL via pandas DataFrames
We will use the pandas-sql package to practice SQL syntax for querying a pandas DataFrame. Later, we will see how to use an actual relational database.
Step1:  Using SQL for Queries
Note that SQL is case-insensitive, but it is traditional to use ALL CAPS for SQL keywords. It is also standard to end SQL statements with a semi-colon.
Step3:  Ordering
Step5:  Aggregate queries
Step6:  Joins
A join occurs when you combine information from two or more database tables, based on information in a column that is common among the tables. As usual, it is easier to understand the concept with examples.
Step8:  Matching students and majors
Inner join
Step10:  Left outer join
SQL also has RIGHT OUTER JOIN and FULL OUTER JOIN but these are not currently supported by SQLite3 (the database engine used by pdsql).
Step12:  Emulating a full outer join with UNION ALL
Only necessary if the database does not proivde FULL OUTER JOIN
Step14:  Using linker tables to match students to classes (a MANY TO MANY join)
Step16:  Same thing but including students with no majors
Step17:  Using SQLite3
SQLite3 is part of the standard library. However, the mechanics of using essentially any database in Python is similar, because of the Python DB-API.
Step18:  SQLite specific commands to get metadata
Unlike SQL syntax for queries, how you get metadata from a relational database is vendor-specific. You'll have to read the docs to find out what is needed for your SQL flavor.
What tables are there in the database?
Step19:  What are the columns of the table "Album"?
Step20:  Standard SQL statements with parameter substitution
Note
Step21:  Not this
Step23:  User defined functions
Sometimes it is useful to have custom functions that run on the database server rather than on the client. These are called User Defined Functions or UDF. How do to do this varies with the database used, but it is fairly simple with Python and SQLite.
A standard UDF
Step25:  An aggregate UDF
We can also add aggregate UDFs similar to SQL MIN, SUM, COUNT etc. Aggregate UDFs require you to write a class __init__, step and finalize methods.
Step26:  Using SQL magic functions
We will use the ipython-sql notebook extension for convenience. This will only work in notebooks and IPython scripts with the .ipy extension.
Step27:  Configuring the SqlMagic extension
Step28:  Connect to SQLite3 database
Step29:  Other databases
See SQLAlchemy connection strings for how to connect to other databases such as Oracle, MySQL or PostgreSQL.
Line magic
Step30:  Cell magic
Step31:  You can assign results of queries to Python names
Step32:  Resutls behave like lists
Step33:  You can use Python variables in your queires.
Use 
Step34:  Convert to pandas dataframe
<ASSISTANT_TASK:>
Python Code:
import seaborn as sns
import pandas as pd
import numpy as np
from pandasql import PandaSQL
pdsql = PandaSQL()
tips = sns.load_dataset('tips')
tips.head()
Explanation: SQL
SQL is a language for getting data from databases. It is also becoming essential to have some basic familiarity with SQL because it is universally used to slice and dice data across many different data storage formats and technologies.
We will only show the use of SQL as a query language here, since that is probably all that a statistician will use SQL for. However, SQL can also be used to create and modify tables, as well as manage database permissions.
SQL via pandas DataFrames
We will use the pandas-sql package to practice SQL syntax for querying a pandas DataFrame. Later, we will see how to use an actual relational database.
End of explanation
pdsql("SELECT * FROM tips LIMIT 5;")
pdsql("SELECT * FROM tips WHERE sex='Female' LIMIT 5;")
pdsql("SELECT tip, sex, size FROM tips WHERE total_bill< 10 LIMIT 5;")
Explanation: Using SQL for Queries
Note that SQL is case-insensitive, but it is traditional to use ALL CAPS for SQL keywords. It is also standard to end SQL statements with a semi-colon.
End of explanation
query = 
SELECT * FROM tips
WHERE sex='Female' and smoker='Yes'
ORDER BY total_bill ASC
LIMIT 5;
pdsql(query)
Explanation: Ordering
End of explanation
query = 
SELECT count(*) AS count, max(tip) AS max, min(tip) AS min FROM tips
WHERE size > 1
GROUP BY sex, day
HAVING max < 6
ORDER BY count DESC
LIMIT 5;
pdsql(query)
Explanation: Aggregate queries
End of explanation
student = pd.read_csv('data/student.txt')
student
cls = pd.read_csv('data/class.txt')
cls
major = pd.read_csv('data/major.txt')
major
student_cls = pd.read_csv('data/student_class.txt')
student_cls
Explanation: Joins
A join occurs when you combine information from two or more database tables, based on information in a column that is common among the tables. As usual, it is easier to understand the concept with examples.
End of explanation
query = 
SELECT s.first, s.last, m.name
FROM student s
INNER JOIN major m 
ON s.major_id = m.major_id;
pdsql(query)
Explanation: Matching students and majors
Inner join
End of explanation
query = 
SELECT s.first, s.last, m.name
FROM student s
LEFT OUTER JOIN major m 
ON s.major_id = m.major_id;
pdsql(query)
Explanation: Left outer join
SQL also has RIGHT OUTER JOIN and FULL OUTER JOIN but these are not currently supported by SQLite3 (the database engine used by pdsql).
End of explanation
query = 
SELECT s.first, s.last, m.name
FROM   student s 
       LEFT JOIN major m 
          ON s.major_id = m.major_id
UNION All
SELECT s.first, s.last, m.name
FROM   major m
       LEFT JOIN student s
          ON s.major_id = m.major_id
WHERE  s.major_id IS NULL;
pdsql(query)
Explanation: Emulating a full outer join with UNION ALL
Only necessary if the database does not proivde FULL OUTER JOIN
End of explanation
query = 
SELECT s.first, s.last, c.code, c.name, c.credits
FROM student s
INNER JOIN student_cls sc ON s.student_id = sc.student_id
INNER JOIN cls c ON c.class_id = sc.class_id;
pdsql(query)
Explanation: Using linker tables to match students to classes (a MANY TO MANY join)
End of explanation
query = 
SELECT s.first, s.last, c.code, c.name, c.credits
FROM student s
LEFT OUTER JOIN student_cls sc ON s.student_id = sc.student_id
LEFT OUTER JOIN cls c ON c.class_id = sc.class_id;
pdsql(query)
Explanation: Same thing but including students with no majors
End of explanation
import sqlite3
c = sqlite3.connect('data/Chinook_Sqlite.sqlite')
Explanation: Using SQLite3
SQLite3 is part of the standard library. However, the mechanics of using essentially any database in Python is similar, because of the Python DB-API.
End of explanation
list(c.execute("SELECT name FROM sqlite_master WHERE type='table';"))
Explanation: SQLite specific commands to get metadata
Unlike SQL syntax for queries, how you get metadata from a relational database is vendor-specific. You'll have to read the docs to find out what is needed for your SQL flavor.
What tables are there in the database?
End of explanation
list(c.execute("PRAGMA table_info(Album);"))
Explanation: What are the columns of the table "Album"?
End of explanation
t = ['%rock%']
list(c.execute("SELECT * FROM Album WHERE Title like ? LIMIT 5;", t))
Explanation: Standard SQL statements with parameter substitution
Note: Using Python string substitution for Python defined parameters is dangerous because of the risk of SQL injection attacks. Use parameter substitution with ? instead. 
Do this
End of explanation
t = ["'%rock%'"]
list(c.execute("SELECT * FROM Album WHERE Title like %s LIMIT 5;" % t[0]))
Explanation: Not this
End of explanation
def encode(text, offset):
    Caesar cipher of text with given offset.
    from string import ascii_lowercase, ascii_uppercase
    tbl = dict(zip(map(ord, ascii_lowercase + ascii_uppercase), 
               ascii_lowercase[offset:] + ascii_lowercase[:offset] + 
               ascii_uppercase[offset:] + ascii_uppercase[:offset]))
    return text.translate(tbl)
c.create_function("encode", 2, encode)
list(c.execute("SELECT Title, encode(Title, 3) FROM Album limit 5;"))
Explanation: User defined functions
Sometimes it is useful to have custom functions that run on the database server rather than on the client. These are called User Defined Functions or UDF. How do to do this varies with the database used, but it is fairly simple with Python and SQLite.
A standard UDF
End of explanation
class CV:
    Aggregate UDF for coefficient of varation in %.
    def __init__(self):
        self.s = []
    def step(self, value):
        self.s.append(value)
    def finalize(self):
        if len(self.s) < 2:
            return 0
        else:
            return 100.0*np.std(self.s)/np.mean(self.s)
c.create_aggregate("cv", 1, CV)
list(c.execute("PRAGMA table_info(Invoice);"))
list(c.execute("SELECT cv(Total) from Invoice limit 10;"))
c.close()
Explanation: An aggregate UDF
We can also add aggregate UDFs similar to SQL MIN, SUM, COUNT etc. Aggregate UDFs require you to write a class __init__, step and finalize methods.
End of explanation
import warnings
with warnings.catch_warnings():
    warnings.simplefilter('ignore')
    %load_ext sql
Explanation: Using SQL magic functions
We will use the ipython-sql notebook extension for convenience. This will only work in notebooks and IPython scripts with the .ipy extension.
End of explanation
%config SqlMagic
%config SqlMagic.displaylimit=10
Explanation: Configuring the SqlMagic extension
End of explanation
%sql sqlite:///data/Chinook_Sqlite.sqlite
Explanation: Connect to SQLite3 database
End of explanation
%sql SELECT * from Album LIMIT 5;
%sql SELECT * from Artist LIMIT 5;
Explanation: Other databases
See SQLAlchemy connection strings for how to connect to other databases such as Oracle, MySQL or PostgreSQL.
Line magic
End of explanation
%%sql
SELECT Artist.Name, Album.Title
FROM Album
INNER JOIN Artist on Album.ArtistId = Artist.ArtistId
ORDER BY Artist.Name ASC
LIMIT 5;
Explanation: Cell magic
End of explanation
result = %sql SELECT * from Album;
type(result)
Explanation: You can assign results of queries to Python names
End of explanation
result[2:4]
Explanation: Resutls behave like lists
End of explanation
artist_id = 10
%sql select * from Artist where ArtistId < :artist_id;
word = '%rock%'
%sql select * from Album WHERE Title LIKE :word;
Explanation: You can use Python variables in your queires.
Use :varname where you want to use a Python variable in your query.
End of explanation
df = result.DataFrame()
df.head(5)
Explanation: Convert to pandas dataframe
End of explanation
<END_TASK>