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