DOC PREVIEW
UT Dallas CS 6360 - CS-6360_ch5 More SQL

This preview shows page 1-2-3-24-25-26-27-48-49-50 out of 50 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 50 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

!" #Chris Irwin Davis, Ph.D.Email: [email protected]: (972) 883-3574Office: ECSS 4.705Chapter 5: More SQLCS-6360 Database Design!" #Chapter 5 Outline§More Complex SQL Retrieval Queries§Specifying Constraints as Assertions and Actions as Triggers§Views (Virtual Tables) in SQL§Schema Change Statements in SQL2!" #More Complex SQL Retrieval Queries§Additional features allow users to specify more complex retrievals from database:§Derived values§Nested queries§Joined tables§Outer joins§Aggregate functions and grouping3!" #Selection Criteria in WHERE Clause•FROM clause (logically) generates Cartesian Product of tables°e.g. FROM T1, T2 ⇔ T1 ╳ T2 •WHERE clause filters based on selection criteria°Each “wide” tuple of FROM clause is considered individually, in sequence4!" #Comparisons Involving NULLand Three-Valued Logic§Meanings of NULL§Unknown value§Unavailable or withheld value§Not applicable attribute§Each individual NULL value considered to be different and distinct from every other NULL value§SQL uses a three-valued logic:§TRUE, FALSE, and UNKNOWN§Why significant?5!" #Comparisons Involving NULLand Three-Valued Logic (cont’d.)6!" #Comparisons Involving NULLand Three-Valued Logic (cont’d.)7!" #Comparisons Involving NULLand Three-Valued Logic (cont’d.)§SQL allows queries that check whether an attribute value is NULL§IS NULL or IS NOT NULL8!" #Comparisons Involving NULLand Three-Valued Logic (cont’d.)§SQL allows queries that check whether an attribute value is NULL§IS NULL or IS NOT NULL9!" #Nested Queries•Nested in WHERE clause°WHERE [NOT] attribute ⟨comp_op⟩ (subquery)°WHERE [NOT] attribute ⟨comp_op⟩ ALL (subquery)°WHERE [NOT] attribute ⟨comp_op⟩ ANY (subquery)°WHERE attribute [NOT] IN (subquery)°WHERE attribute IN (subquery)•Nested in FROM clause (creating a View on-the-fly)°FROM (subquery) AS alias10!" #IN Clause§Comparison set operator IN§Compares value v with a set (or multiset) of values V §Evaluates to TRUE if v is one of the elements in V11!" #Nested Queries (cont’d.)12UNION!" #Nested Queries (cont’d.)§Use tuples of values in comparisons §Place them within parentheses13!" #Nested Queries (cont’d.)§Use tuples of values in comparisons §Place them within parentheses14SELECT DISTINCT EssnFROM Works_on WHERE (Pno, Hours) IN (SELECT Pno, Hours FROM Works_on WHERE Essn = '123456789');!" #IN Operator with Explicit Set• SELECT *FROM DepartmentWHERE Dnumber IN (1,7,8);15!" #§Use other comparison operators to compare a single value v §= ANY (or = SOME) operator •Returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN§Other operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>Nested Queries (cont’d.)16!" #Nested Queries (cont’d.)§Avoid potential errors and ambiguities§Create tuple variables (aliases) for all tables referenced in SQL query17!" #Correlated Nested Queries§Correlated nested query §Whenever a condition in the WHERE clause of a nested query references some attribute of a relation declared in the outer query, the two queries are said to be correlated.§We can understand a correlated query better by considering that the nested query is evaluated once for each tuple (or combination of tuples) in the outer query.§For example, we can think of Q16 as follows: For each EMPLOYEE tuple, evaluate the nested query, which retrieves the Essn values for all DEPENDENT tuples with the same sex and name as that EMPLOYEE tuple; if the Ssn value of the EMPLOYEE tuple is in the result of the nested query, then select that EMPLOYEE tuple.18!" #The EXISTS and UNIQUE Functions in SQL§EXISTS function §Check whether the result of a correlated nested query is empty or not§EXISTS and NOT EXISTS §Typically used in conjunction with a correlated nested query§SQL function UNIQUE(Q)§Returns TRUE if there are no duplicate tuples in the result of query Q19!" #Alternate Query 16 + Query 620!" #Explicit Sets and Renaming of Attributes in SQL§Can use explicit set of values in WHERE clause21!" #Explicit Sets and Renaming of Attributes in SQL§Can use explicit set of values in WHERE clause22!" #Explicit Sets and Renaming of Attributes in SQL§Renaming of Attributes§Use qualifier AS followed by desired new name§Rename any attribute that appears in the result of a query23!" #Joined Tables in SQL and Outer Joins§Joined table§Permits users to specify a table resulting from a join operation in the FROM clause of a query§The FROM clause in Q1A §Contains a single joined table24!" #Joined Tables in SQL and Outer Joins (cont’d.)§Specify different types of join§NATURAL JOIN§Various types of OUTER JOIN§NATURAL JOIN on two relations R and S§No join condition specified§Implicit EQUIJOIN condition for each pair of attributes with same name from R and S25!" #Joined Tables in SQL and Outer Joins (cont’d.)§Inner join§Default type of join in a joined table§Tuple is included in the result only if a matching tuple exists in the other relation26!" #Joined Tables in SQL and Outer Joins (cont’d.)§Outer joins§LEFT JOIN §Every tuple in left table must appear in result§If no matching tuple, padded with NULL values for attributes of right table§RIGHT JOIN§Not supported by SQLite§FULL JOIN§Not supported by MySQL, SQLite§Can be simulated with Left Join / Right Join UNION27!" #28!" #29!" #Aggregate Functions in SQL§Used to summarize information from multiple tuples into a single-tuple summary§Grouping (GROUP BY)§Create subgroups of tuples before summarizing§Built-in Aggregate Functions §COUNT, SUM, MAX, MIN, and AVG§Aggregate functions can be used in the SELECT clause or in a HAVING clause, but not WHERE clause.30!" #Aggregate Functions in SQL (cont’d.)§NULL values discarded when aggregate functions are applied to a particular column31!" #Grouping: The GROUP BY and HAVING Clauses§Partition relation into subsets of tuples§Based on grouping attribute(s)§Apply function to each such group independently§GROUP BY clause §Specifies grouping attributes§If NULLs exist in grouping attribute §Separate group created for all tuples with a NULL value in grouping attribute32!" #Grouping: The


