SQL - (Intermediate Level)

If you are looking for beginner level or more advanced SQL, → SQL (Beginner Level)     SQL (Advanced Level)
Intermediate Level SQL skills bridge the gap between basic data retrieval and complex data manipulation and performance. This level is characterized by the ability to handle more complex business logic, structure data for easier analysis, and understand the basics of query optimization.

Key Intermediate Concepts include:
  1. Common Table Expressions (CTEs) and Subqueries
  2. GROUP BY and HAVING Clause
  3. CASE Statements and Conditional Logic
  4. Views and Stored Procedures (Basics)
  5. Joins and Relationships
  6. Indexing and Query Performance Basics

1. Common Table Expressions (CTEs) and Subqueries

CTEs (using WITH) and subqueries allow you to break down complex problems into manageable, readable steps. They are essential for creating temporary result sets that you can reference within a larger query.

Example (CTE): Calculating average order value by customer. Instead of using a complex subquery in the FROM clause, a CTE improves readability:
SELECT 
WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSpent,
        COUNT(OrderID) AS OrderCount
    FROM
        Orders
    GROUP BY
        CustomerID
 )
 -- Now use the CTE in the main query
 SELECT
    CustomerID,
    TotalSpent,
    OrderCount,
    TotalSpent / OrderCount AS AvgOrderValue
 FROM
    CustomerOrderTotals
 WHERE
    TotalSpent > 1000;

2. The (GROUP BY and HAVING Clause)

While WHERE filters individual rows before aggregation, HAVING filters groups GROUP BY after aggregation. This is crucial for analyzing summary data.

Example (Group By & Having): Finding product categories with more than 100 products: 
 SELECT
    Category,
    COUNT(ProductID) AS ProductCount
 FROM
    Products
 GROUP BY
    Category
 HAVING
    COUNT(ProductID) > 100; -- Filters only the groups where the count is over 100

3. CASE Statements and Conditional Logic

CASE statements are SQL's way of implementing if-else logic, allowing you to categorize data or display different values based on certain conditions.

Example (Case): Labeling product prices as 'High', 'Medium', or 'Low': 
 SELECT
    ProductName,
    Price,
    CASE
        WHEN Price > 500 THEN 'High'
        WHEN Price > 100 THEN 'Medium'
        ELSE 'Low'
    END AS PriceRange
 FROM
    Products;

4. Views and Stored Procedures (Basics)

Views are virtual tables based on the result-set of a query. They simplify complex queries for end-users and enhance security.
Stored procedures are precompiled SQL code stored in the database. They can be called by name, making them reusable and efficient.

Example (View): Creating a simple view for recent sales: 
 CREATE VIEW RecentSales AS
  SELECT
    O.OrderID,
    C.CustomerName,
    O.OrderDate,
    O.TotalAmount
  FROM
    Orders O
  JOIN
    Customers C ON O.CustomerID = C.CustomerID
  WHERE
    O.OrderDate >= DATEADD(day, -30, GETDATE());

5. Advanced Joins and Understanding Database Relationships

A beginner knows INNER JOIN. An intermediate user understands how different join types relate to database relationship types (one-to-many, many-to-many) and can choose the correct join to ensure data integrity and completeness.

  1. LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If no match is found, result is NULL on the right side.
  2. RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
  3. FULL OUTER JOIN: Returns all records when there is a match in either the left or the right table.
  4. CROSS JOIN: Returns the Cartesian product of rows from both tables (every row from the left combined with every row from the right).
Example (Left Join): Listing all customers, including those who have not placed an order. Similarly (Right Join) - will have all orders including those with missing customer_id. 
 SELECT
  C.CustomerName,
  O.OrderID,
  O.OrderDate
 FROM
  Customers C
 LEFT JOIN
  Orders O ON C.CustomerID = O.CustomerID
 WHERE
  O.OrderID IS NULL; -- NULL Filters specifically for customers who have NO orders
Example (Cross Join): Every row from left table combined with every row from right table (Cartesian Product of the two tables), which means all possible pairs of rows from the two tables: Consider
Customer table
customer_id customer_name
101 Alice
102 Bob
103 Charlie

Product table
product_id product_name
2001 Mobile
2002 TV
2003 Laptop 
 SELECT
  C.CustomerName,
  P.ProductName
 FROM
  Customers C
 CROSS JOIN
  Products P; --- The result will be 3 x 3 = (9) records of all possible pairs of customer and products

6. Understanding Indexing and Query Performance Basics

An intermediate user understands that how data is stored, matters. Indexes are like book indexes—they help the database engine find rows quickly without reading the entire table (a slow Table Scan). The ability to view a query's execution plan is key to identifying performance bottlenecks.

Example: Analyzing a slow query
Imagine this query on a large Orders table is slow: 
 SELECT 
  * 
 FROM 
  Orders 
 WHERE 
  OrderDate BETWEEN '2025-01-01' AND '2025-01-31';
An intermediate user would:
  1. View the Execution Plan: They would enable the graphical execution plan in their SQL client.
  2. Identify the Bottleneck: They might see an operation labeled "Table Scan" (reading every row) which has a high cost (e.g., 90% of the total query cost).
  3. Propose a Solution: They suggest adding an index to the column used in the WHERE clause to speed up the lookup:
 CREATE INDEX
  idx_order_date 
 ON Orders (OrderDate);
Re-evaluate: Rerunning the query now shows an "Index Seek" in the execution plan, and the total query cost drops significantly.
If you are looking for more advanced SQL, → SQL (Beginner Level)     SQL (Advanced Level)