SQL AND CTEs

What is a subquery?
Is a query that is written inside another query and allows one to carry out operations that actually depend on the result of another query.
There are different types of subqueries

1. SCALAR SUBQUERY
2. MULTI-ROW SUBQUERY
3. CORRELATED AND NON-CORRELATED SUBQUERY SCALAR SUBQUERY It runs a single value that is one row, one column Its operators mostly are greater than, less than, equal to, and EXISTS Example Find students who scored higher than the class average. SELECT student name, score FROM exam results WHERE score > (SELECT AVG (score) FROM exam results); This should give you the students who scored way above the class average MULTI-ROW SUBQUERY It returns many rows, like one row with multiple columns or one column with multiple rows Example Find the names of students who are enrolled in "CRE," but the names are in the students table, and enrolment data is in the Enrolments table. SELECT student name FROM students WHERE student_id IN ( SELECT student id FROM enrolments WHERE course name = 'CRE' ); This should give the names of students who enrolled in CRE, but their names are in different rows and columns.

CORRELATED AND NON-CORRELATED SUBQUERY
Correlated subquery actually depends on the outer query, and it can never be executed on its own.
Non-correlated subquery does depend on the inner query and can be executed on its own.
Example
Find students who scored higher than the average for their specific subject.
SELECT
student_ name, subject, score
FROM exam_ results AS main
WHERE score > (
SELECT AVG (score)
FROM exam_ results AS sub
WHERE sub. subject = main. Subject
);

WHEN TO USE?

1. Comparisons -between data in the tables
2. Filtering data in a row
3. Replacing temporary tables
4. For the calculation of bigger sums

What are CTEs
Also called common table expressions
CTEs named results in SQL can be referenced in a single statement for easier simplifications of complex logics, be readable, and reusable
Types of CTEs and their uses
Non-recursive CTEs
Mostly used in SQL
Refers to a set of your script that you can refer to anytime
Uses
For simplifying results, e.g., totals, AVG
Used in listing multiple CTEs
Used in referencing, e.g., reusing codes
Recursive CTEs
Reference itself independently
Executions always continue only until you command them to stop
Uses
For organizations of data in a systematic way
Used to create lists of data or series, such as generating all the working days in a certain month
Used in finding paths in different places

Conclusion
We used CTEs mostly because it allows you to write your code once
When you define your data source at the beginning, it makes the query read like a story.

Comparisons between subqueries and CTEs
Readability- Subquery is nested inside, while CTEs are nested outside
Maintenance – Subquery is a bit challenging to maintain due to the large logic, while CTEs are easier
Reusability – Subqueries cannot be reused, while CTEs are commonly reused within the same query
Use case- Subquery is convenient for simple filtrations, while CTEs are convenient for complex filtrations.