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 *s 100 from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by100 Database System Concepts 4.6 OSilberschatz. Korth and Sudarshan
Database System Concepts 4.6 ©Silberschatz, Korth and Sudarshan 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 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 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 / oan-number from loan where branch-name Perryridge and amount>1200 Comparison results can be combined using the logical connectives and. or and not Database System Concepts 4.7 OSilberschatz. Korth and Sudarshan
Database System Concepts 4.7 ©Silberschatz, Korth and Sudarshan 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
The where Clause(Cont SQL includes a between comparison operator E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, ≥$90,000and≤$100,000 select /oan-number from loan where amount between 90000 and 100000 标 Database System Concepts 4.8 OSilberschatz. Korth and Sudarshan
Database System Concepts 4.8 ©Silberschatz, Korth and Sudarshan The where Clause (Cont.) SQL includes a between comparison operator E.g. 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 oan Find the name loan number and loan amount of all customers having a loan at the Perryridge branch select customer-name, borrower oan-number amount from borrower loan where borrower oan-number= loan /oan-number and branch-name =Perryridge Database System Concepts 4.9 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.9 ©Silberschatz, Korth and Sudarshan 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-jd select customer-name, borrower oan-number as loan-id. amount from borrower loan where borrower. oan-number loan loan-number Database System Concepts 4.10 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.10 ©Silberschatz, Korth and Sudarshan 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