5. SQL - Queries, Programming, Triggers

What are nested queries?

A very powerful feature of SQL: a WHERE clause can itself contain an SQL query! (Actually, so can FROM and HAVING clauses.) Find names of sailors who have reserved boat #103: SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103)

What is correlation in nested queries?

SELECT S.sname FROM Sailors S WHERE NOT EXISTS (( SELECT B.bid FROM Boats B ) EXCEPT (SELECT R. bid FROM Reserves R WHERE R.sid = S.sid )) Note that this query is correlated--for each sailor S, we check to see that the set of boats reserved by S includes every boat.

What are the parts of a basic SQL query?

SELECT [DISTINCT] target-list FROM relation-list WHERE qualification

Explain the differences between triggers and integrity constraints, and describe when you would use triggers over integrity constrains and vice versa. What are triggers used for?

A common use of triggers is to maintain database consistency, and in such cases, we should always consider whether using an integrity constraint (e.g., a foreign key constraint) achieves the same goals. The meaning of a constraint is not defined operationally, unlike the effect of a trigger. This property makes a constraint easier to understand, and also gives the DBMS more opportunities to optimize execution. A constraint also prevents the data from being made inconsistent by any kind of statement, whereas a trigger is activated by a specific kind of statement (INSERT, DELETE, or UPDATE). Again, this restriction makes a constraint easier to understand.

target-list

A list of attributes of relations in relation-list

relation-list

A list of relation names (possibly with a range-variable after each name).

What is a trigger, and what are its three parts? What are the differences between row-level and statement-level triggers?

A trigger is a procedure that is automatically invoked by the DBMS in response to specified changes to the database, and is typically specified by the DBA. A database that has a set of associated triggers is called an active database. A trigger description contains three parts: • Event: A change to the database that activates the trigger. • Condition: A query or test that is run when the trigger is activated. • Action: A procedure that is executed when the trigger is activated and its condition is true.

What is grouping? Is there a counterpart in relational algebra? Explain this feature, and discuss the interaction of the HAVING and WHERE clauses. Mention any restrictions that must be satisfied by the fields that appear in the GROUP BY clause.

As opposed to applying aggregate operators to all qualifying tuples, we can instead apply them to each of several groups of tuples. For example: Find the age of the youngest sailor for each rating level. SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification The target-list contains (i) attribute names (ii) terms with aggregate operations (e.g., MIN (S.age)). The attribute list (i) must be a subset of grouping-list. Intuitively, each answer tuple corresponds to a group, and these attributes must have a single value per group. (A group is a set of tuples that have the same value for all attributes in grouping-list.)

What aggregate operators does SQL support?

COUNT (*) COUNT ( [DISTINCT] A) SUM ( [DISTINCT] A) AVG ( [DISTINCT] A) MAX (A) MIN (A)

qualification

Comparisons (Attr op const or Attr1 op Attr2, where op is one of <, >, =, <=, >=, != ) combined using AND, OR and NOT.

What types of SQL constraints can be specified using the query language?

Constraints over a single table, and constraints over multiple tables.

How can you give names to output columns in a query that are defined by arithmetic or string expressions?

Each item in a select-list can be of the form expression AS column_name, where expression is any arithmetic or string expression over column names (possibly prefixed by range variables) and constants, and column_name is a new name for this column in the output of the query. SELECT S.age, age1=S.age-5, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE 'B_%B'

What are null values?

Field values in a tuple are sometimes unknown (e.g., a rating has not been assigned) or inapplicable (e.g., no spouse's name). SQL provides a special value null for such situations.

Are the input and result tables of an SQL query sets or multisets?

If not using DISTINCT, the result tables are a set, else, multiset.

Why can triggers be hard to understand?

In an active database system, when the DBMS is about to execute a statement that modifies the database, it checks whether some trigger is activated by the statement. If so, the DBMS processes the trigger by evaluating its condition part, and then (if the condition evaluates to true) executing its action part. If a statement activates more than one trigger, the DBMS typically processes all of them, in some arbitrary order. An important point is that the execution of the action part of a trigger could in turn activate another trigger. In particular, the execution of the action part of a trigger could again activate the same trigger; such triggers are called recursive triggers. The potential for such chain activations and the unpredictable order in which a DBMS processes activated triggers can make it difficult to understand the effect of a collection of triggers.

Are null values supported in the relational model

Null values are not part of the basic relational model.

How do null values affect the meaning of queries?

Null values will make queries evaluate to the value unknown.

What are range variables in SQL?

Really needed only if the same relation appears twice in the FROM clause. It is good style, however, to use range variables always! SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103

What support does SQL offer for string pattern matching?

SQL provides support for ___________ through the LIKE operator, along with the use of the wild-card symbols % (which stands for zero or more arbitrary characters) and ~ (which stands for exactly one, arbitrary, character). Thus, '_AB%' denotes a ___________ every string that contains at least three characters, with the second and third characters being A and B respectively.

How can you obtain a set of tuples as the result of a query?

Tables are multisets of tuples.

Can primary key fields of a table contain null values?

The fields in a primary key are not allowed to take on null values.

What operations does SQL provide over (multi)sets of tuples, and how would you use these in writing queries?

UNION, INTERSECT, and EXCEPT

How would you use the operators IN, EXISTS, UNIQUE, ANY, and ALL in writing nested queries? Why are they useful? Illustrate your answer by showing how to write the division operator in SQL.

You would use IN, EXISTS, UNIQUE, ANY, and ALL as set comparison operators in the WHERE clause of a query to nest another query. SELECT S.sid FROM Sailors S WHERE S.rating > ANY ( SELECT FROM WHERE S2.rating Sailors S2 S2.sname = 'Horatio' )

Can you express primary key constraints using a kind of SQL constraint? If so, why does SQL provide for a separate primary key constraint syntax?

https://msdn.microsoft.com/en-us/library/ms187550.aspx#Unique You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

DISTINCT

is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated!