{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

421-sql1

# 421-sql1 - Principle Form of a Query SQL Part I Standard...

This preview shows pages 1–2. Sign up to view the full content.

1 SQL Part I: Standard Queries COMP-421: Database Systems - SQL Queries I 2 Principle Form of a Query SELECT desired attributes FROM list of relations WHERE qualification ( where clause is optional ) SELECT rating,age FROM Skaters WHERE rating >= 10 OR age > 15 Conversion to Relational Algebra π rating,age ( σ rating>=10 age>15 (Skaters)) Start with the relation in the FROM clause Apply σ , 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 . 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 rating age 7 16 10 10 sid sname rating age 22 debby 7 16 31 debby 8 10 58 lilly 10 10 S COMP-421: Database Systems - SQL Queries I 3 Selection: The WHERE Clause Comparison terms: attr1 op const , or attr1 op attr2 , op is one of < , = , > , <> , <= , >= , LIKE We may apply the usual arithmetic operations +, *, etc. to numeric values before we compare Example: show name and rating multiplied by age for all skaters Qualification/Condition : Comparisons combined using AND, OR and NOT name = Cheng AND NOT age = 18 name LIKE %e_g (%: any string, _:any character) Further string operations, e.g., concatenation, string-length, etc. Example: show all names that end in y ; show all names that have an i in the second position COMP-421: Database Systems - SQL Queries I 4 Projection: Attribute Lists Distinct Duplicate elimination Ex: show all different names SELECT DISTINCT sname FROM Skaters ( no WHERE clause OK) Star as list of all attributes show all skaters with a rating smaller than 9 SELECT * FROM Skaters WHERE rating < 9 Renaming columns print the sname column with skater as heading for all with rating < 9 SELECT sid, sname AS skater FROM Skaters WHERE rating < 9 sid sname rating age 22 debby 7 16 31 debby 8 10 skater 22 debby 31 debby sname debby lilly sid sid sname rating age 22 debby 7 16 31 debby 8 10 58 lilly 10 10 COMP-421: Database Systems - SQL Queries I 5 Attribute Lists (contd) Expressions as values in columns

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}