Subquery in the SELECT list

Nerddette · #1 (**permalink**) 05-20-04, 00:00

My Uni assignment asks me to:

"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
From Competitors
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.

Thanks!!

Nerddette

andrewst · #2 (**permalink**) 05-20-04, 10:50

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):

Code:

select dept.dname
,      (select count(*) from emp
        where  emp.deptno = dept.deptno /* This is the correlation */
       ) as headcount
from   dept;

r937 · #3 (**permalink**) 05-20-04, 11:43

Code:

select dept.dname
     , ( select count(*) 
           from emp
          where emp.deptno 
              = dept.deptno   /* correlated */
       ) as headcount
     , ( select count(*) 
           from emp
          where emp.deptno 
              = dept.deptno ) * 100
     / ( select count(*) 
           from emp        /* not correlated */
        ) as deptpercent 
  from dept;

Internet Services

Web Development

Digital Marketing

Consumer Tech