Homework assignment

I am in a college system development class, and we are using SQLPlus with some tables and data loaded to answer questions with SQL statements. I am stuck on a few questions. I was able to answer 30 of the 40 using the references. The questions have gotten very difficult in my opinion now. I write the statements, but I am not getting what the question asks for. Here is one that I am stuck on now. Here is the tables, so you know what fields there are to select from.

/* create AIRPORT */

Drop table airport;

create table AIRPORT
( air_code varchar2 (3)
CONSTRAINT airport_code_pk PRIMARY KEY,
air_location varchar2 (35),
elevation number (4,0),
air_phone varchar2 (12),
service_center char (1),
airpt_type_code char(2)
);

/* CREATE AIRPORT CLASS */

drop table airpt_class;

create table airpt_class
(airpt_type_code char(2)
constraint air_type_pk PRIMARY KEY,
airpt_type_descr varchar2 (25) );

/* CREATE Frequent_flyer */

drop table frequent_flyer;

create table frequent_flyer
(ff_no number(4)
constraint freq_flyer_pk primary key,
L_name varchar2(15) not null,
F_name varchar2(15) not null,
Str_Address varchar2(30),
City varchar2(25),
State char(2),
zip_code char(5),
total_miles number (8),
cur_year_miles number (6),
first_pur_date date);

/* CREATE FLIGHT */

drop table flight;

create table FLIGHT
(
flight_no number (4)
CONSTRAINT flight_flight_no_pk PRIMARY KEY,
orig varchar2 (3),
dest varchar2 (3),
orig_time date,
dest_time date,
meal varchar2 (1),
fare number (7,2),
ff_miles number (4)
);

/* CREATE RESERVATION */

drop table reservation;

create table reservation
(
confirm_no number (4),
res_date date,
res_name varchar2 (20),
res_phone varchar2 (12),
paid_flag char (1),
CONSTRAINT reservation_confirm_no_pk
PRIMARY KEY (confirm_no)
);

/* CREATE PASSENGER */

drop table passenger;

create table passenger
(
pass_name varchar2 (20)
CONSTRAINT passenger_pass_name_nn NOT NULL,
itinerary_no number (4)
CONSTRAINT passenger_itin_no_pk PRIMARY KEY,
ff_no number (4),
confirm_no number (4) NOT NULL
);

/* CREATE TICKET */

drop table ticket;

create table ticket
(
itinerary_no number (4)
CONSTRAINT ticket_itin_no_nn NOT NULL,
flight_no number (4)
CONSTRAINT ticket_flight_no_nn NOT NULL,
flight_date date,
seat varchar2 (3),
fare_charged number(7,2),
CONSTRAINT ticket_itin_fltno_fltdate_pk
PRIMARY KEY (itinerary_no, flight_no, flight_date),
CONSTRAINT ticket_itinno_fk
FOREIGN KEY (itinerary_no)
REFERENCES passenger
ON DELETE CASCADE,
CONSTRAINT ticket_fltno_fk
FOREIGN KEY (flight_no)
REFERENCES flight
ON DELETE CASCADE
);

/* CREATE SEAT */

drop table seat;

create table seat
(equip_type varchar2(6),
seat varchar2(3)
);

/* CREATE STAFF */

drop table staff;

create table staff
(
staff_no number (4),
staff_name varchar2 (20),
staff_type varchar2 (20),
staff_date date,
CONSTRAINT staff_staff_no_pk PRIMARY KEY (staff_no)
);

/* CREATE FLIGHT_DAY */

/* drop table */
drop table flight_day;

/* create table */
create table flight_day
(
flight_no number (4)
CONSTRAINT flight_day_flight_no_nn NOT NULL,
flight_date date
CONSTRAINT flight_day_flight_date_nn NOT NULL,
equip_no number (3,0),
pass_ctr integer,
depart_time date,
arrive_time date,
fuel_used integer,
status_note varchar2 (20),
CONSTRAINT flight_day_no_date_pk
PRIMARY KEY (flight_no, flight_date),
CONSTRAINT flight_day_flight_no_fk
FOREIGN KEY (flight_no)
REFERENCES flight
ON DELETE CASCADE
);

