acytoo

Database Advanced SQL

#Accessing SQL From a Programming Language
  • JDBC: Java Database Connectivity
  • ODBC: Open Database Connectivity
  • Embedded SQL ...

Embedded SQL

EXEC-SQL connect to server_addr user user_name using password;
EXEC SQL <embedded SQL statement > END_EXEC

EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount
END_EXEC
#Extensions and stored procedures

SQL provides a module language

  • Permits definition of procedures in SQL, with if-then-else statements, for and while loops, etc.

Stored Procedures:

  • Can store procedures in the database
  • then execute them using the call statement
  • permit external applications to operate on the database without knowing about internal details
#SQL functions

Define a function that, given the name of a department, returns the count of the number of instructors in that department.

Find the department name and budget of all departments with more that 12 instructors.

create function dept_count(dept_name varchar(20))
returns integer
begin
  declare d_count integer;
  select count(*) into d_count
  from instructor
  where instructor.dept_name = dept_name
  return d_count;
end

select dept_name, budget
from department
where dept_count(dept_name) > 1
  • May contain multiple SQL statements between begin and end.
  • returns -- indicates the variable-type that is returned (e.g.,integer)
  • return -- specifies the values that are to be returned as result of invoking the function
  • SQL function are in fact parameterized views that generalize the regular notion of views by allowing parameters.
#Table functions

Example: Return all accounts owned by a given customer

create function instructors_of(dept_name char(20)
returns table (ID varchar(5),
              name varchar(20),
              dept_name varchar(20),
              salary numeric(8,2))
return table (
  select ID, name, dept_name, salary
  from instructor
  where instructor.dept_name = instructors_of.dept_name
)

select *
from table (instructors_of (‘Music’))
#Procedures

The dept_count function could instead be written as procedure:

create procedure dept_count_proc (in dept_name varchar(20), out d_count integer)
begin
  select count(*) into d_count
  from instructor
  where instructor.dept_name = dept_count_proc.dept_name
end

Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement.

declare d_count integer;
call dept_count_proc( ‘Physics’, d_count);

SQL:1999 allows more than one function/procedure of the same name (called name overloading), as long as the number of arguments differ, or at least the types of the arguments differ

#Language Constructs for Procedures & Functions

SQL supports constructs that gives it almost all the power of a general-purpose programming language.

  • Warning: most database systems implement their own variant of the standard syntax below.

  • Compound statement: begin … end,

    • May contain multiple SQL statements between begin and end.
    • Local variables can be declared within a compound statements

  • While and repeat statements:

      while boolean expression do
    sequence of statements ;
  end while

      repeat
    sequence of statements ;
  until boolean expression
  end repeat
#Triggers

A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.

To design a trigger mechanism, we must:

  • Specify the conditions under which the trigger is to be executed.
  • Specify the actions to be taken when the trigger executes.

Triggers introduced to SQL standard in SQL:1999, but supported even earlier using non-standard syntax by most databases.

  • Triggering event can be insert, delete or update
  • Triggers on update can be restricted to specific attributes
    • after update of takes on grade
  • Values of attributes before and after an update can be referenced
    • referencing old row as: for deletes and updates
    • referencing new row as: for inserts and updates
  • Triggers can be activated before an event, which can serve as extra constraints. E.g. convert blank grades to null.
create trigger setnull_trigger before update of takes
referencing new row as nrow
for each row
when (nrow.grade = ' ')
  begin atomic
    set nrow.grade = null;
  end;

create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> 'F' and nrow.grade is not null
and (orow.grade = 'F' or orow.grade is null)
begin atomic
  update student
  set tot_cred= tot_cred +
    (select credits
    from course
    where course.course_id= nrow.course_id)
  where student.id = nrow.id;
end;
#Statement level triggers

Instead of executing a separate action for each affected row, a single action can be executed for all rows affected by a transaction Use for each statement instead of for each row Use referencing old table or referencing new table to refer to temporary tables (called transition tables) containing the affected rows

Can be more efficient when dealing with SQL statements that update a large number of rows

#When Not To Use Triggers

  • Triggers were used earlier for tasks such as

    • maintaining summary data (e.g., total salary of each department)
    • Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica

  • There are better ways of doing these now:

    • Databases today provide built in materialized view facilities to maintain summary data
    • Databases provide built-in support for replication

  • Encapsulation facilities can be used instead of triggers in many cases

    • Define methods to update fields
    • Carry out actions as part of the update methods instead of through a trigger

  • Risk of unintended execution of triggers, for example, when

    • loading data from a backup copy
    • replicating updates at a remote site
    • Trigger execution can be disabled before such actions.

  • Other risks with triggers:

    • Error leading to failure of critical transactions that set off the trigger
    • Cascading execution
#Recursion in SQL

SQL:1999 permits recursive view definition

Example: find which courses are a prerequisite, whether directly or indirectly, for a specific course.

with recursive rec_prereq(course_id, prereq_id) as (
    select course_id, prereq_id
    from prereq
  union
    select rec_prereq.course_id, prereq.prereq_id,
    from rec_rereq, prereq
    where rec_prereq.prereq_id = prereq.course_id
)
select ∗
from rec_prereq;

#Advanced Aggregation Features

#Ranking

Ranking is done in conjunction with an order by specification.

select ID, rank() over (order by GPA desc) as s_rank
from student_grades
order by s_rank

Ranking may leave gaps: e.g. if 2 students have the same top GPA, both have rank 1, and the next rank is 3. dense_rank does not leave gaps, so next dense rank would be 2.

Ranking can be done using basic SQL aggregation, but resultant query is very inefficient.

select ID, (1 + (select count(*)
                from student_grades B
                where B.GPA > A.GPA)) as s_rank
                from student_grades A
order by s_rank;

Ranking can be done within partition of the data.

select ID, dept_name, 
  rank () over (partition by dept_name order by GPA desc) as dept_rank
from dept_grades
order by dept_name, dept_rank;

Multiple rank clauses can occur in a single select clause.

Ranking is done after applying group by clause/aggregation

#Windowing

Used to smooth out random variations.

Example: moving average: “Given sales values for each date, calculate for each date the average of the sales on that day, the previous day, and the next day”

Window specification in SQL:

select date, sum(value) over
(order by date between rows 1 preceding and 1 following)
from sales

See Also: