sql by sayantini
Post on 14-Apr-2018
216 Views
Preview:
TRANSCRIPT
-
7/27/2019 SQL by Sayantini
1/29
SQL
-
7/27/2019 SQL by Sayantini
2/29
BACKGROUND
The language originally called sequel in the early1970s , has evolved since then, and its name haschanged to SQL (Structured Query Language).
SQL has several parts# Data Definition language (DDL)# Interactive data-manipulation language (DML)
# Embedded DML
# View definition
# Authorization# Integrity
# Transaction control
-
7/27/2019 SQL by Sayantini
3/29
BANKING ENTERPRISE
The enterprise used in the examples in this chapter is thebanking enterprise with the following schemas :
Branch-schema =(branch-name, branch-city, assets)Customer-schema =(customer-name, customer-street, customer-city)
Loan-schema=(branch-name, loan-number ,amount)
Borrower-schema=(customer-name,loan-number)
Account-schema=(account-number,branch-name, balance)
Depositor-schema=(customer-name, account-number)
-
7/27/2019 SQL by Sayantini
4/29
BASICSTRUCTUREThe basic structure of an SQL expression consists of three clauses : select , from
and where
# Theselect clause corresponds toprojection operation of the relational algebra.
# The from clause corresponds to cartesian product operation of the relational
algebra.
# The whereclause corresponds to the selection predicate of the relationalalgebra.
A typical SQL query has the form
select A1 ,A2..,An
from r1,r2,rmwhere P
Here each Ai = attribute , ri = relation and P = predicate
Equivalent relational algebra expression
A1,A2,.An( p (r1*r2*.*rm))
-
7/27/2019 SQL by Sayantini
5/29
SELECT Clause
It is used to list the attributes desired in the result of
a query
Example:Find the names of all branches in the loanrelation
selectbranch-name
from loan
The result will list each branch-name once for every tuple in
which it appears in the loan relation.in those cases where we
want to eliminate duplicates ,we insert the keyword distinct
afterselect.
-
7/27/2019 SQL by Sayantini
6/29
WHERE ClauseThe where clause consists of a predicate involving attributes of the
relations that appear in the from clause
Consider the query Find all the loan numbers for loans
made at the Perryridge branch with loan amounts
greater than $1200.
selectloan-number
from loan
where branch-name=Perryridge and amount >1200
SQL uses the logical and ,orand not rather than the mathematical
symbols , and in the where clause.SQL also uses comparisonoperators like < ,>, = ,= and to compare strings and
arithmetic expressions as well as data types.
-
7/27/2019 SQL by Sayantini
7/29
SQL includes abetween comparison operator to simplify
whereclauses that specify that a value be less than or equal tosome value and greater than or equal to some other value. If we
wish to find the loan numbers of those loans with loan amounts
between $90000 and $100000 we can use the between
comparison to writeselect loan-number
from loan
where amountbetween 90000 and 100000
-
7/27/2019 SQL by Sayantini
8/29
FROM ClauseIt lists the relations to be scanned in the evaluation of the expression.
Consider the queryFor all customers who have a loan from thebank ,find their names and loan numbers.
selectdistinct customer-name , borrower.loan-number
fromborrower , loan
whereborrower.loan-number=loan.loan-number
Let us consider another queryFind the names and loan numbers of
all customers who have a loan at the Perryridge branch
select distinct customer-name,borrower.loan-number
fromborrower , loan
where borrower.loan-number=loan.loan-numberand
branch-name=Perryridge
-
7/27/2019 SQL by Sayantini
9/29
The RENAME Operation
SQL provides a mechanism for renaming both relations and
attributes. It uses the as clause, taking the form :
old-name as new-nameThe as clause can appear in both the select and from clauses.
Consider the queryFind the names of all branches that have assets greater than
atleast one branch located in Brooklyn.
select distinct T.branch-name
from branch as T , branch as S
where T.assets > S.assets and S.branch-city =Brooklyn
Observe we could not use branch.assets since it would not be clear
which reference to branch is intended.
-
7/27/2019 SQL by Sayantini
10/29
String OperationThe most commonly used operations on strings is pattern matching
using the operator like.We describe patterns using two special
characters :
Percent(%) :The % character matches any substring
Underscore( _ ): The _character matches any character.
Patterns are case sensitive.
Consider the query Find the names of all customers whose street
address includes the substring `Main`.
select customer-namefrom customer
where customer-street like%Main%
-
7/27/2019 SQL by Sayantini
11/29
Ordering the Display of Tuples
The order by clause causes the tuples in the result of a query to
appear in sorted order.To list in alphabetic order all customers
who have a loan at the Perryridge bank , we write
select distinct customer-name
from borrower , loanwhere borrower.loan-number =loan.loan-number
and branch-name = Perryridge
order by customer-nameBy default, the order by clause lists items in ascending order.
To specify the sort order , we may specify desc for descending
order or asc for ascending order.
-
7/27/2019 SQL by Sayantini
12/29
Suppose that we wish to list the entire loan relation in
descending order of amount and if several loans have the
same amount, we order them in ascending order by loan
number.
We express this query as
select *from loan
orderby amount desc, loan-numberasc
-
7/27/2019 SQL by Sayantini
13/29
SET Operations
The operations union ,intersect and except operate on relations.The relations participating in the operations must be compatible
that is ,they must have the same set of attributes.We will
construct queries involving the union, intersect and except
operations of two sets : the set of all customers who have an
account at the bank, which can be derived
select customer-name
from depositor
And the set of customers who have a loan at thebank,which can be derived by
select customer-name
from borrower
-
7/27/2019 SQL by Sayantini
14/29
UNION OperationLet us consider the query Find all customers having a loan,
an account , or both at the bank.
( select customer-name
from depositor)
union
( select customer-name
fromborrower)The union operation automatically eliminates duplicates.if we
want to retain all duplicates we must write union all in place of
union.
-
7/27/2019 SQL by Sayantini
15/29
INTERSECT OperationLet us consider the query Find all customers who have
both a loan and a account at the bank
(select distinct customer-name
from depositor)
intersect
(select distinct customer-name
from borrower)The intersect operation automatically eliminates duplicates.
Thus in the previous example if the customer say Jones has
several accounts and loans at the bank, then Jones will appear
only once in the result.
If we want to retain duplicates, we must write intersect all in
place ofintersect .
-
7/27/2019 SQL by Sayantini
16/29
EXCEPT Operation
Let us consider the query Find all customers who have anaccount but no loan at the bank
(select customer-name
from depositor)
except(select customer-name
from borrower)
The except operation also automatically eliminates
duplicates.if we want to retain all duplicates we must writeexcept all in place of except in the previous example.
-
7/27/2019 SQL by Sayantini
17/29
AGGREGATE Functions
Aggregate functions are functions that take a collection of values
as input and return a single value.SQL offers five built in
aggregate functions:
Average :avg
Minimum:min
Maximum:max
Total:sum
Count:count
The input to sum and avg must be a collection of numbers, but
other operators can operate on collections of nonnumeric data
types such as strings.
-
7/27/2019 SQL by Sayantini
18/29
Let us consider the query Find the average account balance at
the Perryridge branch
select avg (balance)
from account
where branch-name= Perryridge
The result of this query is a relation with a single attribute,
containing a single row with a numerical value corresponding to
the average balance at the Perryridge branch.
We can apply the aggregate functions to a group of set of tuples,
we specify this using the group by clause .the attribute or
attributes given in the group by clause are used to form groups.
Tuples with the same value on all attributes in the group by
clause are placed in one group.
-
7/27/2019 SQL by Sayantini
19/29
Let us consider the query Find the number of depositors for each branch
select branch-name, count (distinct customer-name)
from depositor,account
where depositor.account-number=account.account-number
group by branch-name
At times it is useful to state a condition that applies to groups rather than to tuples.toexpress such query we use the having clause.For example we might be interested in
only those branches where the average account balance is more than $1200.
select branch-name, avg (balance)
from account
group by branch-name
having avg(balance) >1200
-
7/27/2019 SQL by Sayantini
20/29
Nested SubqueriesA subquery is a select-from-where expression that is nested with
another query.
The in connective tests for set membership, where the set is a
collection of values produced by a select clause.
Consider the query Find the customers who have both a loan and
an account at the bank
select distinct customer-name
from borrower
where customer-name in (select customer-name
from depositor)
-
7/27/2019 SQL by Sayantini
21/29
The not in connective tests for the absence of set membership.
Consider the query Find all customers who have aloan at the
bank but do not have an account select distinct customer-name
from borrower
where customer-name not in (select customer-name
from depositor)
The phrase greater than at least one is represented in SQL by
>some.Consider the query Find the names of all branches that have
assets greater than those of at least one branch located in
Brooklyn
-
7/27/2019 SQL by Sayantini
22/29
select branch-name
from branch
where assets > some (select assetsfrombranch
where branch-city=Brooklyn)
The construct >allcorresponds to the phrase greater than all.Consider the query Find the names of all branches that have assets greater
than that of each branch in Brooklyn .
select branch-name
frombranch
where assets >all (select assets
from branch
where branch-cit = Brookl n
-
7/27/2019 SQL by Sayantini
23/29
The exists construct returns true if the argument subquery is nonempty.
Consider the query Find all customers who have an account and a loan
at the bank
select customer-name
fromborrower
where exists (select *
from depositor
where depositor.customer-name = borrower.customer-name)
The nonexistence of tuples in a subquery is tested by using not exists.
Consider the query Find all customers who have an account at all the
branches located in Brooklyn
-
7/27/2019 SQL by Sayantini
24/29
select distinct S.customer-name
from depositoras S
where not exists ((select branch-name
frombranch
where branch-city = Brooklyn)except
(select R.branch-name
from depositoras T, account as Rwhere T.account-number = R.account-number
and S.customer-name=T.customer-name))
-
7/27/2019 SQL by Sayantini
25/29
VIEWSTo define a view we must give the view a name,and must state the query that
computes the view.The form of the create view command is
create view v as Consider the view consisting of branch names and the names of customers who
either have an account or a loan at that branch.here we want the view to be called
all-customer
create view all-customeras
(select branch-name, customer-name
from depositor , account
where depositor.account-number =account.account-number)
union
( select branch-name, customer-name
fromborrower , loan
where borrower.loan-number=loan.loan-number)
-
7/27/2019 SQL by Sayantini
26/29
UPDATE OF A VIEW
Consider the following view definition
create view branch-loan as
select branch-name ,loan-number
from loan
We can insert into this view as follows
insert into branch-loan
values(Perryridge , L-307)
This insertion is represented by an insertion into the relation
loan, since loan is the actual relation from which the view
branch-loan is constructed.Thus we have null value for amount.
Thus the insert results in the insertion of the tuple
Perr rid e L-307 null
O l i
-
7/27/2019 SQL by Sayantini
27/29
JOINED Relations
Branch name Loan number amount
Downtown L-170 3000
Redwood L-230 4000
Perryridge L-260 1700
Customer-name Loan-number
Jones L-170
Smith L-230
Hayes L-155
loan borrower
We illustrate join operations using the relations loan and borrower
Branch-name Loan-number amount Customer-name Loan-number
Downtown L-170 3000 Jones L-170
Redwood L-230 4000 Smith L-230
Result of loan inner join borroweron
loan.loan-number =borrower.loan-number
-
7/27/2019 SQL by Sayantini
28/29
Branch-name Loan-number amount Customer-name Loan-number
Downtown L-170 3000 Jones L-170
Redwood L-230 4000 Smith L-230
Perryridge L-260 1700 null null
Result of loan left outer join borroweron
loan.loan-number=borrower.loan-number
Branch-name Loan-number amount Customer-name
Downtown L-170 3000 Jones
Redwood L-230 4000 Smith
Result of loan natural inner join borrower
-
7/27/2019 SQL by Sayantini
29/29
Branch-name Loan-number amount Customer-name
Downtown L-170 3000 Jones
Redwood L-230 4000 Smith
null L-155 null Hayes
Result ofnatural right outer join borrower
Branch-name Loan-number amount Customer-name
Downtown L-170 3000 Jones
Redwood L-230 4000 Smith
Perryridge L-260 1700 null
null L-155 null Hayes
Result of loan full outer join borrowerusing (loan-number)
top related