Embedded Files
SDD Topic has been refreshed!
We will continue using the Travel Agency Database as shown below:
What is needed in the design for a query?
What is needed in the design for a query?
The design of an SQL query should indicate:
the fields and/or calculations required
the table(s) or query(-ies) needed to provide the details required
any search criteria to be applied
what grouping is needed (if appropriate)
the criteria to be applied to the grouping (if appropriate)
the field(s) used to sort the data and the type(s) of sort required
The rest of this page will cover the new content required for the design of queries at Adv Higher.
HAVING Clause
HAVING Clause
The SQL HAVING clause is used in combination with the GROUP BY clause or an aggregate function.
It restricts the returned rows to only those where the HAVING condition is true.
HAVING is used to filter records that work on summarised GROUP BY results.
The WHERE clause cannot be used with aggregate functions. The HAVING clause is applied to grouped records, but WHERE is applied to individual records.
HAVING can also be used in combination with WHERE and ORDER BY clauses, for example:
The WHERE clause is used to restrict the rows that are returned from the tables(s).
The ORDER BY clause is used to sequence the rows in the answer table.
The HAVING clause is used to filter summarised and/or aggregated data or grouped data.
So the new order for the clauses within an SQL Query is now:
Logical Operators
Logical Operators
Logical operators are used, together with the comparison operators =, <, >, <=, >= and LIKE, in the WHERE/HAVING clause of a SELECT query to form a condition that restricts the rows returned from a search.
At National 5 we used the following logical operators:
AND
OR
We will now use the following operators at Advanced Higher:
NOT
BETWEEN
IN
ANY
EXISTS
Logical Operator: NOT
Logical Operator: NOT
The NOT operator returns a record from the underlying tables when the specified condition is not true.
Example Syntax
Logical Operator: BETWEEN
Logical Operator: BETWEEN
The BETWEEN operator selects values that fall within a specified range of values. The results returned are inclusive of the lower and upper values.
Example Syntax
Logical Operator: IN
Logical Operator: IN
The IN operator allows multiple values to be specified as an alternative to multiple OR conditions and can be used to select values using subquerys.
Example
Example
Sub Query Example
Sub Query Example
Logical Operator: ANY
Logical Operator: ANY
The ANY operator returns TRUE if any of the subquery values meet the condition specified in the main query. The operator below could be any operator required such as LIKE, =
Additional Notes: ANY
Query 1: If we look at the graphical representation of the query below. The results of the inner query can be viewed as the coloured balls.
>ANY means greater than at least one value, that is, greater than the minimum.
So >ANY(28,59,89,17,6,12) means greater than 6.
As 90>6 is true, data is returned. Using the ANY operator, generates TRUE and so returns data to the main query.
Query 2: using the ANY operator, the query below would generate FALSE and so does not return data to the main query.
<ANY means less than at least one value, that is, less than the maximum.
So <ANY(28,59,89,17,6,12) means less than 89.
As 90<89 is false, no data is returned.
Query 3: using the ANY operator, the query below would generate FALSE and so does not return data to the main query.
<ANY means less than at least one value, that is, less than the maximum.
So <ANY(28,59,89,17,6,12) means less than 89.
As 4<89 is TRUE, data is returned.
Logical Operator: Exists
Logical Operator: Exists
EXISTS:
is a comparison operator
is used in the WHERE clause to validate an ‘it exists’ condition
will tell whether a query returned results
returns a Boolean, (TRUE or FALSE)
returns TRUE if a subquery contains any rows
Query 4: general format of an SQL query that uses the EXISTS operator. The subquery will be evaluated first and if there was ANY results the the EXISTS (subquery) would evaluate as TRUE.
Query 5: In the example below the inner query would return some values ( as there are values in the COL_1 field that are <=15). Using the EXISTS operator, returns TRUE.
The subquery contains more than one row, so it returns TRUE.
Data is therefore returned from the main query.
Query 6: In the example below the inner query would not return any values ( as there are no values in the COL_1 field that are >=15). Using the EXISTS operator, returns FALSE.
The subquery contains no rows, so it returns FALSE.
No data is therefore returned from the main query.
Subqueries in the WHERE clause
Subqueries in the WHERE clause
A subquery is a query embedded within the WHERE clause of another SQL query. A subquery can be referred to as an inner query or a nested query, and an SQL query is sometimes referred to as the outer query or the parent query.
An example of a subquery is shown below:
The subquery which finds the maximum score will be executed first and then the outer query will find all scores which are the same as this maximum value.
The subquery executes before the main query, so the results can be passed to the main query as a condition to further restrict the data to be retrieved. It is useful to test your subqueries as a component before integrating into a larger query.
Rules for using a subquery
Rules for using a subquery
Subqueries must be enclosed within brackets.
Unless the main query has multiple fields in its SELECT clause, a subquery can have only one field in its SELECT clause.
The BETWEEN operator can be used within a subquery but cannot be applied to the results of a subquery returned to the main query.
An ORDER BY clause can be used with the main query, but an ORDER BY clause cannot be used in a subquery; if it is needed, the GROUP BY clause can be used to perform the same function as the ORDER BY within a subquery.
Many subqueries return exactly one record (called single-value subqueries);this must be tested because an error will be generated if a subquery returns more results than expected.
Subqueries that return more than one row (called multiple-value subqueries), can only be used with multiple-value operators such as EXISTS, IN and ANY.
Page updated
Report abuse