Aggregate Operators

Text Box: Use nested SQL to find DISTINCT records Text Box: COUNT (*) COUNT (A) SUM ( A) AVG ( A) MAX (A) MIN (A Text Box: single column

 

 

 

 

 

 

SELECT COUNT (*)
FROM Sailors S;		 SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10;
SELECT  S.sname
FROM     Sailors S
WHERE  S.rating= (SELECT  MAX(S2.rating)
                                    FROM  Sailors S2);		 SELECT COUNT ( *)
FROM (SELECT DISTINCT S.rating
              FROM Sailors S);
Find name and age of the oldest sailor(s)
SELECT S.sname, S.age
FROM Sailors S
Where S.age = (SELECT MAX(S2.age)
                           FROM    Sailors S2);

 Find the names of sailors who are older than the oldest sailor with a rating 10:

SELECT S.sname
FROM    Sailors AS S
WHERE S.age > ALL (SELECT S2.age
                                       FROM Sailors S2
                                       WHERE S2.rating =10);

SELECT S.sname
FROM    Sailors AS S
WHERE S.age > (SELECT MAX(S2.age)
                               FROM Sailors S2
                               WHERE S2.rating =10);

 

 Find the names of sailors who are older than the youngest sailor with a rating 10:

SELECT S.sname 

FROM    Sailors AS S
WHERE S.age > ANY (SELECT S2.age
                                       FROM Sailors S2
                                       WHERE S2.rating =10);

SELECT S.sname
FROM    Sailors AS S
WHERE S.age > (SELECT MIN(S2.age)
                               FROM Sailors S2
                               WHERE S2.rating =10);