Advanced Queries

1What Are Advanced Queries?✨

Advanced queries in SQL allow you to perform complex operations on your data. They help you extract valuable insights and manipulate data in powerful ways!

2Why Learn Advanced Queries?🎯

• ✓ Combine data from multiple tables
• ✓ Filter and sort data efficiently
• ✓ Use subqueries for enhanced functionality
• ✓ Perform aggregations and calculations

3Joining Tables💡

Joining tables lets you combine rows from two or more tables based on a related column. Here’s an example using INNER JOIN:

4SELECT students.name, courses.course_name🚀

FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

This query shows student names along with the courses they are enrolled in.

5Using Subqueries🔍

Subqueries are queries nested inside another query. They can help you filter results based on other data. Here’s an example:

6SELECT name⭐

FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 3);

This retrieves names of students enrolled in the course with ID 3.

7Aggregating Data🧩

Aggregating functions help summarize data. You can use functions like COUNT, SUM, AVG, etc. Here’s an example that counts the number of students in each course:

8SELECT course_id, COUNT(student_id) AS total_students🎈

• FROM enrollments
• GROUP BY course_id;

This gives you the total number of students per course.

9Filtering with HAVING✨

The HAVING clause is used to filter records after aggregation. It’s great for refining your grouped results. For example:

10SELECT course_id, COUNT(student_id) AS total_students🎯

• FROM enrollments
• GROUP BY course_id
• HAVING total_students > 5;

This query finds courses with more than 5 students enrolled.