Database Reference
In-Depth Information
Note:
In typical implementations of SQL, it is not permissible to use the GROUP
BY clause in a join query. The solution below would therefore be rejected by the SQL
compiler. However, there are ways to get around this hurdle (for instance creating a view
or snapshot, then writing a query on the view or snapshot). This will be further discussed
in Chapter 15).
The
Having-Clause
may be used to restrict groups.
HAVING…
works after the
groups have been selected;
WHERE…
works on rows before group selection.
Example 33:
Develop a list from the employee table, showing for each department,
the total salary, average salary, minimum salary, maximum salary and standard
deviation; show only departments with a total salary of at least $60,000:
Note:
The question of when a column should be included in a query that involves
grouping of data has often troubled inexperienced users of SQL. Here is a simple guide:
12.9 Queries Using LIKE, BETWEEN and IN
Operators
The LIKE operator is used to test for the existence of string patterns in a column. The
BETWEEN … AND operator is used to test for column values within a range of values.
The IN operator is used to test for column values within a set of values; the set of values
may be expressed explicitly, or implied from the result of a sub query (sub-queries will be
discussed in the next section).
Example 34:
Get names of all courses with code beginning with the acronym “CS”
(all computer science courses, for instance):
