Cs 122
Spring 2005
Lesson 5: Subquery and its usage.
Written by: Jen Chen
Date: 04/28/05


(1) Purpose: To list all instructors/professors who teach the most number of units in
	Spring 2005.
    Suggestion: First of all, we notice that there is no table that contains the information
	that we want (the most number of total teaching units).
	Then our first task is to find out what is the maximal number of units taught by
	any of the professors who teach in Spring 2005.
	To do this, let's take a look at the three tables: Instructor, Classes, and Courses.
	From these tables we'll investigate to see from which of these tables can we obtain
	the necessary information (maximal number of units taught in SPring 2005).
	The only table that contains the number of units taugh, the year, and the quarter
	is the Classes-table. This means that we would query this table for the maximal number
	of units taught.
	Since each Instructor/Professor teach many classes, then we'll sum up the total number 
	of units of the classes taught by EACH of these Instructor/Professor.
	The SQL query to obtain this information is:

		select instID,sum(unit) AS TOTAL_UNIT from Classes where instID<>'' group by instID		
	
		+-----------+------------+
		| instID    | TOTAL_UNIT |
		+-----------+------------+
		| 000000001 | 12         |
		| 000000002 | 6          |
		| 000000003 | 4          |
		| 000000005 | 5          |
		| 000000006 | 6          |
		| 000000007 | 10         |
		| 000000008 | 12         |
		| 000000009 | 12         |
		| 000000010 | 7          |
		| 000000011 | 6          |
		| 000000012 | 8          |
		| 000000013 | 3          |
		| 000000014 | 4          |
		| 000000015 | 8          |
		| 000000016 | 3          |
		| 000000017 | 5          |
		+-----------+------------+
		16 rows in set (0.00 sec)
	
	Let's look at the result set and we see that there are three Instructors/Professors who
	teach 12 units for Spring 2005 quarter.
	The next thing that we need to do is to qeury from this result set (which comes from a 
	SQL query by itself) the maximal value of units and their corresponding instId.
	The query is as follow:
		
		select max(TOTAL_UNIT) from (select instID,sum(unit) AS TOTAL_UNIT 
			   from Classes where instID<>'' group by instID
		
		+-----------------+
		| max(TOTAL_UNIT) |
		+-----------------+
		| 12              |
		+-----------------+
		1 row in set (0.00 sec)

	So 12 is the maximal number of units taught by any of the professors during Spring 2005.
	using this value as a constraint in our query from the first table, and using the result
	of the first query as a table by itself, we come up with the following query:

	select instID, TOTAL_UNIT
	from
	   (select instID,sum(unit) AS TOTAL_UNIT from Classes where instID<>'' group by instID) B
	where
	   TOTAL_UNIT = (select max(TOTAL_UNIT) from (select instID,sum(unit) AS TOTAL_UNIT 
			   from Classes where instID<>'' group by instID) T)

	Result:

	+-----------+------------+
	| instID    | TOTAL_UNIT |
	+-----------+------------+
	| 000000001 | 12         |
	| 000000008 | 12         |
	| 000000009 | 12         |
	+-----------+------------+
	3 rows in set (0.00 sec)

	The result set exactly what we want.

	
(2) Based on the previous result set, list the names (full names) of all Instructors/
Professors who teach the most number of units in Spring 2005.
    Suggestion: From the result set in (1), we can query the Instructor/Professor's name
from the Instructor's table. (since this is THE table that contains the Instructor/Professor's
information). The field to link these two tables is instID field.
    Again, we'll use the entire SQL-query above as EITHER a condition OR as a TABLE by itself
in our SQL statement.
    (a) To use our query from part (1) as a constraint in the WHERE clause: Note that we
