Subqueries in FROM and SELECT Clauses – Comprehensive Notes

Since I can't draw pictures here, I'll explain how you could use simple drawings or diagrams to understand SQL subqueries better, if you were using a tool that lets you draw:

For FROM-subqueries (Temporary Tables):

  • Flow Chart: Imagine a chart. You start with your normal tables (like 'Student' or 'HighSchool'). Draw an arrow from them into a special box labeled 'FROM subquery'. Inside this box, the subquery does its calculations (like figuring out 'scaled_gpa'). Then, draw another arrow coming out of this box. This arrow points to a 'temporary table' that's been created (let's call it 'G'). Finally, draw an arrow from this 'temporary table' into your 'main query box' (where you do WHERE, SELECT, etc.). This drawing helps you see that the subquery first makes a new, temporary list of data.

  • Tables Before and After: First, draw two simple tables: 'Students' and 'HighSchools'. Then, draw a third table. This third table would be the temporary one from the FROM subquery. It would have columns like 'studentid', 'gpa', 'highschoolid', and a new column called 'scaledgpa'. By seeing these tables side-by-side, you can understand how the subquery added that new, calculated column.

For SELECT-subqueries (One Value Per Row):

  • Row-by-Row Picture: Imagine your 'College' table, one row at a time. For each row (for example, the 'MIT' row), draw an arrow from its 'college_id' into a 'SELECT subquery box'. Inside this box, show that it's looking at the 'Apply' and 'Student' tables and finding the biggest GPA (MAX(s.GPA)) but only for MIT. Then, draw a single arrow coming out of this box with just one number (like 3.9). This number goes into the 'HighestApplicantGPA' space for that 'MIT' row in your final result table. This drawing clearly shows that the subquery takes info from one row, calculates one answer, and puts it back for that same row.

  • Adding a Column Idea: Draw the 'College' table with just 'name' and 'state' columns. Then, draw an empty new column next to it, named 'HighestApplicantGPA'. For each row in the 'College' table, show how the SELECT subquery fills only one single number into one

Since I can't draw pictures here, I'll explain how you could use simple drawings or diagrams to understand SQL subqueries better, if you were using a tool that lets you draw:

For FROM-subqueries (Temporary Tables):

  • Flow Chart: Imagine a chart. You start with your normal tables (like 'Student' or 'HighSchool'). Draw an arrow from them into a special box labeled 'FROM subquery'. Inside this box, the subquery does its calculations (like figuring out 'scaled_gpa'). Then, draw another arrow coming out of this box. This arrow points to a 'temporary table' that's been created (let's call it 'G'). Finally, draw an arrow from this 'temporary table' into your 'main query box' (where you do WHERE, SELECT, etc.). This drawing helps you see that the subquery first makes a new, temporary list of data.

  • Tables Before and After: First, draw two simple tables: 'Students' and 'HighSchools'. Then, draw a third table. This third table would be the temporary one from the FROM subquery. It would have columns like 'studentid', 'gpa', 'highschoolid', and a new column called 'scaledgpa'. By seeing these tables side-by-side, you can understand how the subquery added that new, calculated column.

For SELECT-subqueries (One Value Per Row):

  • Row-by-Row Picture: Imagine your 'College' table, one row at a time. For each row (for example, the 'MIT' row), draw an arrow from its 'college_id' into a 'SELECT subquery box'. Inside this box, show that it's looking at the 'Apply' and 'Student' tables and finding the biggest GPA (MAX(s.GPA)) but only for MIT. Then, draw a single arrow coming out of this box with just one number (like 3.9). This number goes into the 'HighestApplicantGPA' space for that 'MIT' row in your final result table. This drawing clearly shows that the subquery takes info from one row, calculates one answer, and puts it back for that same row.

  • Adding a Column Idea: Draw the 'College' table with just 'name' and 'state' columns. Then, draw an empty new column next to it, named 'HighestApplicantGPA'. For each row in the 'College' table, show how the SELECT subquery fills only one single number into one