advanced sql n.
Skip this Video
Loading SlideShow in 5 Seconds..
Advanced SQL PowerPoint Presentation
Download Presentation
Advanced SQL

Loading in 2 Seconds...

play fullscreen
1 / 62

Advanced SQL - PowerPoint PPT Presentation

  • Uploaded on

Advanced SQL. Data Definition Language Domains Integrity Constraints Assertions Triggers Stored Procedures Embedded & Dynamic SQL ODBC & JDBC. Data Definition Language (DDL). DDL allows the specification of a set of relations, i.e., tables. For each table a DDL statement specifies:

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Advanced SQL' - signa

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
advanced sql
Advanced SQL
  • Data Definition Language
  • Domains
  • Integrity Constraints
  • Assertions
  • Triggers
  • Stored Procedures
  • Embedded & Dynamic SQL
data definition language ddl
Data Definition Language (DDL)
  • DDL allows the specification of a set of relations, i.e., tables.
  • For each table a DDL statement specifies:
    • A name for the table
    • A name for each attribute
    • The domain (i.e., a type) of values associated with each attribute
    • Integrity constraints
    • An associated set of indices
    • Security and authorization information
    • The physical storage structure for the relation
domain types in sql
Domain Types in SQL
  • Standard SQL Types:
    • varchar(n) - Variable length character string, maximum length n.
    • char(n) - Fixed length character string, with length n.
    • int - Integer (machine-dependent).
    • smallint - Small integer (machine-dependent).
    • real - Floating point numbers machine-dependent precision.
    • double precision - Floating point numbers machine-dependent precision.
    • float(n) - Floating point number, precision of at least n digits.
    • numeric(p,d) - Fixed point number; p digits of precision and d digits to the right of decimal point.
date time types in sql cont
Date/Time Types in SQL (Cont.)
  • More complex types are also supported:
    • date - Dates, containing a year, month and date
    • time - Time of day, in hours, minutes and seconds
    • timestamp - Date plus time of day
    • interval - Period of time
    • text, BLOB, CLOB, image, geometry, etc.
  • Operations on complex types: (typical)
    • Subtracting a date/time/timestamp value from another gives a value of type “interval” (or rather “time interval”)
    • Interval values can be added to date/time/timestamp values
    • Values of individual fields can be extracted from date/time/timestamp:

extract (year from student.birth-date)

    • String types can typically be cast to date/time/timestamp:

cast <string-valued-expression> as date

domain types in sql1
Domain Types in SQL
  • Domains can be named:

create domain person-name varchar(32) not null;

  • Most DBMSs provide support for user defined class types (SQL3 and later versions).
create table construct
Create Table Construct
  • An table is defined using the create table command:

create table r (A1D1, A2D2, ..., An Dn,(integrity-constraint1), ..., (integrity-constraintk))

    • r is the name of the table
    • Ai is an attribute name
    • Di is a data type
  • Example:

create table branch (branch-name varchar(16),branch-city varchar(32),assets integer)

integrity constraints in create table
Integrity Constraints in Create Table
  • Integrity constraints:
    • not null
    • primary key (A1, ..., An) - - Also enforces not null
    • check (P), where P is a predicate
  • Example:

create table branch (branch-name varchar(16),branch-city varchar(32) not null,assets integer,primary key (branch-name),

check(assets >= 0))

referential integrity in sql
Referential Integrity in SQL
  • Keys can be specified as part of a create table statement:
    • primary key - attributes in the primary key.
    • unique key – attributes in a (non-primary) candidate key.
    • foreign key - attributes in a foreign key and the name of the relation referenced by the foreign key.
  • A foreign key references the primary key of the referenced table:

foreign key (account-number) references account

  • Reference columns can be explicitly specified:

foreign key (account-number) references account(account-number)\

  • Foreign key references have several implications for insertions, deletions and modifications…
ddl files
DDL Files
  • A DDL file typically contains a collection of:
    • create table statements
    • create index statements
    • statements that create and/or specify other things:
      • Security and authority information
      • Physical storage details
  • A DDL file can be coded by hand, or generated by a schema design or modeling tool.
referential integrity in sql example
Referential Integrity in SQL – Example

