L12 Compositionality

L12 Compositionality - Remember from Friday Remember from...

Info iconThis preview shows pages 1–8. 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

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: Remember from Friday Remember from Friday – – Execution of an SQL Query Execution of an SQL Query Somewhat simplified execution model: ¡ form a cross product of all relations in the from clause ¡ apply filtering according to the where clause ¡ group tuples according to the group by and compute any aggregate functions on each group ¡ apply filtering according to the having clause ¢ having provides another level of filtering that filters out groups instead of single tuples ¡ apply projection according to the select clause Execution of an SQL Query (cont.) Execution of an SQL Query (cont.) ¡ So we have the following set of operations and their respective order of execution (alongside the order in which they physically appear in the query: 5 select 1 from 2 where 3 group by 4 having ¡ Next up -- use of more sophisticated constructs (the CASE construct in particular) and get into integrity as time permits Compositionality in SQL Compositionality in SQL ¡ “Compostionality” in its most general sense means that the meaning of a whole construct is a function of the meanings of its parts ¡ In the context of SQL queries, this principle means that a query may use subqueries in almost any place where it makes sense ¢ A subquery is a query expression enclosed in parentheses. Subqueries can be nested inside INSERT, DELETE, UPDATE, and SELECT statements, or other query expressions, to an arbitrary depth. ¡ We’ve seen some examples of subquery use already, but we’ll explore them some more today. Use of Use of Subqueries Subqueries (i) (i) ¡ Suppose that we want to find the faculty who make the highest salary in their department: Version 1: create table temptable (dept varchar(20), max_sal decimal(8,2) insert into temptable select dept, max(salary) as max_sal from faculty group by dept; select name from faculty, temptable where faculty.dept = temptable.dept and faculty.salary > temptable.max_sal; drop table temptable; Use of Use of Subqueries Subqueries (ii) (ii) ¡ This is a little cumbersome – we might like to avoid having to construct a temporary table Version 2: subq0.txt select name from faculty f, (select dept, max(salary) as max_sal from faculty f1 group by dept) as t1 where salary = max_sal and f.dept = t1.dept; Notice – subquery in the from clause Use of Use of Subqueries Subqueries (iii) (iii) ¡ Retrieving the highest salary is not at all hard: select max(salary) from faculty; ¡ But what if we wanted to retrieve the highest two salaries? This can be done using subqueries: subq2.txt (select max(salary) from faculty) union (select max(salary) from faculty where salary not in (select f1.salary from faculty f1 where f1.salary >= all (select f2.salary from faculty f2))); Notice – subquery in the where clause not once, but in two levels of nesting! Scalar Scalar Subqueries Subqueries ¡ According to the ANSI standard, subqueries fall into three categories: scalar, row, and table....
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 / 27

L12 Compositionality - Remember from Friday Remember from...

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

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