/* Using a Sub-query as a filter in the where clause */

SELECT  course_id, description, price FROM course
WHERE description LIKE '%Web%' 
      AND price  <  
	(
		SELECT AVG(price)  FROM course		/* obtain the average */
		WHERE description LIKE '%Web%'
	)
;

/* Obtain all students taking a SQL class */ 
/* This eample can also be done using table joins */

SELECT  *   FROM student
WHERE ssn  in 
	(
	SELECT stu_ssn  FROM class
	WHERE course_id  in   
		(
		SELECT course_id  FROM course
		WHERE description  LIKE  '%SQL%'
		)
	) 
;

/* Obtain lastest payment for every vendor */
/* in this case it is latest payment for every vendor and description */ 
/* correlated sub-query */ 

SELECT  * from payment o
WHERE pay_date =  
	(
		SELECT max(pay_date) from payment i
		WHERE i.vendor      = o.vendor
          	  and i.description = o.description
	) 
;