Subqueries

Problem setup

Search for candidates, whose grade in mathematics is over average.

Solution: Subquery 1. Find the average 2. Find the candidates, who has grade over the average.

Main query

Step-by-step Solution

1. Find the average.

SELECT AVG(mathematics) FROM candidates; AVG(MATEMAATIKA) --------------- 61,4344828 2. Search for candidates having grade over the average.

SELECT firstname, lastname, mathematics FROM candidates WHERE mathematics> 61,43; FIRSTNAME LASTNAME MATHEMATICS --------------- --------------- ------------- Sulev Kask 95 Ragnar Alo Lepp Saar 100 94

Using a Subquery

SELECT firstname, lastname, mathematics FROM candidates WHERE mathematics > 61,43 (SELECT AVG mathematics) FROM candidates); FIRSTNAME LASTNAME MATHEMATICS ---------------------------------- -------------------------- Sulev Kask 95 Ragnar Alo ................ Lepp Saar 100 94

Types of Subqueries

• Single-row subquery Main query returns Subquery Aaviksaar • Multiple-row subquery Main query Subquery returns Aaviksaar Kivikas • Multiple-column subqueries Main query Subquery returns Aaviksaar 1,86 Kivikas 1,73

Guidelines for using subqueries

• • • • Enclose subqueries in parentheses Place subqueries on the right side of the comparison operator (>, <=, IN,...) .

The ORDER-BY clause in the subquery is not needed unless you are performing the top-N analysis.

Use single-row operators with single-row subqueries and use multiple-row operators (IN, ANY, ALL) with multiple row subqueries

Executing single-row subqueries

Search for candidates having result in mathematics greater than Link’s one, but result on mother language is less than Klade has.

SELECT lastname, mathematics, mother_l FROM candidates WHERE mathematics > (SELECT mathematics FROM candidates WHERE lastname= ' Link') AND mother_l < (SELECT mother_l FROM candidates WHERE lastname = ' Klade ' ); 64 45 LASTNAME MATHEMATICS MOTHER_L --------------- ----------- -------------------------------- Pappel Saar 82 75 30 40

Group Functions in Subqueries

Search for worst result in math: SQL> SELECT lastname, mathematics FROM candidates WHERE mathematics = (SELECT MIN (mathematics) FROM candidates); LASTNAME MATHEMATICS --------------- -------------------------- Remmelgas 12

HAVING Clause in subqueries

Find the year of birth of candidates, the average math of whose exceeds the results of other years SELECT SUBSTR(id, 2,2) AS „Year of Birth“, AVG(mathematics ) AS „Avg. math.“ FROM candidates GROUP BY SUBSTR(id,2,2) HAVING AVG(mathematics) > (SELECT AVG(mathematics) FROM candidates WHERE SUBSTR(id, 2,2) =‘83‘); 58,48...

Year of Birth Avg. math.

-- ---------------------- 79 67,75 81 84 59,8571429 62,8639053

Multiple-row subqueries

• • Result has more than one row You can use multiple-row operators

Operator Meaning

a

IN

{

b

1

,b

2

,b

3

,...} (

a = b

1

) OR

(

a = b

2

) OR

(

a = b

3

) ...

a

<

ANY

{

b

1

,b

2

,b

3

,...} (a <

b

1

) OR

(

a < b

2

) OR

(

a < b

3

) ...

a

<

ALL

{

b

1

,b

2

,b

3

,...} (a <

b

1

) AND

(

a < b

2

) AND

(

a < b

3

) ...

Multiple-row subqueries

Search for women, whose result in math is less than result of any male.

SELECT firstname, lastname, mathematics FROM candidates WHERE id LIKE '4%' AND mathematics (SELECT mathematics FROM candidates < ALL WHERE id LIKE '3%') LASTNAME FIRSTNAME MATHEMATICS --------------- --------------- ---------------------------- Remmelgas Kairi 12

Multiple column subqueries

Find the best mathematicians from males/females SELECT lastname, firstname, mathematics FROM candidates WHERE (SUBSTR(id,1,1) , mathematics) IN (SELECT SUBSTR(id,1,1) , MAX(mathematics) FROM candidates GROUP BY SUBSTR(id,1,1)); LASTNAME FIRSTNAME MATHEMATICS --------------- --------------- ----------------------------- Kuusk Remmelgas Rain Kati 100 96

Subquery in FROM clause

Search for candidates, whose result in math exceeds the result of other candidates, having the same gender SELECT a.lastnamei, a.firstname,a.mathematics, substr(a.id,1,1) AS gender, b.avg_math

FROM candidates a, (SELECT SUBSTR(id,1,1) as gender, AVG(mathematics) AS avg_math FROM candidates GROUP BY SUBSTR(id,1,1)) b WHERE SUBSTR(a.id,1,1) = b.gender

AND a.mathematics> b.avg_math; LASTNAME FIRSTNAME MATHEMATICS G MATH AVG_MATH --------------- --------------- ----------- - --------------------------------------- Sarapuu Tõnu 95 3 63 60,9256198 Kuusik .......

Ragnar 100 3 72 60,9256198

Operator "exists"

Who has sent at least one homework in english: SELECT firstname, lastname FROM lepikult.students s WHERE EXISTS ( SELECT * FROM lepikult. homeworks h, lepikult. subjects o WHERE h. id=s.id

and h.course_code = o.code

and instr(upper(o.name),'ENGLISH')>0); FIRSTNAME LASTNAME --------------- -------------- Anna Laasik Tiina Kuusik

Operator "not exists"

Find the students, who has not sent any homework: SQL> SELECT firstname, lastname from students s 2 WHERE NOT EXISTS 3 ( SELECT * FROM homeworks h 4* WHERE h.id=s.id) FIRSTNAME LASTNAME --------------- -------------- Riina Kaasik Peeter Tuulik Siiri Karu