1 / 26

SQL Structured Query Language

SQL Structured Query Language. Meizhen Huang. Content (4.1 – 4.4). Background Parts of SQL Basic Structure Set Operations Aggregate Functions. Background. SQL – Structured Query Language Developed by IBM in the 1970 ’ s, originally called Sequel

olencia
Télécharger la présentation

SQL Structured Query Language

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. SQLStructured Query Language Meizhen Huang

  2. Content (4.1 –4.4) • Background • Parts of SQL • Basic Structure • Set Operations • Aggregate Functions

  3. Background • SQL – Structured Query Language • Developed by IBM in the 1970’s, originally called Sequel • The standard relational-database language • Uses relational-algebra and relational-calculus constructs

  4. Parts of SQL • DDL: commands for defining relation schemas, deleting relations, and modifying relation schemas. • DML: based on the relational algebra and the tuple relational calculus. • Integrity: commands for specifying integrity constraints for the data in the DB. • Authorization: commands for specifying access rights to relations and views.

  5. Relations using in Examples • Branch-schema = (branch-name, branch-city, assets) • Customer-schema = (customer-name, customer-street, customer-city) • Loan-schema = (loan-number,branch-name, amount) • Borrower-schema = (customer-name, loan-number) • Account-schema = (account-number, branch-name, balance) • Depositor-schema = (customer-name, account-number)

  6. Basic Structure • Basic structure of SQL includes three clauses: select, from and where. • A typical SQL has the form select A1, A2, …, An from r1,r2,…,rm where P Ai – an attribute ri – a relation p – a predicate • This query is equivalent to the relational algebra expression: A1, A2, ..., An (P (r1 x r2 x ... x rm))

  7. select • select – projection in RA • select without elimination of duplicates “Find the names of all branches in the loan relation.” selectbranch-nameselectall branch-name fromloan fromloan • select with elimination of duplicates selectdistinctbranch-name fromloan

  8. select • “*”can be used to denote “ all attributes”. select* fromloan • The select clause may also contain arithmetic expressions involving the operators +, -, *, and / operating on constants or attributes of tuples. select loan-number, branch-name, amount*100 fromloan

  9. where • where – selection predicate of RA • e.g. “Find all loan numbers for loans made at the Perryridge branch with loan amounts greater that $1200.” selectloan-number from loan wherebranch-name = ‘Perryridge’and amount > 1200 Note: SQL uses and, or, and notinstead of , v, andin the where clause. • e.g., selectloan-number fromloan whereamountbetween 90000 and100000 Note: similarly, we can use the not between comparison operator.

  10. from • from – Cartesian-product in RA. • “Find the Cartesian product borrower x loan” select from borrower, loan

  11. from • SQL uses relation-name.attribute-name, as does relational algebra, to avoid ambiguity • “Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch.” select customer-name, borrower.loan-number, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-numberand branch-name = ‘Perryridge’

  12. Rename • Rename can be operated on both relations and attributes. old-nameasnew-name • e.g., selectcustomer-name, borrower.loan-number asloan-id, amount from borrower, loan whereborrower.loan-number = loan.loan-number

  13. Tuple Variables • Tuple variables are defined in the from clause by way of the as clause. “Find all customers who have a loan from the bank, find their names, loan numbers, and loan amount.” selectcustomer-name, T.loan-number, S.amount from borrower asT, loan as S whereT.loan-number = S.loan-number

  14. Tuple Variables • Tuple variables are most useful for comparing two tuples in the same relation. • “Find the names of all branches that have assets greater than at least one branch located in Brooklyn.” select distinctT.branch-name from branch as T, branch as S whereT.assets > S.assetsandS.branch-city = ‘Brooklyn’

  15. String Operations • The strings are enclosed by single quotes, for example,‘Perryridge’. • The most commonly used operation on strings is pattern matching using “like”. Pattern has two special characters: * Percent(%):matches any substring * Underscore(_): matches any character - ‘Perry%’ matches any string beginning with “Perry”. - ‘_ _ _ %’matches any string of at least 3 characters. Note: Patterns are case sensitive.

  16. String Operations • “Find the names of all customers whose street address includes the substring ‘Main’.” select customer-name fromcustomer wherecustomer-street like ‘%Main%’

  17. Ordering the Display of Tuples • The order by clause list the result in sorted order. selectdistinct customer-name fromborrower, loan whereborrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’ order bycustomer-name Note: by default, the order by clause lists items in ascending order. ( desc or asc ) select* from loan order by amount desc, loan-numberasc

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

  19. Union • “ Find all customers having a loan, an account, or both at the bank.” (selectcustomer-name from depositor) union (select customer-name from borrower)

  20. Intersect • “ Find all customers who have both a loan and an account at the bank.” (selectdistinct customer-name from depositor) intersect (select distinct customer-name from borrower)

  21. Except • “Find all customers who have an account but no loan at the bank.” (selectdistinct customer-name from depositor) except (select customer-name from borrower)

  22. Aggregation Functions • Aggregation functions take a collection of values as input and return a single value. * Average: avg (number) * Minimum: min * Maximum: max * Total: sum (number) * Count: count

  23. avg • “Find the average account balance at the Perryridge branch.” select avg(balance) fromaccount wherebranch-name = ‘Perryridge’

  24. group by • Aggregation function can be applied to a group of sets of tuples by using group by clause. “Find the average account balance at each branch.” selectbranch-name, avg(balance) fromaccount groupbybranch-name

  25. having • It is useful to state a condition that applies to groups rather than to tuples. • “Find the branches where the average account balance is more than $1200.” select branch-name,ave(balance) from account group bybranch-name having avg(balance) > 1200

  26. The End

More Related