MORE SQL Complex Queries Trrigers Views and Schema

全体の目次 • 7 -1 More Complex SQL Retrieval Queries • 7 -2 Specifying Constraints

全体の目次 • 7 -1 Specifying Constraints as Assertions and Actions as Triggers • 7

7 -1 の目次 • 7 -1 More Complex SQL Retrieval Queries • • •

7 -1 -8 Grouping: The GROUP BY and HAVING Clauses • 集計関数を何かの属性ごとに適用したい時に GROUP BY節を使用する。

Having節を使う際の注意 d 1 A: 20000 B: 30000 C: 50000 d 2 D: 20000 E:

7 -1 -10 Recursive Queries in SQL WITH RECURSIVE r AS ( SELECT *

7 -2の目次 • 7. 2. 1 Specifying General Constraints as Assertions in SQL •

7 -3の目次 • 7. 3. 1 Concept of a View in SQL • 7.

7 -4の目次 • 7. 4. 1 The DROP Command • 7. 4. 2 The

7 -4 -2 The ALTER Command • ALTERコマンドではテーブルに変更を加えることができる。 ①属性の追加、削除 ALTER TABLE COMPANY. EMPLOYEE ADD

Slides: 64

Download presentation

MORE SQL : Complex Queries, Trrigers, Views, and Schema Modification Chapter 7 20190409 tasuku 1

全体の目次 • 7 -1 More Complex SQL Retrieval Queries • 7 -2 Specifying Constraints as Assertions and Actions as Triggers • 7 -3 Views (Virtual Tables) in SQL • 7 -4 Schema Change Statements in SQL • 7 -5 Summary 2

全体の目次 • 7 -1 Specifying Constraints as Assertions and Actions as Triggers • 7 -2 Specifying Constraints as Assertions and Actions as Triggers • 7 -3 Views (Virtual Tables) in SQL • 7 -4 Schema Change Statements in SQL • 7 -5 Summary 3

7 -1 の目次 • 7 -1 More Complex SQL Retrieval Queries • • • 7 -1 -1 Comparisons Involving NULL and Three-Valued Logic 7 -1 -2 Nested Queries, Tuples, and Set/Multiset Comparisons 7 -1 -3 Correlated Nested Queries 7 -1 -4 The EXISTS and UNIQUE Functions in SQL 7 -1 -5 Explicit Sets and Renaming in SQL 7 -1 -6 Joined Tables in SQL and Outer Joins 7 -1 -7 Aggregate Functions in SQL 7 -1 -8 Grouping: The GROUP BY and HAVING Clauses 7 -1 -9 Other SQL Constructs: WITH and CASE 7 -1 -10 Recursive Queries in SQL 7 -1 -11 Discussion and Summary of SQL Queries 4

TRUE, FALSE, UNKNOWNの論理演算 6

EXIST の用例 15

結合と自然結合の結果 Join Natural Join 20

7 -1 -8 Grouping: The GROUP BY and HAVING Clauses • 集計関数を何かの属性ごとに適用したい時に GROUP BY節を使用する。 SELECT team, COUNT(team) FROM user GROUP BY team; Userテーブル 27

Having の例 30

Having節を使う際の注意 d 1 A: 20000 B: 30000 C: 50000 d 2 D: 20000 E: 30000 F: 50000 G: 60000 H: 70000 d 3 d 4 I: 60000 J: 60000 K: 80000 L: 90000 M: 50000 N: 50000 O: 60000 P: 70000 正しく欲しい結果は８人(F, G, H, L, M, N, O, P) 間違った例だと５人となってしまう(L, M, N, O, P) 32

7 -1 -10 Recursive Queries in SQL 37

7 -1 -10 Recursive Queries in SQL WITH RECURSIVE r AS ( SELECT * FROM tree WHERE id = 2 UNION ALL SELECT tree. * FROM tree, r WHERE tree. parent_id = r. id) SELECT * FROM r; Id = 2の場合 Id = 3の場合 38

7 -2の目次 • 7. 2. 1 Specifying General Constraints as Assertions in SQL • 7. 2. 2 Introduction to Triggers in SQL 42

7 -3の目次 • 7. 3. 1 Concept of a View in SQL • 7. 3. 2 Specification of Views in SQL • 7. 3. 3 View Implementation, View Update, and Inline Views • 7. 3. 4 Views as Authorization Mechanisms 48

7 -4の目次 • 7. 4. 1 The DROP Command • 7. 4. 2 The ALTER Command 59

7 -4 -2 The ALTER Command • ALTERコマンドではテーブルに変更を加えることができる。 ①属性の追加、削除 ALTER TABLE COMPANY. EMPLOYEE ADD COLUMN Job VARCHAR(12); ALTER TABLE COMPANY. EMPLOYEE DROP COLUMN Address CASCADE; CASCADEならこの属性とそれを参照するすべてを消し、 RESTRICTなら参照するものがない場合のみ削除が行われる。 ②属性の初期値の変更 ALTER TABLE COMPANY. DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT; ALTER TABLE COMPANY. DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT ‘ 333445555’; 現状の初期値の定義を消してから自分で設定することになる。 61