/* run the commands below to create the sample tables and populate them with data */

CREATE TABLE Flight_Number 
 (Flight_Code number(3),
 Airport_From char(3) NOT NULL,
 Airport_To char(3) NOT NULL,
 Scheduled_Time_Depart date,
 Scheduled_Time_Arrive date,
 Plane varchar2(5),
 PRIMARY KEY (Flight_Code)
);

insert into flight_number values (13, 'LAX', 'PHL', to_date('00:19','hh24:mi'), to_date('6:00','HH24:mi'), '747');
insert into flight_number values (207, 'PHL', 'JFK', to_date('08:15','hh24:mi'), to_date('09:01','HH24:mi'), 'DC10');
insert into flight_number values (156, 'LAX', 'JFK', to_date('20:10','hh24:MI'), to_date('1:38','HH24:mi'), '747');
insert into flight_number values (219, 'PHL', 'JFK', to_date('14:10','HH24:mi'), to_date('14:57','HH24:mi'), 'DC10');
insert into flight_number values (280, 'JFK', 'PHL', to_date('18:30','HH24:mi'), to_date('19:27','HH24:mi'), 'TURBO');

CREATE TABLE Flight_Instance
 (Flight_Code number(3),
  Flight_Date date,
  Num_Passengers number(3),
  Actual_Time_Depart date,
  Actual_Time_Arrive date,
  PRIMARY KEY (Flight_Code, Flight_Date)
);

insert into flight_instance values (013, to_date('11-jan-01'), 287, to_date ('00:23', 'HH24:mi'), to_date('6:10','HH24:mi'));
insert into flight_instance values (013, to_date('12-jan-01'), 312, to_date ('01:10', 'HH24:mi'), to_date('6:45','HH24:mi'));
insert into flight_instance values (207, to_date('11-jan-01'), 189, to_date ('08:15', 'HH24:mi'), to_date('9:23','HH24:mi'));
insert into flight_instance values (207, to_date('12-jan-01'), 201, to_date ('08:25', 'HH24:mi'), to_date('9:26','HH24:mi'));
insert into flight_instance values (207, to_date('30-jul-01'), Null, Null, Null);

/* Here are some possible queries to run */

select * from cat;
describe flight_instance;

select * from flight instance;
select * from flight_number;

insert into flight_number values (13, 'LAX', 'PHL', to_date('00:19','hh24:mi'), to_date('6:00','HH24:mi'), '747');
(This will not work)

/* Try this with and without the Where condition to see how a join works. */
select flight_number.flight_code, flight_instance.flight_code, flight_instance.flight_date, flight_instance.num_passengers
 from flight_instance, flight_number
 where flight_instance.flight_code = flight_number.flight_code;


select flight_number.flight_code, flight_instance.flight_date, flight_instance.num_passengers
 from flight_instance, flight_number
 where flight_instance.flight_code = flight_number.flight_code
 order by flight_instance.flight_date, flight_instance.flight_code;


/* The (+) here means an OUTER join in Oracle.
Note the use of to_char to convert the date into a suitable time format*/

select num.flight_code, ins.flight_date, to_char(num.scheduled_time_depart,'HH24:mi') , to_char(ins.actual_time_depart, 'HH24:mi')
 from flight_instance ins, flight_number num
 where ins.flight_code (+) = num.flight_code;

** syntax below will not work in Oracle, but is the ISO SQL 92 office standard for joins **

select flight_number.flight_code, flight_instance.flight_code, flight_instance.flight_date, flight_instance.num_passengers
 from flight_instance ins INNER JOIN flight_number num 
 on ins.flight_code = num.flight_code;

/*-----------------------------------*/
/* PART 2 -- FUN WITH FOREIGN KEYS */
/*-----------------------------------*/

/* Now here are some more advanced alternative definitions for the table */
/* first delete the old version of the table */

drop table flight instance;


CREATE TABLE Flight_Instance
 (Flight_Code number(3) CHECK (Flight_Code >=10),
  Flight_Date date,
  Num_Passengers number(3) DEFAULT 0,
  Actual_Time_Depart date,
  Actual_Time_Arrive date ,
  PRIMARY KEY (Flight_Code, Flight_Date),
  FOREIGN KEY (Flight_Code) references Flight_Number(Flight_Code),
  CHECK (Actual_Time_Depart <= Actual_Time_Arrive)
);