create table customer(customer-name varchar(32),customer-street varchar(32),customer-city varchar(16),primary key (customer-name))

create table branch(branch-name varchar(16),branch-city varchar(16),assets integer,primary key(branch-name))

referential integrity in sql example cont
Referential Integrity in SQL – Example (Cont.)

create table account(account-number char(10),branch-name varchar(16),balance integer,primary key (account-number), foreign key (branch-name) references branch)

create table depositor(customer-name varchar(32),account-number char(10),primary key (customer-name, account-number),foreign key (account-number) references account,foreign key (customer-name) references customer)

  • Similarly for loan and borrower.
cascading actions in sql
Cascading Actions in SQL
  • A foreign key reference can be enhanced to prevent insertion, deletion, and update errors.

create table account (

. . .foreign key(branch-name) references branchon delete cascade on update cascade. . . )

  • If a delete of a tuple in branchresults in a referential-integrity constraint violation, the delete “cascades” to the account relation.
  • Cascading updates are similar.
cascading actions in sql cont
Cascading Actions in SQL (Cont.)
  • If there is a chain of foreign-keys across multiple relations, with on delete cascade specified for each, a deletion or update can propagate across the entire chain.
  • Alternative to cascading:
    • on delete set null
    • on delete set default
  • Null values in foreign key attributes complicate SQL referential integrity semantics, and are best prevented using not null.
drop and alter table constructs
Drop and Alter Table Constructs
  • drop table - deletes all information about a table.

drop table customer

  • alter table - used to add or delete attributes to an existing relation.

alter table r add A D // Attribute A and domain D

alter table r drop A // Attribute A

  • More generally, the alter table command can be used to modify an existing table in many ways, such as adding indexes, changing permissions, storage properties, etc.
  • An assertionis a predicate expressing a condition that we wish the database always to satisfy.
  • Assertions are similar to DDL check constraints, but more general in that they can test conditions that apply across mutliple tables.
  • An assertion in SQL takes the form:

create assertion <assertion-name> check <predicate>

  • When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion.
assertion example
Assertion Example

“The sum of all loan amounts for each branch must be no greater than the sum of all account balances at the branch.”

create assertion sum-constraint check (not exists (select * from branchwhere (select sum(amount) from loanwhere loan.branch-name = branch.branch-name) > (select sum(balance) from accountwhere account.branch-name = branch.branch-name)))

assertion example1
Assertion Example

“Every loan has at least one borrower who maintains an account with a minimum balance of $1000.00”

create assertion balance-constraint check (not exists ( select from loanwhere not exists ( select borrower.customer-name from borrower, depositor, accountwhere = borrower.customer-name = depositor.customer-nameand depositor.account-number = account.account-numberand account.balance >= 1000)))

  • 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.
trigger example
Trigger Example
  • Suppose the bank deals with overdrafts by:
    • Setting the account balance to zero
    • Creating a loan in the amount of the overdraft
  • The condition for executing the trigger is an update to the account relation that results in a negative balance value.
  • The actions to be taken by the trigger are to:
    • Create a loan tuple
    • Create a borrower tuple
    • Set the account balance to 0
trigger example in sql 1999
Trigger Example in SQL:1999

create trigger overdraft-trigger after update on account referencing new row as nrow for each rowwhen nrow.balance < 0begin atomicinsert into loan values (nrow.account-number, nrow.branch-name, – nrow.balance);

insert into borrower (select depositor.customer-name, depositor.account-number from depositor where nrow.account-number = depositor.account-number);

update account set balance = 0 where account.account-number = nrow.account-numberend

triggering events and actions in sql
Triggering event can be insert, delete or update.

Triggers on update can be restricted to specific attributes:

create trigger overdraft-trigger after update of balance on account

Values of attributes before and after an update can be referenced

referencing old row as (deletes and updates)

referencing new row as (inserts and updates)

Triggers can be activated before an event, which can serve as extra constraints.

Triggering Events and Actions in SQL
when not to use triggers
When Not To Use Triggers
  • Triggers, along with all the other integrity checking mechanisms, provide yet another opportunity to…slow up the database…
  • Triggers have been used for tasks such as:
    • Maintaining summary or derived data (e.g. total salary of each department).
    • Replicating databases.
  • There are better, more efficient ways to do many of these things:
    • DBMSs typically provide materialized view facilities to maintain summary data.
    • Data warehousing software can be used for maintaining summary/derived data.
    • DBMSs provide built-in support for replication.
procedural extensions and stored procedures
SQL provides a module language that permits definition of procedures:

Conditional (if-then-else) statements

Loops (for and while)

Procedure definition with parameters

Arbitrary SQL statements

Stored Procedures:

Typically stored in the database.

Executed by calling them by name, on the command-line or from a program.

Permit external applications to operate on the database without knowing about internal details about the database or even SQL.

A standard that is not uncommon – put all queries in stored procedures; applications are then only allowed to call stored procedures.

In the simplest case, a stored procedure simply contains a single query.

Procedural Extensionsand Stored Procedures
procedural extensions and stored procedures1

CREATE PROCEDURE stpgetauthors

   @surname varchar(30)=null



   IF @surname = null


      RAISERROR( 'No selection criteria provided !', 10, 1)




      SELECT * FROM authors

         WHERE au_lname LIKE @surname



Procedural Extensionsand Stored Procedures
embedded sql
Embedded SQL
  • The SQL standard defines embeddings of SQL in a variety of (host) programming languages such as Pascal, PL/I, Fortran, C, etc.
    • Requires a vendor provided pre-compiler and libraries.
    • Based on vendor specific APIs
    • Queries are compiled and optimized at compile time for the host program
    • No longer available in all DBMSs for many languages
  • Embedded SQL is explicitly identified in a program (varies by language):

// Ada style

EXEC SQL <embedded SQL statement > END-EXEC;

// Java style

# SQL { …. } ;

example query
Example Query

“Find the names and cities of customers with more than a given amount of dollars in some account.”

  • The following statement declares the query.
  • declare c cursor for select customer-name, customer-cityfrom depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account-numberand account.balance > :amount
  • The open statement causes the query to be evaluated.


embedded sql cont
Embedded SQL (Cont.)
  • The fetch statement causes the values of one tuple in the query result to be placed in host language variables.

EXEC SQL fetch c into :cn, :cc END-EXEC

  • Repeated calls (loop) will fetch successive tuples from the query result

Variable SQLSTATE is set to ‘02000’ to indicate no more data is available

  • The close statement causes the database system to delete the temporary relation that holds the result of the query.


Note: above details vary by language.

dynamic sql
Dynamic SQL
  • Dynamic SQL is similar to embedded SQL in some ways:
    • Syntax is similar - SQL is intermixed with programming language code.
    • Uses vendor provided APIs and libraries.
  • Dynamic SQL, however:
    • Allows programs to construct, compile, optimize and submit queries at run time.
    • Does not require a pre-compiler.
  • The main problem with embedded and dynamic APIs is that they are vendor specific.
Open DataBase Connectivity (ODBC) is a standard for programs to communicate with database servers.

Independent of language, DBMS or operating system.

ODBC defines an API providing the functionality to:

open a connection with a database

send queries and updates

get back results

Each DBMS vendor supporting ODBC provides a "driver" library that must be linked with the client program.

When a client program makes an ODBC API call, the code in the library communicates with the server to carry out the requested action, and fetch results, if necessary.

odbc cont
An ODBC program first allocates an “SQL environment,” and then a “database connection handle.”

An ODBC program then opens the database connection using SQLConnect() with the following parameters:

connection handle

server to connect to



Must also specify types of arguments:

SQL_NTS denotes previous argument is a null-terminated string.

ODBC (Cont.)
odbc code
int ODBCexample()


RETCODE error; /* query return code */

HENV env; /* environment */

HDBC conn; /* database connection */


SQLAllocConnect(env, &conn);

SQLConnect(conn, "", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS);

{ …. Do actual work … }





odbc code cont
ODBC Code (Cont.)
  • Main body of program (i.e., “Do actual work”):

char branchname[80];float balance;int lenOut1, lenOut2;HSTMT stmt;

SQLAllocStmt(conn, &stmt);char* sqlquery = "select branch_name, sum (balance) from account group by branch_name";

error = SQLExecDirect(stmt, sqlquery, SQL_NTS);

