L13 - Integrity - Last time Last time We looked at...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

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

Unformatted text preview: Last time Last time We looked at compositionality and subqueries we saw examples of subqueries in: the select clause the from clause the where clause the having clause Basically, we observed that a subquery can appear wherever it makes sense but what is reasonable to expect the role of a subquery to be depends on the context in which it appears Then we went on to consider the full syntax of the select construct in SQL Select syntax (in full) Select syntax (in full) SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr , ... [ FROM table_references [WHERE where_condition ] [GROUP BY { col_name | expr | position } [ASC | DESC] , ... [WITH ROLLUP] ] [HAVING where_condition ] [ORDER BY { col_name | expr | position } [ASC | DESC], ...] [LIMIT {[ offset ,] row_count | row_count OFFSET offset }] [PROCEDURE procedure_name ( argument_list )] [INTO OUTFILE ' file_name ' export_options | INTO DUMPFILE ' file_name ' | INTO @ var_name [, @ var_name ]] [FOR UPDATE | LOCK IN SHARE MODE] ] Things we considered further last time: select-expr, table_references, various factors in doing joins where_condition where_condition where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause. In the WHERE clause, you can use any of the functions and operators that MySQL supports, except for aggregate (summary) functions. It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. However, the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. To accommodate both standard SQL and the MySQL- specific behavior of being able to refer columns in the SELECT list, MySQL 5.0.2 and up allows HAVING to refer to columns in the SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and to aggregate functions. the the order by order by clause clause This clause is applied after the having clause (which is the last of the clauses to be applied that weve already discussed) the order by clause is used to specify that the output should be ordered using some criterion Suppose we want to output faculty members ordered in descending order of their salary and grouped with all faculty in a given department together ord1.txt select dept, name, salary from faculty order by dept asc, salary desc; Select syntax (in full Select syntax (in full one last time) one last time) SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]...
View Full Document

This note was uploaded on 04/08/2008 for the course EE 468 taught by Professor Conry during the Spring '08 term at Clarkson University .

Page1 / 21

L13 - Integrity - Last time Last time We looked at...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online