9569

Part 2. Correct the mistakes in the following code.

1. List the aid, DateInService, aname, and MachSpeed for all aircrafts.

SELECT AID, DateInService, Aname, MachSpeed

FROM Aircraft, AircraftTypes

Write corrected code:

2. List the athlete id, meet name and date, event name and score for all athlete performances.

SELECT AID, meet, event, score, meetdate

FROM performances NATURAL JOIN meets

Write corrected code:

3. Using the SOdetail table (short for Sales Order Detail) find the PartNo and the total quantity of units sold (renamed as UNITSSOLD) for each Part.

SELECT partno, qty AS UNITSSOLD

FROM SOdetail

GROUP BY partno

Write corrected code:

4. Using the SOdetail table (short for Sales Order Detail) find the PartNo and the total quantity of units sold (renamed as UNITSSOLD) for each Part, only show those inventory items that have sold more than 40000 units.

SELECT partno, SUM(qty) AS UNITSSOLD

FROM SOdetail

GROUP BY partno

WHERE SUM(qty) > 40000

Write corrected code:

Part 3. Write SQL code for the following questions

1. Use INNER JOIN (not NATURAL JOIN) to join tables Sections and Enrollments, list term, course number, sid, and grade. Note, the PK of Sections (consequently the FK in Enrollments) is composite. When joining the two tables, the joining condition need to specify both columns.

2. List term, course number, faculty name who teaches the class, sid, and grade for all enrollment records.

3. List the sid and last and first name of all students who have not enrolled in any classes.