The select Clause(Cont.) An asterisk in the select clause denotes "all attributes" select from loan The select clause can contain arithmetic expressions involving the operation,+-*and /and operating on constants or attributes of tuples. The query: select loan number,branch name,amount 100 from loan would return a relation that is the same as the loan relation,except that the value of the attribute amount is multiplied by 100 Database System Concepts,5th Edition,Oct 5,2006 3.12 Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.12 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 The select Clause (Cont.) An asterisk in the select clause denotes “all attributes” select * from loan The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. The query: select loan_number, branch_name, amount 100 from loan would return a relation that is the same as the loan relation, except that the value of the attribute amount is multiplied by 100
The where Clause The where clause specifies conditions that the result must satisfy Corresponds to the selection predicate of the relational algebra. To find all loan number 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 Comparison results can be combined using the logical connectives and, or,and not. Comparisons can be applied to results of arithmetic expressions. Database System Concepts,5th Edition,Oct 5,2006 3.13 Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.13 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 The where Clause The where clause specifies conditions that the result must satisfy Corresponds to the selection predicate of the relational algebra. To find all loan number 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 Comparison results can be combined using the logical connectives and, or, and not. Comparisons can be applied to results of arithmetic expressions
The where Clause (Cont.) SQL includes a between comparison operator Example: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 Database System Concepts,5th Edition,Oct 5,2006 3.14 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.14 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 The where Clause (Cont.) SQL includes a between comparison operator Example: 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 lists the relations involved in the query Corresponds to the Cartesian product operation of the relational algebra. Find the Cartesian product borrower X loan select from borrower.loan Find the name,loan number and loan amount of all customers having a loan at the Perryridge branch. select customer_name,borrower.loan_number,amount from borrower,loan where borrower.loan_number loan.loan_number and branch_name ='Perryridge Database System Concepts,5th Edition,Oct 5,2006 3.15 ©Silberschat乜，Korth and Sudarshan
Database System Concepts, 5 3.15 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 The from Clause The from clause lists the relations involved in the query Corresponds to the Cartesian product operation of the relational algebra. Find the Cartesian product borrower X loan select from borrower, loan Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch. select customer_name, borrower.loan_number, amount from borrower, loan where borrower.loan_number = loan.loan_number and branch_name = 'Perryridge
The Rename Operation The SQL allows renaming relations and attributes using the as clause: old-name as new-name Find the name,loan number and loan amount of all customers;rename the column name loan number as loan_id. select customer_name,borrower.loan_number as loan id,amount from borrower.loan where borrower.loan_number loan.loan_number Database System Concepts,5th Edition,Oct 5,2006 3.16 Silberschatz,Korth and Sudarshan
Database System Concepts, 5 3.16 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 The Rename Operation The SQL allows renaming relations and attributes using the as clause: old-name as new-name Find the name, loan number and loan amount of all customers; rename the column name loan_number as loan_id. select customer_name, borrower.loan_number as loan_id, amount from borrower, loan where borrower.loan_number = loan.loan_number