SQL Structured Query LanguageContent (4.1 –4.4)BackgroundParts of SQLRelations using in ExamplesBasic StructureselectselectwherefromSlide 11RenameTuple VariablesSlide 14String OperationsString OperationsOrdering the Display of TuplesSet OperationsUnionIntersectExceptAggregation Functionsavggroup byhavingThe EndSQLStructured Query LanguageMeizhen HuangContent (4.1 –4.4)BackgroundParts of SQLBasic StructureSet OperationsAggregate FunctionsBackgroundSQL – Structured Query LanguageDeveloped by IBM in the 1970’s, originally called SequelThe standard relational-database languageUses relational-algebra and relational-calculus constructsParts of SQLDDL: 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 ExamplesBranch-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 StructureBasic 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 predicateThis query is equivalent to the relational algebra expression:A1, A2, ..., An (P (r1 x r2 x ... x rm))selectselect – projection in RAselect without elimination of duplicates “Find the names of all branches in the loan relation.” select branch-name select all branch-name from loan from loanselect with elimination of duplicates select distinct branch-name from loanselect“*” can be used to denote “ all attributes”. select * from loanThe 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 from loanwherewhere – selection predicate of RAe.g. “Find all loan numbers for loans made at the Perryridge branch with loan amounts greater that $1200.” select loan-numberfrom loanwhere branch-name = ‘Perryridge’ and amount > 1200Note: SQL uses and, or, and not instead of , v, and in the where clause.e.g., select loan-number from loan where amount between 90000 and 100000 Note: similarly, we can use the not between comparison operator.fromfrom – Cartesian-product in RA.“Find the Cartesian product borrower x loan”select from borrower, loanfromSQL 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-number andbranch-name = ‘Perryridge’RenameRename can be operated on both relations and attributes. old-name as new-namee.g., select customer-name, borrower.loan-number as loan-id, amount from borrower, loan where borrower.loan-number = loan.loan-numberTuple VariablesTuple 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.” select customer-name, T.loan-number, S.amount from borrower as T, loan as S where T.loan-number = S.loan-numberTuple VariablesTuple 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 distinct T.branch-name from branch as T, branch as S where T.assets > S.assets and S.branch-city = ‘Brooklyn’String OperationsThe 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 from customer where customer-street like ‘%Main%’Ordering the Display of TuplesThe order by clause list the result in sorted order. select distinct customer-name from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’ order by customer-name Note: by default, the order by clause lists items in ascending order. ( desc or asc ) select * from loan order by amount desc, loan-number ascSet OperationsThe 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.” (select customer-name from depositor) union (select customer-name from borrower)Intersect“ Find all customers who have both a loan and an account at the bank.” (select distinct 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.” (select distinct customer-name from depositor) except (select customer-name from borrower)Aggregation FunctionsAggregation functions take a collection of values as input and return a single value. * Average: avg (number) * Minimum: min * Maximum: max * Total: sum (number) * Count: countavg“Find the average account balance at the Perryridge branch.” select avg (balance) from account where branch-name = ‘Perryridge’group byAggregation function can be applied to a group of sets of tuples by using group by clause. “Find the average account balance at each branch.” select branch-name, avg(balance) from account group by branch-namehavingIt is useful to state a condition that
View Full Document
Unlocking...