if (error == SQL_SUCCESS) {SQLBindCol(stmt, 1, SQL_C_CHAR, branchname , 80, &lenOut1);SQLBindCol(stmt, 2, SQL_C_FLOAT, &balance, 0 , &lenOut2);

while (SQLFetch(stmt) >= SQL_SUCCESS) {printf (" %s %g\n", branchname, balance); }}SQLFreeStmt(stmt, SQL_DROP);

  • JDBC is a Java specific API for communicating with database systems supporting SQL.
  • JDBC supports a variety of features for querying and updating data, and for retrieving query results.
  • Similar to ODBC in general structure and operation:
    • Open a connection
    • Create a “statement” object
    • Execute queries using the Statement object to send queries and fetch results
    • Exception mechanism to handle errors
with clause
With Clause
  • With clause allows views to be defined locally to a query, rather than globally.
    • Analogous to procedures in a programming language.
  • Find all accounts with the maximum balance withmax-balance(value) asselect max (balance)fromaccountselectaccount-numberfromaccount, max-balancewhereaccount.balance = max-balance.value
complex query using with clause
Complex Query using With Clause
  • Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.

withbranch-total (branch-name, value) asselectbranch-name, sum (balance)fromaccountgroupbybranch-namewithbranch-total-avg(value) asselectavg (value)frombranch-totalselect branch-namefrombranch-total, branch-total-avg wherebranch-total.value >= branch-total-avg.value

update of a view
Update of a View
  • Create a view of all loan data in the loan relation, hiding the amount attribute:

create view branch-loan as select branch-name, loan-numberfrom loan

  • Add a new tuple to branch-loan

insert into branch-loanvalues (‘Perryridge’, ‘L-307’)

  • This insertion must be represented by the insertion of the tuple

(‘L-307’, ‘Perryridge’, null)

into the loan relation (but does it take place automatically?).

update of a view1
Update of a View
  • Updates on more complex views are difficult or impossible to translate, and hence are disallowed.
  • Most SQL implementations allow updates only on simple views (without aggregates) defined on a single relation.
  • It is probably best not to allow insertion into views.
other sql features
Other SQL Features
  • SQL sessions
    • client connects to an SQL server, establishing a session
    • executes a series of statements
    • disconnects the session
    • can commit or rollback the work carried out in the session
  • An SQL environment contains several components, including a user identifier, and a schema, which identifies which of several schemas a session is using.
schemas catalogs and environments
Three-level hierarchy for naming relations.

Database contains multiple catalogs

Each catalog can contain multiple schemas

SQL objects such as relations and views are contained within a schema


Each user has a default catalog and schema, and the combination is unique to the user.

Default catalog and schema are set up for a connection

Catalog and schema can be omitted, defaults are assumed

Multiple versions of an application (e.g. production and test) can run under separate schemas

Schemas, Catalogs, and Environments
transactions in jdbc
Transactions in JDBC
  • As with ODBC, each statement gets committed automatically in JDBC
  • To turn off auto commit useconn.setAutoCommit(false);
  • To commit or abort transactions use conn.commit() or conn.rollback()
  • To turn auto commit on again, use conn.setAutoCommit(true);
procedure and function calls in jdbc
JDBC provides a class CallableStatement which allows SQL stored procedures/functions to be invoked.

CallableStatement cs1 = conn.prepareCall( “{call proc (?,?)}” ) ;

CallableStatement cs2 = conn.prepareCall( “{? = call func (?,?)}” );

Procedure and Function Calls in JDBC
result set metadata
The class ResultSetMetaData provides information about all the columns of the ResultSet.

Instance of this class is obtained by getMetaData( ) function of ResultSet.

Provides Functions for getting number of columns, column name, type, precision, scale, table from which the column is derived etc.

ResultSetMetaData rsmd = rs.getMetaData ( );

for ( int i = 1; i <= rsmd.getColumnCount( ); i++ ) {

String name = rsmd.getColumnName(i);

String typeName = rsmd.getColumnTypeName(i); }

Result Set MetaData
database meta data
The class DatabaseMetaData provides information about database relations

Has functions for getting all tables, all columns of the table, primary keys etc.

E.g. to print column names and types of a relation

