retrieve the first 5 rows in the results fetchrs n

Info iconThis preview shows page 1. Sign up to view the full content.

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

Unformatted text preview: dth == 32)! Addi;onal clauses: GROUP BY! •  The GROUP BY clause makes the aggregate func;ons in SQL more useful. It enables the aggregates to be applied to subsets of the rows in a table. SELECT width, MAX(mips) FROM chips! GROUP BY width;! width | max -------+------ 8 | 0.64 16 | 1 32 | 1700 (3 rows) •  More than one a>ribute can be included in the GROUP BY clause. Addi;onal clauses: HAVING! •  The WHERE clause can’t contain an aggregate func;on, but the HAVING clause can be used to refer to the groups to be selected. SELECT width, MAX(mips) FROM chips! GROUP BY width HAVING MAX(mips) >= 1;! width | max -------+------ 16 | 1 32 | 1700 (2 rows) •  First the chips table is separated into sets of rows by width. For each set, MAX(mips) is calculated, and the set is discarded if MAX(mips) < 1. Finally, width and MAX(mips) are returned for each set. A few other predicates and clauses •  DISTINCT - forces values of an a>ribute in the results table to have unique values •  NOT - negates condi;ons in WHERE or HAVING clause •  LIMIT - limits the number of rows returned SELECT * FROM chips LIMIT 3;! SELECT DISTINCT width FROM chips;! Order of Execu;on The order of execu;on of the clauses in a SELECT statement is as follows: 1. FROM: The working table is constructed. 2. WHERE: The WHERE clause is applied to each row of the table, and only the rows that test TRUE are retained. 3. GROUP BY: The results are broken into groups of rows all with the same value of the GROUP BY clause. 4. HAVING: The HAVING clause is applied to each group and only those that test TRUE are retained. 5. SELECT: The a>ributes not in the list are dropped, aggregates are calculated, and op;ons DISTINCT, ORDER BY and LIMIT are applied. Using SQL with R The DBI and RSQLite libraries allow you to connect to an SQL database, submit a query, and receive the results as a data frame. l...
View Full Document

This document was uploaded on 02/16/2014 for the course STATISTICS 3026 at Columbia.

Ask a homework question - tutors are online