Tuple Variables Tuple variables are defined in the from clause via the use of the as clause. Find the customer names and their loan numbers for all customers having a loan at some branch. select customer name,T.loan number,S.amount from borrower as T,loan as S where T.loan number S.loan number Find the names of all branches that have greater assets than some 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' Keyword as is optional and may be omitted borrower as T=borrower T Database System Concepts,5th Edition,Oct 5,2006 3.17 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.17 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Tuple Variables Tuple variables are defined in the from clause via the use of the as clause. Find the customer names and their loan numbers for all customers having a loan at some branch. Find the names of all branches that have greater assets than some 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' Keyword as is optional and may be omitted borrower as T ≡ borrower T select customer_name, T.loan_number, S.amount from borrower as T, loan as S where T.loan_number = S.loan_number
String Operations SQL includes a string-matching operator for comparisons on character strings.The operator "like"uses patterns that are described using two special characters: percent(%).The character matches any substring. underscore ()The character matches any character. Find the names of all customers whose street includes the substring “Main”. select customer_name from customer where customer street like 'Main% Match the name“Main%” like 'Mainl%'escape SQL supports a variety of string operations such as concatenation (using " converting from upper to lower case (and vice versa) finding string length,extracting substrings,etc. Database System Concepts,5th Edition,Oct 5,2006 3.18 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.18 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 String Operations SQL includes a string-matching operator for comparisons on character strings. The operator “like” uses patterns that are described using two special characters: percent (%). The % character matches any substring. underscore (_). The _ character matches any character. Find the names of all customers whose street includes the substring “Main”. select customer_name from customer where customer_street like '% Main%' Match the name “Main%” like 'Main\%' escape '\' SQL supports a variety of string operations such as concatenation (using “||”) converting from upper to lower case (and vice versa) finding string length, extracting substrings, etc
Ordering the Display of Tuples List in alphabetic order the names of all customers having a loan in Perryridge branch select distinct customer name from borrower,loan where borrower loan_number loan.loan_number and branch name ='Perryridge order by customer name We may specify desc for descending order or asc for ascending order,for each attribute;ascending order is the default. Example:order by customer_name desc Database System Concepts,5th Edition,Oct 5,2006 3.19 Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.19 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Ordering the Display of Tuples List in alphabetic order the names of all customers having a loan in Perryridge branch select distinct customer_name from borrower, loan where borrower loan_number = loan.loan_number and branch_name = 'Perryridge' order by customer_name We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default. Example: order by customer_name desc
Duplicates In relations with duplicates,SQL can define how many copies of tuples appear in the result. Multiset versions of some of the relational algebra operators-given multiset relations r and r2: 1.(r):If there are c copies of tuple f in n,and satisfies selections o2,then there are c copies of ti in o(r). 2.IIA(r):For each copy of tuple t,in n,there is a copy of tuple IA(f)in IA(r)where IA(f)denotes the projection of the single tuple f. 3.rx r2:If there are c copies of tuple f,in n and c2 copies of tuple t2 in r2,there are c x c2 copies of the tuplet1.t2 in rx r2 Database System Concepts,5th Edition,Oct 5,2006 3.20 Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.20 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Duplicates In relations with duplicates, SQL can define how many copies of tuples appear in the result. Multiset versions of some of the relational algebra operators – given multiset relations r1 and r2 : 1. (r1 ): If there are c1 copies of tuple t1 in r1 , and t1 satisfies selections , , then there are c1 copies of t1 in (r1 ). 2. A (r ): For each copy of tuple t1 in r1 , there is a copy of tuple A (t1 ) in A (r1 ) where A (t1 ) denotes the projection of the single tuple t1 . 3. r1 x r2 : If there are c1 copies of tuple t1 in r1 and c2 copies of tuple t2 in r2 , there are c1 x c2 copies of the tuple t1 . t2 in r1 x r2
Duplicates (Cont.) Example:Suppose multiset relations r(A,B)and r2(C)are as follows: r1={(1,a)(2,a}2={(2),(3),(3)} Then IIB(r)would be {(a),(a)},while IIB(r)x r2 would be {(a,2),(a,2),(a,3),(a,3),(a,3),(a,3)} SQL duplicate semantics: select A1,A2,...An from n,r2,....rm where P is equivalent to the multiset version of the expression: ΠAA(o(G×2×…×rm)》 Database System Concepts,5th Edition,Oct 5,2006 3.21 Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.21 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Duplicates (Cont.) Example: Suppose multiset relations r1 (A, B) and r2 (C) are as follows: r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)} Then B (r1 ) would be {(a), (a)}, while B (r1 ) x r2 would be {(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)} SQL duplicate semantics: select A1 ,, A2 , ..., An from r1 , r2 , ..., rm where P is equivalent to the multiset version of the expression: ( ( )) A1 ,A2 , ,A P 1 2 m r r r n