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/oan-number S amount from borrower as T loan as s where Tloan-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 Tassets>Sassets and Sbranch-city Brooklyn Database System Concepts 4.11 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.11 ©Silberschatz, Korth and Sudarshan 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 distinct T.branch-name from branch as T, branch as S where T.assets > S.assets and S.branch-city = ‘Brooklyn’ Find the names of all branches that have greater assets than some branch located in Brooklyn. 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. Patterns 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 SQL supports a variety of string operations such as concatenation(using "p) converting from upper to lower case(and vice ver finding string length, extracting substrings, etc Database System Concepts 4.12 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.12 ©Silberschatz, Korth and Sudarshan String Operations SQL includes a string-matching operator for comparisons on character strings. Patterns 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%’ 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 E.g. order by customer-name desc 标 Database System Concepts 4.13 OSilberschatz. Korth and Sudarshan
Database System Concepts 4.13 ©Silberschatz, Korth and Sudarshan 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. E.g. order by customer-name desc
Set Operations The set operations union, intersect, and except operate on relations and correspond to the relational algebra operations Each of the above operations automatically eliminates duplicates, to retain all duplicates use the corresponding multiset versions union all, intersect all and except all Suppose a tuple occurs m times in r and n times in s, then, it occurs m + n times in r union all s min(m, n)times in r intersect all s max(O, m-n) times in r except all S Database System Concepts 4.14 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.14 ©Silberschatz, Korth and Sudarshan Set Operations The set operations union, intersect, and except operate on relations and correspond to the relational algebra operations − Each of the above operations automatically eliminates duplicates; to retain all duplicates use the corresponding multiset versions union all, intersect all and except all. Suppose a tuple occurs m times in r and n times in s, then, it occurs: m + n times in r union all s min(m,n) times in r intersect all s max(0, m – n) times in r except all s
Set Operations Find all customers who have a loan. an account, or both (select customer-name from depositor union (select customer-name from borrower) Find all customers who have both a loan and an account (select customer-name from depositor) intersect (select customer-name from borrower) Find all customers who have an account but no loan (select customer-name from depositor) except (select customer-name from borrower Database System Concepts 4.15 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.15 ©Silberschatz, Korth and Sudarshan Set Operations Find all customers who have a loan, an account, or both: (select customer-name from depositor) except (select customer-name from borrower) (select customer-name from depositor) intersect (select customer-name from borrower) Find all customers who have an account but no loan. (select customer-name from depositor) union (select customer-name from borrower) Find all customers who have both a loan and an account