1 / 63

Bank Schema

Learn SQL query examples for banking databases including finding loans, customers, and employees using various operations and techniques.

mccloy
Download Presentation

Bank Schema

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Bank Schema branch (BranchName, BranchCity, assets) customer (CustomerName, CustomerStreet, CustomerCity) account (AccountNumber, BranchName, balance) loan (LoanNumber, BranchName, amount) depositor (CustomerName, AccountNumber) borrower (CustomerName, LoanNumber)

  2. Where clause • To find all loan number for loans made at the London branch with loan amounts greater than $1200.select LoanNumberfrom loanwhere BranchName = ‘London’ and amount > 1200 Comparison results can be combined using the logical connectives and, and or.

  3. Dealing with Ambiguous Attribute Names I • Find the name, loan number and loan amount of all customers having a loan at the London branch. select CustomerName, borrower.LoanNumber, amount from borrower, loan where borrower.LoanNumber = loan.LoanNumber andBranchName = ‘London’

  4. Alias For each employee, retrieve the employee’s name, branch name and the name of his or her immediate supervisor. • Select E.name, E.BranchName,S.name From employee as E, employee as S where E.supervisorSSN = S.SSN • Select E.name, E.BranchName,S.name From employee E, employee S where E.supervisorSSN = S.SSN

  5. Rename operation • Find the name, loan number and loan amount of all customers; rename the column name LoanNumber as loan-id. • select CustomerName, borrower.LoanNumber as loan-id, amountfrom borrower, loanwhere borrower.LoanNumber = loan.LoanNumber

  6. Using Asterisk • An asterisk in the select clause denotes “all attributes” Select *From loan

  7. Arithmetic Operators • The query: selectloan-number, branch-name, amount  100from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100.

  8. Tables as Sets in SQL SQL allows duplicates in relations as well as in query results. • the keyword distinct specifies that duplicates be removed. • Find the names of all distinct branches in the loan relations. select distinct BranchNamefrom loan • The keyword all specifies that duplicates not be removed. select allBranchNamefrom loan

  9. String Comparisons I • SQL includes a string-matching operator for comparisons on character strings. Patterns are described using two special characters: • percent (%). The % character matches any substring. • underscore (_). The _ character matches any character.

  10. String Comparisons I • Find the names of all customers whose street begin with “Main” and followed by three characters select CustomerNamefrom customerwhere CustomerStreet like ‘%Main_ _ _’

  11. Between comparison • SQL includes a between comparison operator • E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, $90,000 and $100,000) • select LoanNumberfrom loanwhere amountbetween 90000 and 100000

  12. Order • List in alphabetic order the names of all customers having a loan in London branch select CustomerNamefrom borrower, loanwhere borrower LoanNumber = loan.LoanNumber and BranchName = ‘London’order by CustomerName

  13. Nested Sub queries • SQL provides a mechanism for the nesting of subqueries. • A subquery is a select-from-where expression that is nested within another query.

  14. Example Nested Queries (I) • Find the name and loan number of all customers having a loan at the London branch. select CustomerName, LoanNumber from borrower where LoanNumber in (select LoanNumber from Loan where BranchName = ‘London’)

  15. Example Nested Queries (II) • Find all customers who have a loan at the bank but do not have an account at the bank select distinct CustomerName from borrower where CustomerName not in (select CustomerName from depositor)

  16. Example Nested Queries (III) • Find all customers who have a loan at a branch but do not have an account at the same branch. select CustomerName from depositor as D, account as A where D. AccountNumber = A. AccountNumberand BranchName not in (select BranchName from loan L, borrower B where D.CustomerName = B.CustomerName and l.LoanNumber = B.LoanNumber)

  17. Example Nested Queries (IV) • Find all customers who have a loan at a branch but do not have an account at the same branch. select CustomerName from depositor as D, account as A where D.AccountNumber = A. AccountNumberand not Exists (select * from loan L, borrower B where D.CustomerName = B.CustomerName and L.LoanNumber = B.LoanNumber and L.branchName = A.BranchName)

  18. Example Nested Querie (IV) • Find the names of all branches that have greater assets than all branches located in Brooklyn. • select branch-namefrom branchwhere assets > all (select assetsfrom branchwhere branch-city = ‘Brooklyn’)

  19. Two level Nested Queries (I) • Find all customers who have an account in all cities where the bank has any branches. Select CustomerName From Customer C Where not exists (select BranchCity from Branch B where not exist (select AccountNumber from account A, depositor D Where A.AccountNumber = D.AccountNumber and B.BranchName = A.BranchName and C.CustomerNumber = D.CustomerNumber)

  20. Two level Nested Queries (II) • Find all customers who have an account in all cities where the bank has any branches. Select CustomerName From Customer C Where not exists (select * from Branch B where not exist (select * from account A, depositor D Where A.AccountNumber = D.AccountNumber and B.BranchName = A.BranchName and C.CustomerNumber = D.CustomerNumber)

  21. Set Operations • The set operations union, intersect, and except operate on relations and correspond to the relational algebra operations 

  22. Set operation: union • Find all customers who have a loan, an account, or both: (selectcustomer-name from depositor)union (selectcustomer-name from borrower)

  23. Set operation: Intersect • Find all customers who have both a loan and an account (selectcustomer-name from depositor) intersect (selectcustomer-name from borrower)

  24. Set operation: Except (I) • Find all customers who have an account but no loan. (selectcustomer-name from depositor) Except (selectcustomer-name from borrower)

  25. Set operation: Except (II) • Find all customers who have an account in all cities where the bank has any branches. Select CustomerName From Customer C Where not exists (select BranchCity from Branch except (select BranchCity from account A, depositor D, Branch B Where C.CustomerNumber = D.CustomerNumber and A.AccountNumber = D.AccountNumber and B.BranchName = A.BranchName)

  26. Null Values • The predicate is null can be used to check for null values. • E.g. Find all loan number which appear in the loan relation with null values for amount. select loan-numberfrom loanwhere amount is null • The result of any arithmetic expression involving null is null • E.g. 5 + null returns null

  27. Renaming attributes • SQL can rename any attribute that appears in the result of query Example: selectcustomer-name as Name from depositor

  28. Joined Table (I) • Find the name, loan number and loan amount of all customers having a loan at the London branch. select CustomerName, borrower.LoanNumber, amount from (borrower as B JOIN loan as L On B.LoanNumber = L.LoanNumber ) where BranchName = ‘London’

  29. Joined Table (II) Same example can be written as: select CustomerName, borrower.LoanNumber, amount from (borrower NATURAL JOIN loan) where BranchName = ‘London’

  30. Inner and Out join • The default type of join is an inner join, where a tuple is included in the result only if a matching tuple exists in the other relation. For example, in the following example, only customer who has some loans are included in the result. Select E.Name as EmployeeName, S.Name as SupervisorName From (Employee as E JOIN Employee as S on E.SupervisorSSN = S.SSN)

  31. Out Join • The LEFT OUT JOIN operation keeps every tuple in the left relation if no matching tuple is found in the right relation S, then the attributes of S in the join results are filled with null values. • Example: Select E.Name as EmployeeName, S.Name as SupervisorName From (Employee as E LEFT OUT JOIN Employee as S on E.SupervisorSSN = S.SSN)

  32. Aggregate Functions • These functions operate on the multiset of values of a column of a relation, and return a value avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values

  33. Aggregate Functions (I) • Find the average account balance at the Perryridge branch. select avg (balance)from accountwhere branch-name = ‘Perryridge’

  34. Aggregate Functions (II) • Find the number of tuples in the customer relation. select count (*)from customer

  35. Aggregate Functions (III) • Find the number of depositors in the bank. select count (distinct customer-name)from depositor

  36. Aggregate Functions – Group By • Find the number of depositors for each branch. select branch-name, count (distinctcustomer-name)from depositor, accountwhere depositor.account-number = account.account-numbergroup by branch-name • Note: Attributes in select clause outside of aggregate functions must appear in group by list

  37. Aggregate Functions – Having Clause • Find the names of all branches where the average account balance is more than $1,200. select branch-name, avg (balance)from accountgroup by branch-namehaving avg (balance) > 1200 • Note: predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups

  38. Modification of the Database – Deletion (I) • Delete all account records at the Perryridge branch delete from accountwhere branch-name = ‘Perryridge’

  39. Modification of the Database – Deletion (II) • Delete all accounts at every branch located in Needham city. delete from accountwhere branch-name in (select branch-namefrom branchwhere branch-city = ‘Needham’)delete from depositorwhere account-number in (select account-numberfrom branch, accountwhere branch-city = ‘Needham’and branch.branch-name = account.branch-name)

  40. Modification of the Database – Insertion (I) • Add a new tuple to account insert into accountvalues (‘A-9732’, ‘Perryridge’,1200)or equivalentlyinsert into account (branch-name, balance, account-number)values (‘Perryridge’, 1200, ‘A-9732’)

  41. Modification of the Database – Insertion (II) • Add a new tuple to account with balance set to null insert into accountvalues (‘A-777’,‘Perryridge’, null)

  42. Modification of the Database – Insertion (III) • Provide as a gift for all loan customers of the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account insert into accountselect loan-number, branch-name, 200from loanwhere branch-name = ‘Perryridge’insert into depositorselect customer-name, loan-numberfrom loan, borrowerwhere branch-name = ‘Perryridge’ and loan.account-number = borrower.account-number • The select from where statement is fully evaluated before any of its results are inserted into the relation (otherwise queries like insert intotable1 select * fromtable1 would cause problems

  43. Modification of the Database – Updates • Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. • Write two update statements: update accountset balance = balance  1.06where balance > 10000 update accountset balance = balance  1.05where balance  10000 • The order is important

  44. Views • Provide a mechanism to hide certain data from the view of certain users. To create a view we use the command: create view v as<query expression> • where: • <query expression> is any legal expression • The view name is represented by v

  45. View: example • A view consisting of branches and their customers • create view all-customer as(select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) • union(select branch-name, customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number)

  46. View: example (cont.) • Find all customers of the Perryridge branch • select customer-namefrom all-customerwhere branch-name = ‘Perryridge

  47. DDL Allows the specification of not only a set of relations but also information about each relation, including: • The schema for each relation. • The domain of values associated with each attribute. • Integrity constraints • The set of indices to be maintained for each relations. • Security and authorization information for each relation. • The physical storage structure of each relation on disk.

  48. Domain Types in SQL • char(n). Fixed length character string, with user-specified length n. • varchar(n). Variable length character strings, with user-specified maximum length n. • int. Integer (a finite subset of the integers that is machine-dependent). • smallint. Small integer (a machine-dependent subset of the integer domain type). • numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point. • real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision. • float(n). Floating point number, with user-specified precision of at least n digits. • Null values are allowed in all the domain types. Declaring an attribute to be not null prohibits null values for that attribute.

  49. Date/Time Types in SQL (Cont.) • date. Dates, containing a (4 digit) year, month and date • E.g. date ‘2001-7-27’ • time. Time of day, in hours, minutes and seconds. • E.g. time ’09:00:30’ time ’09:00:30.75’ • timestamp: date plus time of day • E.g. timestamp ‘2001-7-27 09:00:30.75’

  50. Create domain • create domain construct in SQL-92 creates user-defined domain types create domain person-name char(20) not null

More Related