SQL Select and Relational Algebra

SQL Select and Relational Algebra

SELECT SELECT fields FROM tables WHERE conditions GROUP BY fields HAVING conditions ORDER BY fields

SELECT * FROM expeople

SELECT name (Projection) FROM expeople

SELECT name, age FROM expeople

SELECT DISTINCT name FROM expeople

SELECT sex AS gender (renaming) FROM expeople

SELECT * FROM expeople WHERE age = 17 (selection)

= Equal <> Not Equal > Greater Than < Less Than >= Greater Than or Equal <= Less Than or Equal IS NULL IS NOT NULL

LIKE NOT LIKE % _ New% will match Newark, New York, etc. _ow will match Cow, Bow, Now, etc. AND OR

SELECT * FROM expeople WHERE age = 17 OR name = ‘JoJo’

SELECT * FROM expeople ORDER BY expeople.id desc (or can use asc)

SELECT sum(nums) FROM exnum

count() Total number of items sum() Sum of the items avg() Average of the items min() Smallest of the items max() Biggest of the items

SELECT name, sum(number) FROM exnum GROUP BY name

SELECT sum(number) FROM exnum GROUP BY name HAVING sum(nums) > 30

SELECT expeople.*, exjobs.* FROM expeople, exjobs (cartesian product)

SELECT expeople.*, exjobs.* FROM expeople, exjobs WHERE expeople.id = exjobs.jid (equi-joins)

SELECT * FROM expeople WHERE name = ‘JoJo’ UNION (union) SELECT * FROM expeople WHERE age = ‘17’

SELECT * FROM expeople WHERE name = ‘Mike’ INTERSECT (intersection) SELECT * FROM expeople WHERE age = ‘17’

SELECT * FROM expeople WHERE name = ‘Mike’ MINUS (set-difference) SELECT * FROM expeople WHERE age = ‘17’

DONE

SQL Select and Relational Algebra