SQL : Advanced SQL Queries

Objectives:

• Explore advanced SQL queries including subqueries, joins with conditions, and common table expressions (CTEs)
• Learn about SQL functions and their applications
• Practice writing complex queries to retrieve and manipulate data

1. Subqueries

Definition:

• A subquery is a query nested inside another query and is used to return data that will be used in the main query.

Syntax Overview:

• Using subqueries in SELECT, FROM, WHERE, and other clauses.

  SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS subquery_result
  FROM table1;

Example:

SELECT StudentID, FirstName, LastName
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 1);

Exercise:

• Write SQL queries using subqueries to:

1. List students enrolled in ‘Math’ course.
2. Find courses with more than 3 enrollments.

2. Joins with Conditions

Advanced Joins:

• Using more complex conditions in JOIN clauses for specific data retrieval.

  SELECT column1, column2, ...
  FROM table1
  INNER JOIN table2 ON table1.column = table2.column AND condition;

Example:

SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID AND Enrollments.CourseID = 1
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Exercise:

• Write SQL queries using joins with conditions to:

1. Retrieve students enrolled in specific courses with conditions.
2. Join multiple tables using specific criteria.

3. Common Table Expressions (CTEs)

Definition:

• CTEs provide a way to write more readable and manageable complex queries.

Syntax Overview:

• Define CTEs using WITH clause before the main query.

  WITH cte_name AS (
      SELECT column1, column2, ...
      FROM table_name
      WHERE condition
  )
  SELECT column1, column2, ...
  FROM cte_name;

Example:

WITH EnrolledStudents AS (
    SELECT StudentID, FirstName, LastName
    FROM Students
    WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 1)
)
SELECT *
FROM EnrolledStudents;

Exercise:

• Write SQL queries using CTEs to:

1. Create a temporary table of enrolled students in a specific course.
2. Use CTEs to simplify complex queries involving multiple tables.

4. SQL Functions

Overview:

• Explore SQL functions for data manipulation and retrieval.

Common Functions:

• Aggregate functions (SUM, AVG, COUNT, MIN, MAX)
• String functions (CONCAT, SUBSTRING, UPPER, LOWER)
• Date functions (DATE_FORMAT, DATE_ADD, DATE_DIFF)
• Conditional functions (CASE, IF)

Examples:

SELECT COUNT(*) AS TotalStudents
FROM Students;

SELECT UPPER(FirstName) AS UppercaseName
FROM Students
WHERE StudentID = 1;

SELECT DATE_FORMAT(EnrollmentDate, '%Y-%m-%d') AS FormattedDate
FROM Enrollments
WHERE StudentID = 1;

Exercise:

• Write SQL queries using various functions to:

1. Count total students.
2. Format dates and manipulate strings.
3. Apply conditional logic using CASE statements.

5. Practical Exercise: Advanced SQL Queries

• Task:
• Use the Students, Courses, and Enrollments tables.
• Exercises:

1. Write a query to find courses with the highest number of enrollments.
2. Use a subquery to find students who enrolled in more than one course.
3. Create a CTE to list students and their average grades.

Exercises

1. Using Subqueries:

• Exercise: Write SQL queries to list students enrolled in multiple courses.

1. Joins with Conditions:

• Exercise: Write SQL queries to join tables with specific conditions for data retrieval.

1. Common Table Expressions (CTEs):

• Exercise: Write SQL queries to create CTEs and use them in main queries.

1. SQL Functions:

• Exercise: Write SQL queries using various functions (aggregate, string, date, conditional).

Homework:

• Practice Questions:
• Write SQL queries to:
  • Find courses with the highest number of enrollments using subqueries.
  • Use joins with conditions to retrieve specific data sets.
  • Experiment with CTEs and functions to solve complex data problems.