# 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
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
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
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
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
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%’
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
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
9 Attribute Lists Renaming; Expressions and constants as values in columns

#### You've reached the end of your free preview.

Want to read all 28 pages?

• Fall '19

### What students are saying

• 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.

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

• 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.

Dana University of Pennsylvania ‘17, Course Hero Intern

• 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.

Jill Tulane University ‘16, Course Hero Intern