Subqueries

1. Subqueries

2. 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.

3. 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

4. Use sub-query Select distinct name From staffmember join class on (teacher = staffid) Join subject on (class.subjectid = subject.subjectid) Join marks on (subject.subjectid = marks.subjectid) Where mark >= 40 And staffid not in (Select staffid From staffmember join class on (teacher = staffid) Join marks using (subjectid) where mark < 40); Need to pull from Subject to get sname Who has passed Lists the names of staff With failed students and Excludes them

5. 4 3 2 1 The basic concept is to pass a single value or many values from the subquery to the next query and so on. 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.

6. Worked example .... Based on the database that is used for the ‘Tasks’ (University database) Student names are held in the STUDENT table 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

7. 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’ • And mark < 40); Why use IN?

8. 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.

9. Rules for Subqueries: The data types must match, if the studentid expects a number then the subquery must return a number. 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. You can nest / use as many subqueries as you like. 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)

10. 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

11. Exercise - do in pairs 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.