{ "cells": [ { "cell_type": "markdown", "id": "8af4b1b3", "metadata": {}, "source": [ "# Introduction to SQL with SQLite\n", "\n", "In this tutorial, we will learn the basics of SQL (Structured Query Language) using **SQLite**. \n", "SQLite is a lightweight, file-based database system that integrates easily with Python.\n", "\n", "We will:\n", "1. Create a simple relational table.\n", "2. Insert data.\n", "3. Write queries to explore the data.\n", "4. Practice with exercises.\n" ] }, { "cell_type": "code", "execution_count": 8, "id": "b02f6c47", "metadata": {}, "outputs": [], "source": [ "# Step 0: Setup\n", "import sqlite3\n", "import pandas as pd\n", "\n", "# Create an in-memory SQLite database\n", "conn = sqlite3.connect(\":memory:\")\n", "\n", "# Create a cursor object\n", "cur = conn.cursor()" ] }, { "cell_type": "markdown", "id": "deed311f", "metadata": {}, "source": [ "## Step 1: Create a Table\n", "\n", "Let's create a `Students` table with the following columns:\n", "- `roll_no` (Primary Key)\n", "- `name`\n", "- `age`\n", "- `grade`" ] }, { "cell_type": "code", "execution_count": 9, "id": "7a26c28c", "metadata": {}, "outputs": [], "source": [ "cur.execute(\"\"\"\n", "CREATE TABLE Students (\n", " roll_no INTEGER PRIMARY KEY,\n", " name TEXT,\n", " age INTEGER,\n", " grade TEXT\n", ")\n", "\"\"\")\n", "conn.commit()" ] }, { "cell_type": "markdown", "id": "084238da", "metadata": {}, "source": [ "## Step 2: Insert Data\n", "\n", "We will now add some rows into the `Students` table." ] }, { "cell_type": "code", "execution_count": 10, "id": "00e7ea42", "metadata": {}, "outputs": [], "source": [ "students = [\n", " (1, \"Alice\", 20, \"A\"),\n", " (2, \"Bob\", 21, \"B\"),\n", " (3, \"Charlie\", 19, \"A\"),\n", " (4, \"Diana\", 22, \"C\"),\n", " (5, \"Evan\", 20, \"B\"),\n", "]\n", "\n", "cur.executemany(\"INSERT INTO Students VALUES (?, ?, ?, ?)\", students)\n", "conn.commit()" ] }, { "cell_type": "markdown", "id": "fda10281", "metadata": {}, "source": [ "## Step 3: Query the Data\n", "\n", "Let's start exploring the table." ] }, { "cell_type": "code", "execution_count": 11, "id": "e5d58f5d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
roll_nonameagegrade
01Alice20A
12Bob21B
23Charlie19A
34Diana22C
45Evan20B
\n", "
" ], "text/plain": [ " roll_no name age grade\n", "0 1 Alice 20 A\n", "1 2 Bob 21 B\n", "2 3 Charlie 19 A\n", "3 4 Diana 22 C\n", "4 5 Evan 20 B" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Select all rows\n", "df = pd.read_sql_query(\"SELECT * FROM Students\", conn)\n", "df" ] }, { "cell_type": "markdown", "id": "67f10bc0", "metadata": {}, "source": [ "## Step 4: Exercises\n", "\n", "👉 Write SQL queries for the following tasks:\n", "\n", "1. Show the names of all students who got grade **'A'**.\n", "2. Find the average age of students.\n", "3. List all students ordered by age (youngest first).\n", "4. Count how many students got each grade.\n", "5. Find the names of students older than 20.\n", "\n", "Replace the SQL query inside the quotes and re-run the cell or create a cell for each query." ] }, { "cell_type": "code", "execution_count": 12, "id": "f3a4fd2e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
0Alice
1Charlie
\n", "
" ], "text/plain": [ " name\n", "0 Alice\n", "1 Charlie" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Example: Exercise 1\n", "df = pd.read_sql_query(\"SELECT name FROM Students WHERE grade = 'A'\", conn)\n", "df" ] }, { "cell_type": "markdown", "id": "52fd7287", "metadata": {}, "source": [ "## Step 5: Bonus Challenge\n", "\n", "Suppose we add another table: \n", "- `Courses(course_id, course_name)` \n", "- `Enrollments(roll_no, course_id)` \n", "\n", "Write SQL queries to show **which student is enrolled in which course**. \n", "This will require using **JOIN** between tables.\n", "\n", "(*Try creating the tables and data yourself!*)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.6" } }, "nbformat": 4, "nbformat_minor": 5 }