Final Destination 2

Here is my problem:

10. How many charters a flight each pilot. List driver number, name, last name and the number of flights in the result. Sort the final result by the number of charters. (COUNT function, a GROUP BY)

The tables I have included all the tables that were created for some may not be necessary for the problem.

- CREATE TABLE ... --------------------------------- SECTION
- Table Definition c_model
CREATE TABLE c_model
(C_model_code CHAR (8)
Manufacturer VARCHAR2 (20) NOT NULL,
C_model_name VARCHAR2 (20) NOT NULL,
total_seats NUMBER (3) DEFAULT 0 NOT NULL,
chrg_per_mile NUMBER (4,2) NOT NULL DEFAULT 0.00,
C_model_code_pk CONSTRAINT PRIMARY KEY (c_model_code));

- Table Definition c_aircraft
CREATE TABLE c_aircraft
(Ac_no CHAR (5)
C_model_code CHAR (8) NOT NULL,
ac_ttaf NUMBER (7,1) DEFAULT 0.0,
ac_ttel NUMBER (7,1) DEFAULT 0.0,
ac_tter NUMBER (7,1) DEFAULT 0.0,
C_aircraft_ac_no_pk CONSTRAINT PRIMARY KEY (ac_no)
C_aircraft_c_model_code_fk CONSTRAINT FOREIGN KEY (c_model_code) c_model REFERENCES (c_model_code));

- Table Definition c_employee
CREATE TABLE c_employee
(Emp_no NUMBER (3)
Lname VARCHAR2 (20) NOT NULL,
Fname VARCHAR2 (20) NOT NULL,
Dob DATE NOT NULL,
NOT NULL DATE hire_dt,
C_employee_emp_no_pk CONSTRAINT PRIMARY KEY (emp_no));

- Table Definition c_pilot
CREATE TABLE c_pilot
(Emp_no NUMBER (3)
License_type CHAR (3)
Medical_type CHAR (1)
NOT NULL DATE medical_dt,
pt135_dt DATE
C_pilot_empno_pk CONSTRAINT PRIMARY KEY (emp_no)
C_pilot_empno_fk CONSTRAINT FOREIGN KEY (emp_no) c_employee REFERENCES (emp_no)
c_pilot_licensetype_ch constraint CHECK (license_type = ATP or license_type = 'COM')
c_pilot_medicaltype_ch constraint CHECK (medical_type = '1 'or '2 medical_type ='));

- Table Definition c_customer
CREATE TABLE c_customer
(Cust_no NUMBER (5)
Lname VARCHAR2 (20) NOT NULL,
Fname VARCHAR2 (20) NOT NULL,
CHAR area code (3) NOT NULL,
Phone CHAR (7) NOT NULL,
Balance NUMBER (7,2) DEFAULT 0.0,
C_customer_cust_no_pk CONSTRAINT PRIMARY KEY (cust_no));

- Definition Table Charter
CREATE TABLE c_charter
(Trip_no number (6)
NOT NULL DATE chrtr_dt,
c_pilot_no NUMBER (3) NOT NULL,
coc_pilot_no NUMBER (3)
ac_no CHAR (5) NOT NULL,
CHAR destination (3) NOT NULL,
distance NUMBER (6) DEFAULT 0 NOT NULL,
flight_hrs NUMBER (3,1) NOT NULL DEFAULT 0.0,
flight_hrs_wait NUMBER (3,1) NOT NULL DEFAULT 0.0,
gal_of_fuel NUMBER (5,1) NOT NULL DEFAULT 0.0,
cust_no NUMBER (5) NOT NULL,
C_charter_tripno_pk CONSTRAINT PRIMARY KEY (trip_no)
C_charter_c_pilotno_fk CONSTRAINT FOREIGN KEY (c_pilot_no) c_pilot REFERENCES (emp_no)
KEY CONSTRAINT FOREIGN c_charter_coc_pilotno_fk (coc_pilot_no) c_pilot REFERENCE (emp_no)
C_charter_acno_fk CONSTRAINT FOREIGN KEY (ac_no) c_aircraft REFERENCES (ac_no)
C_charter_custno_fk CONSTRAINT FOREIGN KEY (cust_no) c_customer REFERENCES (cust_no));

Thank you!

I guess the driver is c_pilot_no, coc_pilot_no is the co-pilot and all that you want the old.

First, you need a subquery to get the drivers and the number of charters they have stolen:

c_pilot_no SELECT COUNT (*) as charters GROUP BY c_pilot_no

Then just join this table to the other requirements for other columns. In dealing with subqueries, I prefer explicit clauses WHERE instead of JOIN for allowing me to control the order of search table (and subsequently the overall speed of the application):

AS SELECT p.emp_no driver #, + e.fname "e.lname +" as a noun,
c.charters AS "Charters.