SQL Select and Relational Algebra

# SQL Select and Relational Algebra

## SQL Select and Relational Algebra

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. SQL Select and Relational Algebra

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

3. SELECT * FROM expeople

4. SELECT name (Projection) FROM expeople

5. SELECT name, age FROM expeople

6. SELECT DISTINCT name FROM expeople

7. SELECT sex AS gender (renaming) FROM expeople

8. SELECT * FROM expeople WHERE age = 17 (selection)

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

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

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

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

13. SELECT sum(nums) FROM exnum

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

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

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

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

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

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

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

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

22. DONE