Week 2: Mastering SQL Queries and Advanced Features in MySQL

Week 2: Advanced SQL Queries and Features (Days 8–14)

In Week 2, you will dive into more complex SQL queries, including joins, subqueries, aggregate functions, and grouping. Understanding these concepts will significantly enhance your ability to interact with and manage data in MySQL. By the end of this week, you’ll be able to write powerful SQL queries that retrieve and manipulate data efficiently.

---

Day 8: Introduction to SQL Joins

• What is a Join? Learn about SQL joins, which allow you to retrieve data from multiple tables based on a related column.
• Types of Joins:
• INNER JOIN: Returns rows when there is a match in both tables.
  sql SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table.
  sql SELECT orders.id, customers.name FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;
• RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN, but retrieves all rows from the right table.
• FULL OUTER JOIN: Retrieves all rows when there is a match in either left or right table.

---

Day 9: Using Subqueries

• What is a Subquery? A subquery is a query nested inside another query, usually in the WHERE, HAVING, or FROM clauses.
• Examples of Subqueries:
• Subquery in WHERE Clause:
  sql SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
• Subquery in FROM Clause:
  sql SELECT * FROM (SELECT name, salary FROM employees WHERE department = 'Sales') AS sales_employees;

---

Day 10: Aggregate Functions

• What are Aggregate Functions? Aggregate functions allow you to perform calculations on multiple rows of data.
• Common Aggregate Functions:
• COUNT() – Counts the number of rows.
• SUM() – Sums up the values.
• AVG() – Calculates the average value.
• MIN() – Finds the minimum value.
• MAX() – Finds the maximum value.
• Examples:

  SELECT AVG(salary) FROM employees;
  SELECT COUNT(*) FROM orders WHERE status = 'shipped';

---

Day 11: GROUP BY and HAVING Clauses

• GROUP BY: Used to group rows that have the same values in specified columns into summary rows, like finding the average salary per department.

  SELECT department, AVG(salary)
  FROM employees
  GROUP BY department;

• HAVING Clause: Works like WHERE, but it’s used for filtering groups, not individual rows.

  SELECT department, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department
  HAVING employee_count > 5;

---

Day 12: Managing Indexes

• What is an Index? An index is a performance optimization technique that helps speed up the retrieval of data from a table.
• Creating an Index: Learn how to create an index to improve query performance.

  CREATE INDEX idx_employee_name ON employees(name);

• Dropping an Index: If an index is no longer needed, it can be dropped.

  DROP INDEX idx_employee_name ON employees;

---

Day 13: Normalization and Relationships

• What is Database Normalization? Learn how to design efficient and structured databases using normalization, which eliminates redundancy and ensures data integrity.
• Types of Normal Forms: Understand the different normal forms (1NF, 2NF, 3NF) and how to apply them to your database design.
• Foreign Key Relationships: Learn how to use foreign keys to create relationships between tables.

  CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
  );

---

Day 14: Practice Complex Queries

• Complex Query Exercises: Use everything you’ve learned this week to practice writing complex queries. This can include joins, subqueries, aggregations, and using GROUP BY and HAVING.
• Example Exercise:
• Create a query that returns the total number of orders per customer, but only for customers who have placed more than 10 orders.

---

Conclusion

By the end of Week 2, you will have mastered advanced SQL techniques in MySQL, including joins, subqueries, aggregate functions, and data grouping. You will also be more comfortable working with indexes and foreign keys. These skills are essential for optimizing your queries and managing data in more complex scenarios.

What’s Next?

In Week 3, you will dive into MySQL performance optimization, including query optimization, indexing strategies, and more advanced features. Keep practicing your queries and explore real-world database scenarios!