DatabaseMetaData dbmd = conn.getMetaData( );

ResultSet rs = dbmd.getColumns( null, “BANK-DB”, “account”, “%” ); //Arguments: catalog, schema-pattern, table-pattern, column-pattern // Returns: 1 row for each column, with several attributes such as // COLUMN_NAME, TYPE_NAME, etc.

while ( ) ) { System.out.println( rs.getString(“COLUMN_NAME”) , rs.getString(“TYPE_NAME”); }

There are also functions for getting information such as

Foreign key references in the schema

Database limits like maximum row size, maximum no. of connections, etc

Database Meta Data
application architectures
Application Architectures
  • Applications can be built using one of two architectures
    • Two tier model
      • Application program running at user site directly uses JDBC/ODBC to communicate with the database
    • Three tier model
      • Users/programs running at user sites communicate with an application server. The application server in turn communicates with the database
two tier model
E.g. Java code runs at client site and uses JDBC to communicate with the backend server


flexible, need not be restricted to predefined queries


Security: passwords available at client site, all database operation possible

More code shipped to client

Not appropriate across organizations, or in large ones like universities

Two-tier Model
three tier model

CGI Program






Three Tier Model





HTTP/Application Specific Protocol


three tier model cont
E.g. Web client + Java Servlet using JDBC to talk with database server

Client sends request over http or application-specific protocol

Application or Web server receives request

Request handled by CGI program or servlets

Security handled by application at server

Better security

Fine granularity security

Simple client, but only packaged transactions

Three-tier Model (Cont.)
statistical databases
Problem: how to ensure privacy of individuals while allowing use of data for statistical purposes (e.g., finding median income, average bank balance etc.)


System rejects any query that involves fewer than some predetermined number of individuals.

 Still possible to use results of multiple overlapping queries to deduce data about an individual

Data pollution -- random falsification of data provided in response to a query.

Random modification of the query itself.

There is a tradeoff between accuracy and security.

Statistical Databases
physical level security
Protection of equipment from floods, power failure, etc.

Protection of disks from theft, erasure, physical damage, etc.

Protection of network and terminal cables from wiretaps non-invasive electronic eavesdropping, physical damage, etc.


Replicated hardware:

mirrored disks, dual busses, etc.

multiple access paths between every pair of devises

Physical security: locks,police, etc.

Software techniques to detect physical security breaches.

Physical Level Security
human level security
Protection from stolen passwords, sabotage, etc.

Primarily a management problem:

Frequent change of passwords

Use of “non-guessable” passwords

Log all invalid access attempts

Data audits

Careful hiring practices

Human Level Security
operating system level security
Protection from invalid logins

File-level access protection (often not very helpful for database security)

Protection from improper use of “superuser” authority.

Protection from improper use of privileged machine intructions.

Operating System Level Security
network level security
Each site must ensure that it communicate with trusted sites (not intruders).

Links must be protected from theft or modification of messages


Identification protocol (password-based),


Network-Level Security
database level security
Assume security at network, operating system, human, and physical levels.

Database specific issues:

each user may have authority to read only part of the data and to write only part of the data.

User authority may correspond to entire files or relations, but it may also correspond only to parts of files or relations.

Local autonomy suggests site-level authorization control in a distributed database.

Global control suggests centralized control.

Database-Level Security
external world actions
External World Actions

We sometimes require external world actions to be triggered on a database update.

For example, re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light.

Triggers cannot be used to directly implement external-world actions, but:

Triggers can be used to record actions-to-be-taken in a separate table.

Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table.

Suppose a warehouse has the following tables

inventory(item, level) :How much of each item is in the warehouse

minlevel(item, level) : What is the minimum desired level of each item

reorder(item, amount) :What quantity should we re-order at a time

orders(item, amount) :Orders to be placed (read by external process)

external world actions cont
External World Actions (Cont.)

create trigger reorder-trigger after update of amount on inventory

referencing old row as orow, new row as nrow

for each row

when nrow.level < = (select level

from minlevel

where minlevel.item = orow.item)

and orow.level > (select level

from minlevel

where minlevel.item = orow.item)


insert into orders

(select item, amount

from reorder

where reorder.item = orow.item)