Database Reference
In-Depth Information
As a final example, suppose we want to obtain a list of all cities where more
than 10 engineers, working in low-budget departments, earn more than their
managers. The following SQL query calculates the result:
SELECT D.City
FROM Emp E, Emp M, Dept D
WHERE E.DId = D.DId AND E.Title='Engineer' AND D.Budget < 100K
AND E.MId = M.EId AND E.Salary > M.Salary
GROUP BY D.City
HAVING count(*) > 10
This is quite a sophisticated query that would not be straightforward to
implement eciently in an imperative language. Moreover, note that in all
the previous examples we have not given any specific procedure to reach the
results but instead have specified what the result should be. Going back to
the motivating example in Section 1.1, the algorithm countMatches can be
expressed using the following simple SQL query :
SELECT COUNT(R.s)
FROM R, S
WHERE R.r = S.s
In addition to being very concise, this query would be executed by a care-
fully chosen algorithm that takes into account characteristics of both data and
the environment. Since the actual procedure to evaluate a query is abstracted
out from the developer, extending countMatches to handle more complex sce-
narios is much easier than for the imperative examples of Section 1.1. As an
example, suppose we want to count all matches greater than 500 from three
tables R , S , and T . We could then use the following query variation, which is
straightforward to write and would still be executed eciently by the database
engine:
SELECT COUNT(R.s)
FROM R, S, T
WHERE R.r = S.s and S.s = T.t and R.r > 500
1.2.1 Updating Data
SQL queries that select data are very common, but they cannot modify data
stored in tables. For that purpose, SQL introduces data manipulation primi-
tives, which can insert, delete, or update values in tables. As an example, to
When there are aggregate expressions in the SELECT clause but no GROUP BY clause, the result
implicitly considers all tuples in the result as belonging to the same group.
Search WWH ::




Custom Search