90-802 Computer Applications for Public Managers

Chapter 3: SQL

Exercise 2a, 2c, 2d and 4 Homework Answer


Note - they do not need to precede the attribute names in the SELECT clause with the table names, even if they are ambiguous. I neglected to discuss this in class. However, I did mention the need for table names in the WHERE clause.

When helping them with this homework, make sure they have first identifed the PK and FKs for each table involved in the query. I assign SQL not so much so they can learn the language, but more to enforce the concepts of PK and FK as the mechanism for re-combining the data to produce useful information. 


2a.	SELECT C_DATE, AC_NO, DEST, DIST, HR_FL, FUEL
	FROM CHARTER
	WHERE AC_NO="2778V";					

2c.	SELECT C_DATE, AC_NO, DEST, AC_TYPE, DIST, HR_FL, 	
                           FUEL,  P_LNAME
	FROM CHARTER , PILOTS , AIRCRAFT 
	WHERE CHARTER.AC_NO  =  "2778V"       AND
                            CHARTER.PIL = PILOTS.P_INIT AND
                            AIRCRAFT.AC_NO = .CHARTER.AC_NO;				

2d.	SELECT C_DATE, AC_NO, DIST, HR_FL, FUEL
                           DIST/HR_FL,
        	               DIST/FUEL, FUEL/HR_FL
 	FROM CHARTER, PILOTS
	WHERE CHARTER.PIL = PILOTS.P_INIT AND
                          CHARTER.AC_NO="2778V";
				
4.	SELECT C_DATE, DEST, AC_TYPE, S_CNAME
	FROM CHARTER, AIRCRAFT, CHAR_CUS 
	WHERE CHAR_CUS.C_BALANCE > 0 AND
                            CHAR_CUS.C_CUSNO = CHARTER.C_CUSNO AND
                            AIRCRAFT.AC_NO = CHARTER.AC_NO;