Select & where Statement
select statement
n The select clause list the attributes desired in the result of a query
l corresponds to the projection operation of the relational algebra
n Example: find the names of all branches in the loan relation:
select branch_name
from loan
n In the relational algebra, the query would be:
Õbranch_name (loan)
n NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.)
l E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name
l Some people use upper case wherever we use bold font.
n SQL allows duplicates in relations as well as in query results.
n To force the elimination of duplicates, insert the keyword distinct after select.
n Find the names of all branches in the loan relations, and remove duplicates
select distinct branch_name
from loan
n The keyword all specifies that duplicates not be removed.
select all branch_name
from loan
n An asterisk in the select clause denotes “all attributes”
select *
from loan
n The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or attributes of tuples.
n 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.
where clauses
n The where clause specifies conditions that the result must satisfy
l Corresponds to the selection predicate of the relational algebra.
n 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
n Comparison results can be combined using the logical connectives and, or, and not.
n Comparisons can be applied to results of arithmetic expressions.
n SQL includes a between comparison operator
n 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