Download Subqueries - Buisness Management - Lecture Notes and more Study notes Business Administration in PDF only on Docsity!
Practical 7
Page 1 of 4
SUB-QUERIES
Sub-Queries or “Where one query depends upon another”.
Anybody can do a SELECT…FROM…WHERE. Joins, of all types and what we intend to cover today involve the application of mathematical logic. This is not natural to all people and the only way to overcome this is through repetition. If anyone is having any problems, let me know.
IN/NOT IN (Reminder) Back in the Third Tutorial, we covered the use of IN and NOT IN, which were used in WHERE conditions to check results against a set list of values. For example…
… WHERE temperature IN (8, 10, 23); Or … WHERE temperature NOT IN (8, 10, 23)
Sub Queries Introduction Given the WEATHER and REGION tables we have already, what query would produce a result listing the provinces which have a temperature of less than 10 anywhere within the province?
SELECT DISTINCT R.province FROM region R, weather W WHERE R.city = W.city AND W.temperature < 10;
There is another way of doing this through the use of sub-queries. A Sub Query is a query ‘nested’ within the WHERE clause of a query. For example:
SELECT province FROM region WHERE city IN ( SELECT city FROM weather WHERE temperature < 10);
What is happening here? You have to A). Examine the query as a whole. B). Examine the Sub-Query and C). examine the effect of the sub-query’s results upon the first query. (BTW, the sub-query is often referred to as the ‘inner’ query and the original main query as the ‘outer’ query’).
So following this logic here what do we expect Oracle to do. A). Generally we want Province information, where cities are contained within the output of the inner query. B). The Inner query gives us a list of all cities where the temperature is less than 10 degrees. C). Now we can see that the outer query will only list those provinces which have cities where the temperature is less than 10 degrees.
A hint based on practical experience: It is not necessary, but does make things a lot clearer if you always specify the table name when specifying columns. To make things easier, it is best to use table aliases, so the last query could be re-written as:
Practical 7
Page 2 of 4
SELECT R.province FROM region R WHERE R.city IN ( SELECT W.city FROM weather W WHERE W.temperature < 10);
Also note the use of indentation. You will have noticed that SQL*plus doesn’t recognise a TAB as a TAB, but as a space, a space is fine, just use some sort of standard indentation when specifying a sub-query.
Some examples for yourselves to be carried out using sub-queries:
- List Provinces where temperature is greater than 10 and humidity is less than 70.
- List the cities and outlooks where the province is Munster.
Single Row Sub Queries The above examples in the introduction used IN, which you will remember is used to check against a list of values. That means in this context, that our sub query can return several results, (Cities from several rows). Single Row sub queries depend on the sub query returning only one value. With Single Row Sub queries we use the single row operators =, >, <, >=, <=, != (<>).
An example of a single row sub query:
List the cities which have a temperature greater then Cork on the 12th Feb ’98…
SELECT W.city FROM weather W WHERE W.temperature > ( SELECT W2.temperature FROM weather W WHERE W2.city = ‘CORK’ AND TO_CHAR(W2.day, ‘DD-MON-YY’) = ’12-FEB-98’);
Remember the use of TO_CHAR and Date Pictures. (See tutorial 4).
Notice the way that this sub-query is used to query the same table. Can you think of another way to do this query?
Some examples for yourselves to be carried out using sub-queries:
- List the city name, temperature and outlook for all cities that have a temperature greater than the average.
- List all provinces which have a string length greater the smallest string length of all the provinces.
Multiple Row Sub Queries This goes back to our first example where we used the IN operator to check against several values. The output of the inner query was several rows, and these were checked like a list would be, before the first query’s output was decided. The Multiple Row Comparison Operators as laid out in the lectures are IN, ANY and ALL.
We have already had an example of IN.
ANY is used when you want to “compare a value to each value returned by the subquery”.
Practical 7
Page 4 of 4
Explanation of the ALL operator Just so it is clear, I have never used this operator or seen it used. It can be substituted in most cases with something more intuitive. The following examples are given in order to explain it’s usage. (derived from “The complete reference”).
TEMPERATURE = ALL(4, 2, 7) Temp is equal to every item on the list. No number returned. TEMPERATURE > ALL(4, 2, 7) Temp is greater than the greatest value in the list, anything larger than 7 qualifies. TEMPERATURE >= ALL(4, 2, 7) Temp is greater than or equal to the greatest item on the list. Anything larger or equal to 7 qualifies. TEMPERATURE < ALL(4, 2, 7) Temp is less than the lowest item on the list. Anything below 2 qualifies. TEMPERATURE <= ALL(4, 2, 7) Temp is less than or equal to the lowest item on the list, so 2 or below. TEMPERATURE != ALL(4, 2, 7) Temp is not equal to any item on the list. So anything except 4, 2 or 7 qualifies.
An example…
SELECT W.city FROM weather W WHERE W.temperature > ALL( SELECT W2.temperature FROM weather W WHERE W2.city = ‘CORK’);
Try the same query with different operators. (Also note that ALL is not restricted for use in sub-queries).
Explanation of the ANY operator The same goes for this, never used it or seen it used. Here are some more examples from the book to help explain.
TEMPERATURE = ANY(4, 2, 7) Temp is equal to any item on the list. 2, 4, 7 would all qualify. TEMPERATURE > ANY(4, 2, 7) Temp is greater than any value in the list, so even 3 qualifies. TEMPERATURE >= ANY(4, 2, 7) Temp is greater than any value in the list, so even 2 qualifies. TEMPERATURE < ANY(4, 2, 7) Temp is less than any item on the list. 6 qualifies. TEMPERATURE <= ANY(4, 2, 7) Temp is less than or equal to any item on the list, so 7 or below. TEMPERATURE != ANY(4, 2, 7) Temp is not equal to any single item on the list. Any number qualifies as long as the list has more than one value.
Example…
SELECT W.city FROM weather W WHERE W.temperature > ALL( SELECT W2.temperature FROM weather W WHERE W2.city = ‘CORK’);
Practical 7
Page 5 of 4
Using Group Functions With Sub-Queries Last week when we covered Grouping, we learned how the use of HAVING allows us to place conditions on the groups of data our query deals with. For example:
SELECT city, MIN(temperature) FROM weather GROUP BY city HAVING MIN(temperature) > 5;
Here we have split the data in the weather table into groups of rows, which have common data in the CITY column. From these groups, we asked for the City name and the Minimum temperature for each group. The groups have first been screened with the HAVING clause to make sure that the minimum temperature in each group is greater than 5 degrees.
Instead of supply the number 5, what if we wanted the condition to be “Greater than the average temperature minus five degrees”… Because we don’t know the value of the average temperature, we can use a sub-query to find out for us.
SELECT W.city, MIN(W.temperature) FROM weather W GROUP BY W.city HAVING MIN(W.temperature) > ( SELECT AVG(W2.temperature)- FROM weather W2);
So we can also use Sub queries as part of a HAVING clause, not just a WHERE clause.
Multiple Column Queries So far we have covered sub queries where we have only been comparing one value between the queries. But we can just as easily specify more than one column.
SELECT W.city, W. temperature FROM weather W WHERE (W.temperature, W.humidity) IN ( SELECT W2.temperature, W2.humidity FROM weather W WHERE W2.city > ‘DUBLIN’ AND TO_CHAR(W2.day, ‘DD-MON-YY’) = ’12-FEB-00’) AND W.city != ‘TRALEE’;
Two things to note in this query:
- The specification of two columns in the WHERE clause.
- The addition of a new query outside of the sub-query, i.e. It is part of the outer query.
