Reading from SQL databases in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of SQL query read into pandas DataFrame

What is the output of the following Python code that reads data from a SQL database into a pandas DataFrame?

Data Analysis Python

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE sales (id INTEGER, amount INTEGER)')
cursor.executemany('INSERT INTO sales VALUES (?, ?)', [(1, 100), (2, 200), (3, 300)])
conn.commit()

query = 'SELECT id, amount FROM sales WHERE amount > 150'
df = pd.read_sql_query(query, conn)
print(df)

A

Empty DataFrame
Columns: [id, amount]
Index: []

B

   id  amount
0   2     200
1   3     300

C

   id  amount
0   1     100
1   2     200
2   3     300

D

   id  amount
0   1     100

💡 Hint

Look at the SQL WHERE clause filtering amounts greater than 150.

The SQL query selects rows where amount is greater than 150, so only rows with amounts 200 and 300 are included in the DataFrame.

❓ data_output

intermediate

Number of rows returned by SQL query

How many rows will the DataFrame contain after running this code?

Data Analysis Python

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE employees (name TEXT, age INTEGER)')
cursor.executemany('INSERT INTO employees VALUES (?, ?)', [('Alice', 30), ('Bob', 25), ('Charlie', 35)])
conn.commit()

query = 'SELECT * FROM employees WHERE age < 30'
df = pd.read_sql_query(query, conn)
print(len(df))

A 2

B 0

C 3

D 1

💡 Hint

Check which employees have age less than 30.

Only Bob has age less than 30, so the DataFrame has 1 row.

🔧 Debug

advanced

Identify the error when reading SQL data

What error will this code produce when trying to read from the SQL database?

Data Analysis Python

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE products (id INTEGER, price REAL)')
cursor.execute('INSERT INTO products VALUES (1, 9.99)')
conn.commit()

query = 'SELECT id, price FROM product'
df = pd.read_sql_query(query, conn)
print(df)

A sqlite3.OperationalError: no such table: product

B KeyError: 'product'

C TypeError: read_sql_query() missing 1 required positional argument

D

Empty DataFrame
Columns: [id, price]
Index: []

💡 Hint

Check the table name in the SQL query versus the created table.

The query references 'product' but the table created is 'products', causing a 'no such table' error.

❓ visualization

advanced

Plotting data read from SQL

Which option shows the correct matplotlib bar plot code to visualize the total sales amount per product from the DataFrame read from SQL?

Data Analysis Python

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE sales (product TEXT, amount INTEGER)')
cursor.executemany('INSERT INTO sales VALUES (?, ?)', [('A', 10), ('B', 20), ('A', 15), ('B', 25)])
conn.commit()

query = 'SELECT product, amount FROM sales'
df = pd.read_sql_query(query, conn)

A

df.groupby('product')['amount'].sum().plot(kind='bar')
plt.show()

B

df.plot.bar(x='product', y='amount')
plt.show()

C

plt.bar(df['product'], df['amount'])
plt.show()

D

df.plot(kind='line', x='product', y='amount')
plt.show()

💡 Hint

You need to sum amounts per product before plotting a bar chart.

Option A groups data by product and sums amounts, then plots a bar chart showing total sales per product.

🚀 Application

expert

Efficiently reading large SQL tables in chunks

You want to process a large SQL table in parts to avoid memory issues. Which code snippet correctly reads the table in chunks of 1000 rows using pandas?

A

df = pd.read_sql_query('SELECT * FROM big_table LIMIT 1000', conn)
process(df)

B

chunks = pd.read_sql_query('SELECT * FROM big_table', conn)
for i in range(0, len(chunks), 1000):
    process(chunks[i:i+1000])

C

for chunk in pd.read_sql_query('SELECT * FROM big_table', conn, chunksize=1000):
    process(chunk)

D

while True:
    df = pd.read_sql_query('SELECT * FROM big_table', conn, chunksize=1000)
    process(df)
    break

💡 Hint

Look for the pandas function parameter that allows chunked reading.

Option C uses the chunksize parameter to read the SQL query in parts, yielding each chunk for processing.