/* Notice that the FK is part of the PK -- this is because flight_instance
was a weak entity*/
/* This will enforce constraints against deletes or updates on flight_number
 that would break the relational integrity between the two tables */

insert into flight_instance values (013, to_date('11-jan-01'), 287, to_date ('00:23', 'HH24:mi'), to_date('6:10','HH24:mi'));
insert into flight_instance values (013, to_date('12-jan-01'), 312, to_date ('01:10', 'HH24:mi'), to_date('6:45','HH24:mi'));
insert into flight_instance values (207, to_date('11-jan-01'), 189, to_date ('08:15', 'HH24:mi'), to_date('9:23','HH24:mi'));
insert into flight_instance values (207, to_date('12-jan-01'), 201, to_date ('08:25', 'HH24:mi'), to_date('9:26','HH24:mi'));
insert into flight_instance values (207, to_date('30-jul-01'), Null, Null, Null);

/* Now here are some queries to test the conditions */

insert into flight_instance values (100, to_date('31-jul-01'), Null, Null, Null);

insert into flight_instance values (207, to_date('13-jan-01'), 201, to_date ('08:25', 'HH24:mi'), to_date('7:25','HH24:mi'));

delete from flight_number where flight_code = 207;

delete from flight_instance where flight_code = 207;

delete from flight_number where flight_code = 207;

insert into flight_instance values (207, to_date('29-jul-01'), Null, Null, Null);

/* Make sure you know why each of these succeeds or fails -- running them in the order shown should make this clear!*/

/* Now drop the original version of the table, and experiement with the same things using the definition below */

CREATE TABLE Flight_Instance
 (Flight_Code number(3) CHECK (Flight_Code >=10),
  Flight_Date date,
  Num_Passengers number(3),
  Actual_Time_Depart date,
  Actual_Time_Arrive date ,
  PRIMARY KEY (Flight_Code, Flight_Date),
  FOREIGN KEY (Flight_Code) references Flight_Number(Flight_Code) ON DELETE CASCADE,
  CHECK (Actual_Time_Depart <= Actual_Time_Arrive)
);

/* If you try the following you will see if ORACLE implements the ISO standard items yet!*/

CREATE TABLE Flight_Instance
 (Flight_Code number(3) CHECK (Flight_Code >=10),
  Flight_Date date,
  Num_Passengers number(3),
  Actual_Time_Depart date,
  Actual_Time_Arrive date ,
  PRIMARY KEY (Flight_Code, Flight_Date),
  FOREIGN KEY (Flight_Code) references Flight_Number(Flight_Code) ON DELETE SET NULL,
  CHECK (Actual_Time_Depart <= Actual_Time_Arrive)
);

/* Finally, here is a trivial example of how you can use constraints to enforce different subtypes.
   Here, the entity being modelled has two subtypes -- 0 and 1. Subtype 0 has field A. Subtype 1 has field B.
   Neither field is mandatory. */

 create table constraint_test2 
(field_A char(1),
 field_B char(1),
 type_flag number(1) check (type_flag between 0 and 1),
 CHECK ((field_A is null and type_flag = 1) or (field_b is null and type_flag =0))
);

/* Try the following: */

insert into constraint_test2 values (Null, 'A', 0);
insert into constraint_test2 values ('A' , Null, 0);
insert into constraint_test2 values (Null, 'A', 1);
insert into constraint_test2 values ('A' , Null, 1);
insert into constraint_test2 values ('A', 'A', 0);
insert into constraint_test2 values ('A', 'A', 1);

/*-----------------------------------*/
/* PART 2 -- VIEWS                   */
/*-----------------------------------*/

/* MOST BASIC VIEW */

create view useless as select * from flight_number;

create view philly_routes as select * from flight_number where airport_from = 'PHL' or airport_to = 'PHL';

create view flight_codes as select flight_code from flight_number;

create view flight_load as
 select flight_number.flight_code, flight_number.airport_from, flight_number.airport_to,
    flight_instance.flight_date, flight_instance.num_passengers
 from flight_instance, flight_number
 where flight_instance.flight_code = flight_number.flight_code;

/* That last view is based on one of the queries used earlier. Notice that the "order by" clause is
not valid in a view*/

 create view flight_instance_times as 
 select num.flight_code, ins.flight_date, to_char(num.scheduled_time_depart,'HH24:mi') , to_char(ins.actual_time_depart, 'HH24:mi')
 from flight_instance ins, flight_number num
 where ins.flight_code = num.flight_code;

 /* notice that a view can be based on another view. Also notice the use of an alias makes it much easier to change
  to another underlying table */

 create view flight_instance_times_philly as
 select num.flight_code, ins.flight_date,
   to_char(num.scheduled_time_depart,'HH24:mi') schd_time, to_char(ins.actual_time_depart, 'HH24:mi') actl_time
 from flight_instance ins, philly_routes num
 where ins.flight_code = num.flight_code;

/*------------------------------------------*/
/* PART 3 -- AGGREGATE QUERIES   */
/*------------------------------------------*/

select flight_code, count(flight_code) num_instances
 from flight_instance;

/* the above won't work. That's why we need group by*/

select flight_code, count(flight_code) num_instances
 from flight_instance
 group by flight_code;

/* we get one row for each unique value of the field or fields we group by*/

/* group by can be combined with order by and where */

select flight_code, count(flight_code) num_instances
 from flight_instance
 where actual_time_depart is not null
 group by flight_code
 order by flight_code desc;

/* we can sort on the aggregate columns as well as the regular ones */

 select flight_code, count(flight_code) num_instances, avg(num_passengers) avg_load
 from flight_instance
 group by flight_code
 order by num_instances;

/* But to use one of these results as a selection criteria, we need to 
   use a HAVING clause -- adding to the where clause will not work */

 select flight_code, count(flight_code) num_instances
 from flight_instance
 where count(flight_code) >= 3
 group by flight_code;

 select flight_code, count(flight_code) num_instances
 from flight_instance
 group by flight_code
 having count(flight_code) >= 3;

/* We can also make the results of a group query into a view, so it can be used elsewhere */

 create view flight_totals as
 select flight_code, count(flight_code) num_instances, avg(num_passengers) avg_load
  from flight_instance
  group by flight_code;

/* This view can be used just like a table, and even combined with other views.
  Now we can make a view based on the flight_number table that includes 

 create view flight_information as
 select flight_number.*, num_instances, avg_load
 from flight_number, flight_totals
 where flight_number.flight_code = flight_totals.flight_code;

describe flight_information;
select * from flight_information;

/* try adding and removing rows from these tables and see how the totals change */

/* Now for subqueries. */

/* It's always possible to use a list of values in the WHERE part of an expression */

select num.flight_code, ins.flight_date, to_char(num.scheduled_time_depart,'HH24:mi') , to_char(ins.actual_time_depart, 'HH24:mi')
 from flight_instance ins, flight_number num
 where ins.flight_code = num.flight_code
 and num.flight_code in (13, 156);

/* One thing a subquery lets you do is to write a query to retreive this list of values, and embedd it */

select num.flight_code, ins.flight_date, to_char(num.scheduled_time_depart,'HH24:mi') , to_char(ins.actual_time_depart, 'HH24:mi')
 from flight_instance ins, flight_number num
 where ins.flight_code = num.flight_code
 and num.flight_code in 
  (select flight_code from flight_number where plane='747');

/* Say we want to see all flights with no instances (this is a common database need)*/

select * from flight_number where
 flight_code not in
  (Select flight_code from flight_instance);

/* As well as "IN", subqueries can be used with "EXISTS" */
/* So, to list all the flight numbers without flights instances we could also write */

select * from flight_number where
 not exists (Select flight_code from flight_instance where flight_number.flight_code = flight_instance.flight_code);

/* Generally speaking, subqueries can be replaced with joins. Joins will usually be faster 
   In the example above, you would need to use an outer join and match with a null -- this is less intuitive to write*/

select flight_number.* from
 flight_number, flight_instance 
 where flight_number.flight_code = flight_instance.flight_code (+)
 and flight_instance.flight_code is null;

/* One time we HAVE to use a subquery (in Oracle) is to delete rows from one table based on their relationship with another table
   We can't put both tables in the "from" clause -- Oracle only lets you put one table there*/

/* Example: to delete all flights without instances */

delete from flight_number
 where
 not exists (Select flight_code from flight_instance where flight_number.flight_code = flight_instance.flight_code);