SQL

1. SQL By Wen-Lin Chang

2. Background • IBM developed the original version of SQL at San Jose Research Laboratory • SQL has clearly established itself as the standard relational-database language • Evolved as The Sequel language, its name has changed to SQL (Structured Query Language)

3. Basic Structure • Select: It is used to list the attributes desired in the result of a query. • From: It lists the relations to be scanned in the evaluation of the express. • Where: It consists of predicate involving attributes of the relations that appear in the from clause.

4. A typical SQL query select A1, A2,…,An from r1,r2,…,rn where P A represents attribute r represents relation P represents predicate “Find all loan number for loans made at the San Jose branch with loan amounts greater than $1200.” select loan-number from loan Where branch-name = ‘San Jose’ and amount >1200

5. If we want to force the elimination of duplicates, we insert the keyword distinct after select select distinct branch-name from loan • We also can use tge keyword all to specify exlicitly that duplicates are not removed select all branch-name from loan

6. SQL uses the logical connective and, or, not in the where clause select loan-number from loan where amount <= 10000 and amount >= 90000

7. String Operations The most commonly used operation strings is pattern matching using the operator like Precent (%):The % character matchs any substing. Underscore(_): The _character matchs any character.

8. We consider the following example: ‘San Jose%’ match any string beginning with “ San Jose”. ‘%idge%’ matches any string containing “idge” ‘---’ match any string of exactly three characters. ‘---%’ match any string of at least three characters.

9. Find the names of all customers whose street address address include the substring ‘Main’. select customer-name from customer where customer-street like ‘%Main%’

10. The order by clause cause the tuples in the result of a query to appear in sortf order. To list in alphabetic order all customers who have a loan at the San Jose branch. select distinct customer-name from borrow, loan where borrow.loan-number = loan.loan- numebr and branch-name =‘San Jose’ order by customer-name

11. Set Operations • The Union Operation: To find all customer having a loan, an account, or both at the bank. (select customer-name from depositor union (select customer-name from borrower)

12. The Intersect Operation: To find all customers who have both a loan and an account at the back. (select distinct customer-name from depositor) intersect (select distinct customer-name from borrower)

13. The Except Operation: To find all customers who have an account but no loan at the bank. (select distinct customer-name from depositor) except (select customer-name from borrower)

14. Aggregate Function • Aggregate function are functions that take a collection of values as input and return a single value Five built-in aggregate function: • Average: avg • Minimum: min • Maximum: max • Total: sum • Count: count

15. “Find the average account balance at the San Jose branch.” select avg (balance) from account where branch-name = ‘San Jose’

16. Null Values • SQL allows the use of null values to indicate absence of information about the value of an attribute. select loan-number from loan where amount is null

17. Modification of the database • Deletion A delete request is expressed in much the same way as q query. We can delete only whole tuples. delete from r where P Delete all account tuples in the San Jose branch delete from account where branch-name = ‘San Jose’

18. Insertion We wish to insert that fact that there is an account A-9732 at the San Jose branch and that is has a balance of $1200. insert into account values (‘A-9732’, ‘San Jose’, 1200)

19. Updates We may wish to change a value in a tuple without changing all value in the tuple, then we can use update statement. Suppose that annual interest payment are being made, and all balance are to be increased by 5 percent. update account set balance = balance * 1.05

20. The End