08-421-sql1_v01.pdf - SQL SELECT Principle Form of a Query...

  • No School
  • AA 1
  • 28

This preview shows page 1 - 10 out of 28 pages.

SQL SELECT
Image of page 1
2 Principle Form of a Query SELECT desired attributes FROM list of relations WHERE qualification ( where clause is optional ) Example: SELECT sname, rating FROM Skaters WHERE rating > 9 OR age < 12 sid sname rating age 28 yuppy 9 15 31 debby 7 10 22 conny 5 10 58 lilly 10 13 sname rating debby 7 conny 5 lilly 10
Image of page 2
Principle Form of a Query Conversion to Relational Algebra p rating,age ( s rating>=10 Ú age>15 (Skaters)) Start with the relation in the FROM clause Apply s , using condition in WHERE clause (selection) Apply , using attributes in SELECT clause (projection) Operational Semantics as in Relational Algebra Imagine a tuple variable ranging over all tuples of the relation For each tuple: check if is satisfies the WHERE clause. If so, print the attributes in SELECT . sid sname rating age 28 yuppy 9 15 31 debby 7 10 22 conny 5 10 58 lilly 10 13 sname rating debby 7 conny 5 lilly 10 SELECT rating,age FROM Skaters WHERE rating >= 10 OR age > 15
Image of page 3
4 Set vs. Multi-Set Difference SQL and RELATIONAL ALGEBRA No elimination of duplicates (as long as no violation of primary key / unique constraint) Tables in relational databases are generally NO sets (but “multi- sets”) Results of SQL queries are generally NO sets SELECT age FROM Skaters sid sname rating age 28 yuppy 9 15 31 debby 7 10 22 conny 5 10 58 lilly 10 13 age 15 10 10 13
Image of page 4
5 Selection: The WHERE Clause Comparison terms: attr1 op const : age > 10 attr1 op attr2 : age < rating op is one of < , = , > , <> , <= , >= , LIKE We may apply the usual arithmetic operations +, *, etc. to numeric values before we compare Example: rating more than double the age WHERE rating > 2*age
Image of page 5
6 Selection: The WHERE Clause Boolean Operators: Comparisons combined using AND, OR and NOT name =‘Cheng’ AND NOT age = 18 Strings name LIKE ‘%e_g’ (%: any string, _:any character) Further string operations, e.g., concatenation, string-length, etc. show all names that end in “y” name LIKE ‘%y’ show all names that have an “i” in the second position name LIKE ‘_i%’
Image of page 6
7 Projection: Attribute Lists Distinct Duplicate elimination SELECT DISTINCT age FROM Skaters sid sname rating age 28 yuppy 9 15 31 debby 7 10 22 conny 5 10 58 lilly 10 13 age 15 10 13
Image of page 7
8 Projection: Attribute Lists Star as list of all attributes show all skaters with a rating smaller than 9 SELECT * FROM Skaters WHERE rating < 9 sid sname rating age 28 yuppy 9 15 31 debby 7 10 22 conny 5 10 58 lilly 10 13 sid sname rating age 31 debby 7 10 22 conny 5 10
Image of page 8
9 Attribute Lists Renaming; Expressions and constants as values in columns
Image of page 9
Image of page 10

You've reached the end of your free preview.

Want to read all 28 pages?

  • Fall '19

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

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes
A+ icon
Ask Expert Tutors