/* CREATE FLIGHT_CREW */

drop table flight_crew;

create table flight_crew
(
flight_no number (4)
CONSTRAINT flight_crew_flight_no_nn NOT NULL,
flight_date date
CONSTRAINT flight_crew_flight_date_nn NOT NULL,
staff_no number (4)
CONSTRAINT flight_crew_staff_no_nn NOT NULL,
work_hrs number (4,1),
CONSTRAINT flight_crew_fltno_date_staf_pk
PRIMARY KEY (flight_no, flight_date, staff_no),
CONSTRAINT flight_crew_fltno_date_fk
FOREIGN KEY (flight_no, flight_date)
REFERENCES flight_day
ON DELETE CASCADE,
CONSTRAINT flight_crew_staffno_fk
FOREIGN KEY (staff_no)
REFERENCES staff
ON DELETE CASCADE
);

/* create table equipment */

drop table equipment;

create table equipment
( equip_no number (3,0)
CONSTRAINT equipment_no_pk PRIMARY KEY,
equip_type varchar2 (6),
equip_date date
);

/* CREATE EQUIP_TYPE */

drop table equip_type;

create table equip_type
( equip_type varchar2 (6)
CONSTRAINT equip_type_type_pk PRIMARY KEY,
mfg varchar2 (15),
seats number (3),
fuel_cap number
);

/* CREATE NOTES */

drop table notes;

create table notes
(
confirm_no integer not null PRIMARY KEY,
note varchar2 (50),
CONSTRAINT notes_confirm_no_fk
FOREIGN KEY (confirm_no)
REFERENCES reservation
ON DELETE CASCADE
);

/* ADD FOREIGN KEYS TO FLIGHT */

alter table flight
add constraint orig_for_key
foreign key (orig) references airport;
alter table flight
add constraint dest_for_key
foreign key (dest) references airport;

The question wants - the people's name with reservations, flight numbers, reservation date, and the flight date for all reservations between 30 and 60 days in advance in 2002. The professor wants us to join the reservation.confirm_no to the ticket.Itinerary_no. Make it a select distinct query to keep it to 22 rows coming back.

I am getting the reservation date after the flight date with my statement. I wrote
SELECT DISTINCT reservation.res_date, reservation.Res_name, ticket.flight_date, ticket.flight_d
ate-30 AS "ADVANCE NOTICE"
2 FROM reservation, ticket
3 WHERE ticket.itinerary_no=reservation.confirm_no
4 AND reservation.res_date > '01-JAN-02'
5 AND reservation.res_date < '01-JAN-03';
Any pointers?Well, I doubt anyone will do all the work for you, but if all you are looking for is pointers...

Take a look at the BETWEEN command.

good luck!|||Originally posted by amesjustin
Well, I doubt anyone will do all the work for you, but if all you are looking for is pointers...

Take a look at the BETWEEN command.

good luck!

I wouldn't want anyone to the work. I have tried a between and couldn't figure how to get the statement to reflect between 30 and 60 days. How do you set up the statement to minus the thirty or sixty? I tried to To_char but my resources did not have good examples.|||I have tried a "between" but which variables would it be best to use it on or can you use two? We have had a real quick lesson on SQL. It is an Internet class so seeing the teacher is through emails. I put my statement of what I wrote at the very bottom. I was asking for pointers on my statement. I guess I get confused since there is two or three variables I am looking at. 1) all reservations in 2002 with 30 to 60 days reserved in advance, 2) setting it to be between Jan 1 and Dec 31 3) getting a number back on how many days it was too.
The "between" examples I saw usually followed the "where" and my "where" items are comparing an itinerary number and confirmation number. I like learning SQL but have noticed it is like java and any small detail can through it off like comma, period, paranthesis, and etc...

I have worked on this one problem over six hours. That is why I decided to enlist a discussion group for help. Thanks.