4%20-%20SQL

4%20-%20SQL - Basic Structure Set Operations Aggregate...

Info icon This preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
Basic Structure Set Operations Aggregate Functions Null Values Nested Subqueries Derived Relations Views Modification of the Database Joined Relations Data Definition Language
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-city) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number)
Image of page 2
SQL is based on set and relational operations with certain modifications and enhancements A typical SQL query has the form: select A 1 , A 2 , ..., A n from r 1 , r 2 , ..., r m where P A i s represent attributes r i s represent relations P is a predicate. This query is equivalent to the relational algebra expression. A 1 , A2, ..., An ( σ P (r 1 x r 2 x ... x r m )) The result of an SQL query is a relation.
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. Find the names of all branches in the loan relation select branch-name from loan In the “pure” relational algebra syntax, the query would be: branch-name ( loan) An asterisk in the select clause denotes “all attributes” select * from loan NOTE : SQL does not permit the ʻ - ʼ character in names, so you would use, for example, branch_name instead of branch-name in a real implementation. We use ʻ - ʼ since it looks nicer! NOTE : SQL names are case insensitive, meaning you can use upper case or lower case. You may wish to use upper case in places where we use bold font.
Image of page 4
SQL allows duplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. Find the names of all branches in the loan relations, and remove duplicates select distinct branch-name from loan The keyword all specifies that duplicates not be removed. select all branch-name from loan
Image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
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.
Image of page 6
The where clause corresponds to the selection predicate of the relational algebra. If consists of a predicate involving attributes of the relations that appear in the from clause. The find all loan number for loans made a 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.
Image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern