L02.pdf

IT5008 Database Design & Programming L02: Simple Queries IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 1 / 34 Single Table IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from...

IT5008 Database Design & Programming L02: Simple Queries IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 1 / 34 Single Table IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 2 / 34 Single Table ❱ Basic Simple Queries Basic Wildcards Simple Queries Duplicate Operation SELECT-FROM-WHERE A simple SQL query includes SELECT clause that indicates the columns to be output, a FROM clause that indicates the table(s) to be queried, and possibly a WHERE clause that indicates a possible condition on the records to be printed. Code Table SELECT first_name, last_name first_name last_name FROM customers "Deborah" "Ruiz" WHERE country = 'Singapore'; "Tammy" "Lee" "Walter" "Leong"... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 3 / 34 Single Table ❱ Basic Simple Queries Basic Wildcards Wildcards Duplicate Operation Asterisk The asterisk is a shorthand indicating that all the column names, in order of the CREATE TABLE declaration, should be considered in the SELECT clause. Full Listing Using Wildcard SELECT first_name, last_name, SELECT * email, dob, since, FROM customers; customerid, country FROM customers; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 4 / 34 Single Table Basic Duplicate ❱ Duplicate Ordering Ordering Distinct Composition Operation Projection Selecting a subset of the columns (i.e., projection) of a table may result in duplicate rows even if the original table has a primary key. This can be made clearer if we order the result (i.e., on the right). No Ordering ORDER BY SELECT name, version SELECT name, version FROM downloads; FROM downloads ORDER BY name, version; Default By default, order in ASC and not DESC. ✱ Always use ASC and DESC for good practice. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 5 / 34 Single Table Basic Duplicate ❱ Duplicate Ordering Ordering Distinct Composition Operation Unordered If we only order partially (i.e., not using all columns), the columns not mentioned are in no particular order. Partial Ordering SELECT name, version Note FROM downloads We only order by name. version may ORDER BY name ASC; appear in any order. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 6 / 34 Single Table Basic Duplicate ❱ Duplicate Ordering Ordering Distinct Composition Operation Unmentioned We can order the result according to a column that is not printed. But this may interfere with other constructs. Partial Ordering SELECT name, version Note FROM games price is used in ORDER BY but not ORDER BY price ASC; present in the SELECT clause. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 7 / 34 Single Table Basic Duplicate ❱ Duplicate Ordering Distinct Distinct Composition Operation Deduplication The keyword DISTINCT eliminates eventual duplicates in the result of the query. It requests that the results contains distinct rows (it does not apply to columns individually). DISTINCT SELECT DISTINCT name, version Note FROM downloads The query with DISTINCT displays 422 records. The query without DISTINCT displays 4214 records. It often gives a sorted result but this is not guaranteed. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 8 / 34 Single Table Basic Duplicate ❱ Duplicate Ordering Composition Distinct Composition Operation Combining Constructs You can combine different constructs but not always. Not all combinations make sense. Both DISTINCT and ORDER BY conceptually involve sorting and ORDER BY is applied before DISTINCT. SQL does not know which row to remove. Compatible Incompatible SELECT DISTINCT name SELECT DISTINCT name, version FROM games FROM games ORDER BY name ASC; ORDER BY price ASC; price does not appear in DISTINCT. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 9 / 34 Single Table Basic Operation Duplicate ❱ Operation Condition Condition Operators WHERE Clause The WHERE clause is used to filter rows on a Boolean condition. The Boolean condition uses Boolean operators such as AND, OR, and NOT as well as various comparison operators such as >, =, = '2016-12-01') AND last_name LIKE 'B%'; LIKE==with sample pattern matching ✱ See: BETWEEN.. AND and LIKE documentations. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 10 / 34 Single Table Basic Operation Duplicate ❱ Operation Operators Condition Operators Operators in PostgreSQL Arithmetic and other functions can be used in SELECT and WHERE clauses (as well as in the ORDER BY, GROUP BY, and HAVING clauses). In SELECT Clause SELECT DISTINCT price * 0.09 AS gst FROM games ORDER BY gst ASC; ✱ More info on operators. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 11 / 34 Single Table Basic Operation Duplicate ❱ Operation Operators Condition Operators Operators in PostgreSQL Arithmetic and other functions can be used in SELECT and WHERE clauses (as well as in the ORDER BY, GROUP BY, and HAVING clauses). In WHERE Clauses SELECT SELECT name || ' ' || version AS game, name || ' ' || version AS game, ROUND(price * 1.09, 2) AS price price FROM games FROM games WHERE price * 0.09 >= 0.3; WHERE price * 0.09 < 0.3; ✱ We perform operations on price and rename the result as price again. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 12 / 34 Single Table Basic Operation Duplicate ❱ Operation Operators Condition Operators Case Analysis We can do this but not preferrable. SELECT name || ' ' || version, CASE WHEN price * 0.09 >= 0.3 THEN ROUND(price * 1.09, 2) ELSE price END AS price FROM games; ✱ The syntax of operations and functions can be specific to DBMS, see PostgreSQL documentation. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 13 / 34 Logic and NULL IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 14 / 34 Logic and NULL ❱ Reasoning Reasoning De Morgan's Law Boolean Differences? Operations SELECT first_name, last_name FROM customers WHERE (country = 'Singapore' OR country = 'Indonesia') AND ((dob >= '2000-01-01' AND dob = '2016-12-01') AND last_name LIKE 'B%'; SELECT first_name, last_name FROM customers WHERE country IN ('Indonesia', 'Singapore') AND last_name LIKE 'B%' AND (since >= '2016-12-01' OR NOT (dob < '2000-01-01' OR dob > '2000-12-01')); SELECT first_name, last_name FROM customers WHERE (country = 'Singapore' OR country = 'Indonesia') AND (dob BETWEEN '2000-01-01' AND '2000-12-01' OR since >= '2016-12-01') AND last_name LIKE 'B%'; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 15 / 34 Logic and NULL Reasoning De Morgan's Law ❱ De Morgan's Law Boolean Equivalence Operations Negation of Conjunction/Disjunction ¬(A ∧ B) ≡ ¬A ∨ ¬B ¬(A ∨ B) ≡ ¬A ∧ ¬B SELECT name FROM games WHERE (version = '1.0' OR version = '1.1'); SELECT name FROM games WHERE version IN ('1.0', '1.1'); SELECT name FROM games WHERE NOT (version '1.0' AND version '1.1'); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 16 / 34 Logic and NULL Reasoning Boolean De Morgan's Law ❱ Boolean Truth Table Truth Table NULL Value P Q P AND Q P OR Q NOT P Three Valued Operations True True True True False True False False True False False True False True True False False False False True WHERE Clause "SELECT FROM WHERE " returns results when the is true (later on, you will see that it does not return result when the is false or NULL). IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 17 / 34 Logic and NULL Reasoning Boolean De Morgan's Law ❱ Boolean NULL Value Truth Table NULL Value Meaning of NULL Three Valued Operations Every domain has the additional value: the NULL value. In SQL it generally (but not always) has the meaning of "unknown". "something = NULL" is unknown (even if "something" is NULL). "something NULL" is unknown (even if "something" is NULL). "something > NULL" is unknown. "something < NULL" is unknown. "10 + NULL" is null. "0 * NULL" is null! IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 18 / 34 Logic and NULL Reasoning Boolean De Morgan's Law ❱ Boolean NULL Value Truth Table NULL Value CREATE TABLE example ( INSERT INTO example VALUES Three Valued Operations column1 VARCHAR(32), ('abc', 1), column2 NUMERIC ('def', NULL), ); ('ghi', NULL); SELECT * column1 column2 FROM example; "abc" 1 "def" "ghi" IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 19 / 34 Logic and NULL Reasoning Boolean De Morgan's Law ❱ Boolean NULL Value Truth Table NULL Value SELECT column1, column2 SELECT column1, column2 Three Valued Operations FROM example FROM example WHERE column2 > 0; WHERE column2