module-04 structured query language … 4 : structured query language (sql) module-04 structured...
TRANSCRIPT
Module 4 : Structured Query Language (SQL)
Module-04
STRUCTURED QUERY LANGUAGE (SQL)
4.1 Motivation
The knowledge SQL is essential for software developer hence SQL is an important subject
in computer programming around the world.
4.2 Objective
DBMS is the concept for the management of database but SQL is a set of tool or software
For the implementation of all the concept. In SQL we will going to see how each and every
concept of DBMS is implemented with SQL.
4.3 Syllabus
Prerequisites: Syllabus: Duration: Self study:
Relational
model.
Overview of SQL
Data Definition Commands
Set operations
Aggregate function
Null values,
Data Manipulation commands,
Data Control commands
Views in SQL
Complex Retrieval Queries using
Group By, Recursive Queries, nested
Queries ;
Referential integrity in SQL. Event
Condition Action (ECA) model
(Triggers) in SQL
Database Programming with JDBC,
Security and authorization in SQL
Functions and Procedures in SQL and
cursors.
10 hours
create the college
library database
using SQL
command then
and use all the
DML, DDL, and
DCL command.
4.4 Definition
Data definition: SQL lets a user define the structure and organization of the stored data
and relationships among the stored data items.
Module 4 : Structured Query Language (SQL)
Data retrieval: SQL allows a user or an application program to retrieve stored data from
the database and use it.
Data manipulation: SQL allows a user or an application program to update the database by
adding new data, removing old data, and modifying previously stored data.
Access control: SQL can be used to restrict a user’s ability to retrieve, add, and modify
data, protecting stored data against unauthorized access.
Data sharing: SQL is used to coordinate data sharing by concurrent users, ensuring that
they do not interface with one another.
Data integrity: SQL defines integrity constraints in the database, protecting in the database,
protecting it from corruption due to inconsistent updates or system failures.
4.5 Course Outcome and Learning Outcome
CO3:-
Student should be able to retrieve any type of information from a data base by formulating
complex queries in SQL.
CO4:-
Create and populate a RDBMS for a real life application, with constraints and keys, using
SQL.
LO:- Write queries in SQL to retrieve any type of information from a data base.
4.6 Key definition
SQL: SQL is a tool for organizing, managing, and retrieving data stored by a computer
database. SQL is a computer language that we use to interact with the database. In fact,
SQL works with one specific type of database, called a relational data base.
Relational Database: A relational database is a database that can be perceived as a set of
tables and manipulated in accordance with the relational model of data. It contains a set of
objects used to store, manage, and access data. Examples of such objects are tables, views,
indexes, functions, triggers, and packages
4.7 Theory
Commercial database systems require more user-friendly query languages. We will look at
SQL in detail. Although referred to as query languages, SQL contains facilities for
designing and modifying the database.
The relation schemes for the banking example are:
Branch-scheme = (bname, bcity, assets)
Module 4 : Structured Query Language (SQL)
Customer-scheme = (cname, street, ccity)
Depositor-scheme = (cname, account#)
Account-scheme = (bname, account#, balance)
Loan-scheme = (bname, loan#, amount)
Borrower-scheme = (cname, loan#)
Background
1. SQL has become the standard relational database language. It has several parts:
o Data definition language (DDL) - provides commands to
Define relation schemes.
Delete relations.
Create indices.
Modify schemes.
o Interactive data manipulation language (DML) - a query language based on
both relational algebra and tuple relational calculus, plus commands to insert,
delete and modify tuples.
o Embedded data manipulation language - for use within programming
languages like C, PL/1, Cobol, Pascal, etc.
o View Definition - commands for defining views
o Authorization - specifying access rights to relations and views.
o Integrity - a limited form of integrity checking.
o Transaction control - specifying beginning and end of transactions.
4.7.1 Basic Structure
1. Basic structure of an SQL expression consists of select, from and where clauses.
o select clause lists attributes to be copied - corresponds to relational algebra
project.
o from clause corresponds to Cartesian product - lists relations to be used.
o where clause corresponds to selection predicate in relational algebra.
2. Typical query has the form
Select A1,A2……..An
from R1,R2……………Rn
where P
where each Ai represents an attribute, each Ri a relation, and P is a predicate.
Module 4 : Structured Query Language (SQL)
3. This is equivalent to the relational algebra expression
a1,a2……..an( p(R1*R2*…………*Rm))
o If the where clause is omitted, the predicate P is true.
o The list of attributes can be replaced with a * to select all.
o SQL forms the Cartesian product of the relations named, performs a selection
using the predicate, then projects the result onto the attributes named.
o The result of an SQL query is a relation.
o SQL may internally convert into more efficient expressions.
The select Clause
The where Clause
The from Clause
The Rename Operation
Tuple Variables
String Operations
Ordering the Display of Tuples
Duplicate Tuples
The select Clause
1. An example: Find the names of all branches in the account relation.
select bname
from account
2. distinct vs. all: elimination or not elimination of duplicates.
Find the names of all branches in the account relation.
select distinct bname
from account
By default, duplicates are not removed. We can state it explicitly using all.
select all bname
from account
3. select * means select all the attributes. Arithmetic operations can also be in the
selection list.
The where Clause
1. The predicates can be more complicated, and can involve
o Logical connectives and, or and not.
Module 4 : Structured Query Language (SQL)
o Arithmetic expressions on constant or tuple values.
o The between operator for ranges of values.
Example: Find account number of accounts with balances between $90,000 and $100,000.
select account#
from account
where balance between 90000 and 100000
The from Clause
1. The from class by itself defines a Cartesian product of the relations in the clause.
2. SQL does not have a natural join equivalent. However, natural join can be expressed
in terms of a Cartesian product, selection, and projection.
3. For the relational algebra expression
Πename,loan#(borrower1 loan)
we can write in SQL,
select distinct cname, borrower.loan#
from borrower, loan
where borrower.loan# = loan.loan#
4. More selections with join: ``Find the names and loan numbers of all customers who
have a loan at the SFU branch,'' we can write in SQL,
select distinct cname, borrower.loan#
from borrower, loan
where borrower.loan# = loan.loan#
and bname=``SFU''
The Rename Operation
1. Rename: a mechanism to rename both relations and attributes.
2. as-clause can appear in both the select and from clauses:
old-name as new-name.
Example.
select distinct cname, borrower.loan# as loan_id
from borrower, loan
where borrower.loan# = loan.loan#
and bname= ``SFU"
Tuple Variables
1. Tuple variables can be used in SQL, and are defined in the from clause:
Module 4 : Structured Query Language (SQL)
select distinct cname, T.loan#
from borrower as S, loan as T
where S.loan# = T.loan#
2. These variables can then be used throughout the expression. Think of it as being
something like the rename operator.
Finds the names of all branches that have assets greater than at least one branch
located in Burnaby.
select distinct T.bname
from branch S, branch T
where S.bcity=``Burnaby'' and T.assets > S.assets
String Operations
1. The most commonly used operation on strings is pattern matching using the
operator like.
2. String matching operators % (any substring) and _ (underscore, matching any
character).
E.g., ``___%'' matches any string with at least 3 characters.
3. Patterns are case sensitive, e.g., ``Jim" does not match ``jim".
4. Use the keyword escape to define the escape character.
E.g., like ``ab%tely % '' escape `` '' matches all the strings beginning with ``ab''
followed by a sequence of characters and then ``tely'' and then ``% ''.Backslash
overrides the special meaning of these symbols.
5. We can use not like for string mismatching.
Example. Find all customers whose street includes the substring ``Main''.
select cname
from customer
where street like ``%Main%''
6. SQL also permits a variety of functions on character strings, such as concatenating
(using `` ''), extracting substrings, finding the length of strings, converting between
upper case and lower case, and so on.
4.7.2 Ordering the Display of Tuples
1. SQL allows the user to control the order in which tuples are displayed.
o order by makes tuples appear in sorted order (ascending order by default).
Module 4 : Structured Query Language (SQL)
o desc specifies descending order.
o asc specifies ascending order.
select *
from loan
order by amount desc, loan# asc
Sorting can be costly, and should only be done when needed
Duplicate Tuples
Formal query languages are based on mathematical relations. Thus no duplicates
appear in relations.
As duplicate removal is expensive, SQL allows duplicates.
To remove duplicates, we use the distinct keyword.
To ensure that duplicates are not removed, we use the all keyword.
Multiset (bag) versions of relational algebra operators.
o if there are c1 copies of tuples t1 in R1, and t1 satisfies selection θ, then there
are c1 copies of t1 in σθ (R1) .
o for each copy of tuple t1 in R1 , there is a copy of tuple a(t1) in πa(R1) .
o if there are c1 copies of tuple t1 in R1, and C2 copies of tuple t2 in R2, there is
C1× C2 copies of tuple t1,t2 in R1×R2.
An SQL query of the form
select A1,A2……….An
from R1,R2…………Rn
where P
is equivalent to the algebra expression
A1,A2……..An(σp(R1×R2×………×Rm))
using the multiset versions of the relational operators σ , and .
Set Operations
1. SQL has the set operations union, intersect and except.
2. Find all customers having an account.
select distinct cname
from depositor
3. union: Find all customers having a loan, an account, or both. branch.
(select cname
Module 4 : Structured Query Language (SQL)
from depositor)
union
(select cname
from borrower)
4. intersect: Find customers having a loan and an account.
(select distinct cname
from depositor)
intersect
(select distinct cname
from borrower)
5. except: Find customers having an account, but not a loan.
(select distinct cname
from depositor)
except
(select cname
from borrower)
6. Some additional details:
o union eliminates duplicates, being a set operation. If we want to retain
duplicates, we may use union all, similarly for intersect and except.
o Not all implementations of SQL have these set operations.
o except in SQL-92 is called minus in SQL-86.
o It is possible to express these queries using other operations.
Module 4 : Structured Query Language (SQL)
4.7.3 Aggregate Functions
1. In SQL we can compute functions on groups of tuples using the group by clause.
Attributes given are used to form groups with the same values. SQL can then
compute
o average value -- avg
o minimum value -- min
o maximum value -- max
o total sum of values -- sum
o number in group -- count
These are called aggregate functions. They return a single value.
2. Some examples:
1. Find the average account balance at each branch.
select bname, avg (balance)
from account
group by bname
2. Find the number of depositors at each branch.
select bname, count (distinct cname)
from account, depositor
where account.account# = depositor.account#
group by bname
We use distinct so that a person having more than one account will not be
counted more than once.
3. Find branches and their average balances where the average balance is more
than $1200.
select bname, avg (balance)
from account
group by bname
having avg (balance) > 1200
Predicates in the having clause are applied after the formation of groups.
4. Find the average balance of each customer who lives in Vancouver and has at
least three accounts:
select depositor.cname, avg (balance)
Module 4 : Structured Query Language (SQL)
from depositor, account, customer
where depositor.cname = customer.cname and
account.account# = depositor.account#
and ccity=``Vancouver''
group by depositor.cname
having count (distinct account#) >= 3
If a where clause and a having clause appear in the same query, the where clause
predicate is applied first.
o Tuples satisfying where clause are placed into groups by the group by clause.
o The having clause is applied to each group.
o Groups satisfying the having clause are used by the select clause to generate
the result tuples.
o If no having clause is present, the tuples satisfying the where clause are
treated as a single group.
Null Values
1. With insertions, we saw how null values might be needed if values were unknown.
Queries involving nulls pose problems.
2. If a value is not known, it cannot be compared or be used as part of an aggregate
function.
3. All comparisons involving null are false by definition. However, we can use the
keyword null to test for null values:
select distinct loan#
from loan
where amount is null
4. All aggregate functions except count ignore tuples with null values on the argument
attributes.
Module 4 : Structured Query Language (SQL)
4.7.4 Nested Subqueries
Set Membership
Set Comparison
Test for Empty Relations
Test for the Absence of Duplicate Tuples
We use the in and not in operations for set membership.
select distinct cname
from borrower
where cname in
(select cname
from account
where bname=``SFU'')
Note that we can write the same query several ways in SQL.
We can also test for more than one attribute:
select distinct cname
from borrower, loan
where borrower.loan# = loan.loan#
and bname=``SFU''
and (bname, cname) in (select bname, cname
from account, depositor where depositor.account# = account.account#)
This finds all customers who have a loan and an account at the SFU branch in yet
another way.
Finding all customers who have a loan but not an account, we can use the not in
operation.
4.7.5 Set Comparison
1. To compare set elements in terms of inequalities, we can write
select distinct T.bname
from branch T,branch S
where T.assets > S.assets
and S.bcity=``Burnaby''
or we can write
select bname
from branch
Module 4 : Structured Query Language (SQL)
where assets > some
(select assets
from branch
where bcity=``Burnaby'')
to find branches whose assets are greater than some branch in Burnaby.
2. We can use any of the equality or inequality operators with some. If we change >
some to > all, we find branches whose assets are greater than all branches in
Burnaby.
3. Example. Find branches with the highest average balance. We cannot compose
aggregate functions in SQL, e.g. we cannot do max (avg ...)). Instead, we find the
branches for which average balance is greater than or equal to all average balances:
select bname
from account
group by bname
having avg (balance) >= all
(select avg (balance)
from account
group by bname)
Test for Empty Relations
1. The exists construct returns true if the argument subquery is nonempty.
2. Find all customers who have a loan and an account at the bank.
select cname
from borrower
where exists (select *
from depositor
where depositor.cname = borrower.cname)
Test for the Absence of Duplicate Tuples
1. The unique construct returns true if the argument subquery contains no duplicate
tuples.
2. Find all customers who have only one account at the SFU branch.
aaaaa aa - select T.cname
from depositor as T
where unique (select R.cname
Module 4 : Structured Query Language (SQL)
from account, depositor as R
where T.cname = R.cname and
R.account# = account.account# and
account.bname = ``SFU")
Derived Relations
1. SQL-92 allows a subquery expression to be used in the from clause.
2. If such an expression is used, the result relation must be given a name, and the
attributes can be renamed.
3. Find the average account balance of those branches where the average account
balance is greater than $1,000.
select bname, avg-balance
from (select bname, avg(balance)
from account
group by bname)
as result(bname, avg-balance)
where avg-balance > 1000
Module 4 : Structured Query Language (SQL)
4.7.6 Views
1. A view in SQL is defined using the create view command:
2. create view v as query expression
where query expression is any legal query expression.
The view created is given the name v.
To create a view all-customer of all branches and their customers:
create view all-customer as
(select bname, cname
from depositor, account
where depositor.account# = account.account#)
union
(select bname, cname
from borrower, loan
where borrower.loan# = loan.loan#)
3. Having defined a view, we can now use it to refer to the virtual relation it creates.
View names can appear anywhere a relation name can.
4. We can now find all customers of the SFU branch by writing
select cname
from all-customer
where bname=``SFU'
Modification of the Database
Up until now, we have looked at extracting information from the database.
Deletion
Insertion
Updates
Update of a view
Deletion
1. Deletion is expressed in much the same way as a query. Instead of displaying, the
selected tuples are removed from the database. We can only delete whole tuples.
2. A deletion in SQL is of the form
delete from r
where P
Module 4 : Structured Query Language (SQL)
Tuples in r for which P is true are deleted. If the where clause is omitted, all tuples
are deleted.
3. The request delete from loan deletes all tuples from the relation loan.
4. Some more examples:
1. Delete all of Smith's account records.
delete from depositor
where cname=``Smith''
2.Delete all loans with loan numbers between 1300 and 1500.
delete from loan
where loan# between 1300 and 1500
3.Delete all accounts at branches located in Surrey.
delete from account
where bname in
(select bname
from branch
where bcity=``Surrey'')
5. We may only delete tuples from one relation at a time, but we may reference any
number of relations in a select-from-where clause embedded in the where clause of
a delete.
6. However, if the delete request contains an embedded select that references the
relation from which tuples are to be deleted, ambiguities may result.
For example, to delete the records of all accounts with balances below the average,
we might write
delete from account
where balance <
(select avg(balance) from account)
You can see that as we delete tuples from account, the average balance changes!
Solution: The delete statement first test each tuple in the relation account to check
whether the account has a balance less than the average of the bank. Then all tuples
that fail the test are deleted. Perform all the tests (and mark the tuples to be deleted)
before any deletion then delete them en masse after the evaluations!
Insertion
Module 4 : Structured Query Language (SQL)
1. To insert data into a relation, we either specify a tuple, or write a query whose result
is the set of tuples to be inserted. Attribute values for inserted tuples must be
members of the attribute's domain.
2. Some examples:
1. To insert a tuple for Smith who has $1200 in account A-9372 at the SFU
branch.
insert into account
values (``SFU'', ``A-9372'', 1200)
2. To provide each loan that the customer has in the SFU branch with a $200
savings account.
insert into account
select bname, loan#, 200
from loan
where bname=``SFU''
Here, we use a select to specify a set of tuples.
It is important that we evaluate the select statement fully before carrying out
any insertion. If some insertions were carried out even as the select statement
were being evaluated, the insertion
insert into account
select *
from account
might insert an infinite number of tuples. Evaluating the select statement
completely before performing insertions avoids such problems.
3. It is possible for inserted tuples to be given values on only some attributes of
the schema. The remaining attributes are assigned a null value denoted by
null.
We can prohibit the insertion of null values using the SQL DDL
Updates
1. Updating allows us to change some values in a tuple without necessarily changing
all.
2. Some examples:
1. To increase all balances by 5 percent.
Module 4 : Structured Query Language (SQL)
update account
set balance=balance * 1.05
This statement is applied to every tuple in account.
To ke two different rates of interest payment, depending on balance
amount:
update account
2. set balance=balance * 1.06
where balance > 10,000
update account
set balance=balance * 1.05
where balance 10,000
Note: in this example the order of the two operations is important. (Why?)
3. In general, where clause of update statement may contain any construct legal in a
where clause of a select statement (including nesting).
4. A nested select within an update may reference the relation that is being updated.
As before, all tuples in the relation are first tested to see whether they should be
updated, and the updates are carried out afterwards.
For example, to pay 5% interest on account whose balance is greater than average,
we have
update account
set balance=balance * 1.05
where balance >
select avg (balance)
from account
Update of a view
1. The view update anomaly previously mentioned in Chapter 3 exists also in SQL.
2. An example will illustrate: consider a clerk who needs to see all information in the
loan relation except amount.
Let the view branch-loan be given to the clerk:
create view branch-loan as
select bname, loan#
from loan
Module 4 : Structured Query Language (SQL)
Since SQL allows a view name to appear anywhere a relation name may appear, the
clerk can write:
insert into branch-loan
values (``SFU'', ``L-307'')
This insertion is represented by an insertion into the actual relation loan, from which
the view is constructed. However, we have no value for amount.
This insertion results in (``SFU'', ``L-307'', null) being inserted into the loan relation.
As we saw, when a view is defined in terms of several relations, serious problems
can result. As a result, many SQL-based systems impose the constraint that a
modification is permitted through a view only if the view in question is defined in
terms of one relation in the database.
Module 4 : Structured Query Language (SQL)
4.7.7 Joined Relations
Examples
Join types and conditions
Examples
1. Two given relations: loan and borrower.
Figure 4.1: The loan and borrower relations.
2. inner join:
loan inner join borrower on loan.loan# = borrower.loan#
Notice that the loan# will appear twice in the inner joined relation.
Figure 4.2: Result of loan inner join borrower.
3. left outer join:
loan left outer join borrower on loan.loan# = borrower.loan#
Figure 4.3: Result of loan left outer join borrower.
4. natural inner join:
loan natural inner join borrower
Figure 4.4: Result of loan natural inner join borrower
Join types and conditions
1. Each variant of the join operations in SQL-92 consists of a join type and a join
condition.
2. Join types: inner join, left outer join, right outer join, full outer join.
Module 4 : Structured Query Language (SQL)
The keyword inner and outer are optional since the rest of the join type enables us to
deduce whether the join is an inner join or an outer join.
SQL-92 also provides two other join types:
1. cross join: an inner join without a join condition.
2. union join: a full outer join on the ``false'' condition, i.e., where the inner join
is empty.
3. Join conditions: natural, on predicate, using (A1,A2,……….An)
The use of join condition is mandatory for outer joins, but is optional for inner joins
(if it is omitted, a Cartesian product results).
4. Ex. A natural full outer join:
loan natural full outer join borrower
using (loan#)
Figure 4.5: Result of loan natural full outer join borrower using (loan#).
5. Ex. Find all customers who have either an account or a loan (but not both) at the
bank.
select cname
from (natural full outer join borrower)
where account# is null or
loan# is null
Module 4 : Structured Query Language (SQL)
4.7.8 Trigger A trigger is an event-condition-action (ECA) rule • When event occurs, test condition; if condition is satisfied, execute action • Example: • Event: some user’s popularity is updated • Condition: the user is a member of “Jessica’s Circle,” and pop drops below 0.5 • Action: kick that user out of Jessica’s Circle A trigger is a PL/SQL block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed. In other words Triggers are a stored PL/SQL code block attached and executed by an event which occurs to a database table.
Triggers are stored as text and compiled at execution time, because of wise not to include much code in them but to call out previously stored procedures or packages. Triggers contained SQL or PL/SQL statements which are executed as a unit and can call other procedures and triggers.
Triggers are used to improve the performance of Oracle in order to provide a more convenient database. A trigger may not issue any transaction control statements like Commit, Rollback and Savepoint.
Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations.
When does the Trigger Fire?
A trigger fires based on a triggering statement, which specifies:
The SQL statement or the database event or DDL event that fires the trigger body. The options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.
The table, view, DATABASE, or SCHEMA on which the trigger is defined.
Creating Trigger
To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement. You must have the privilege .I.e. CREATE TRIGGER.
Module 4 : Structured Query Language (SQL)
Syntax:
CREATE [OR REPLACE] TRIGGER trigge_name
{BEFORE ! AFTER I INSTEAD OF}
{INSERT [OR] ! UPDATE [OR] I DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS old, NEW AS new]
[FOR EACH ROW]
WHEN (condition)
BEGIN
- Sql statements
END;
Where:
CREATE [OR REPLACE] TRIGGER trigger_name -This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
{BEFORE I AFTER I INSTEAD OF} - This clause indicates at what time the trigger should get fired. i.e. for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. Before and after cannot be used to create a trigger on a view.
{INSERT [OR] I UPDATE [OR] I DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
[OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
[ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
[REFERENCING OLD AS old, NEW AS new] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or: new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
[FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected (i.e. a Row Level Trigger) or just once when the entire sql statement is executed (i.e. statement level Trigger).
Module 4 : Structured Query Language (SQL)
WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
Parts of a Trigger
A trigger has three basic parts:
A triggering event or statement
A trigger restriction
A trigger action
A triggering event can be an insert, update, or delete statement or an instance shutdown or startup etc. The trigger fires automatically when any of these events occur a trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires.
The Triggering Event or Statement
A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:
An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)
II A CREATE, ALTER, or DROP statement on any schema object
II A database startup or instance shutdown
II A specific error message or any error message
II A user logon or logoff
Syntax:
[INSERT | DELETE | UPDATE of column-list]
ON <table-name>
[REFERENCING <OLD as old, NEW as new>]
A triggering event can specify multiple SQL statements:
[... INSERT OR UPDATE OR DELETE OF inventory ... ]
This part means that when an INSERT, UPDATE, or DELETE statement is issued against the inventory table, fire the trigger. When multiple types of SQL statements can fire a trigger, you can use conditional predicates to detect the type of triggering statement.
Module 4 : Structured Query Language (SQL)
Trigger Restriction
A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown in the example, the trigger restriction is:
new.parts_on_hand < new. reorder_point
Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount.
Trigger Action
The trigger action is a procedure that contains the code to be executed when the trigger fires. Oracle will execute the block when a triggering statement is issued and the trigger restriction evaluates to true.
[DECLARE]
BEGIN
[EXCEPTION SECTION]
END [trigger-name]
Types of PL/SQL Triggers
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. Different types of triggers are as under:
Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events
Row Triggers and Statement Triggers
When you define a trigger, you can specify the number of times the trigger action is to be run:
Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows.
• Once for the triggering statement, no matter how many rows it affects.
Module 4 : Structured Query Language (SQL)
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
A Row trigger fires once for each row affected. It uses FOR EACH ROW clause. They are useful if trigger action depends on number of rows affected. Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, statement-level DELETE trigger is fired only once.
In other words Statement Trigger fires once, irrespective of number of rows affected in the table. Statement triggers are useful when triggers action does not depend on Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
For example, use a statement trigger to:
Make a complex security check on the current time or user.
Generate a single audit record.
Before and after Triggers
'When defining a trigger, you can specify the trigger timing-whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.
BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.
Before Triggers
Module 4 : Structured Query Language (SQL)
BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:
When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
To derive specific column values before completing a triggering INSERT or UPDATE statement.
After Triggers
AFTER triggers run the trigger action after the triggering statement is run. AFTER TRIGGERS are used when you want the triggering statement to complete before executing the trigger action.
Instead of Triggers
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
You can write normal INSERT, UPDATE, and DELETE statements against the view, and the INSTEAD OF trigger is fired to update the underlying tables appropriately INSTEAD OF triggers are activated for each row of the view that gets modified.
4.7.9 Procedure
A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.
In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.
Before you create a stored procedure you need to know what your end result is, whether you are selecting data, inserting data, etc..
Module 4 : Structured Query Language (SQL)
In this simple example we will just select all data from the Person.Address table that is stored in the AdventureWorks database.
So the simple T-SQL code excuting in the AdventureWorks database would be as follows which will return all rows from this table.
SELECT * FROM Person.Address
To create a stored procedure to do this the code would look like this:
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress
AS
SELECT * FROM Person.Address
GO
To call the procedure to return the contents from the table specified, the code would be:
EXEC dbo.uspGetAddress
-- or
EXEC uspGetAddress
--or just simply
uspGetAddress
When creating a stored procedure you can either use CREATE PROCEDURE or CREATE PROC. After the stored procedure name you need to use the keyword "AS" and then the rest is just the regular SQL code that you would normally execute.
One thing to note is that you cannot use the keyword "GO" in the stored procedure. Once the SQL Server compiler sees "GO" it assumes it is the end of the batch.
Also, you can not change database context within the stored procedure such as using "USE dbName" the reason for this is because this would be a separate batch and a stored procedure is a collection of only one batch of statements.
Just like you have the ability to use parameters with your SQL code you can also setup your stored procedures to accept one or more parameter values.
One Parameter
Module 4 : Structured Query Language (SQL)
In this example we will query the Person.Address table from the AdventureWorks database, but instead of getting back all records we will limit it to just a particular city. This example assumes there will be an exact match on the City value that is passed.
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
To call this stored procedure we would execute it as follows:
EXEC dbo.uspGetAddress @City = 'New York'
We can also do the same thing, but allow the users to give us a starting point to search the data. Here we can change the "=" to a LIKE and use the "%" wildcard.
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City LIKE @City + '%'
GO
In both of the proceeding examples it assumes that a parameter value will always be passed. If you try to execute the procedure without passing a parameter value you will get an error message such as the following:
Msg 201, Level 16, State 4, Procedure uspGetAddress, Line 0
Procedure or function 'uspGetAddress' expects parameter '@City', which was not supplied.
Module 4 : Structured Query Language (SQL)
Default Parameter Values
In most cases it is always a good practice to pass in all parameter values, but sometimes it is not possible. So in this example we use the NULL option to allow you to not pass in a parameter value. If we create and run this stored procedure as is it will not return any data, because it is looking for any City values that equal NULL.
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
We could change this stored procedure and use the ISNULL function to get around this. So if a value is passed it will use the value to narrow the result set and if a value is not passed it will return all records. (Note: if the City column has NULL values this will not include these values. You will have to add additional logic for City IS NULL)
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = ISNULL(@City,City)
GO
Multiple Parameters
Setting up multiple parameters is very easy to do. You just need to list each parameter and the data type separated by a comma as shown below.
Module 4 : Structured Query Language (SQL)
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
GO
To execute this you could do any of the following:
EXEC dbo.uspGetAddress @City = 'Calgary'
--or
EXEC dbo.uspGetAddress @City = 'Calgary', @AddressLine1 = 'A'
--or
EXEC dbo.uspGetAddress @AddressLine1 = 'Acardia'
-- etc...
Setting up output paramters for a stored procedure is basically the same as setting up input parameters, the only difference is that you use the OUTPUT clause after the parameter name to specify that it should return a value. The output clause can be specified by either using the keyword "OUTPUT" or just "OUT". For these examples we are still using the AdventureWorks database, so all the stored procedures should be created in the AdventureWorks database.
Simple Output
CREATE PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT
AS
SELECT @AddressCount = count(*)
FROM AdventureWorks.Person.Address
WHERE City = @City
Module 4 : Structured Query Language (SQL)
Or it can be done this way:
CREATE PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUT
AS
SELECT @AddressCount = count(*)
FROM AdventureWorks.Person.Address
WHERE City = @City
To call this stored procedure we would execute it as follows. First we are going to declare a variable, execute the stored procedure and then select the returned valued.
DECLARE @AddressCount int
EXEC dbo.uspGetAddressCount @City = 'Calgary', @AddressCount = @AddressCount OUTPUT
SELECT @AddressCount
This can also be done as follows, where the stored procedure parameter names are not passed.
DECLARE @AddressCount int
EXEC dbo.uspGetAddressCount 'Calgary', @AddressCount OUTPUT
SELECT @AddressCount
Dropping Single Stored Procedure
To drop a single stored procedure you use the DROP PROCEDURE or DROP PROC command as follows.
DROP PROCEDURE dbo.uspGetAddress
GO
-- or
DROP PROC dbo.uspGetAddress
GO
Dropping Multiple Stored Procedures
To drop multiple stored procedures with one command you specify each procedure separated by a comma as shown below.
DROP PROCEDURE dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress
Module 4 : Structured Query Language (SQL)
GO
-- or
DROP PROC dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress
GO
Modifying an Existing Stored Procedure
Let's say we have the following existing stored procedure: This allows us to do an exact match on the City.
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
Let's say we want to change this to do a LIKE instead of an equals.
To change the stored procedure and save the updated code you would use the ALTER PROCEDURE command as follows.
ALTER PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City LIKE @City + '%'
GO
4.7.10 Cursor
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a CURSOR lets you name a work area and access its stored information. A Cursor in its simplest form can be thought of as a pointer to the records in database table or a virtual table represented by the result of a SELECT statement.
A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed
Module 4 : Structured Query Language (SQL)
by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
A cursor is basically a set of rows that you can access one at a time. These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
Explicit cursors must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row. Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.
Example:
SQL) SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO=20;
The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. As Figure shows, an explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.
Types of Cursors
You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor. Generally, there are two types of SQL that you can execute in PL/SQL.
There are two types of cursors in PL/SQL:
Implicit cursors
Explicit cursors
Implicit Cursors
When you execute DML statements like Delete, Insert, Update and Select statements, implicit statements are created to process these statements. Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL.
For example, when you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a
Module 4 : Structured Query Language (SQL)
SELECT. .. INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
Explicit Cursors
When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package. An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.
4.8 Objective question
1) SQL stand for.
A) Syntax query language
B) Structure Query Language.
C) Structure quantum language.
D) None of these.
2) Create is………command.
A) DML.
B) DCL.
C) DDL
D) All of them.
3) Where clause in SQL is use to define?
A) Condition.
B) Entity.
C) Attribute.
D) Set of tuple .
4) Sigma operator in SQL represents.
A) Select row.
B) Select column.
C) Select set of tuple.
D) All of these.
5) Roll back command is……
A) DDL
B) DML.
Module 4 : Structured Query Language (SQL)
C) DCL.
D) None of these.
6) Command is use to erase the all the data of table.
A) DELET.
B) TRUNCATE.
C) UPDATE
D) WHERE
7) From clause in SQL refer to
A) ROW.
B) COLUMN.
C) TABLE.
D) DATABASE.
8) Following function is not aggregate function.
A) Min.
B) Max.
C) Count.
D) All of these.
9)’*’ in SQL is use for selection of
A) All rows.
B) All columns.
C) All table.
D) All of these.
10) Aggregate function can be use only in.
A) Select clause.
B) WHERE cause.
C) FROM clause.
D) All of these.
4.9 Subjective question:
1) List and explain the name of all the DDL command.
2) List and explain all the DML command.
3) Give the name of all aggregate function and explain.
Module 4 : Structured Query Language (SQL)
4) Explain the group by having clause with example.
5) Give the short note with example.
A) UNION B) INTERSECTION
6) List the set of all operators in SQL.
7) What is the DCL command explains with example.
8) Create the any five table of library system with 10 entries in each table.
9) Explain COMMIT and ROLLBACK command in SQL.
Long Answer Question
1. List and explain the name of all the DDL command. (10 Marks)
2. List and explain all the DML command. (10 Marks)
3. Give the name of all aggregate function and explain. (10 Marks)
4. Create the any five table of library system with 10 entries in each table.
5. Create the any five table of University with 10 entries in each table with all
constrains.
4.10 University exam question
1) what is SQL? Explain the following structures of SQL queries with
appropriate Example
i) Select clause iv) Aggregate function
ii) Where clause v) Assignment
iii) From clause.
2) For the given employee database give an expression in SQL for the following.
Employee (empname, street, city)
Works (empname, company-name, salary)
Company (company-name, City)
Manages (empname, manager-name)
(i) Create table,instert values for all the table given
(ii) Modify the database so that ‘jones’ now lives in ‘newtown’
(iii) Give all employees of first bank corporation a 10 percent raise.
2) For the given database with SQL query:
Employee (eid, employee_name, street, city)
Module 4 : Structured Query Language (SQL)
Works (eid, cid,salary)
Company (cid,company_name,and city)
Manager (eid,manager_name)
i) Find the name, street and city of all employee who work for “ABC” and more than rs.200
ii) Find the name of all employee having “i” as the second letter in there name.
iii) Display the annual salary of all employees.
3) How many cods rule must a DBMS satisfy to quality of DBMS?
4) For the following given database, write a sql query.
Person (driver_id#, name, address)
Car (license, model, year)
Accident (report_no, date, location)
Owns (driver_id,car,report_no,damage_amt)
i) Find the total number of people who owned cars that were involved in accident
in 1995.
ii) Find the number of accident in which the cars belonging to “Sunil K”.were
involved.
iii) Update damage amount for car with license no. “Mum2022”
In the accident with report number “AR2197” to Rs.5000.
5) Give the name of all aggregate function and explain.
6) Explain the group by having clause with example.
8) Give the short note with example.
A) UNION B) INTERSECTION
4.11 References
1. Microsoft SQL Server 2000 Bible, Wiley.
2. BALTER, MS SQL SERVER 2005 EXPRESS IN 24 Hours, Pearson Education