Duplicate Removal SQL allows duplicates in relations as well as in query results. Use select distinct to force the elimination of duplicates Find the names of all branches in the loan relation and remove duplicates select distinct branch-name force the dbms to remove duplicates from loan The keyword all specifies that duplicates are not removed select all branch-name force the dbms not from loan to remove duplicates COMP3311 Fall 2011 CSE, HKUST Slide 6
COMP3311 Fall 2011 CSE, HKUST Slide 6 Duplicate Removal • SQL allows duplicates in relations as well as in query results. Use select distinct to force the elimination of duplicates. Find the names of all branches in the loan relation, and remove duplicates select distinct branch-name from loan • The keyword all specifies that duplicates are not removed. select all branch-name from loan force the DBMS to remove duplicates force the DBMS not to remove duplicates
Arithmetic Operations on Retrieved Results The select clause can contain arithmetic expressions involving the operators tr- and x, and operating on constants or attributes of tuples The query: select branch-name, loan-number amount 100 from loan would return a relation which is the same as the loan relations except that the attribute amount is multiplied by 100 COMP3311 Fall 2011 CSE, HKUST Slide 7
COMP3311 Fall 2011 CSE, HKUST Slide 7 Arithmetic Operations on Retrieved Results • The select clause can contain arithmetic expressions involving the operators,+,−, and , and operating on constants or attributes of tuples. • The query: select branch-name, loan-number, amount * 100 from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100
The where clause The where clause specifies conditions that tuples in the relations in the from clause must satisfy Find all loan numbers for loans made at the perryridge branch with loan amounts greater than $1200 select loan-number from /oan where branch-name="perryridge"and amount>1200 SQL allows logical connectives and or, and not. arithmetic expressions can be used in the comparison operators Note: attributes used in a query(both select and where parts) must be defined in the relations in the from clause COMP3311 Fall 2011 CSE, HKUST Slide 8
COMP3311 Fall 2011 CSE, HKUST Slide 8 The where Clause • The where clause specifies conditions that tuples in the relations in the from clause must satisfy. • Find all loan numbers for loans made at the Perryridge branch with loan amounts greater than $1200. select loan-number from loan where branch-name=“Perryridge” and amount >1200 • SQL allows logical connectives and, or, and not. Arithmetic expressions can be used in the comparison operators. • Note: attributes used in a query (both select and where parts) must be defined in the relations in the from clause
The where Clause( cont SQL includes the between operator for convenience Find the loan number of those loans with loan amounts between 90,000and$100.000 that is,≥$90,000and≤$100,000) select loan-number from loan Where amount between 90000 and 100000 COMP3311 Fall 2011 CSE, HKUST Slide 9
COMP3311 Fall 2011 CSE, HKUST Slide 9 The where Clause (Cont.) • SQL includes the between operator for convenience. • Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, $90,000 and $100,000) select loan-number from loan where amount between 90000 and 100000
The from Clause The from clause corresponds to the cartesian product operation of the relational algebra Find the cartesian product borrower x loan select x from borrower loan It is rarely used without a where clause Find the name and loan number of all customers having a loan at the perryridge branch select distinct customer-name borrower loan - number from borrower, loan where borrower,loan-number=loan, Joan-number and branch-name ="Perryridge COMP3311 Fall 2011 CSE, HKUST Slide 10
COMP3311 Fall 2011 CSE, HKUST Slide 10 The from Clause • The from clause corresponds to the Cartesian product operation of the relational algebra. • Find the Cartesian product borrower loan select * from borrower, loan It is rarely used without a where clause. • Find the name and loan number of all customers having a loan at the Perryridge branch. select distinct customer-name, borrower.loan-number from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = “Perryridge