Intermediate SQL Objectives

In this lesson, you’ll explore intermediate SQL concepts that empower you to design, query, and manage relational databases effectively. By the end, you will have the skills to build structured and efficient data relationships, perform advanced queries, and design database schemas like a pro. Think of SQL as your toolbox, and these intermediate concepts are the power tools for tackling more complex tasks.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain the role of primary and foreign keys in building relationships between data in a relational database.
• Create a one-to-many relationship between two tables using foreign key references.
• Create a many-to-many relationship between two tables using another table.
• Describe how using DELETE on one entry can necessitate deletions to cascade to other tables.
• Design a database using a database relationship diagrams design tool.
• Selectively query for multiple entries in a single table using WHERE, LIKE, and =.
• Selectively query for a single entry using LIMIT.
• Order a query using ORDER BY.
• Use the correct order of operations in a SELECT query.
• Use JOIN to connect data in more than one table.

1. Primary and Foreign Keys: The Building Blocks of Relationships

Think of a primary key as a unique identifier, like a social security number, for each row in a table. A foreign key is like a reference in one table that points to a primary key in another table. Together, they establish relationships between tables.

Example: A "users" table and an "orders" table:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
    

Here, user_id in the orders table is a foreign key referencing the users table, linking orders to their respective users.

2. One-to-Many Relationships

A one-to-many relationship is when a single record in one table is associated with multiple records in another table.

Example: A user can place many orders:

SELECT users.name, orders.product 
FROM users
JOIN orders ON users.user_id = orders.user_id;
    

3. Many-to-Many Relationships

For a many-to-many relationship, you use an intermediary table to link two tables. Imagine "students" and "courses," where students can enroll in multiple courses, and courses can have multiple students.

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
    

4. Cascading Deletions

Cascading deletions ensure referential integrity. For example, if you delete a user, all their associated orders are also deleted.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
    

5. Designing Databases with Tools

Use tools like dbdiagram.io to visually design your database schema. These tools help you map out tables and their relationships before implementation.

6. Advanced Query Techniques

Filtering with WHERE, LIKE, and =

Use these operators to filter data based on conditions.

SELECT * FROM users WHERE name LIKE 'A%'; -- Names starting with 'A'
    

Limiting Results with LIMIT

Fetch a specific number of rows:

SELECT * FROM orders LIMIT 5;
    

Ordering Results with ORDER BY

Sort data in ascending or descending order:

SELECT * FROM orders ORDER BY product ASC;
    

7. Using Joins

JOIN connects data across multiple tables. Common types include:

• INNER JOIN: Matches records in both tables.
• LEFT JOIN: Includes all records from the left table, even if there’s no match.

SELECT students.name, courses.title
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.course_id;
    

What You’ve Learned

Intermediate SQL opens the door to advanced database design and querying. You now know how to:

• Design relational databases with primary and foreign keys.
• Query data selectively and efficiently using filtering, limits, and ordering.
• Establish relationships between tables using JOIN.
• Handle cascading deletions to maintain referential integrity.

Real-World Applications

• E-commerce: Manage customer orders, products, and inventory relationships.
• Education platforms: Track student enrollments and course materials.
• Social networks: Connect users, posts, and comments.

SQL is a powerful language for interacting with databases. Practice these techniques and apply them to real-world projects to deepen your understanding!