Transcript lecture 8 subqueries REVISED

Subqueries
SQL Subqueries: queries within queries
So far when data has been filtered the filter has been known
and simply added to the Where clause but often you don’t
know what the filter value is OR you don’t want to hardcode
a value into the query. If the filter can be found elsewhere in
the database then you can put a subquery in the WHERE
clause.
•Allows for variable values (list student name who have got
higher than average marks, name is in one table and
average marks from another table.)
•Hard coding is poor practice
•Values change
•Etc.
Seminar task solution
 Which tutors teach modules that all students
have passed.
 The question asked IF there were tutors who had all
students pass, select all tutors who have passed
students and select tutors who have failed then
compare the results (2 queries needed)
 Sub-queries are a better way
 Put the query that lists the tutors with failures
and add it to the where clause
Use sub-query
Select distinct name
From staffmember join class on (teacher = staffid)
Join subject on (class.subjectid = subject.subjectid)
Need to pull from
Subject to get sname
Join marks on (subject.subjectid = marks.subjectid)
Where mark >= 40
Who has passed
And staffid not in (Select staffid
From staffmember join class on (teacher = staffid)
Lists the names of staff
With failed students and
Excludes them
Join marks using (subjectid)
where mark < 40);
The basic concept is to pass a single value or many
values from the subquery to the next query and so on.
4
3
2
1
When reading or writing SQL subqueries, you should start
from the bottom upwards, working out which data is to be
passed to the next query up.
Worked example ....
Based on the database that is used for the ‘Tasks’
(University database)
1. Student names are held in the STUDENT table
2. Student marks are recorded in the MARKS table but
marks are allocated against the STUDENTID
If we want to list the names of the students who have failed
the module we need to first identify the students who have
failed the module, then use this list to select the names for
students with those id’s
To identify the students who have failed module COMP1011
Select studentid
From marks
Where subjectid = ‘COMP1011’
And mark < 40;
If we want to retrieve a name based on a student id
Select stuname
From student
Where studentid = 2271234;
Simply swap the known value for the query that returns the ID
Select stuname
From Student
Where studentid in ( select studentid
From marks
Where subjectid = ‘COMP1011’
Why
And mark < 40);
use IN?
Select stuname
From Student
Where studentid in ( select studentid
From marks
Where subjectid =
‘COMP1011’
And mark < 40);
Retrieve a list of
student ids who
have mark < 40
for COMP1011
Retrieve the
name of the
studentids in
this list.
Rules for Subqueries:
1.The data types must match, if the studentid expects
a number then the subquery must return a number.
2.Remember = means that it is expecting a single
value, you must therefore be sure that the subquery
returns only 1 result, if there is any doubt you should
use the IN keyword.
3.You can nest / use as many subqueries as you like.
4.This is not a very efficient way of coding or pulling
data from multiple tables, and you may be able to
generate the required result using joins (covered later
in the module)
Comparators
=
>
>=
<
<=
<>
equal to
greater than
greater than or equal to
less than
less than or equal to
not equal to
and other keywords ……
IN
NOT IN
ANY
ALL
Exercise - do in pairs
1.
Write a query that will list the names of who is older than the
average student.
TIP the sub-query needs to select the average age of students
this should be used then as a filter.
select stuname
from student
where age >
(select avg(age) from student);
This will return 25 students of the 74 who are
enrolled as being older than the average age.