260 likes | 523 Views
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
E N D
SQLStructured 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 • The standard relational-database language • Uses relational-algebra and relational-calculus constructs
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.
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)
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))
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
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
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, andin the where clause. • e.g., selectloan-number fromloan whereamountbetween 90000 and100000 Note: similarly, we can use the not between comparison operator.
from • from – Cartesian-product in RA. • “Find the Cartesian product borrower x loan” select from borrower, loan
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’
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
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
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’
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.
String Operations • “Find the names of all customers whose street address includes the substring ‘Main’.” select customer-name fromcustomer wherecustomer-street like ‘%Main%’
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
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.
Union • “ Find all customers having a loan, an account, or both at the bank.” (selectcustomer-name from depositor) union (select customer-name from borrower)
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)
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)
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
avg • “Find the average account balance at the Perryridge branch.” select avg(balance) fromaccount wherebranch-name = ‘Perryridge’
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
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