SQL ENTIRE SELECT MIN MAX SELECT min returns
- Slides: 8
SQL – ENTIRE SELECT
MIN MAX • SELECT min returns the minimum value for a column. • SELECT max returns the maximum value for a column. SELECT min(Milliseconds) FROM Track; -- shortest track SELECT max(Milliseconds) FROM Track; -- longest track http: //www. pristerm. it/prodotti. Dettuk. asp? id. Cat=4
COUNT SUM TOTAL AVG • SELECT count returns a count of non-NULL values. SELECT count(Milliseconds) FROM Track; SELECT count() FROM Track; -- if you just care about number of rows • SELECT sum returns the sum of the data values (NULL if no non-NULL rows). SELECT sum(Milliseconds) FROM Track; • SELECT total returns the sum of the data values (0. 0 if no non-NULL rows). SELECT total(Milliseconds) FROM Track; • SELECT avg returns the (arithmetic) average of the data values. SELECT avg(Milliseconds) FROM Track;
DISTINCT • SELECT DISTINCT returns only distinct (different) values. • SELECT DISTINCT eliminates duplicate records from the results. • DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc. • DISTINCT operates on a single column. DISTINCT for multiple columns is not supported. SELECT DISTINCT Unit. Price FROM Track; SELECT count(DISTINCT Name) FROM Track;
GROUP BY • The GROUP BY clause groups records into summary rows. • GROUP BY returns one records for each group. • GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc. • GROUP BY can group by one or more columns. SELECT count(), Composer FROM Track GROUP BY Composer; SELECT total(Milliseconds), Composer, Album. Id FROM Track GROUP BY Composer, Album. Id;
HAVING • HAVING filters records that work on summarized GROUP BY results. • HAVING applies to summarized group records, whereas WHERE applies to individual records. • Only the groups that meet the HAVING criteria will be returned. • HAVING requires that a GROUP BY clause is present. • WHERE and HAVING can be in the same query. SELECT count(), Composer FROM Track GROUP BY Composer HAVING count() > 10;
LIMIT • Last clause of SELECT statement. • Limits the number of rows to the value (or less). • Often useful with ORDER BY to get extremes. SELECT Name, Milliseconds FROM Track ORDER BY Milliseconds DESC LIMIT 10; http: //www. psych 2 go. net/our-greatest-limit/
ALL THE PARTS OF A SELECT QUERY • • SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names LIMIT max-rows ;
