Database Systems Spring 2024 Instructor Jianer Chen Notes

CSCE-608 Database Systems Spring 2024 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 16: From Parse Tree to Query Plan

lock table DDL language DDL complier database administrator concurrency control file logging & recovery manager transaction manager index/file manager buffer manager query execution engine database programmer DML (query) language DML complier main memory buffers secondary storage (disks) DBMS

select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

select-statement select-statement ::= SELECT [DISTINCT] select-list FROM table-list [WHERE search-condition] [ORDER BY column-name] FROM SELECT WHERE From From From SELECT-LIST SEARCH-CONDITION TABLE-LIST SELECT-STMT 1. w = SCANNER(); \\ w = SELECT 2. w = SCANNER(); 3. if w = DISTINCT then w = SCANNER(); 4. CALL SELECT-LIST; 5. w = SCANNER(); \\ w = FROM 6. CALL TABLE-LIST; 7. w = SCANNER(); 8. if w = WHERE then CALL SEARCH-CONDITION w = SCANNER(); 9. if w = ORDER then w = SCANNER(); \\ w = BY w = SCANNER(); \\w = column-name <= create a node v for select-statement <= make w a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make the result a child of v <= make w a child of v <= make w a child of v <= make w a child of v

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 1. 2. 3. Break the program P into words (lexical analysis); Use the language grammar to parse the program P; Make a parse tree for the program P.

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security. Convert the input SQL program P into a parse tree

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security. tree Tk for yk, and let the root of Tk be the kth child of x. Convert the input SQL program P into a parse tree Lexical scanner: write a regular expression based on the lexical rules, convert it into a finite state automaton, and implement the automaton. Parse tree construction: for each nonterminal x, write a subroutine that constructs a parse tree with a root labeled with x: Given a syntax rule x ::= y1 y2 yh: for each yk, if yk is a terminal, make yk the kth child of x; if yk is a nonterminal, recursively construct the parse

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security.

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security.

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security. ?

What is an algebra? Roughly, an algebra is a set S of elements plus a collection of operations that on given element(s) in S constructs element in S. Example: S: the set of all positive integers Operations: +,

Relational What is a algebra? Roughly, an algebra is a set S of elements plus a collection of operations that on given element(s) in S constructs element in S.

Relational What is a algebra? Roughly, an algebra is a set S of elements plus a collection of operations that on given element(s) in S constructs element in S. Answer: S: the elements are tables (i.e., relations) Operations: selection , projection , set operations ( , ,\), renaming , joins ( , , C) extended operations ( , , , outer-joins)

Relational operations Core relational operations: union , intersection , difference \ -- usual set operations -- extended to bags selection : picking certain rows projection : picking certain columns products , joins , C Renaming of relations and attributes.

Relational operations extended relational operations: = eliminate duplicates from bags. = sort tuples. = grouping and aggregation. extended projection L : L may contain renaming and expressions.

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security.

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security. Really, an expression tree

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security. parse tree into an expression tree Therefore, this is to convert a

Expressions in relational algebra presented by expression trees In general, operands are children of the operation

Expressions in relational algebra presented by expression trees In general, operands are children of the operation R S

Expressions in relational algebra presented by expression trees In general, operands are children of the operation C C(R) R S R

Expressions in relational algebra presented by expression trees In general, operands are children of the operation C C(R) R S R L L( C(R S) T) C T S R

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; parse tree SetOpt UNION subquery 1 subquery 2 DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; parse tree SetOpt UNION subquery 1 subquery 2 DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; exp. tree for subquery 1 Expression tree exp. tree for subquery 2 INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; parse tree -join JOIN ON parse tree for S parse tree for condition C parse tree for R DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; parse tree -join JOIN ON parse tree for S parse tree for condition C parse tree for R DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; exp. tree for S exp. tree for R expression tree INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; parse tree -join JOIN ON parse tree for S parse tree for condition C parse tree for R DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; or (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; exp. tree for S exp. tree for R expression tree INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; No relational operations are involved. The system records and manages the schemas of relations and other structures DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

General SQL Statements: DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; In principle, 1. Make a cross product of the relations from FROM; 2. Apply the select based on the condition given in WHERE; 3. Apply the projection given in SELECT. DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; In principle, 1. Make a cross product of the relations from FROM; 2. Apply the select based on the condition given in WHERE; 3. Apply the projection given in SELECT.

SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; In principle, 1. Make a cross product of the relations from FROM; 2. Apply the select based on the condition given in WHERE; 3. Apply the projection given in SELECT. select-statement FROM SELECT WHERE table- List T T1 Th select- List L A1 Ak Conditions C