L05.pdf

Some sugestion with group project in ERD: identites:...

Some sugestion with group project in ERD: identites: 1. an attribute should noe be duplicated relationship … IT5008 Database Design & Programming L05: Nested Queries IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 1 / 25 Splitting Query IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 2 / 25 Splitting Query ❱ Copy Permanent Copy Temporary Unchanged Permanent Query Remark Copy of Table We can make a copy of a subquery in a new table. CREATE TABLE singapore_customer AS SELECT * FROM customers c WHERE c.country = 'Singapore'; SELECT cs.last_name, d.name FROM singapore_customers cs, downloads d WHERE cs.customerid = d.customerid; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 3 / 25 Splitting Query ❱ Copy Permanent Copy Temporary Unchanged Temporary Query Remark Copy of Table We can make a copy of a subquery in a temporary table. CREATE TEMPORARY TABLE singapore_customer AS SELECT * FROM customers c WHERE c.country = 'Singapore'; Note A temporary table only exists for the duration of the database session. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 4 / 25 Splitting Query ❱ Copy Permanent Copy Temporary Unchanged Unchanged Query Remark No Change in Copy The copies do not change when the base table (in the example, the table customers) change (i.e., INSERT, DELETE, UPDATE, etc). Note It is rarely a good idea. Most of the time, the same query can be rewritten as a simple query. SELECT c.last_name, d.name FROM customers c, downloads d WHERE c.country = 'Singapore' AND c.customerid = d.customerid; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 5 / 25 Splitting Query Copy Query ❱ Query View View CTE FROM SELECT Visible Change Remark It could be a good idea since VIEW change when the base table change. Unfortunately, views are unmaterialized and materialized views need to be refreshed. CREATE VIEW singapore_customer AS SELECT * FROM customers c WHERE c.country = 'Singapore'; Note Most of the time, the same query can be rewritten as a simple query. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 6 / 25 Splitting Query Copy Query ❱ Query View CTE CTE FROM SELECT A Single Query Remark Common table expression (CTE) is a copy of a subquery in a temporary table that only exists for the query. WITH singapore_customer AS ( SELECT * FROM customers c WHERE c.country = 'Singapore' ) SELECT cs.last_name, d.name FROM singapore_customers cs, downloads d WHERE cs.customerid = d.customerid; ✱ Most of the time, the same query can be rewritten as a simple query. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 7 / 25 Splitting Query Copy Query ❱ Query View FROM CTE FROM SELECT A Single Query Remark We can use subquery in the FROM clause. As a good practice, you should rename the result using AS operator. SELECT cs.last_name, d.name FROM ( SELECT * FROM customers c WHERE c.country = 'Singapore' ) AS cs, downloads d WHERE cs.customerid = d.customerid; ✱ Most of the time, the same query can be rewritten as a simple query. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 8 / 25 Splitting Query Copy Query ❱ Query View SELECT CTE FROM SELECT Scalar Subquery Remark We can use subquery in the SELECT clause, but it must return only one column and one row (also known as scalar subquery). SELECT ( SELECT COUNT(*) FROM customers s WHERE c.country = 'Singapore' ); SELECT COUNT(*) FROM customers s WHERE c.country = 'Singapore'; ✱ Most of the time, the same query can be rewritten without nesting. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 9 / 25 Splitting Query Copy Remark Query ❱ Remark Readable and Maintainable Copies, temporary tables, views, common table expressions✱, and nested queries have legitimate and appropriate usage. It is --however-- recommended to seek simpler solutions first or, at least, to be able to justify their usage. These may not always yield readable or efficient queries. ✱ We do not discuss recursive queries here. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 10 / 25 Nesting Query IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 11 / 25 Nesting Query ❱ WHERE IN WHERE ANY ALL IN EXISTS Correlation Computing Tuples Negated Queries We can use subquery in WHERE clause to compute the tuples for IN clause. Nested Having SELECT d.name FROM downloads d WHERE d.customerid IN ( SELECT c.customerid FROM customers c WHERE c.country = 'Singapore' ); ✱ Most of the time, the same query can be rewritten as a simple query. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 12 / 25 Nesting Query ❱ WHERE IN WHERE ANY ALL ANY EXISTS Correlation Equals to Any Negated Queries The following two queries are the same. Never use comparison to a subquery without Nested Having specifying the quantifier ALL or ANY. SELECT d.name SELECT d.name FROM downloads d FROM downloads d WHERE d.customerid IN ( WHERE d.customerid = ANY ( SELECT c.customerid SELECT c.customerid FROM customers c FROM customers c WHERE c.country = 'Singapore' WHERE c.country = 'Singapore' ); ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 13 / 25 Nesting Query ❱ WHERE IN WHERE ANY ALL ALL EXISTS Correlation Outer Join, Except, Aggregate Negated Queries ALL adds expressive power similar to that of OUTER JOIN, EXCEPT, and aggregate functions. Nested Having The query below finds the most expensive games. SELECT g1.name, g1.version, g1.price FROM games g1, WHERE g1.price >= ALL ( SELECT g2.price FROM games g2 ); ✱ Change ALL to ANY and we print all games! IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 14 / 25 Nesting Query ❱ WHERE IN WHERE ANY ALL ALL EXISTS Correlation ALL to the Rescue Negated Queries The following queries do not work (GROUP BY limitation, syntax error) but could be rewritten as a Nested Having nested query using ALL subquery. Error OK SELECT g.name, g.version, g.price SELECT g1.name, g1.version, g1.price FROM games g WHERE g.price = MAX(g.price) FROM games g1 WHERE g1.price = ALL( SELECT g1.name, g1.version, g1.price SELECT MAX(g2.price) FROM games g1 FROM games g2 WHERE g1.price = MAX( ); SELECT g2.price FROM games g2 ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 15 / 25 Nesting Query ❱ WHERE IN WHERE ANY ALL EXISTS EXISTS Correlation Empty or Not Empty Negated Queries EXISTS evaluates to true if the subquery has some result. It evaluates to false if the Nested Having subquery has no result. SELECT d.name Correlated Subquery FROM downloads d The subquery is correlated to the query. WHERE EXISTS ( The column d.customerid of the SELECT c.customerid customer table of the outer query appears FROM customers c in the WHERE clause of the inner query. WHERE d.customerid = c.customerid AND c.country = 'Singapore' ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 16 / 25 Nesting Query WHERE Correlation ❱ Correlation Subquery Subquery Scoping Scalar Negated Queries Correlated Subquery Nested Having All subqueries can be correlated. The query below finds the names, versions, and prices of the games that are most expensive among the games of the same name. SELECT g1.name, g1.version, g1.price FROM games g1 WHERE g1.price >= ALL ( SELECT g2.price FROM games g2 WHERE g1.name = g2.name ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 17 / 25 Nesting Query WHERE Correlation ❱ Correlation Subquery Scoping Scoping Scalar Negated Queries Nested Scoping Nested Having You can always use column from an outer table in an inner query but not the other way around. This is similar to lexical scoping. SELECT c.customerid, d.name FROM downloads d WHERE d.customerid IN ( SELECT c.customerid FROM customers c WHERE c.country = 'Singapore' ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 18 / 25 Nesting Query WHERE Correlation ❱ Correlation Subquery Scalar Scoping Scalar Negated Queries Correlated SELECT Nested Having We can use subquery in SELECT clause, it still needs to be a scalar subquery, but it can be correlated. SELECT ( SELECT c.last_name FROM customers c WHERE c.country = 'Singapore' AND d.customerid = c.customerid ), d.name FROM downloads d; ✱ Most of the time, the same query can be rewritten as a simple query. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 19 / 25 Nesting Query WHERE Negated Queries Correlation ❱ Negated Queries Nested + Negation Nested + Negation Nested Having Where It Matters Nested queries are powerful when combined with negation. SELECT c.customerid SELECT c.customerid SELECT c.customerid FROM customers c FROM customers c FROM customers c WHERE c.customerid NOT IN ( WHERE c.customerid ALL ( WHERE NOT EXISTS ( SELECT d.customerid SELECT d.customerid SELECT d.customer id FROM downloads d FROM downloads d FROM downloads d ); ); WHERE c.customerid = d.customerid ); The three queries above find the 22 customers who never downloaded a game. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 20 / 25 Nesting Query WHERE Nested Having Correlation Negated Queries Nested + HAVING ❱ Nested Having Where It Matters Nested queries may be necessary if we have are using aggregation functions on two different groupings. SELECT c1.country Query? FROM customers c1 The query on the left finds the countries GROUP BY c1.country with the largest number of customers. HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM customers c2 GROUP BY c1.country ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 21 / 25 Conclusion IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 22 / 25 Conclusion ❱ Solving Solving Reading Question Who are our best customers in each country (i.e., those who spend the most money among all the customers in their country)? Your code here... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 23 / 25 Conclusion Solving Reading ❱ Reading Question What does this query find? (We need to understand the theoretical foundation of SQL to master reading and writing such queries) SELECT c.first_name, c.last_name Your idea here... FROM customers c WHERE NOT EXISTS ( SELECT * FROM games g WHERE g.name = 'Aerified' AND NOT EXISTS ( SELECT * FROM downloads d WHERE d.customerid = c.customerid AND d.name = g.name AND d.version = g.version )); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 24 / 25 postgres=# exit Press any key to continue... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan)