Title: Theory / Important / CS / Algo / Database / Database System Concept 6 - McGraw Hill+27{ / 1 Introduction / Sql;
Tags: FromInstruction computer_to_instruction hasNotNewEffectReturnsMeaning some_instruction automatedNewLineAfterCommaSpace type declaration todo_declaration fromMath mathToDef effectToValue Copy examplesSwitchExample1 fromAlgorithm hasSeenConjDef has_seen_instruction mathToSee removeBrInInstruction seen_description

Name: Structured Query Language
Page: 57
Chapter: 3
Section: 1
Abbreviation: SQL

Typ: char(n)
Meaning: A fixed-length character string with user-specified length n. The full form, character, can be used instead. Complete the smallest text with spaces
Page: 59
Language: sql
Chapter: 3
Section: 2.1
Context: Sql type
Variables: n: integer

Typ: varchar(n)
Meaning: A variable-length character string with user-specified maximum length n.
Page: 59
Language: sql
Chapter: 3
Section: 2.1
Variables: n: integer

Typ: int
Meaning: Machine dependent integers
Page: 59
Language: sql
Chapter: 3
Section: 2.1

Typ: smallint
Meaning: A machine dependent subset of the integers
Page: 59
Language: sql
Chapter: 3
Section: 2.1

Typ: numeric(p,d)
Meaning: A fixed-point number with p digits (plus a sign), and d of those are to the right of the decimal point.
Page: 59
Language: sql
Chapter: 3
Section: 2.1

Typ: double precision
Meaning: Double precision floating-point numbers with machine-dependent precision.
Page: 59
Language: sql
Chapter: 3
Section: 2.1

Typ: float(n)
Meaning: A floating-point number, with precision of at least n digits.
Page: 59
Language: sql
Chapter: 3
Section: 2.1

Name: Boolean operators for where
Definition: and, or, not
Page: 66
Chapter: 3

Question: Arithmetic operators for where
Answer: <, <=, >, >=, =, <>
Page: 66
Chapter: 3

Question: How to deal with the Cartesian product of a table with itself ?
Answer: Rename one of the table
Page: 75
Chapter: 3

Typ: Real
Meaning: Floating-point numbers with machine-dependent precision.
Page: 59
Language: sql
Chapter: 3
Section: 2.1

Name: Aggregate functions
Definition: Functions that take a collection (a set or multiset) of values as input and return a single value
Page: 84
Chapter: 3
Section: 7
Example: avg min max sum count

Instruction: fuz natural left outer join bar
Page: 117
Effect: Each tuple from query fuz not appearing in the result is added once to the result, filled with null values
Language: sql
Chapter: 4
Section: 1
Variables: fuz, bar relations
Name: Left outer join

Instruction: fuz natural right outer join bar
Page: 117
Effect: Takes the inner joint of fuz and bar.
Each tuple from right query not appearing in the result is added once to the result, filled with null values
Language: sql
Chapter: 4
Section: 1
Variables: fuz and bar tables
Name: The right outer join of foo and bar

Instruction: fuz natural full outer join bar
Page: 117
Effect: Takes the inner joint.
Each tuple from some query not appearing in the result is added once to the result, filled with null values
Language: sql
Chapter: 4
Section: 1
Name: Full outer join

Instruction: all
Page: 80-82
Effect: consider the set operation as multiset
Language: sql
Chapter: 3
Section: 5.1
Where to use it: union, intersection, except
Assuming: Both relations have the same schema

Instruction: create table table_name (
  name_1 type_1,
  ...
  name_n type_n,
  <integrity-constraint_1>,
  ...
  <integrity_constraint_m>
);
Page: 60
Effect: Creating a table table_name, with attributes name_i of type type_i, plus integrity_constraint_i
Language: sql
Chapter: 3
Section: 2.1

Instruction: primary key (
  A_1,
  ...,
  A_n
)
Page: 60
Effect: State that the tuple A_1, ..., A_n is the primary key of the table
Language: sql
Chapter: 3
Section: 2.2

Instruction: not null
Page: 61, 139
Meaning: State that the elements in the last defined columns can not be null
Language: sql
Chapter: 3, 4
Section: 2.2, 4.2
Where to use it: integrity constrant: after the type of a column in a definition of a table

Instruction: insert into tableName values (tuple)
Page: 61
Effect: add tuple in tableName
Language: sql
Chapter: 3
Section: 2.2

Instruction: drop table tableName
Page: 63
Effect: Remove the table tableName and its content
Language: sql
Chapter: 3
Section: 2.2

Instruction: alter table tableName add AttributeName AttributeType
Page: 63
Effect: Add an attribute named AttributeName, of type AttributeType to the table TableName and set AttributeName to null in every row of the relation
Language: sql
Chapter: 3
Section: 2.2

Instruction: alter table tableName drop AttributeName
Page: 63
Effect: Remove the attribute named AttributeName from the table TableName
Language: sql
Chapter: 3
Section: 2.2

Instruction: select distinct attributeNameList from tableNameList where conditions
Page: 64
Language: sql
Chapter: 3
Returns: Every values in tableNameList for the attribute(s) attributeNameList without duplicates which satisfies the conditions

Instruction: select all attributeNameList from tableNameList where conditions
Page: 65
Language: sql
Chapter: 3
Returns: Every values in tableNameList for the attribute(s) attributeNameList with duplicates which satisfies the conditions

Instruction: table.attributeName
Page: 68
Language: sql
Chapter: 3
Returns: The attribute attributeName from table table
Abbreviation: attributeName if there is no ambiguity.

Instruction: natural join
Page: 73
Effect: tell that two attributes from distinct tables with the same name must be considered as a single attribute
Language: sql
Chapter: 3

Instruction: table1 join table2 using (attributeNames)
Page: 74
Effect: Tell that the attributess attributeNames from tables table1 and table2 must be considered as a single attribute
Language: sql
Chapter: 3
Where to use it: from

Instruction: name1 as name2
Page: 75
Effect: name2 represents name1 and should be selected or considered as a table
Language: sql
Chapter: 3
Section: 4.1
Where to use it: select or from

Instruction: %
Page: 76
Effect: match any string
Language: sql
Context: SQL
Chapter: 3
Section: 4.2

Instruction: _
Page: 76
Effect: match any character
Language: sql
Chapter: 3
Section: 4.2

Instruction: ||
Page: 76
Effect: Concatenation of strings
Language: sql
Chapter: 3
Section: 4.2

Instruction: upper/lower
Page: 76
Effect: Transform a string in uper/lower case
Language: sql
Chapter: 3
Section: 4.2
Where to use it: request condition

Instruction: trim
Page: 76
Effect: remove space at end of the string
Language: sql
Context: request condition
Chapter: 3
Section: 4.2

Instruction: string like pattern
Page: 76
Effect: true if string satisfies pattern
Language: sql
Context: request condition
Chapter: 3
Section: 4.2

Instruction: \cX
Page: 77
Effect: char is intepreted literally
Language: sql
Chapter: 3
Section: 4.2
Variables: a special character char

Instruction: *
Page: 77
Effect: Select all attributes
Language: sql
Chapter: 3
Section: 4.3
Where to use it: select clause

Instruction: order by name_1 (desc),
  ...,
  name_n (desc)
Page: 77
Effect: Sort the selected values according by name_1, .., name_n, in lexicographical order, in increasing (decreasing) order
Language: sql
Chapter: 3
Section: 4.4
Variables: name_i be names from select
Where to use it: select clause

Instruction: name between min and max
Page: 78
Effect: State that name's value must lie between min and max included
Language: sql
Chapter: 3
Section: 4.5
Where to use it: conditions in select clause

Instruction: c except c'
Page: 82
Effect: Output the elements of c not belonging to c'
Language: sql
Chapter: 3
Section: 5.3
Variables: c,c' two relations
Assuming: Both relations have the same schema

Instruction: c Union c'
Page: 51, 80
Effect: Output the union of tuples from the two input relations, without duplicate.
Language: sql
Chapter: 2, 3
Section: 6, 5.1
Variables: c,c' two relations
Assuming: Both relations have the same schema

Instruction: c intersect c'
Page: 81
Effect: Output the intersection of tuples from the two input relations, without duplicate.
Language: sql
Chapter: 3
Section: 5.2
Variables: c,c' two relations
Assuming: Both relations have the same schema

Question: arithmetic operation with null operand
Answer: null
Page: 83
Chapter: 3
Section: 9
Context: conditions in select clause

Question: arithmetic comparision with null operand
Answer: unknown
Page: 83
Chapter: 3
Section: 9
Context: conditions in select clause

Instruction: Boolean operations with unknown
Page: 83
Effect: if the output is the same, independently of the Boolean which replaces unknown, then this output, otherwise unknown
Language: sql
Chapter: 3
Section: 9

Instruction: name is (not) null/unkwown
Page: 84
Effect: true if name's interpretation is (not) null/unkwown, otherwise false
Language: sql
Chapter: 3
Section: 9

Instruction: avg ((distinct) name)
Page: 85, 89
Effect: Compute the select of (distinct) name, and then average its non-null value. null on emptyset
Language: sql
Context: select
Chapter: 3
Section: 7.1, 7.3
Assuming: name's type is number
Typ: A relation containing a unique numerical element

Instruction: sum ((distinct) name)
Page: 85, 89
Effect: Compute the select of (distinct) name, and then sum their non-null values.Null on emptyset
Language: sql
Chapter: 3
Section: 7.1, 7.3
Where to use it: select
Assuming: name's type is number
Typ: A relation containing a unique numerical element

Instruction: count ((distinct) name(s))
Page: 85, 89
Language: sql
Chapter: 3
Section: 7.1, 7.3
Returns: the number of (distinct) elements of this table instance. Ignoring null value apart if names is *. 0 on empty set.
Where to use it: select
Typ: A relation containing a unique numerical element

Instruction: max/min (name)
Page: 85, 89
Language: sql
Chapter: 3
Section: 7.1, 7.4
Returns: Compute the select of name, and then returns its greater/least element. Ignoring null value.
Null on empty set.
Where to use it: select
Typ: A relation containing a unique element with the same type than name

Instruction: GROUP BY GROUPING SETS ((name_1, ...,  name_n))
Page: 86, 87
Effect: State that the aggregating functions must be computed on the elements having the same values for T.name_1, ..., and T.name_nAll columns which are not in aggregation nor a name_i are set to null
Language: sql
Context: SELECT ... FROM T
Chapter: 3
Section: 7.2,
  7.2
Abbreviation: group by name_1, ..., name_n
Extra: this is a simplification of the actual group rule

Instruction: having condition
Page: 88
Effect: Similar to where, but applied to the result of the grouping operation
Language: sql
Context: select with grouping
Chapter: 3
Section: 7.3

Instruction: foo in bar
Page: 91
Language: sql
Context: where clause
Chapter: 3
Section: 8.1
Returns: whether table bar contains a line with content foo

Instruction: foo > some bar
Page: 92
Effect: Whether bar is a set containing an element less than foo
Language: sql
Chapter: 3
Section: 8.2
Variables: foo: numerical
bar: a set of numerical value

Instruction: foo > all bar
Page: 92
Effect: Whether foo is greater than each element of bar
Language: sql
Chapter: 3
Section: 8.2
Assuming: foo: numerical
bar: a set of numerical value

Instruction: exists bar
Page: 93
Effect: True if bar is a non empty set
Language: sql
Chapter: 3
Section: 8.3
Assuming: foo: numerical
bar: a set of numerical value

Instruction: not exists bar
Page: 93
Effect: True if bar is an empty set
Language: sql
Chapter: 3
Section: 8.3
Assuming: foo: numerical
bar: a set of numerical value

Name: A correlated subquery
Definition: A subquery that uses a correlation name from an outer query
Page: 93
Chapter: 3
Section: 8.3

Instruction: unique foo
Page: 94, 130
Effect: The boolean stating that foo should not contain two identical lines without null values
Language: sql
Chapter: 3, 4
Section: 8.4, 4.3
Variables: foo a multiset
Where to use it: Table constraint

Name: A correlation name
Definition: The variable introduced as the right part of an «as» statement.
Page: 96
Chapter: 3
Section: 8.5
Name2: table alias
Name3: Correlation variable
Name4: Tuple variable

Instruction: lateral foo
Page: 97
Effect: foo is a select statement, where variables can come from attributes of preceding tables, or subqueries in the from clauses
Language: sql
Chapter: 3
Section: 8.5
Where to use it: from subquery

Instruction: with foo(bar) as zig
Page: 97
Language: sql
Chapter: 3
Section: 8.6
Identifier: foo
Typ: table with type bar
Initialization: value of zig

Name: Scalar subqueries
Definition: A subqueries whose evaluation is a single tuple with a single attribute
Page: 97
Chapter: 3
Section: 8.7

Instruction: delete from foo where bar;
Page: 98
Effect: removes the tuples satisfying the predicates bar from the relation foo
Language: sql
Chapter: 3
Section: 9.2

Instruction: insert into foo bar;
Page: 100
Effect: Evaluates the query bar and add the result to the table foo
Language: sql
Chapter: 3
Section: 9.2
Assuming: bar's type is equal to the type of the table foo

Instruction: update foo set bar=buz where bing;
Page: 103
Effect: Editing the tuples of table foo satisfying bing, changing the value(s) bar to buz (in which variables are interpreted as their old values)
Language: sql
Chapter: 3
Section: 9.3

Instruction: case
when foo_1 then res_1
_ ...
when foo_n then res_n
_ else fiz
end
Page: 103
Effect: Let i the least integer such that foo_i holds. The value is res_i. If no such i exists, the value is fiz
Language: sql
Chapter: 3
Section: 9.3

Instruction: bar join buz on fiz
Page: 114, 115, 199
Language: sql
Chapter: 4
Section: 1.1, 1.3
Returns: Takes the elements of the Cartesian product of bar and buz satisfying fiz
Apply the rules of the outer foojoin to add query if required

Instruction: bar foojoin buz on fiz
Page: 114, 115, 199
Language: sql
Chapter: 4
Section: 1.1, 1.3
Returns: «bar join buz on fiz»
Apply the rules of the outer foojoin

Name: Inner join
Definition: A join operation which does not preserve nonmatched tuples
Page: 117
Chapter: 4
Section: 1.1

Name: A view
Definition: A relation which is the result of a query
Page: 121
Chapter: 4
Section: 2

Instruction: create view v as foo;
Page: 121
Effect: Introduce in the db a view v containing the result of the query foo
Language: sql
Chapter: 4
Section: 2.1

Instruction: create view v(bar_1,...,bar_n) as foo;
Page: 121
Effect: Introduce in the db a view v containing the result of the query foo.
Its elements are called bar_1,...,bar_n
Language: sql
Chapter: 4
Section: 2.2

Question: How are views implemented ?
Answer: DB store the query, and replace the name of the view by the query when the view is called
Page: 123
Chapter: 4
Section: 2.2

Name: Materialized view maintenance
Definition: Keeping a materialized view up-to-date
Page: 124
Chapter: 4
Section: 2.3
Name2: View maintenance

Name: A stale view
Definition: A view which is not up-to-date
Page: 124
Chapter: 4
Section: 2.3

Name: An updatable view
Definition: The from clause has only one database relation.
Page: 125
Chapter: 4
Section: 2.3
Definition2: The select clause contains only attribute names of the relation, and does not have any expressions, aggregates, or distinct specification.
Conjdef: all
Definition3: Any attribute not listed in the select clause can be set to null; that is, it does not have a not null constraint and is not part of a primary key.
Intuition: A view which admits inserts, updates or deletes
Definition4: The query does not have a group by or having clause.
DefType: A view

Instruction: with check option
Page: 126
Effect: Reject insertion into the view of tuples not satisfying the where clause conditions.
Language: sql
Context: End of view creation
Chapter: 4
Section: 2.3

Instruction: begin atomic foo end.
Page: 128
Effect: Either do entirely foo as a single transaction. Or do nothing (start and rollback)
Language: sql
Chapter: 4
Section: 3

Instruction: alter table foo add bar
Page: 129
Effect: Add the constraint bar to the definition of the table foo
Language: sql
Chapter: 4

Instruction: check (foo)
Page: 130
Effect: state that the table must satisfy constraint foo
Language: sql
Chapter: 4
Section: 4.4
Where to use it: table definition/addition

Instruction: foo in bar
Page: 130
Language: sql
Chapter: 4
Section: 4.4
Returns: whether entity foo belong to table bar
Where to use it: constraint/where

Instruction: foreign key foo references bar
Page: 131
Effect: each entry of the current table, in column key must appear as primary key of table bar
Language: sql
Chapter: 4
Section: 4.5
Where to use it: constraint
Assuming: foo and the primary key of bar have the same number of columns

Instruction: references bar
Page: 131
Effect: state that the column references the primary key of table bar
Language: sql
Chapter: 4
Section: 4.5
Where to use it: constraint, after a column definition

Instruction: on delete cascade
Page: 133
Effect: if the foreign key mentionned in the current column of an entity e is deleted, the entity e is also deleted.
Language: sql
Chapter: 4
Section: 4.5
Where to use it: after foreign key reference

Instruction: on update cascade
Page: 133
Effect: if the foreign key mentionned in the current column of an entity e is modified, this column of entity e is modified similarly
Language: sql
Chapter: 4
Section: 4.5
Where to use it: after foreign key reference

Name: Deferrable constraint
Definition: A constraint which the database may wait until the end of the transaction to check
Page: 134
Chapter: 4
Section: 4.6

Instruction: set constraints foo deferred
Page: 134
Effect: state to wait until end of transaction to check constraint foo
Language: sql
Chapter: 4
Section: 4.6
Where to use it: in a transaction

Instruction: create assertion foo check bar;
Page: 134
Effect: Create an assertion in the database, with name foo, ensuring that bar holds
Language: sql
Chapter: 4
Section: 4.7
Where to use it: in a transaction

Page: 134
Implementation: not exists x not foo(x)
Section: 4.7
Chapter: 4
Effect: «for all x. foo(x)»
Language: sql

Instruction: cast foo as date/time/timestamp
Page: 136
Language: sql
Chapter: 4
Section: 5.1
Returns: The date/time represented by the text foo
Variables: foo a stringbar a time/date type

Instruction: extract (year/month/day/hour/minute/second from bar)
Page: 136
Language: sql
Context: SQL
Chapter: 4
Section: 5.1
Returns: The year/month/day/hour/minute/second of the date/time bar
Variables: bar a date/time value

Instruction: current_date
Page: 137
Language: sql
Chapter: 4
Section: 5.1
Returns: The current date

Instruction: current_time
Page: 137
Language: sql
Chapter: 4
Section: 5.1
Returns: The current time and timezone

Instruction: localtime
Page: 137
Language: sql
Chapter: 4
Section: 5.1
Returns: The current time

Instruction: current_timestamp
Page: 137
Language: sql
Chapter: 4
Section: 5.1
Returns: current time stamp with time zone

Instruction: localtimestamp
Page: 137
Language: sql
Chapter: 4
Section: 5.1
Returns: current time stamp without time zone

Instruction: x-y
Page: 137
Language: sql
Chapter: 4
Section: 5.1
Returns: the interval between times x and y
Variables: x,y two time/date

Instruction: default foo
Page: 137
Effect: state that, when a row is added with no information on the current column, then its value will be foo
Language: sql
Chapter: 4
Section: 5.2
Where to use it: sql column

Name: an index on a
Definition: a data structure that allows the database system to find those tuples in the relation that have a specified value for attribute a efficiently,
Page: 137
Chapter: 4
Section: 5.3
Variables: R a table
a an attribute
Applied to3: sql column

Instruction: create index foo on bar(plop);
Page: 138
Effect: Add to the database an index, called foo, on the column plop of table bar
Language: sql
Chapter: 4
Section: 5.3

Name: locator
Definition: An attribute designating a blop/clob, allowing to read it later
Page: 138
Chapter: 4
Section: 5.3
Context: sql

Instruction: alter type foo as bar final;
Page: 139
Effect: redefine the type of name foo as a synonym for bar
Language: sql
Chapter: 4
Section: 5.5

Instruction: create type foo as bar final;
Page: 139, 1132
Effect: Define a type of name foo as a synonym for bar
Language: sql
Chapter: 4
Section: 5.5

Instruction: drop type foo
Page: 139
Effect: remove the name foo from the set of type of this database
Language: sql
Chapter: 4
Section: 5.5

Instruction: create domain foo as bar;
Page: 140
Effect: create a domain foo as similar to type bar with potentially some restriction
Language: sql
Chapter: 4
Section: 5.5

Instruction: create domain foo as bar constraint plop check miam;
Page: 139
Effect: create a domain foo as similar to type bar,
add also a restriction named plop stating that the elements of this domain satisfy miam
Language: sql
Chapter: 4
Section: 5.5

Question: what's the difference between distinct types and domains ?
Answer: distinct types are strongly typed. Domains can be used as the underlying type without casting
Page: 139
Chapter: 4
Section: 5.5
Context: sql

Instruction: create table foo like bar;
Page: 141
Effect: Add to the database a table with name foo and the same columns as table bar
Language: sql
Chapter: 4
Section: 5.6

Instruction: create table foo as bar with data;
Page: 141
Effect: Add to the database a table with name foo with the same content than bar
Language: sql
Chapter: 4
Section: 5.6

Instruction: create table foo as bar;
Page: 141
Effect: Add to the database a table with name foo with the same columns as bar, with or without the data (implementation dependant)
Language: sql
Chapter: 4
Section: 5.6

Question: Difference between «create table ... as foo» and «create view ... as foo»
Answer: table is similar to foo at a fixed time, view is always similar to foo at current time
Page: 142
Chapter: 4
Section: 5.6
Context: sql

Instruction: foo.bar.plop
Page: 142
Language: sql
Context: sql query
Chapter: 4
Section: 5.7
Returns: The column plop of schema bar of catalog foo

Instruction: bar.plop
Page: 142
Language: sql
Context: sql query
Chapter: 4
Section: 5.7
Returns: The column plop of schema bar of current default catalog

Name: authorization identifier
Page: 142
Chapter: 4
Section: 5.7
Context: sql query
Name2: user identifier

Question: How to create and drop catalogs
Answer: it is implementation dependant
Page: 142
Chapter: 4
Section: 5.7
Context: sql query

Name: privilege
Definition: an authorization to a user to perform some kind of actions
Page: 143
Chapter: 4
Section: 6
Context: database
Example: select, insert, update, and delete

Question: Default permission when creating a relation
Answer: user creating has all permission
Page: 144
Chapter: 4
Section: 6.1
Context: database

Instruction: revoke foo on bar to plop_1,...,plop_n;
Page: 145
Language: sql
Context: database
Chapter: 4
Section: 6.1
Returns: remove from the user plop_1,...,plop to do foo on the schema bar

Instruction: grant foo on bar to plop_1,...,plop_n;
Page: 144
Language: sql
Context: database
Chapter: 4
Section: 6.1
Returns: Allow the user/roles plop_1,...,plop to do foo on the schema bar

Instruction: select
Page: 144
Language: sql
Context: database privilege
Chapter: 4
Section: 6.1
Returns: privilege to read tuples in the relation

Instruction: update(
  foo_1,...,
  foo_n
)
Page: 144
Language: sql
Context: database privilege
Chapter: 4
Section: 6.1
Returns: privilege to update columns foo_i of tuples in the relation

Instruction: delete
Page: 144
Language: sql
Context: database privilege
Chapter: 4
Section: 6.1
Returns: privilege to delete tuples from the relation

Instruction: insert
Page: 144
Language: sql
Context: database privilege
Chapter: 4
Section: 6.1
Returns: privilege to insert tuples from the relation

Instruction: public
Page: 144
Effect: all current and futur users
Language: sql
Context: database privilege
Chapter: 4
Section: 6.1
Typ: role

Instruction: execute
Page: 147
Language: sql
Context: sql privilege
Chapter: 4
Section: 6
Returns: right to execute a function/procedure

Instruction: references(
  foo_1,
  ...,
  foo_n
)
Page: 147
Effect: add the right to a user to reference the columns foo_i
Language: sql
Context: privilege
Chapter: 4
Section: 6.4

Question: Why should there be a privilege to add reference to key
Answer: because the table referencing a key may forbid a tuple to be deleted. Thus a reference may limit the edition of a schema
Page: 148
Chapter: 4
Section: 6.4
Context: sql privilege

Instruction: with grant option
Page: 148
Effect: allow the person to who the privilege is granted to also grant it to other users
Language: sql
Context: sql privilege
Chapter: 4
Section: 6.5
Where to use it: after granting a privilege

Instruction: restrict
Page: 148
Effect: if the restriction cascade, then raise an error
Language: sql
Context: sql privilege
Chapter: 4
Section: 6.5
Where to use it: after privilege revokation

Instruction: cascade
Page: 148
Effect: state that the privilege revokation should cascade
Language: sql
Context: sql privilege
Chapter: 4
Section: 6.5
Where to use it: after privilege revokation

Instruction: set role foo;
Page: 150
Effect: Continue the execution as if executed from an arbitrary foo instead of the specific current user
Language: sql
Chapter: 4
Section: 6.5
Assuming: current user has role foo
Intuition: Ensure that if current users loses role foo, the role they granted still persists as those roles are not granted by a specific user but by the role he plays.

Instruction: revoke grant option for ..
Page: 148
Effect: revoke only the fact that a user can grant permission, and not the permission itself
Language: sql
Context: sql privilege
Chapter: 4
Section: 6.5

Instruction: granted by foo
Page: 148
Effect: state that the permission is given by role foo and not by the user itself
Language: sql
Context: privilege
Chapter: 4
Section: 6.5
Where to use it: after privilege

Instruction: ?
Page: 162
Language: sql
Context: query
Chapter: 5
Section: 1.1.4
Returns: A parameter to be replaced later by a particular value

Question: Why to use ? in queries
Answer: Queries can be compiled and then only the part depending of the parameter will change
Page: 162
Chapter: 5
Section: 1.1.4
Context: sql
Answer2: it ensure that the content of the variable is not considered as a keyword

Instruction: create function function_name(
  arg_1 type_1,
  ...,
  arg_n,
  type_n
)
  returns return_type
  begin
    declare var_name type;
    select value into var_name
    ...
    ;
  return var_name;
  end
Page: 174
Meaning: generate a function function_name with arguments arg_i of type type_i; which return var_name
Language: sql
Chapter: 5
Section: 2.1

Instruction: create procedure function_name(
  in arg_1 type_1,
  ...,
  in arg_n,
  type_n,
  out var_name return_type
)
  begin
    select value into var_name
    ...
  end
Page: 175
Meaning: generate a function function_name with arguments arg_i of type type_i; setting value var_name of type return_type
Language: sql
Chapter: 5
Section: 2.2

Name: online analytic processing
Definition: an interactive system that permits an analyst to view different summaries of multidimensional data
Page: https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html
Context: database
Abbreviation: OLAP

Instruction: declare var_name type;
Page: 175
Meaning: Introduce a variable var_name of type type
Language: sql
Chapter: 5
Section: 2.1

Instruction: declare var_name type default value;
Page: 176
Meaning: declare a variable var_name of type type with initial value value
Chapter: 5
Section: 2.1

Instruction: set var = value
Page: 176
Effect: Change the value assigned to var to value
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: while expr do
  body
end while
Page: 176
Effect: if expr then execute body; and do this again
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: repeat
  body
until expr
end repeat
Page: 176
Effect: execute body.
If expr then execute this instruction again
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: for var as
  selection
do
  body
end for
Page: 176
Effect: Execute body with var iterating over the rows of selection
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: leave
Page: 176
Effect: quit the innermost loop (similar to break in most language)
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: iterate
Page: 176
Effect: restart the execution of the innermost loop (similar to continue in most language)
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: if expr_1
  then case_1
elseif expr_2
  then case_2
...
else case_else
end if
Page: 176
Effect: For i the first expr_i which holds, then execute case_i. Otherwise execute case_else
Language: sql
Context: procedure/function
Chapter: 5
Section: 2.1

Instruction: return(foo)
Page: 176
Effect: Halt the function and return foo
Language: sql
Context: function
Chapter: 5
Section: 2.1

Instruction: select foo into var from_expression;
Page: 176
Effect: same as
select foo from_expression,
and put the result into var
Language: sql
Context: function
Chapter: 5
Section: 2.1
Assuming: from_expression evaluate to a single line

Instruction: todo
Page: 176
Effect: exception
Language: sql
Context: function
Chapter: 5
Section: 2.1

Instruction: todo
Page: 176
Effect: external language routine (depends on the actual database)
Language: sql
Context: function
Chapter: 5
Section: 2.1

Name: trigger
Definition: a condition
Page: 176
Chapter: 5
Section: 3
Context: sql
Definition2: an action
Example: add the item to the table of item to order again
Conjdef: A pair with
Example2: increase the sum the account owners own by the same amount (a material vue should be used if possible)
Applied to: If the inventory number of an item is below a threshold
Counterexample: when a key is deleted, delete the element referencing it. Instead use "on delete cascade"
Applied to2: A credit is added to an account

Name: A transition variable/table
Definition: A variable referencing a row/table being currently processed
Page: 181
Chapter: 5
Section: 3.2
Context: sql

Instruction: create trigger trigger_name after/before effect on table
references
for each row/transaction
when (condition)
begin
  action
end;
Page: 182
Effect: Create a trigger named trigger_name, which get executed after/before effect occurs on table. For each row/transaction satisfying condition (with old and new row named as in referneces), action is done
Context: sql
Chapter: 5
Section: 3.2

Instruction: new row/table as n
Page: 182
Effect: state that the new value of the modified row/table is n
Language: sql
Context: trigger
Chapter: 5
Section: 3.2

Instruction: old row/table as o
Page: 182
Effect: state that the previous value of the modified row/table is nrow
Language: sql
Context: trigger
Chapter: 5
Section: 3.2

Instruction: update of column on table
Page: 182
Effect: trigger execute only if table.column is modified
Language: sql
Context: trigger condition
Chapter: 5
Section: 3.2

Instruction: alter trigger trigger_name en/disable
Page: 185
Effect: en/disable trigger named trigger_name
Language: sql
Context: trigger
Chapter: 5
Section: 3.2

Instruction: drop trigger trigger_name.
Page: 185
Effect: delete trigger named trigger_name
Language: sql
Context: trigger
Chapter: 5
Section: 3.2

Question: When not to use trigger
Answer: If maintained view or "ON action cascade/abord..." would be better
Page: 186
Chapter: 5
Section: 3.3
Context: trigger
Answer2: during backup/duplication, as the trigger was already executed the first time

Name: The transitive closure of R
Definition: The relation that contains \((a_0, a_n)\) if there exists \(a_1,\dots, a_{n-1}\) such that \(R(a_0,a_1),\dots, R(a_{n-1}, a_n)\)
Page: 188
Chapter: 5
Section: 3.3
Variables: \(R(a, b)\) a binary relation
Context: trigger
Example: is_an_ancestor_of
Example2: is a prerequisite of
Applied to: is_a_parent_of
Applied to2: is a direct prerequisite of

Instruction: create temporary table table_creation
Page: 188
Effect: Similar to
create table table_creation
but the table is deleted at the end of the transaction
Context: trigger
Chapter: 5
Section: 3.3

Name: A base query
Definition: A query to start creating a table for a recursive definition
Page: 190
Chapter: 5
Section: 3.3
Context: sql recursion

Name: A recursive query
Definition: A query to compute the next step of a recursion
Page: 190
Chapter: 5
Section: 3.3
Context: sql recursion

Instruction: with recursive table_name(table_type) as (query)
Page: 190
Meaning: introduce a temporary table table_name, initially empty, and execute query as long as it change table table_name
Language: sql
Chapter: 5
Section: 3.3
Name: A recursive query
Assuming: query is monotonic

Name: A fixed point of query
Definition: A table t such that query(t) do not change t
Page: 190
Chapter: 5
Section: 3.3
Variables: query a query taking the same type as input and output

Name: A monotonic query
Definition: A query such that the output is a superset or equal to the input
Page: 190
Chapter: 5
Section: 3.3

Question: Example of non recursive query
Answer: Aggregation on the recursive view
Page: 192
Chapter: 5
Section: 4
Context: SQL
Answer2: Not exists on a subquery that uses the recursive view
Answer3: set difference whose right-hand side uses the recrusive view

Instruction: rank() over (order_rule)
Page: 193
Language: sql
Context: select
Chapter: 5
Section: 5.1
Returns: the rank of the current row according to order_rule. (1 + the number of element below/above. So in case of equality, all have the same rank, as low as possible)

Instruction: order by
  column_1 (asc|desc),
  ... column_n (
  asc|desc
)
Page: 194
Effect: The order is done according to column_1 first, then column_2, ..., then column_n, ascending or descending as indicated in each case
Language: sql
Context: order rule
Chapter: 5

Instruction: partition by column order_rule
Page: 194
Effect: restrict the rank to rows with equal column value,
Language: sql
Context: order rule
Chapter: 5

Instruction: limit n
Page: 194
Effect: only return the n first element
Language: sql
Context: end of the query
Chapter: 5

Instruction: percent_rank() over (order_rule)
Page: 194
Language: sql
Chapter: 5
Returns: The proportion of other tuples that are above current row (i.e. if there are r tuples above and n tuples in total, then \(\frac{r-1}{n-1}\))

Instruction: cume_dist() over (order_rule)
Page: 194
Language: sql
Chapter: 5
Returns: If there are p tuples above or equal to current row and n tuples in total, then \(\frac{p}{n}\)
Name: cumulative distribution of this rowe

Instruction: row_number() over (order_rule)
Page: 194
Language: sql
Chapter: 5
Returns: The position of this row according to order_rule. If there are multiple row at equality, each has a unique position

Instruction: ntile(n) over (order_rule)
Page: 194
Language: sql
Chapter: 5
Returns: The \(n\)-tile in which the current row belong according to order_rule

Instruction: nulls firsts/last
Page: 194
Effect: Ensure that null values are at the beginning/end of the sort
Language: sql
Context: end of order rule
Chapter: 5

Name: A window
Definition: A time range of some fixed duration
Page: 195
Chapter: 5
Context: sql analysis

Instruction: order by column rows n preceding
Page: 195
Effect: similar to "group by n",  and the aggregation function of row r also consider the value of \(n-1\) preceding row
Language: sql
Context: order_rule
Chapter: 5

Instruction: order by column rows unbounded preceding
Page: 195
Effect: similar to "group by n",  and the aggregation function of row r also consider the value of preceding rows
Language: sql
Context: order_rule
Chapter: 5
Intuition: cumulative aggregation

Instruction: order by column rows n following
Page: 196
Effect: similar to "group by n",  and the aggregation function of row r also consider the value of \(n\) following rows
Language: sql
Context: order_rule
Chapter: 5
Section: 5.6

Instruction: order by column rows between p preceding and f following
Page: 196
Effect: similar to "group by n",  and the aggregation function of row r also consider the value of the \(p-1\) preceding rows and the \(f\) following rows
Language: sql
Context: order_rule
Chapter: 5
Section: 5.6

Instruction: order by column range between m(column) and M(column)
Page: 196
Effect: similar to "group by n",  and the aggregation function of row r also consider the rows whose column's value are between m(r.column) and M(r.column)
Language: sql
Context: order_rule
Chapter: 5
Section: 5.6

Instruction: grouping(column_name)
Page: 208
Context: database
Chapter: 5
Section: 6.3
Returns: in row r, 1 if r.column_name is a null created by a rollup or cube

Instruction: decode(
  expr,
  match_1,
  replacement_1,
  ...
  match_n,
  replacement_n,
  default_replacement
)
Page: 208
Language: sql
Context: database
Chapter: 5
Section: 6.3
Returns: if expr match against match_i, then replacement_i, else default_replacement

Instruction: CUBE ()
Page: 208
Effect: GROUPING SETS (())
Language: sql
Context: GROUPING SET
Chapter: 5
Section: 6.3

Instruction: CUBE (
  column_1,
  ...,
  column_n
)
Page: 208
Effect: GROUPING SETS ((column_1), ()), CUBE (column_2, ..., column_n)
Language: sql
Context: GROUPING SET
Chapter: 5
Section: 6.3

Instruction: ROLLUP(
  column_1,
  ...,
  column_n
)
Page: 208, https://www.postgresql.org/docs/10/queries-table-expressions.html
Effect: GROUPING SETS (
  (column_1, ..., column_{n-1}, column_n), (column_1, ..., column_{n-1}), ...
  (column_1), ()
)
Language: sql
Context: group by
Chapter: 5
Section: 6.3

Instruction: group_set_1, ..., group_set_n
Page: 208
Language: sql
Context: group by
Chapter: 5
Section: 6.3
Returns: The group_set obtained by cartesian product of all group_set_i

Instruction: {column_list_1, ..., column_list_n}
Page: 208
Language: sql
Context: group by
Chapter: 5
Section: 6.3
Returns: union of the same query with group by restricted to {column_list_i}, for each i

Question: what is the risk with natural join
Answer: If a table schema change, the meaning of the query change and it can't be detected by searching for occurrence of the column in queries
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html

Instruction: T1 CROSS JOIN T2
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
Effect: T1 INNER JOIN T2 ON TRUE
Language: sql
Notation: \(T1\times T2\)

Instruction: T_LEFT foo JOIN T_RIGHT USING (
  column_1,
  ...,
  colmn_n
)
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
Effect: T_LEFT foo JOIN T_RIGHT ON T_LEFT.column_1 = T_RIGHT.column_1 and ... and T_LEFT.column_n and T_RIGHT.column_n
Language: sql
Typ: columns in T_LEFT and T_RIGHT in T_LEFT order. Then columns from T_LEFT not in T_RIGHT. Then Columns from T_RIGHT not in T_LEFT

Instruction: T1 NATURAL foo JOIN T2
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html ,
51, 229
Effect: T1 foo JOIN T2 USING (columns_name_appearing_in_T1_and_T2)
Language: sql
Chapter: 2, 6
Section: 6, 1.3.2
Name: natural join
Notation: \(T1\Join T2\)

Instruction: T_LEFT INNER JOIN T_RIGHT ON θ
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
232
Language: sql
Chapter: 6
Section: 1.3.2
Returns: SELECT * FROM T1, T2 WHERE θ, \(\sigma_\theta (T\_LEFT\times T\_RIGHT)\)
Abbreviation: T_LEFT JOIN T_RIGHT ON θ
Name: θ join
Notation: \(T\_LEFT\Join_{\theta}T\_RIGHT\)

Instruction: T_LEFT LEFT OUTER JOIN T_RIGHT ON θ
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
233
Language: sql
Chapter: 6
Section: 1.3.2
Returns: (SELECT * FROM T_LEFT, T_RIGHT WHERE θ)
UNION
(SELECT T_LEFT_columns, NULL, ..., NULL WHERE NOT EXISTS (SELECT * from T_RIGHT WHERE θ))
Abbreviation: T_LEFT LEFT JOIN T_RIGHT ON θ
Name: left outer join
Intuition: The union of the inner join on θ and all elements of T_LEFT not appearing in this inner join
Notation: [$]T\_LEFT{\tiny \textifsym{d|><|}}_\theta T\_RIGHT[/$]

Instruction: T_LEFT RIGHT OUTER JOIN T_RIGHT ON θ
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
233
Language: sql
Chapter: 6
Section: 1.3.2
Returns: (SELECT * FROM T_LEFT, T_RIGHT WHERE θ)
UNION
(SELECT NULL, ..., NULL, T_RIGHT_columns WHERE NOT EXISTS (SELECT * from T_LEFT WHERE θ))
Abbreviation: T_LEFT RIGHT JOIN T_RIGHT ON θ
Name: Right Outer join
Intuition: The union of the inner join on θ and all elements of T_RIGHT not appearing in this inner join
Notation: [$]T\_LEFT{\tiny \textifsym{|><|d}}_\theta T\_RIGHT[/$]

Instruction: T_LEFT FULL OUTER JOIN T_RIGHT ON θ
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
234
Language: sql
Chapter: 6
Section: 1.3.2
Returns: (SELECT * FROM T_LEFT, T_RIGHT WHERE θ)
UNION
(SELECT NULL, ..., NULL, T_RIGHT.* WHERE NOT EXISTS (SELECT * from T_LEFT WHERE θ))
UNION
(SELECT T_LEFT.*, NULL, ..., NULL WHERE NOT EXISTS (SELECT * from T_RIGHT WHERE θ))
Abbreviation: T_LEFT FULL JOIN T_RIGHT ON θ
Name: Outer join
Intuition: The result of inner join. Plus, all entries that are not in this result as added as extra, padded with nulls
Notation: [$]T\_LEFT{\tiny \textifsym{d|><|d}}_\theta T\_RIGHT[/$]

Instruction: GROUPING SETS (
  LIST_1,
  ...,
  LIST_N
),
GROUPING SETS (
  LIST'_1,
  ...,
  LIST'_M
)
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
Language: sql
Context: GROUP BY
Returns: GROUPING SETS (  LIST_1 :: LIST'_1, ..., LIST_1 :: LIST'_M, ..., LIST_N :: LIST'_1, ..., LIST_N :: LIST'_M)

Instruction: SELECT foo GROUP BY GROUPING SETS (
  expr_1,
  ...,
  expr_n
)
Page: https://www.postgresql.org/docs/10/queries-table-expressions.html
Language: sql
Context: GROUP BY
Returns: SELECT foo GROUP BY expr_1
UNION
...
UNION
SELECT foo GROUP BY expr_n

Question: Basic types of SQL
Answer: char
Page: 59
Chapter: 3
Section: 2.1
Answer2: varchar
Answer3: int
Answer4: smallint
Answer5: numeric
Answer6: real
Answer7: float