Advanced SQL

Question 1

Advanced SQL Topics

Answer 1

• Views
  
  • Using Views
  • Views of Views
  • View Expansion
  • Materialized Views
  • Updating a View
• Transactions
• Constraints
  
  • Check Clause
  • Assertions
• Advanced Operations
  
  • Create Index
  • Create Functions
  • Triggers

Question 2

Views:

Overview

Answer 2

Views allow the construction of a custom relation

• It will remain up to date with the underlying data
• Views are not precomputed or stored
• Instead, the query expression is stored and executed whenever the view is needed
• A view can be used as if it were a normal relation

Question 3

Views:

Common Uses

Answer 3

• Limit what parts of a database a user can interact with
• Present data with simplified schema

Question 4

Creating a View

Code Example

Answer 4

CREATE VIEW myView AS <query></query>

or

CREATE VIEW myView AS

SELECT titles, name, etc

FROM some_table

Question 5

Views of Views

Answer 5

One view may be used in the expression defining another view.

Views may also be recursive and depend on themselves.

• A view v1 is said to depend directly on a view v2
  
  • if v2 is used in the expression defining v1
• A view v1 is said to depend on view v2 if
  
  • v1 depends directly on v2 OR
  • there is a path of dependencies from v1 to v2

Question 6

View Expansion

Answer 6

The meaning of views can be defined in terms of other views

For Example:

• Let view v1 be defined by an expression e1
  
  • e1 may itself contain uses of view relations
• View expansion of an expression repeats:

While there are no more view relations in e1:

Find any view relation vi in e1

Replace the view relation vi with expression defining vi

Question 7

Materialized Views:

Definition and overview

Answer 7

Views that are physically stored

• Some database systems allow this
  
  • MySQL requires a lot of manual work to do this
• Physical copy is created when the view is defined
• If relations used in the query are updated, the materialized view result becomes out of date
  
  • Need to maintain the view by updating it whenever underlying relations are updated

Question 8

Updating a View

Answer 8

• Can insert new values into a view
  
  • These updates can be cascaded to update the underlying relations(the “real” data
• Can present some problems:
  
  • What if data in view is missing an attribute that the underlying relation requires
    
    • Can insert a null, or reject
  • Not always possible
    
    • May not be able to determine which “real” tuple to update

Question 9

Updates in SQL Views

Answer 9

• Most SQL implementations allow updates on simple views,
• where:
  
  • ​The FROM clause has only one relation
  • SELECT clause contains only attribute names of the relation
    
    • No expressions
    • No aggs
    • No “distinct”s
  • Any attribute not listed in the SELECT clause can be set to null
  • Query does not have a “group by” or “having” clause

Question 10

SQL Transactions

Answer 10

A Transaction consists of a sequence of query and/or update statements.

Can be thought of as a “unit” of work.

• SQL standard specifies that a transaction begins implicitly when a SQL statement is executed
• Transaction must end in either a “commit” or “rollback” option
• They are atomic and isolated

Question 11

Transactions:

Constraints:

Common Constraints put on relations

Answer 11

• not null
• primary keys
• unique
• check

Question 12

Transactions:

Constraints:

What do we have to ensure on queries?

Answer 12

Because each query might be multiple steps,

we have to ensure that

all of the constraints are satisfied

before we commit.

If all constraints are NOT satisfied, must rollback.

Question 13

Constraints:

Check Clause

Basic idea and example

Answer 13

The check(P) clause specifies

a predicate P that must be satisfied

by every tuple in a relation.

Added on table creation.

Example:

create table characters(

charid int,

status varchar(30),

primary key (charid),

check (status in (‘Alive’, ‘Dead’) )

)

Question 14

Constraints:

Assertions

Answer 14

An Assertion is a predicate that expresses a condition

which the database must always satisfy

• For example, check that all student GPAs are greater than 0
• Or an instructor cannot teach two different classrooms in a semester on the same time slot

Form:

CREATE ASSERTION <name> CHECK <predicate></predicate></name>

Question 15

Advanced SQL Operations:

Create an Index

Answer 15

CREATE INDEX <myindex> ON relation(attribute)</myindex>

• Remember, MySQL has InnoDB as the default(most of the time) and it uses B+ trees
• MySQL can use Hash Indexes as well, but it requires special setups

Question 16

Advanced SQL Operations:

Creating Functions

Answer 16

• Different databases use different syntaxes
• But it is a SQL standard to have functions
• Another option is to use an external language, such as python, to handle the functions for you.

Question 17

Triggers

Answer 17

A Trigger is a statement that is executed automatically

by the system as a side effect of a modification to the database

• Specify the conditions that cause the trigger to be executed
• • Specify the actions to be taken when executed

Question 18

Executing Triggers

Answer 18

• Can execute triggers for each row
  
  • ​Alternatively, can execute triggers for all affected rows
• Can be more efficient when dealing with SQL statements that update many tuples
• Consider when it is appropriate to use triggers:
  
  • Maintaining data summaries
  • Replicating databases by recording changes
• Using views might be a better option in some cases
• Must be careful not to trigger unintentionally.
  
  • Example: Loading backup data

Question 19

Other Interesting Topics in SQL

Answer 19

Ranking in SQL

• Can rank using some aggregate functions
• Allows us to add a ranked value to some aggregate result

Windowing

• Can specify a “window” to apply different aggregate functions over, such as average

Permissions

• Many DBMSs have a permissions systems
• Allows us to restrict access to various tables and views