Download
1 / 27
270 likes | 384 Vues
This guide provides an in-depth overview of SQL SELECT statements and relational algebra concepts. Learn how to retrieve data from tables using various SQL queries, including projections, selections, and joins. Discover how to apply aggregate functions like SUM, AVG, and GROUP BY, and filter results using HAVING and WHERE conditions. Understand the importance of ordering results with ORDER BY and the use of set operations such as UNION, INTERSECT, and MINUS for effective data manipulation.
E N D
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’
