subqueries - university of washingtonsubquery. (why?) 2. subqueriesin from sometimes we need to...
TRANSCRIPT
![Page 1: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/1.jpg)
CSE 344JANUARY 19TH – SUBQUERIES 2 AND RELATIONAL ALGEBRA
![Page 2: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/2.jpg)
ASSORTED MINUTIAE• Winter storm Inga• Online quiz out after class
• Still due Wednesday, will be shorter but through today’s lecture
• For SQLite submissions, please use AS when aliasing
![Page 3: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/3.jpg)
TODAY’S LECTURE• Review of ordering• Subqueries in FROM and WHERE• Intro to relational algebra (maybe)
![Page 4: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/4.jpg)
SEMANTICS OF SQL WITH GROUP-BY
CSE 344 - 2017au
Evaluation steps:1. Evaluate FROM-WHERE using Nested Loop Semantics2. Group by the attributes a1,…,ak
3. Apply condition C2 to each group (may have aggregates)4. Compute aggregates in S and return the result
SELECT SFROM R1,…,RnWHERE C1GROUP BY a1,…,akHAVING C2
FWGHOS
![Page 5: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/5.jpg)
SUBQUERIES
A subquery is a SQL query nested inside a larger querySuch inner-outer queries are called nested queriesA subquery may occur in:
• A SELECT clause• A FROM clause• A WHERE clause
Rule of thumb: avoid nested queries when possible• But sometimes it’s impossible, as we will see
![Page 6: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/6.jpg)
SUBQUERIES…Can return a single value to be included in a SELECT clauseCan return a relation to be included in the FROMclause, aliased using a tuple variableCan return a single value to be compared with another value in a WHERE clauseCan return a relation to be used in the WHERE or HAVING clause under an existential quantifier
![Page 7: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/7.jpg)
1. SUBQUERIES IN SELECT
Product (pname, price, cid)Company (cid, cname, city)
For each product return the city where it is manufactured
SELECT X.pname, (SELECT Y.cityFROM Company YWHERE Y.cid=X.cid) as City
FROM Product X
What happens if the subquery returns more than one city?
We get a runtime error(and SQLite simply ignores the extra values…)
“correlated subquery”
![Page 8: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/8.jpg)
1. SUBQUERIES IN SELECT
Whenever possible, don’t use a nested queries:
SELECT X.pname, Y.cityFROM Product X, Company YWHERE X.cid=Y.cid
=
SELECT X.pname, (SELECT Y.cityFROM Company YWHERE Y.cid=X.cid) as City
FROM Product X
We have “unnested”the query
![Page 9: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/9.jpg)
1. SUBQUERIES IN SELECT
Compute the number of products made by each company
SELECT DISTINCT C.cname, (SELECT count(*)FROM Product P WHERE P.cid=C.cid)
FROM Company C
![Page 10: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/10.jpg)
1. SUBQUERIES IN SELECT
CSE 344 - 2017au
Compute the number of products made by each company
SELECT DISTINCT C.cname, (SELECT count(*) FROM Product P WHERE P.cid=C.cid)
FROM Company C
Better: we can unnest using a GROUP BY
SELECT C.cname, count(*)FROM Company C, Product PWHERE C.cid=P.cidGROUP BY C.cname
![Page 11: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/11.jpg)
1. SUBQUERIES IN SELECT
But are these really equivalent?
SELECT DISTINCT C.cname, (SELECT count(*) FROM Product P WHERE P.cid=C.cid)
FROM Company C
SELECT C.cname, count(*)FROM Company C, Product PWHERE C.cid=P.cidGROUP BY C.cname
![Page 12: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/12.jpg)
1. SUBQUERIES IN SELECT
CSE 344 - 2017au
But are these really equivalent?
SELECT DISTINCT C.cname, (SELECT count(*) FROM Product P WHERE P.cid=C.cid)
FROM Company C
No! Different results if a company has no products
SELECT C.cname, count(*)FROM Company C, Product PWHERE C.cid=P.cidGROUP BY C.cname
SELECT C.cname, count(pname)FROM Company C LEFT OUTER JOIN Product PON C.cid=P.cidGROUP BY C.cname
![Page 13: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/13.jpg)
2. SUBQUERIES IN FROM
Find all products whose prices is > 20 and < 500
SELECT X.pnameFROM (SELECT *
FROM Product AS Y WHERE price > 20) as X
WHERE X.price < 500
![Page 14: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/14.jpg)
2. SUBQUERIES IN FROM
Find all products whose prices is > 20 and < 500
SELECT X.pnameFROM (SELECT *
FROM Product AS Y WHERE price > 20) as X
WHERE X.price < 500
Try unnest this query !
![Page 15: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/15.jpg)
2. SUBQUERIES IN FROM
Find all products whose prices is > 20 and < 500
SELECT X.pnameFROM (SELECT *
FROM Product AS Y WHERE price > 20) as X
WHERE X.price < 500
Try unnest this query !
Side note: This is not a correlated subquery. (why?)
![Page 16: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/16.jpg)
2. SUBQUERIES IN FROM
Sometimes we need to compute an intermediate table only to use it later in a SELECT-FROM-WHEREOption 1: use a subquery in the FROM clauseOption 2: use the WITH clause
![Page 17: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/17.jpg)
2. SUBQUERIES IN FROM
CSE 344 - 2017au 17
SELECT X.pnameFROM (SELECT *
FROM Product AS Y WHERE price > 20) as X
WHERE X.price < 500=WITH myTable AS (SELECT * FROM Product AS Y WHERE price > 20)SELECT X.pnameFROM myTable as XWHERE X.price < 500
A subquery whoseresult we called myTable
![Page 18: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/18.jpg)
3. SUBQUERIES IN WHERE
Find all companies that make some products with price < 200
![Page 19: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/19.jpg)
3. SUBQUERIES IN WHERE
Find all companies that make some products with price < 200
Existential quantifiers
![Page 20: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/20.jpg)
3. SUBQUERIES IN WHERE
Find all companies that make some products with price < 200
SELECT DISTINCT C.cnameFROM Company CWHERE EXISTS (SELECT *
FROM Product PWHERE C.cid = P.cid and P.price < 200)
Existential quantifiers
Using EXISTS:
![Page 21: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/21.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company CWHERE C.cid IN (SELECT P.cid
FROM Product PWHERE P.price < 200)
Using IN
Find all companies that make some products with price < 200
Existential quantifiers
![Page 22: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/22.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company CWHERE 200 > ANY (SELECT price
FROM Product PWHERE P.cid = C.cid)
Using ANY:
Find all companies that make some products with price < 200
Existential quantifiers
![Page 23: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/23.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company CWHERE 200 > ANY (SELECT price
FROM Product PWHERE P.cid = C.cid)
Using ANY:
Find all companies that make some products with price < 200
Existential quantifiers
Not supported in sqlite
![Page 24: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/24.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company C, Product PWHERE C.cid = P.cid and P.price < 200
Now let’s unnest it:
Find all companies that make some products with price < 200
Existential quantifiers
![Page 25: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/25.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company C, Product PWHERE C.cid = P.cid and P.price < 200
Existential quantifiers are easy!
Now let’s unnest it:
Find all companies that make some products with price < 200
Existential quantifiers
![Page 26: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/26.jpg)
3. SUBQUERIES IN WHERE
same as:
Find all companies that make only products with price < 200
Find all companies s.t. all their products have price < 200
![Page 27: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/27.jpg)
3. SUBQUERIES IN WHERE
same as:
Universal quantifiers
Find all companies that make only products with price < 200
Find all companies s.t. all their products have price < 200
![Page 28: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/28.jpg)
3. SUBQUERIES IN WHERE
Universal quantifiers are hard!
same as:
Universal quantifiers
Find all companies that make only products with price < 200
Find all companies s.t. all their products have price < 200
![Page 29: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/29.jpg)
3. SUBQUERIES IN WHERE
1. Find the other companies that make some product ≥ 200
SELECT DISTINCT C.cnameFROM Company CWHERE C.cid IN (SELECT P.cid
FROM Product PWHERE P.price >= 200)
Find all companies s.t. all their products have price < 200
![Page 30: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/30.jpg)
3. SUBQUERIES IN WHERE
2. Find all companies s.t. all their products have price < 200
1. Find the other companies that make some product ≥ 200
SELECT DISTINCT C.cnameFROM Company CWHERE C.cid IN (SELECT P.cid
FROM Product PWHERE P.price >= 200)
Find all companies s.t. all their products have price < 200
SELECT DISTINCT C.cnameFROM Company CWHERE C.cid NOT IN (SELECT P.cid
FROM Product PWHERE P.price >= 200)
![Page 31: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/31.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company CWHERE NOT EXISTS (SELECT *
FROM Product PWHERE P.cid = C.cid and P.price >= 200)
Using EXISTS:
Universal quantifiers
Find all companies s.t. all their products have price < 200
![Page 32: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/32.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company CWHERE 200 >= ALL (SELECT price
FROM Product PWHERE P.cid = C.cid)
Using ALL:
Universal quantifiers
Find all companies s.t. all their products have price < 200
![Page 33: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/33.jpg)
3. SUBQUERIES IN WHERE
SELECT DISTINCT C.cnameFROM Company CWHERE 200 >= ALL (SELECT price
FROM Product PWHERE P.cid = C.cid)
Using ALL:
Universal quantifiers
Find all companies s.t. all their products have price < 200
Not supported in sqlite
![Page 34: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/34.jpg)
QUESTION FOR DATABASE THEORY FANSAND THEIR FRIENDS
Can we unnest the universal quantifierquery?
We need to first discuss the concept of monotonicity
![Page 35: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/35.jpg)
MONOTONE QUERIESDefinition A query Q is monotone if:
• Whenever we add tuples to one or more input tables, the answer to the query will not lose any of the tuples
![Page 36: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/36.jpg)
MONOTONE QUERIESTheorem: If Q is a SELECT-FROM-WHERE query that does not have subqueries, and no aggregates, then it is monotone.
CSE 344 - 2017au 36
![Page 37: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/37.jpg)
MONOTONE QUERIESTheorem: If Q is a SELECT-FROM-WHERE query that does not have subqueries, and no aggregates, then it is monotone.
Proof. We use the nested loop semantics: if we insert a tuple in a relation Ri, this will not remove any tuples from the answer
CSE 344 - 2017au 37
SELECT a1, a2, …, akFROM R1 AS x1, R2 AS x2, …, Rn AS xnWHERE Conditions
for x1 in R1 dofor x2 in R2 do
…for xn in Rn doif Conditionsoutput (a1,…,ak)
![Page 38: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/38.jpg)
MONOTONE QUERIESThe query:
is not monotone
38
Find all companies s.t. all their products have price < 200
Product (pname, price, cid)Company (cid, cname, city)
![Page 39: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/39.jpg)
MONOTONE QUERIESThe query:
is not monotone
39
Find all companies s.t. all their products have price < 200
pname price cid
Gizmo 19.99 c001
cid cname city
c001 Sunworks Bonn
cname
Sunworks
Product (pname, price, cid)Company (cid, cname, city)
![Page 40: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/40.jpg)
MONOTONE QUERIESThe query:
is not monotone
Consequence: If a query is not monotonic, then we cannot write it as a SELECT-FROM-WHERE query without nested subqueries
40
Find all companies s.t. all their products have price < 200
pname price cid
Gizmo 19.99 c001
cid cname city
c001 Sunworks Bonn
cname
Sunworks
pname price cid
Gizmo 19.99 c001
Gadget 999.99 c001
cid cname city
c001 Sunworks Bonn
cname
Product (pname, price, cid)Company (cid, cname, city)
![Page 41: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/41.jpg)
QUERIES THAT MUST BE NESTEDQueries with universal quantifiers or with negation
CSE 344 - 2017au 41
![Page 42: subqueries - University of Washingtonsubquery. (why?) 2. SUBQUERIESIN FROM Sometimes we need to compute an intermediate table only to ... that does not have subqueries, and no aggregates,](https://reader033.vdocuments.us/reader033/viewer/2022042021/5e77f1ff2c8fd754d6372128/html5/thumbnails/42.jpg)
QUERIES THAT MUST BE NESTEDQueries with universal quantifiers or with negation
Queries that use aggregates in certain ways
• sum(..) and count(*) are NOT monotone, because they do not satisfy set containment
• select count(*) from R is not monotone!
CSE 344 - 2017au 42