Scribd Logo
Upload

Welcome to Scribd!

  • 34 views

    From Clause & Tuples

    Uploaded by

    Ayisha
    The from clause lists the relations involved in the query and corresponds to the Cartesian product operation. It finds the Cartesian product of the employee and department relations to select the employee name, department number, and department name of all employees. The from clause can also define tuple variables using the as clause, such as selecting customer names and their loan numbers for customers having a loan at some branch by defining the borrower and loan relations as tuple variables T and S.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd

    From Clause & Tuples

    Uploaded by

    Ayisha
    34 views8 pages
    The from clause lists the relations involved in the query and corresponds to the Cartesian product operation. It finds the Cartesian product of the employee and department relations to select the employee name, department number, and department name of all employees. The from clause can also define tuple variables using the as clause, such as selecting customer names and their loan numbers for customers having a loan at some branch by defining the borrower and loan relations as tuple variables T and S.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The from clause lists the relations involved in the query and corresponds to the Cartesian product operation. It finds the Cartesian product of the employee and department relations to select the employee name, department number, and department name of all employees. The from clause can also define tuple variables using the as clause, such as selecting customer names and their loan numbers for customers having a loan at some branch by defining the borrower and loan relations as tuple variables T and S.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    34 views8 pages

    From Clause & Tuples

    Uploaded by

    Ayisha
    The from clause lists the relations involved in the query and corresponds to the Cartesian product operation. It finds the Cartesian product of the employee and department relations to select the employee name, department number, and department name of all employees. The from clause can also define tuple variables using the as clause, such as selecting customer names and their loan numbers for customers having a loan at some branch by defining the borrower and loan relations as tuple variables T and S.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 8

    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 employee x
    department select 
    from employee, department
    ■   Find the  employee name, deptno and deptname of all employees

    select name, employee.eno, deptname 
    from employee, dept 
    where  employee. eno = dept.eno
    Figure 3.3: Tuples inserted into
    Figure 3.4:
    Tuple Variables
    • Tuple variables are defined in the from
    clause via the use of the as clause.
    • Find the customer names and their loan
    numbers for all customers having a loan at
    some branch.
    select customer­name, T.loan­number, S.amount
              from borrower as T, loan as S
              where T.loan­number = S.loan­number
    ■    Find the names of all branches that have greater assets than 
          some branch located in Brooklyn.

    select distinct T.branch­name
        from branch as T, branch as S
        where T.assets > S.assets and S.branch­city = ‘Brooklyn’
    Set Operations
    • Find all customers who have a loan, an
    account, or both:
    (select customer­name from depositor)
    union
    (select customer­name from borrower)
    ■    Find all customers who have both a loan and an 

    (select customer­name from depositor)
    intersect
    (select customer­name from borrower)
    ■   Find all customers who have an account but no loan.

    (select customer­name from depositor)
    except
    (select customer­name from borrower)
    Ordering the Display of
    • List in alphabeticTuples
    order the names of all
    customers having a loan in Perryridge branch
    select distinct customer-name
    from borrower, loan
    where borrower loan-number =
    loan.loan-number and
    branch-name = ‘Perryridge’
    order by customer-name
    • We may specify desc for descending order or
    asc for ascending order, for each attribute;
    ascending order is the default.
    – E.g. order by customer-name desc
    Example 2
    • Sailors (sid,sname, rating,age)
    • Boats(bid, bname,color)
    • Reserves(sid,bid ,date)

    • Refer to attached list for rows

    You might also like