421-sql1

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

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

View Full Document Right Arrow Icon
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 = b Cheng ` AND NOT age = 18 name LIKE b %e_g ` (%: any string, _:any character) Further string operations, e.g., concatenation, string-length, etc. Example: show all names that end in l y z ; show all names that have an l i z 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
Background image of page 1

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

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

{[ snackBarMessage ]}

Page1 / 3

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

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online