"Write a query that has a subquery in its SELECT list." It then says "a subquery in a SELECT list usually makes more sense if it is correlated however subqueries don't have to be correlated if a fixed value is wanted. "
I have written my first attempt at the query that I want for this question:
Select count (*) As CountTable (
Select CompetitorNum, FamilyName, Gender, CountryCode
Where Gender = 'Male')
I have two questions:
1. Can you please explain to me what it means about subqueries being correlated?
2. The query I have written returns a table, with a count of 1 for the first half. The second half returns all the records which match the criteria. Why do I get the table of 1? How can I change this query so that it satisifies the criteria but doesn't just return a table of 1?
I have attached the database script if necessary. I am using both SQL Server and Postgres, but I'm analysing these purely from a SQL viewpoint that's why I didn't post under those forums.
Correlated means that the subquery is restricted by a value from the main query. My understanding of "subquery in the select list" would be what Oracle (at least) calls a "scalar subquery". An example (correlated, too):
, (select count(*) from emp
where emp.deptno = dept.deptno /* This is the correlation */
) as headcount