only need to COMPARE each instID from the Instructor table with each instID from the result set
in part (1). Thus we'll modify our SELECT-statement in part (1) so that the result set contain
ONLY the instID but not the total number of units as follow:

	select instID
	from
	   (select instID,sum(unit) AS TOTAL_UNIT 
		from Classes 
		where instID<>'' group by instID) B
	where
	   TOTAL_UNIT = (select max(TOTAL_UNIT) 
			    from (select instID,sum(unit) AS TOTAL_UNIT 
				     from Classes 
				     where instID<>'' 
				     group by instID) T)

	+-----------+
	| instID    |
	+-----------+
	| 000000001 |
	| 000000008 |
	| 000000009 |
	+-----------+
	3 rows in set (0.00 sec)

	Use the above query as a constraint in the WHERE clause when we 
	query our Instructor table, we obtain the following SQL-statement:

	select concat(firstName,' ', lastName) from Instructor
	where
	instID in (
	select instID
	from
	   (select instID,sum(unit) AS TOTAL_UNIT 
		from Classes 
		where instID<>'' group by instID) B
	where
	   TOTAL_UNIT = (select max(TOTAL_UNIT) 
			    from (select instID,sum(unit) AS TOTAL_UNIT 
				     from Classes 
				     where instID<>'' 
				     group by instID) T)
			)

	+------------------+
	| Name             |
	+------------------+
	| Jen Chen         |
	| Valentino Crespi |
	| Elaine Kang      |
	+------------------+
	3 rows in set (0.00 sec)	

	The result set contains all of the information that we want.

	OR you can use a better query to improve performance: (This time, we use the
	subquery as a table to query from, NOT as a constraint in the WHERE clause)

	select concat(firstName,' ', lastName)
	   from Instructor I,
       	    (select instID,sum(unit) AS TOTAL_UNIT
        	from Classes
                where instID <> '' group by instID) C
	   where
	        	  I.instID = C.instID
	   AND
        	   C.TOTAL_UNIT = (select max(TOTAL_UNIT)
                	            from (select instID,sum(unit) AS TOTAL_UNIT
                        	             from Classes
                                	     where instID<>''
	                                     group by instID) T)

	+------------------+
	| Name             |
	+------------------+
	| Jen Chen         |
	| Valentino Crespi |
	| Elaine Kang      |
	+------------------+
	3 rows in set (0.00 sec)	


(3) Based on the result from part 2, display the total number of unit next to the
Instructor/Professor' name.
    Suggestion:	Use the subquery

	select max(TOTAL_UNIT) from (select instID,sum(unit) AS TOTAL_UNIT from Classes where instID<>'' group by instID) T

    as a returning field in our SELECT-statement. (since this query returns the 
    maximal number of unit taught by any professors during Spring 2005) 

	select concat(firstName,' ', lastName)  AS Name,
	(select max(TOTAL_UNIT) 
	   from (select instID,sum(unit) AS TOTAL_UNIT 
		   from Classes 
		   where instID<>'' 
		   group by instID) T) AS Unit
	from Instructor
	where
	instID in (
		select instID
		from
		   (select instID,sum(unit) AS TOTAL_UNIT from Classes where instID<>'' group by instID) B
		where
		   TOTAL_UNIT = (select max(TOTAL_UNIT) 
				    from (select instID,sum(unit) AS TOTAL_UNIT 
					     from Classes 
					     where instID<>'' 
					     group by instID) T)
		  		)

	+------------------+------+
	| Name             | Unit |
	+------------------+------+
	| Jen Chen         | 12   |
	| Valentino Crespi | 12   |
	| Elaine Kang      | 12   |
	+------------------+------+
	3 rows in set (0.00 sec)


	OR for better performance, use the following query:

	select concat(firstName, ' ', lastName) AS NAME,
	   (select max(TOTAL_UNIT)
	          from (select instID,sum(unit) AS TOTAL_UNIT
        	           from Classes
                	   where instID<>''
	                   group by instID) S) AS UNITS
	from 
	     Instructor I,
	     (select instID,sum(unit) AS TOTAL_UNIT
	         from Classes
	          where instID <> '' group by instID) C
	where
	   I.instID = C.instID
	AND
	   C.TOTAL_UNIT = (select max(TOTAL_UNIT)
        	              from (select instID,sum(unit) AS TOTAL_UNIT
                	               from Classes
                        	        where instID<>''
                                	group by instID) T
                  	)

	+------------------+-------+
	| NAME             | UNITS |
	+------------------+-------+
	| Jen Chen         | 12    |
	| Valentino Crespi | 12    |
	| Elaine Kang      | 12    |
	+------------------+-------+
	3 rows in set (0.00 sec)