View Full Document

UT Dallas CS 6360 - CS-6360_ch5 More SQL

Documents in this Course
Ch22(1)

Ch22(1)

44 pages

Ch21

Ch21

38 pages

Ch19

Ch19

46 pages

Ch18

Ch18

25 pages

Ch17

Ch17

21 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch05

Ch05

34 pages

Ch22

Ch22

45 pages

Ch21

Ch21

38 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch16

Ch16

17 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

34 pages

Ch06

Ch06

43 pages

Ch05

Ch05

34 pages

Ch04

Ch04

39 pages

Ch03(2)

Ch03(2)

36 pages

Ch02

Ch02

33 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

lab-manual

lab-manual

215 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch17

Ch17

25 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch04(1)

Ch04(1)

43 pages

Ch07

Ch07

40 pages

Ch03

Ch03

42 pages

Ch01

Ch01

36 pages

Ch02

Ch02

38 pages

Ch05

Ch05

41 pages

Ch06

Ch06

47 pages

Ch08

Ch08

39 pages

Ch17

Ch17

25 pages

Ch18

Ch18

24 pages

Ch09

Ch09

42 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04(1)

Ch04(1)

43 pages

Ch03

Ch03

42 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Load more
Download CS-6360_ch5 More SQL
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view CS-6360_ch5 More SQL and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view CS-6360_ch5 More SQL 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?