/* Using subqueries as additional derived columns */

/* generating an average column */

SELECT description, price,
       (SELECT AVG(price) FROM course) AS avg_price 
FROM course;



/* generating an average column and percent of average */

SELECT description, price,
       (SELECT AVG(price) FROM course) AS avg_price, 
       ( price / (SELECT AVG(price) FROM course)  ) AS percent_to_avg 
FROM course;



/* Correlated sub-query */
/* Obtaining the number of classes each student is taking */
/* this could also be done using joins */

SELECT lname, fname, ssn,   
      (							/* a derived column */
	SELECT  COUNT(stu_ssn)  
     	FROM class c
     	WHERE  c.stu_ssn = s.ssn			/* inner/outer table join */
      )  
	AS  classes_taken
FROM student s
ORDER BY lname;