1 sql n structured query language n declarative l specify the properties that should hold in the...
TRANSCRIPT
1
SQL Structured Query Language Declarative
Specify the properties that should hold in the result, not how to obtain the result
Complex queries have procedural elements International Standard
SQL1 (1986) SQL2 (SQL-92) SQL3 (pieces have started to appear)
Two components DDL statements DML statements
2
Basic SQL structure SELECT A1, A2, …, An
FROM R1, R2, …, Rm
WHERE P where
Ai are attributes from…
Ri which are relations
P is a predicate* can replace Ai’s if all attributes are to be retrieved
SQL Queries
3
What Kind of Predicates?
Simple predicates: Expression Value where Expression can be an
attribute or an arithmetic expression involving attributes = {<, >, =, <=, >=, <>} and Value can be from one of the data types
Example: Name = ‘J. Doe’ (Age + 30) >= 65
Compound predicates Simple predicates combined with logical connectives
AND, OR, NOT
4
Example Simple Queries
List names of all branches in the bank.SELECT B-nameFROM branch
List names of all customers who have an account together with their account numbers.
SELECT C-name, Acc-numberFROM deposit
Find all cities where at least one customer lives.SELECT DISTINCT CityFROM customer
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
5
Queries With Predicates
Find the names of all branches with assets greater than $2,500,000.
SELECT B-nameFROM branchWHERE Assets > 2500000
Find the names of all branches in Edmonton with assets greater than $2,500,000.
SELECT B-nameFROM branch’WHERE Assets>2500000 AND City=‘Edmonton’
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
6
Ordering the Results
Find the names and assets of all branches with assets greater than $2,500,000 and order the result in ascending order of asset values.
SELECT B-name, AssetsFROM branchWHERE Assets > 2500000ORDER BY Assets
Default is ascending order, but descending order can be specified by the DESC keyword.
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
7
Queries Over Multiple Relations
List the name and cities of all customers who has an account with balance greater than $2,000.
SELECT C-name, CityFROM customer, depositWHERE Balance > 2000AND customer.C-name = deposit.C-name
Find the name and assets of all branches which have customers living in Jasper.
SELECT B-name, AssetsFROM branch,customer,depositWHERE customer.City = ‘Jasper’ AND customer.C-name = deposit.C-nameAND deposit.B-name = branch.B-name
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
8
Queries Over Multiple Relations
List the pairs of customer names who have loans from the same bank branch.
SELECT P1.C-name, P2.C-nameFROM borrow P1, borrow P2 WHERE P1.B-name = P2.B-name
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
9
UNION, MINUS, INTERSECT Operands may be specified by
create temp relationsASSIGN TO temp-1 ASSIGN TO temp-1SELECT A1, ..., An SELECT B1, ..., Bp
FROM R1, ..., Rm FROM S1, ..., Sr
WHERE P; WHERE Q;temp-1 UNION temp-2;
use parentheses(SELECT A1, ..., An
FROM R1, ..., Rm WHERE P)
UNION(SELECTB1, ..., Bp
FROM S1, ..., Sr
WHERE Q);
Queries Involving Set Operators
10
Queries With Set Operators
Find all cities where there is either a customer or a branch.(SELECT CityFROM customer)UNION(SELECT CityFROM branch)
Find all cities that has a branch but no customers.(SELECT CityFROM branch)MINUS(SELECT CityFROM customer)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
11
Queries With Set Operators
Find all the cities that have both customers and bank branches.
(SELECT CityFROM customer)INTERSECT(SELECT CityFROM branch)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
12
Queries within the WHERE clause of an outer query
SELECTFROMWHERE OPERATOR
(SELECT FROM WHERE)
There can be multiple levels of nesting These can usually be written using other constructs
(UNION, JOIN, etc). Three operators: IN, (NOT) EXISTS, CONTAINS
Queries With Nested Structures
13
SELECTFROMWHERE A1, ..., An IN
(SELECT A1, ..., An FROM R1, ..., Rm WHERE P)
Semantics: Values of attributes A1, ..., An are found in the relation that is returned as a result of the calculation of the inner query.
Other comparison operators {<, <=, >, >=, <>} can be used in place of IN.
“IN” Construct
14
“IN” Construct Example
Find all the customers who have a deposit at some branch at which John Doe has a deposit.
SELECTC-nameFROM depositWHERE B-name IN
(SELECT B-name FROM deposit WHERE C-name = ‘John
Doe’)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
15
“IN” Construct Example
Find all the customers who have either a deposit or a loan and their cities.
SELECT C-name, CityFROM customerWHERE C-name IN (SELECT C-name
FROM deposit )OR
C-name IN (SELECT C-name FROM borrow)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
16
“IN” Construct Example
Find all the branches that have assets greater than all branches in Calgary.
SELECT B-nameFROM branchWHERE Assets > ALL
(SELECT Assets FROM branch WHERE City = ‘Calgary’)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
17
SELECTFROMWHERE (NOT) EXISTS
(SELECT *
FROM R1, ..., Rm WHERE P)
Semantics: For each tuple of the outer query, execute the inner query; if there is (no) at least one tuple in the result of the inner query, then retrieve that tuple of the outer query.
This accounts for the “there exists” type of queries
“EXISTS” Construct
18
“EXISTS” Construct Example
Find the names of customers who live in a city which has a bank branch.
SELECT C-nameFROM customerWHERE EXISTS (SELECT *
FROM branch WHERE customer.City=branch.City)
Find the names of customers who live in a city with no bank branches.
SELECT C-nameFROM customerWHERE NOT EXISTS (SELECT *
FROM branch WHERE customer.City=branch.City)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
19
“EXISTS” Construct Example
Find the names and addresses of all the customers who have no deposit in the bank.
SELECT C-name, Street, CityFROM customerWHERE NOT EXISTS
(SELECT * FROM deposit WHERE customer.C-name=deposit.C-name)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
20
“EXISTS” Construct Example
Find all the customers who have deposits in every branch of the bank. Find all customers such that there is no bank branch where they
do not have depositsSELECT *FROM customerWHERE NOT EXISTS
(SELECT B-name FROM branch WHERE NOT EXISTS
(SELECT * FROM deposit
WHERE branch.B-name=deposit.B-name AND customer.C-name = deposit.C-name))
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
21
“CONTAINS” Construct
(SELECT A1, ..., An
FROM R1, ..., Rm WHERE P)CONTAINS
(SELECT B1, ..., Bp
FROM S1, ..., Sr
WHERE Q)
Semantics: Compare the the result relations of the two queries and return TRUE if the second one is contained in the first.
22
“CONTAINS” Construct Example
Find all the customers who have a loan from every branch in Edmonton.
SELECT *FROM customerWHERE ( (SELECT B-name
FROM borrow WHERE customer.C-name=borrow.C-name)CONTAINS(SELECT B-name FROM branch WHERE City = ‘Edmonton’))
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
23
Specify a function that calculates a numeric value from a given relation. The function is usually applied to an attribute. COUNT, SUM, MAX, MIN, AVG
SELECT AggFunc(Ai), …, AggFunc(Aj)
FROM R1, ..., Rm
WHERE P
Aggregate Functions
24
Aggregate Query Examples
Find the total assets of branches in Edmonton.SELECT SUM(Assets)FROM branchWHERE City = ‘Edmonton’
Find the number of cities where John Doe has an account with any bank branch.
SELECT COUNT(DISTINCT City)FROM branch, depositWHERE branch.B-name = deposit.B-nameAND deposit.C-name = ‘John Doe’
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
25
Aggregate Query Examples
Find the names of branches which have assets greater than the average assets of all bank branches.
SELECT B-nameFROM branchWHERE Assets >
(SELECT AVG(Assets) FROM branch)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
26
Grouping Queries Group the results according to a set of attributes
SELECT Ai, …, An
FROM R1, ..., Rm
WHERE PGROUP BY Aj …, Ak
Rules: All of the attributes in the SELECT clause that are not
involved in an aggregation operation have to be included in the GROUP BY clause.
GROUP BY can have more attributes (k n)
27
Grouping Query Examples
For each city, find the names of branches.SELECT City, B-nameFROM branchGROUP BY City, B-name
For each branch at each city, list the account numbers of the customer’s loans over $100,000.
SELECT City, C-name, Acc-numberFROM customer, borrowWHERE customer.C-name = borrow.C-nameAND Amount > 100000GROUP BY City,B-name,C-name,Acc-number
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
28
Predicates on Groups Group the results according to a set of attributes if
they satisfy a certain conditionSELECT Ai, …, An
FROM R1, ..., Rm WHERE PGROUP BY Aj …, Ak
HAVING Q Rules:
Q must have a single value per group. An attribute in Q has to either appear in an aggregation
operator (in the SELECT clause) or be listed in the GROUP BY
29
Grouping Query Examples
Find the cities with more than two bank branches.
SELECT CityFROM branchGROUP BY CityHAVING COUNT(*) > 2
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
30
Grouping Query Examples
For each city where there are more than two branches, find the names of branches.
SELECT City, B-nameFROM branchWHERE City IN
(SELECT City FROM branch GROUP BY City HAVING COUNT(*) > 2)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
31
Grouping Query Examples
Find the branches where the average account balance is greater than $1,200.
SELECT B-name, AVG(Balance)FROM depositGROUP BY B-nameHAVING AVG(Balance) > 1200
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
32
Grouping Query Examples
For each branch, list the names and number of loans of customers who have more than 2 loans over $100,000.
SELECT B-name, C-name, COUNT(Acc-number)FROM borrowWHERE Amount > 100000GROUP BY B-name, C-nameHAVING COUNT(*) > 2
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
33
Update Commands
Assume R(A1, …, An)
INSERT Form
INSERT INTO RVALUES (value(A1), …, value(An))
You can explicitly specify attribute names
INSERT INTO R(Ai, …, Ak)VALUES (value(Ai), …, value(Ak))
DELETEDELETE FROM RWHERE P
34
Update Commands
Assume R(A1, …, An)
UPDATEUPDATE RSET Ai=value, …, Ak=valueWHERE P
35
Update Command Examples
Insert a new customer record for John Smith who lives on 345 Jasper Avenue, Edmonton.
INSERT INTO customerVALUES (‘John Smith’,‘345 Jasper
Avenue’,‘Edmonton’) Delete all the customers who have less than $1,000 in their
account.
DELETE FROM customerWHERE C-name IN
(SELECT C-name FROM deposit WHERE Balance < 1000)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
36
Update Command Examples
Increase by 5% the balances of customers who live in Edmonton and have a balance of more than $5,000.
UPDATE depositSET Balance = Balance*1.05WHERE Balance > 5000AND C-name IN
(SELECT C-name FROM customer WHERE City = ‘Edmonton’)
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
37
View Definition
General formCREATE VIEW V[(Ai, …, Ak)]
AS SELECT A1, …, An
FROM R1, …, Rm
WHERE P
In queries, treat view V as any base relationSELECT Aj, …, Al
FROM V
WHERE Q
Updates of views may be restricted
38
View Definition Example
Create a view jasper-customer of customers who live in Jasper.
CREATE VIEW jasper-customerAS SELECT *
FROM customerWHERE City = ‘Jasper’
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
39
View Definition Example
Create a view total-balance that gives, for each customer at each city, the number of accounts owned by the customer and the total balance.
CREATE VIEW total-balance(Name, City, Number, Total)
AS SELECT C.City,C.C-name,COUNT(Acc-number), SUM(Balance)
FROM deposit D, customer CWHERE D.C-name=C.C-nameGROUP BY C.C-name,City
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)borrow(Acc-number, C-name, B-name, Amount)
40
View Update
A view with a single defining table is updatable if the view attributes contain the primary key or some other candidate key.
Views defined on multiple tables using joins are generally not updatable.
Views defined using aggregate functions are not updatable.
41
View Management
Two strategies for view management Query modification
A view is a virtual relation Multiple queries on complex views Query processor modifies a query on a view with the view
definition
View materialization View is a materialized table in the database Incrementally update the view (update propagation)
42
Handling Nulls
Attribute values can be NULL NULL is not a constant, nor can it be used as an operand
NAME = NULL Wrong! NULL + 30 Wrong!
Operating on variables with NULL values If x is NULL
x <arithmetic op.> constant is NULL x <arithmetic op.> y is NULL
Comparing a NULL value with any other value returns UNKNOWN (three-valued logic).
43
Outer Join
Ensures that tuples from one or both relations that do not satisfy the join condition still appear in the final result with other relation’s attribute values set to NULL
Only available in SQL2 via join expression R [NATURAL] JOIN S [ON <condition>]
Alternatives R [NATURAL] FULL OUTER JOIN S [ON <condition>] R [NATURAL] LEFT OUTER JOIN S [ON <condition>] R [NATURAL] RIGHT OUTER JOIN S [ON <condition>]
44
SQL DDL Statements
Create schemaCREATE SCHEMA Schema_Name AUTHORIZATION User_Name
Create table Specify a new relation scheme General form
CREATE TABLE <Table_Name>
(Attribute_1 <Type>[DEFAULT <value>][<Null constraint>],
Attribute_2 <Type>[DEFAULT <value>][<Null constraint>],
…
Attribute_n <Type>[DEFAULT <value>][<Null constraint>],
[<Constraints>])
45
Example
Givenbranch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Account-number, C-name, B-name,
Balance) Definition of branch
CREATE TABLE branch( B_name CHAR(15),
Address VARCHAR(20),City CHAR(9),Assets DECIMAL(10,2)DEFAULT 0.00);
Assume customer is defined similarly
46
Allowable Data Types Numeric
INT, SHORTINT REAL (FLOAT), DOUBLE PRECISION DECIMAL(i,j)
Character-string CHAR(n), VARCHAR(n)
Bit-string BIT(n), BIT VARYING(n)
Date YYYY-MM-DD
Time HH:MM:SS
Timestamp both DATE and TIME fields plus a minimum of six positions for
fractions of seconds
47
User-Defined Types
Create a DOMAINCREATE DOMAIN <domain_name> AS <primitive_type>
ExampleCREATE DOMAIN Gender AS CHAR(1)
Generally useful only for easy modification of type specification.
The value can be defaulted by the DEFAULT specification
48
NOT NULL specifies that an attribute cannot contain null values should be specified for all primary keys
PRIMARY KEY designates a set of columns as the table’s primary key
UNIQUE specifies the alternate keys
FOREIGN KEY designates a set of columns as the foreign key in a referential
constraint
Key Constraints
49
Example
Givenbranch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Account-number, C-name, B-name,
Balance)
Enhance the previous definition of branch:CREATE TABLE branch
( B_name CHAR(15)NOT NULL,
Address VARCHAR(20),
City CHAR(9),
Assets DECIMAL(10,2)DEFAULT 0.00,
PRIMARY KEY (B_name));
50
REFERENTIALLY TRIGGERED ACTION Referential integrity: The primary key of one relation appears as an
attribute (foreign key) of another relation. Example:
customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance) Deletion or update of primary key tuple requires action on the foreign
key tuple. Specify constraint on delete or update. How to manage?
reject cascade: (on delete) automatically remove foreign keys if a referenced key is
removed or (on update) change the foreign key value to the new value of the referenced key
set null set default
Referential Constraints
51
Example
branch (B-name, Address, City, Assets)customer(C-name, Street, City)deposit(Acc-number, C-name, B-name, Balance)
Definition of depositCREATE TABLE deposit( Acc-number CHAR(9)NOT NULL,
C-name VARCHAR(15),B-name VARCHAR(15),Balance DECIMAL(10,2)DEFAULT 0.00,
PRIMARY KEY (Acc-number),FOREIGN KEY (C-name) REFERENCES customer(C-name)ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (B-name) REFERENCES branch(B-name));
52
Attribute & Domain Constraints
CHECK specifies the condition that each row has to satisfy
Enhance the previous definition of branch:CREATE TABLE branch(B_name CHAR(15)NOT NULL,
Address VARCHAR(20), City CHAR(9), Assets DECIMAL(10,2)DEFAULT 0.00
CHECK (Assets >= 0),PRIMARY KEY (B_name));
Can be specified on domains as domain constraintsCREATE DOMAIN Gender AS CHAR(1) CHECK (VALUE IN (‘F’, ‘M’));
53
Tuple Constraints Use CHECK command and specify condition The condition is checked every time a tuple is inserted or
updated and the action rejected if the condition is false Example
CREATE TABLE deposit(Acc-number CHAR(9)NOT NULL,
C-name VARCHAR(15), B-name VARCHAR(15), Balance DECIMAL(10,2)DEFAULT 0.00,
PRIMARY KEY (Acc-number),FOREIGN KEY (C-name) REFERENCES customer(C-name)ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (B-name) REFERENCES branch(B-name))CHECK (NOT(Acc-number<36300) OR Balance>100000);
54
Assertions
Global constraints of the form CREATE ASSERTION name CHECK (condition)
Example: No branch can have more than two customers who have more than 2 loans over $100,000.
CREATE ASSERTION deposit CHECK
(NOT EXISTS
(SELECT COUNT(Acc-number) FROM borrow WHERE Amount > 100000 GROUP BY B-name, C-name HAVING COUNT(*) > 2));
55
Other SQL DDL Commands
DROP SCHEMA Delete an entire schema CASCADE: delete all the tables in the schema RESTRICT: delete only if empty
DROP TABLE RESTRICT: delete only if not referenced in a
constraint
ALTER TABLE change definition
56
Embedded SQL
Ability to access a database from within an application program.
Issues Interface
The same as ad hoc SQL but with EXEC SQL command Using query results within the application program
Define shared variables using the format allowed in host language Shared variables can be used in INSERT, DELETE, UPDATE as well
as regular queries and schema modification statements Retrieval queries require care
If the result relation has a single tuple: Each value in the SELECT statement requires a shared variable
If the result relation has a set of tuples: Cursor has to be defined
57
Example for Update
Consider an example in which will transfer $50,000 from a customer’s account in one branch to another customer’s account in another branch.
…EXEC SQL BEGIN DECLARE SECTION;int cname1, cname2; /* two customer names */char bname1[15], bname2[15]; /* branch names */int amount; /* amount to be transferred */
EXEC SQL END DECLARE SECTION;/* Code (omitted) to read the customer and branch names and amount */
EXEC SQL UPDATE deposit SET Balance = Balance + :amount WHERE C-name = :cname2 AND B-name = :bname2;
EXEC SQL UPDATE depositSET Balance = Balance - :amountWHERE C-name = :cname1 AND B-name = :bname1;
…
58
Retrieval Queries
If the result is only only one tuple, follow the same approach. Example: For a given branch, retrieve the names and number of
loans of customers who have more than 2 loans over $100,000.…EXEC SQL BEGIN DECLARE SECTION;
char bname[15], cname[15]; /* branch & customer name */int no-acct; /* number of accounts */
EXEC SQL END DECLARE SECTION;…/* Code (omitted) to get the branch name from use */EXEC SQL SELECT C-name, COUNT(Acc-number)INTO :cname, :no-acctFROM borrowWHERE B-name = :bname AND Amount > 100000GROUP BY C-nameHAVING COUNT(*) > 2;/* Code (omitted) to print the total balance */…
59
More General Retrieval Queries…EXEC SQL BEGIN DECLARE SECTION;
<shared variable declarations>EXEC SQL END DECLARE SECTION;…EXEC SQL DECLARE <cursor-name> [options] CURSOR FOR
<query> [options]…EXEC SQL OPEN <cursor-name>…while(condition) {
EXEC SQL FETCH FROM <cursor-name> INTO <shared-variable(s)>if(tuple exists) process itelse break
}EXEC SQL CLOSE <cursor-name>…
60
Cursors Use when the embedded SQL query is expected to return a relation with
more than one tuple Think about it almost as a pointer to successive tuples in the result
relation General form
EXEC SQL DECLARE <cursor-name> [INSENSITIVE][SCROLL] CURSOR FOR <query>ORDER BY <attribute(s)>[FOR READ ONLY];
INSENSITIVE: The cursor is insensitive to changes in the relations referred to in the query while the cursor is open.
SCROLL: Allows the subsequent FETCH command to retrieve tuples other than the default which is moving forward. FETCH [NEXT|PRIOR|FIRST|LAST|RELATIVE [+|-]n|ABSOLUTE [+|-]n]
ORDER BY: Orders the results of the query according to some attribute(s).FOR READ ONLY: Ensures that accesses to the underlying relation(s) via this cursor will not change the
relation.
61
Cursor Example For each branch, retrieve the names and number of loans of customers
who have more than 2 loans over $100,000.…EXEC SQL BEGIN DECLARE SECTION;
char bname[15], cname[15]; /* branch & customer name */int no-acct; /* number of accounts */
EXEC SQL END DECLARE SECTION;…EXEC SQL DECLARE loans CURSOR FOR
SELECT B-name, C-name, COUNT(Acc-number)FROM borrowWHERE B-name = :bname AND Amount > 100000GROUP BY C-nameHAVING COUNT(*) > 2;
…EXEC SQL OPEN loans;…while(1) {
EXEC SQL FETCH FROM loans INTO :bname, :cname, :no-acctif(strcmp(SQLSTATE, “02000”) then breakelse print the info
}EXEC SQL CLOSE loans…