Getting Practical with SQLite: Foreign Keys and JOIN Operations

Introduction

Mastering database relationships and complex queries is crucial for effective data management. In this tutorial, we’ll explore how to create tables with foreign key relationships in SQLite and perform JOIN operations to retrieve related data. This hands-on approach will provide you with practical skills essential for database design and querying.

If you have not started to learn SQL then my suggestion is go through below blog to understand more furthur.

Task 1: Getting Started with SQL on Windows

Getting Started with SQL on Windows: A Beginner’s Guide to Database Management

In this tutorial, we’ll dive deeper into SQLite database management by creating tables with foreign key relationships, inserting data, and performing JOIN operations. This hands-on approach will help you understand these concepts better and provide you with practical skills for database management.

Setting Up the Database

First, let’s create our database and tables:

import sqlite3
connection = sqlite3.connect('example.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS departments (
                       id INTEGER PRIMARY KEY,
                       name TEXT NOT NULL
                   );''')
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
                       id INTEGER PRIMARY KEY,
                       name TEXT NOT NULL,
                       department_id INTEGER,
                       FOREIGN KEY (department_id) REFERENCES departments(id)
                   );''')
connection.commit()
connection.close()

It will create our db file i.e example.db.

This script creates two tables: departments and employees. The employees table has a foreign key department_id that references the id column in the departments table. This establishes a relationship between the two tables.

Inserting Data

Now, let’s populate our tables with some data:

connection = sqlite3.connect('example.db')
cursor = connection.cursor()
cursor.execute("INSERT INTO departments (name) VALUES ('Human Resources');")
cursor.execute("INSERT INTO departments (name) VALUES ('Engineering');")
cursor.execute("INSERT INTO departments (name) VALUES ('Sales');")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Alice', 1);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Bob', 2);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Charlie', 2);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('David', 1);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Eva', 3);")
connection.commit()
connection.close()

This script inserts three departments and five employees into their respective tables. Notice how the department_id in the employees table corresponds to the id in the departments table.

Performing a JOIN Operation

Now that we have data in both tables, let’s write a query that retrieves data using a JOIN operation:

import sqlite3
connection = sqlite3.connect('example.db')
cursor = connection.cursor()
query = '''
SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;
'''
cursor.execute(query)
results = cursor.fetchall()
connection.close()
print(results)

#whole code 
import sqlite3


# Step 1: Create database and tables
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

cursor.execute('''CREATE TABLE IF NOT EXISTS departments (
                       id INTEGER PRIMARY KEY,
                       name TEXT NOT NULL
                   );''')

cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
                       id INTEGER PRIMARY KEY,
                       name TEXT NOT NULL,
                       department_id INTEGER,
                       FOREIGN KEY (department_id) REFERENCES departments(id)
                   );''')

# Step 2: Insert data
cursor.execute("INSERT INTO departments (name) VALUES ('Human Resources');")
cursor.execute("INSERT INTO departments (name) VALUES ('Engineering');")
cursor.execute("INSERT INTO departments (name) VALUES ('Sales');")

cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Alice', 1);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Bob', 2);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Charlie', 2);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('David', 1);")
cursor.execute("INSERT INTO employees (name, department_id) VALUES ('Eva', 3);")

connection.commit()

# Step 3: Perform a JOIN query
query = '''
SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;
'''

cursor.execute(query)
results = cursor.fetchall()

print(results)

connection.close()

This query joins the employees and departments tables based on the department_id foreign key. It retrieves the employee's name and their corresponding department name.The output of this query will be:

[('Alice', 'Human Resources'),
 ('Bob', 'Engineering'),
 ('Charlie', 'Engineering'),
 ('David', 'Human Resources'),
 ('Eva', 'Sales')]

Practical Learning Tips

1. Experiment with Different Queries: Try modifying the JOIN query to retrieve additional information or filter the results.
2. Add More Data: Insert more departments and employees to see how the relationships work with a larger dataset.
3. Explore Other JOIN Types: Research and implement LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN to understand how they differ.
4. Implement Error Handling: Add try-except blocks to handle potential SQLite errors gracefully.
5. Create a Simple CLI: Build a command-line interface that allows users to insert new employees or departments and view the joined data.

Conclusion

You’ve now learned how to create tables with foreign key relationships, insert related data, and perform JOIN operations in SQLite. These skills form the foundation of relational database management and are crucial for building robust data-driven applications.Remember, practice is key to mastering these concepts.

Try creating more complex relationships between tables, experiment with different types of JOINs, and challenge yourself to write increasingly sophisticated queries. As you become more comfortable with these operations, you’ll find yourself better equipped to handle real-world database scenarios.

So, whether you’re a tech enthusiast, a professional, or just someone who wants to learn more, I invite you to follow me on this journey. Subscribe to my blog and follow me on social media to stay in the loop and never miss a post.

Together, let’s explore the exciting world of technology and all it offers. I can’t wait to connect with you!”

Connect me on Social Media: https://linktr.ee/mdshamsfiroz

Happy coding! Happy learning!