Introduction to SQL Queries: A Comprehensive Guide

# Install libraries
!pip install pymysql
!pip install sqlalchemy

Requirement already satisfied: pymysql in /home/kevin/anaconda3/lib/python3.11/site-packages (1.1.1)
Requirement already satisfied: sqlalchemy in /home/kevin/anaconda3/lib/python3.11/site-packages (2.0.30)
Requirement already satisfied: typing-extensions>=4.6.0 in /home/kevin/anaconda3/lib/python3.11/site-packages (from sqlalchemy) (4.11.0)
Requirement already satisfied: greenlet!=0.4.17 in /home/kevin/anaconda3/lib/python3.11/site-packages (from sqlalchemy) (3.0.1)

# Import libraries
import pymysql # Connecting to Mariadb using mysql client
import pandas as pd
from sqlalchemy import create_engine, text # Will be used to create sql engine. We also use text to specify SQL string
# Set the maximum width for columns to 200 characters
pd.set_option('display.max_colwidth', 200)

# The purpose of this function is to hide the index when displaying dataframes for SQL results.
def hide_index(df):
    empty_index=[''] * len(df)
    df.index=empty_index
    return df

-- Table Users => Stores information about the users
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table Quizzes => Stores information about quizzes created by users
CREATE TABLE Quizzes (
    quiz_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

-- Table Questions => Stores questions that are associated with a quiz
CREATE TABLE Questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    quiz_id INT,
    question_text TEXT NOT NULL,
    correct_answer TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quiz_id) REFERENCES Quizzes(quiz_id) ON DELETE CASCADE
);

-- Table UserQuizQuestions => Tracks each user's answers and score for the questions in a quiz
CREATE TABLE UserQuizQuestions (
    user_quiz_question_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    quiz_id INT,
    question_id INT,
    user_answer TEXT NOT NULL,
    score INT DEFAULT 0,
    answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (quiz_id) REFERENCES Quizzes(quiz_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES Questions(question_id) ON DELETE CASCADE,
    UNIQUE (user_id, quiz_id, question_id) -- Ensures a user can only answer each question once in a quiz.
                                           -- We could have also used a composite primary key for this. 
);

-- idx_user_quiz this will speed up queries that need to find all answers a specific user gave for a quiz
CREATE INDEX idx_user_quiz ON UserQuizQuestions(user_id, quiz_id);
CREATE INDEX idx_quiz_question ON UserQuizQuestions(quiz_id, question_id);

# Create the SQLAlchemy engine. 
# NB: REPLACE VARIABLES BELOW
host='localhost'
user = 'root'
passwd = "pp08763547*T"
db = 'my_quiz_db'

engine = create_engine(f"mysql+pymysql://{user}:{passwd}@{host}/{db}")

df = pd.read_sql_query("SELECT * FROM Users", engine)
hide_index(df) # Call hide_index to hide the index from the result

df = pd.read_sql_query("SELECT * FROM Users", engine)
hide_index(df)

df = pd.read_sql_query("SELECT * FROM Questions", engine)
hide_index(df)

df = pd.read_sql_query("SELECT * FROM Questions", engine)
hide_index(df)

# I did not want to continue writing `pd.read_sql_query` every time, so I created a simple function to run and return results.
def runSQL(sql_string, engine):
    df = pd.read_sql_query(sql_string, engine)
    return df

df = pd.read_sql_query("SELECT username, email FROM Users", engine)
hide_index(df)

df = runSQL("SELECT title, description FROM Quizzes WHERE user_id=1",engine)
hide_index(df)

query="""
SELECT quiz_id, COUNT(*) AS question_count
FROM Questions
GROUP BY quiz_id;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
SELECT u.username, q.title
FROM Users u
JOIN Quizzes q ON u.user_id = q.user_id;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
SELECT u.username, q.question_text, uq.user_answer, uq.score
FROM UserQuizQuestions uq
JOIN Users u ON uq.user_id = u.user_id
JOIN Questions q ON uq.question_id = q.question_id
WHERE uq.quiz_id = 2;
"""
df = runSQL(query, engine)
hide_index(df)
# This will result in an empty set as shown below

query="""
SELECT username
FROM Users
WHERE user_id IN (SELECT user_id FROM UserQuizQuestions WHERE quiz_id = 1);
"""
df = runSQL(query, engine)
hide_index(df)

query="""
WITH UserScores AS (
  SELECT user_id, SUM(score) AS total_score
  FROM UserQuizQuestions
  WHERE quiz_id = 3
  GROUP BY user_id
)
SELECT u.username, us.total_score
FROM UserScores us
JOIN Users u ON us.user_id = u.user_id
ORDER BY us.total_score DESC
LIMIT 1;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
WITH UserScores AS (
  SELECT user_id, SUM(score) AS total_score
  FROM UserQuizQuestions
  WHERE quiz_id = 3
  GROUP BY user_id
)
SELECT u.username, us.total_score,
       RANK() OVER (ORDER BY us.total_score DESC) AS rank
FROM UserScores us
JOIN Users u ON us.user_id = u.user_id;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
SELECT quiz_id, GROUP_CONCAT(question_text SEPARATOR '| ') AS questions
FROM Questions
WHERE quiz_id = 2
GROUP BY quiz_id;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
SELECT u1.username AS user_1, u2.username AS user_2, uq.quiz_id
FROM UserQuizQuestions uq
JOIN Users u1 ON uq.user_id = u1.user_id
JOIN UserQuizQuestions uq2 ON uq.quiz_id = uq2.quiz_id
JOIN Users u2 ON uq2.user_id = u2.user_id
WHERE u1.user_id <> u2.user_id;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
WITH QuizScores AS (
  SELECT quiz_id, user_id, SUM(score) AS total_score
  FROM UserQuizQuestions
  GROUP BY quiz_id, user_id
),
AverageScores AS (
  SELECT quiz_id, AVG(total_score) AS avg_score
  FROM QuizScores
  GROUP BY quiz_id
)
SELECT q.quiz_id, avg_score
FROM AverageScores q;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
  SELECT quiz_id, user_id, SUM(score) AS total_score
  FROM UserQuizQuestions
  GROUP BY quiz_id, user_id;
"""
df = runSQL(query, engine)
hide_index(df)

query="""
 SELECT quiz_id, AVG(score) AS total_score
  FROM UserQuizQuestions
  GROUP BY quiz_id
"""
df = runSQL(query, engine)
hide_index(df)

query="UPDATE Users SET email = 'my_new_email@example.com' WHERE user_id = 5;"
# Execute the update query
with engine.connect() as connection:
    connection.execute(text(query))
    connection.commit()  # Explicit commit to persist changes

query="DELETE from Users WHERE user_id = 10;"
# Execute the update query
with engine.connect() as connection:
    connection.execute(text(query))
    connection.commit()  # Again we explicitly commit to persist changes

query="""
 SELECT q.quiz_id, q.title
 FROM Quizzes q
 WHERE NOT EXISTS (
 SELECT 1 FROM Questions WHERE quiz_id = q.quiz_id
);
"""
df = runSQL(query, engine)
hide_index(df)
# The set below will be empty

query="""
 SELECT u.username, q.title
 FROM Users u
 CROSS JOIN Quizzes q;
"""
df = runSQL(query, engine)
hide_index(df)