Subqueries Problem setup Search for candidates whose grade

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 Lepp 100 Alo Saar 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 Lepp 100 Alo Saar 94. . . .

Types of Subqueries • Single-row subquery Main query returns Subquery • Aaviksaar Multiple-row subquery Main query returns Subquery • Aaviksaar Kivikas Multiple-column subqueries Main query returns Subquery 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 multiplerow 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'); LASTNAME MATHEMATICS MOTHER_L ---------------------Pappel 82 40 Saar 75 30 64 45

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

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‘); Year of Birth Avg. math. -- -----------79 67, 75 81 59, 8571429 84 62, 8639053 58, 48. . .

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< (a < b 1 ) OR (a < b 2) OR (a < b 3). . . ANY {b 1, b 2, b 3, . . . } a< (a < b 1 ) AND (a < b 2) AND (a < b 3) ALL {b 1, b 2, 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 < ALL (SELECT mathematics FROM candidates 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 Rain 100 Remmelgas Kati 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