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.

Practice

1. What is the main purpose of using pandas.read_sql_query() in Python when working with SQL databases?

easy

A. To close the database connection

B. To create a new SQL database

C. To write data from a DataFrame back to the database

D. To run an SQL query and get the result as a DataFrame

Solution

1. Step 1: Understand the function purpose

   pandas.read_sql_query() runs an SQL query on a database connection and returns the result as a pandas DataFrame.

2. Step 2: Compare options with function use

   Creating databases, closing connections, or writing data are not done by this function.

3. Final Answer:

   To run an SQL query and get the result as a DataFrame -> Option D

4. Quick Check:

   read_sql_query() = runs query and returns DataFrame [OK]

Hint: Remember: read_sql_query fetches data as DataFrame [OK]

Common Mistakes:

• Thinking it creates or modifies the database
• Confusing it with connection closing functions
• Assuming it writes data back to the database

2. Which of the following is the correct way to open a connection to a SQLite database file named data.db using Python's sqlite3 module?

easy

A. conn = sqlite3.open('data.db')

B. conn = sqlite3.connect('data.db')

C. conn = sqlite3.start('data.db')

D. conn = sqlite3.create('data.db')

Solution

1. Step 1: Recall sqlite3 connection syntax

   The correct method to open a connection is sqlite3.connect() with the database filename as argument.

2. Step 2: Check other options

   Methods like open, start, or create do not exist in sqlite3 for opening connections.

3. Final Answer:

   conn = sqlite3.connect('data.db') -> Option B

4. Quick Check:

   sqlite3.connect() opens DB connection [OK]

Hint: Use connect() to open SQLite DB connections [OK]

Common Mistakes:

• Using non-existent methods like open() or start()
• Confusing connect() with create()
• Forgetting to pass the database filename as a string

3. Given the following code, what will be the output?

import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER, name TEXT)')
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
cursor.execute("INSERT INTO users VALUES (2, 'Bob')")
df = pd.read_sql_query('SELECT * FROM users WHERE id = 2', conn)
print(df)

medium

A. id name
0 2 Bob

B. id name
0 1 Alice

C. Empty DataFrame with columns [id, name]

D. SyntaxError due to SQL query

Solution

1. Step 1: Understand the SQL query

   The query selects rows from 'users' where id equals 2, so only Bob's row matches.

2. Step 2: Check DataFrame output

   The DataFrame will have one row with id=2 and name='Bob'.

3. Final Answer:

   id name 0 2 Bob -> Option A

4. Quick Check:

   SELECT * WHERE id=2 returns Bob [OK]

Hint: Filter by id=2 returns Bob's row only [OK]

Common Mistakes:

• Confusing id=1 with id=2
• Expecting all rows instead of filtered
• Thinking the query has syntax errors

4. What is wrong with this code snippet for reading data from a SQLite database?

import sqlite3
import pandas as pd
conn = sqlite3.connect('mydb.db')
df = pd.read_sql_query('SELECT * FROM sales', conn)
conn.close()
print(df.head())
print(conn.execute('SELECT * FROM sales'))

medium

A. Trying to execute a query after closing the connection causes an error

B. The SQL query syntax is incorrect

C. pandas.read_sql_query() cannot read from SQLite databases

D. The connection is never closed

Solution

1. Step 1: Identify connection usage

   The connection is closed with conn.close() before the last execute call.

2. Step 2: Understand error cause

   After closing, trying to run conn.execute() causes a runtime error because the connection is no longer open.

3. Final Answer:

   Trying to execute a query after closing the connection causes an error -> Option A

4. Quick Check:

   Closed connection cannot execute queries [OK]

Hint: Don't use connection after closing it [OK]

Common Mistakes:

• Assuming queries work after closing connection
• Thinking SQL syntax is wrong here
• Believing pandas can't read SQLite data

5. You want to read data from a SQL database and filter rows where the column age is greater than 30. Which of the following code snippets correctly does this and returns a pandas DataFrame?

hard

A. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', 'conn')

B. df = pd.read_sql_query('SELECT * FROM users WHERE age => 30', conn)

C. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', conn)

D. df = pd.read_sql_query('SELECT * FROM users WHERE age > "30"', conn)

Solution

1. Step 1: Check SQL syntax for filtering

   The correct SQL syntax for greater than is age > 30. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', conn) uses this correctly.

2. Step 2: Identify errors in other options

   df = pd.read_sql_query('SELECT * FROM users WHERE age => 30', conn) uses invalid operator =>. df = pd.read_sql_query('SELECT * FROM users WHERE age > "30"', conn) compares age to a string which may cause type issues. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', 'conn') passes connection as a string, which is incorrect.

3. Final Answer:

   df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', conn) -> Option C

4. Quick Check:

   Correct SQL and connection object needed [OK]

Hint: Use > operator and pass connection object, not string [OK]

Common Mistakes:

• Using => instead of >
• Comparing numeric column to string
• Passing connection as string instead of object