drop view PersonTrip ; 

drop table Trip;
drop table Person;
drop table TripType;
drop table NoRelation;

drop table Flight ;
drop table CarRental ;
drop table Hotel ;
drop table Trip ;


create Table TripType (
TripTypeID INTEGER NOT NULL PRIMARY KEY, 
Name VARCHAR(15), 
Description VARCHAR(50)
)
;

INSERT INTO TripType Values(1, 'TRNG', 'Training')
;
INSERT INTO TripType Values(2, 'SALES', 'Sales')
;
INSERT INTO TripType Values(3, 'OTHER', 'Other')
;
INSERT INTO TripType Values(4, 'PR/AR', 'Press and Analyst Meeting')
;
INSERT INTO TripType Values(5, 'OFFSITE', 'Offsite Meeting')
;
INSERT INTO TripType Values(6, 'CONF', 'Conference/Tradeshow')
;
INSERT INTO TripType Values(7, 'REM MTG', 'Remote Office Meeting')
;
INSERT INTO TripType Values(8, 'CUST VST', 'Customer Visit')
;
INSERT INTO TripType Values(9, 'RECRUIT', 'Recruiting')
;
INSERT INTO TripType Values(10, 'BUS DEV', 'Business Development')
;


create Table Person(
PersonID INTEGER NOT NULL PRIMARY KEY, 
Name VARCHAR(50), 
JobTitle VARCHAR(50), 
FrequentFlyer smallint
)
;

INSERT INTO Person Values(1, 'Able, Tony', 'CEO', 1)
;
INSERT INTO Person Values(2, 'Black, John', 'VPO/CXO - SGMS', 1)
;
INSERT INTO Person Values(3, 'Kent, Richard', 'VP', 1)
;
INSERT INTO Person Values(4, 'Chen, Larry','VP/CXO - SGMS', 0)
;
INSERT INTO Person Values(5, 'Donaldson, Sue', 'VP', 1)
;        


Create table Trip (
TripID INTEGER NOT NULL PRIMARY KEY, 
PersonID INTEGER NOT NULL, 
DepDate date, 
DepCity VARCHAR(32), 
DestCity VARCHAR(32), 
TripTypeID INTEGER NOT NULL,
constraint TripPersonFK FOREIGN KEY (PersonID) REFERENCES Person (PersonID),
constraint TripTypeFK FOREIGN KEY (TripTypeID) REFERENCES TripType (TripTypeID)
)
;

--            //Scott McNealy
INSERT INTO Trip VALUES(128, 1,'2003-06-16', 'San Francisco', 'New York', 4)
;
INSERT INTO Trip VALUES(199, 1,'2003-09-14', 'San Francisco', 'New York', 4)
;
INSERT INTO Trip VALUES(202, 1,'2003-10-22', 'San Francisco', 'Toronto', 4)
;
INSERT INTO Trip VALUES(203, 1,'2003-11-23', 'San Francisco', 'Tokyo', 5)
;
INSERT INTO Trip VALUES(367, 1,'2003-12-12', 'San Francisco', 'Chica;', 2)
;
--            //Bill Joy
INSERT INTO Trip VALUES(100, 4,'2004-05-01', 'Aspen', 'San Francisco', 7)
;
INSERT INTO Trip VALUES(159, 4,'2003-09-01', 'Aspen','Park City', 4)
;
INSERT INTO Trip VALUES(252, 4,'2003-11-01', 'Aspen','Chicago', 4)
;
INSERT INTO Trip VALUES(359, 4,'2004-01-26', 'Aspen','Los Angeles', 4)
;
INSERT INTO Trip VALUES(460, 4,'2004-05-06', 'Aspen', 'San Francisco', 6)
;
--            //Jonathan Schwartz
INSERT INTO Trip VALUES(200, 2,'2004-06-11', 'San Jose', 'Washington DC', 3)
;
INSERT INTO Trip VALUES(310, 2,'2003-08-03', 'San Jose', 'Washington DC', 3)
;
INSERT INTO Trip VALUES(333, 2,'2004-02-02', 'San Jose', 'Tokyo', 5)
;
INSERT INTO Trip VALUES(422, 2,'2004-04-11', 'San Jose', 'Washington DC', 3)
;
INSERT INTO Trip VALUES(455, 2,'2004-05-13', 'San Jose', 'Stockholm', 8)
;
--            //Rich Green
INSERT INTO Trip VALUES(592, 3,'2003-06-16', 'San Jose', 'Novosibirsk', 10)
;
INSERT INTO Trip VALUES(201, 3,'2003-07-01', 'San Jose', 'Washington DC', 8)
;
INSERT INTO Trip VALUES(590, 3,'2003-08-11', 'San Jose', 'Orlando', 6)
;
INSERT INTO Trip VALUES(380, 3,'2003-10-23', 'San Jose', 'Washington DC', 3)
;
INSERT INTO Trip VALUES(421, 3,'2003-11-09', 'San Jose', 'Washington DC', 3)
;
--            //James ;sling
INSERT INTO Trip VALUES(198, 5, '2004-06-11', 'San Jose', 'Grenoble', 3)
;
INSERT INTO Trip VALUES(208, 5, '2003-06-21', 'San Jose',  'Washington DC', 2)
;
INSERT INTO Trip VALUES(383, 5, '2003-10-23', 'San Jose', 'Grenoble', 3)
;
INSERT INTO Trip VALUES(420, 5, '2004-06-11', 'San Jose', 'Philadelphia', 8)
;
INSERT INTO Trip VALUES(463, 5, '2004-05-26', 'San Jose', 'Los Angeles', 6)
;

create table Flight (
    TripID INTEGER NOT NULL,
    FlightID INTEGER NOT NULL,
    Direction CHAR(1) NOT NULL,
    FlightNum VARCHAR(20),
    DepTime TIMESTAMP,
    DepAirport VARCHAR(35),
    ArrTime TIMESTAMP,
    ArrAirport VARCHAR(35),
    AirlineName VARCHAR(35),
    BookingStatus VARCHAR(20),
    primary key(TripID, FlightID)
);
alter table Flight add constraint FlightFK FOREIGN KEY (TripID) REFERENCES 
                Trip (TripID)  on delete cascade ;

//Flights
insert into Flight values (128, 1, 'D', 'United Airlines 71', '2005-06-16 13:00:00.00', 'Oakland (OAK)', '2005-06-16 22:30:00.00', 'New York Newark Intl Arpt (EWR)', 'United Airlines', 'Confirmed');
insert into Flight values (128, 2, 'R', 'United Airlines 73', '2005-06-23 13:00:00.00', 'New York Newark Intl Arpt (EWR)', '2005-06-23 23:10:00.00', 'Oakland (OAK)', 'United Airlines', 'Confirmed');
insert into Flight values (199, 1, 'D', 'United Airlines 71', '2005-09-14 13:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-09-14 22:30:00.00', 'New York Newark Intl Arpt (EWR)', 'United Airlines', 'Confirmed');
insert into Flight values (199, 2, 'R', 'United Airlines 73', '2005-09-17 13:00:00.00', 'New York Newark Intl Arpt (EWR)', '2005-09-17 23:10:00.00', 'San Francisco Intl Arpt (SFO)', 'United Airlines', 'Confirmed');
insert into Flight values (202, 1, 'D',  'Air Canada 101', '2005-10-22 10:00:00.00', 'Oakland (OAK)', '2005-10-22 23:30:00.00', 'Toronto Intl Arpt (YYZ)', 'Air Canada', 'Confirmed');
insert into Flight values (202, 2, 'R',  'Air Canada 220', '2005-10-24 13:00:00.00', 'Toronto Intl Arpt (YYZ)', '2005-10-24 17:10:00.00', 'Oakland (OAK)', 'Air Canada', 'Confirmed');
insert into Flight values (203, 1, 'D', 'Japan Airlines 22', '2005-10-23 13:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-10-23 22:30:00.00', 'Tokyo Narita Intl Arpt (NRT)', 'Japan Airlines', 'Confirmed');
insert into Flight values (203, 2, 'R', 'Japan Airlines 01', '2005-10-30 13:00:00.00', 'Tokyo Narita Intl Arpt (NRT)', '2005-10-30 23:10:00.00', 'San Francisco Intl Arpt (SFO)', 'Japan Airlines', 'Confirmed');
insert into Flight values (367, 1, 'D', 'United Airlines 62', '2005-12-12 09:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-12-12 17:30:00.00', 'Chicago Ohare Intl Arpt (ORD)', 'United Airlines', 'Confirmed');
insert into Flight values (367, 2, 'R', 'United Airlines 32', '2005-12-19 17:00:00.00', 'Chicago Ohare Intl Arpt (ORD)', '2005-12-19 23:10:00.00', 'San Francisco Intl Arpt (SFO)', 'United Airlines', 'Confirmed');

insert into Flight values (200, 1, 'D', 'United Airlines 101', '2005-06-11 09:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-06-11 17:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (200, 2, 'R', 'United Airlines 121', '2005-06-18 17:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2005-06-18 23:10:00.00', 'San Francisco Intl Arpt (SFO)', 'United Airlines', 'Confirmed');
insert into Flight values (310, 1, 'D', 'American Airlines 10', '2005-08-03 07:00:00.00', 'San Jose Intl Arpt (SJC)', '2005-08-03 18:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'American Airlines', 'Confirmed');
insert into Flight values (310, 2, 'R', 'American Airlines 12', '2005-08-08 14:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2005-08-08 23:10:00.00', 'San Jose Intl Arpt (SJC)', 'American Airlines', 'Confirmed');
insert into Flight values (333, 1, 'D', 'Japan Airlines 33', '2006-02-02 09:00:00.00', 'San Francisco Intl Arpt (SFO)', '2006-02-03 17:30:00.00', 'Tokyo Narita Intl Arpt (NRT)','Japan Airlines', 'Confirmed');
insert into Flight values (333, 2, 'R', 'Japan Airlines 101', '2006-02-09 17:00:00.00', 'Tokyo Narita Intl Arpt (NRT)', '2006-02-09 23:10:00.00', 'San Francisco Intl Arpt (SFO)', 'Japan Airlines', 'Confirmed');
insert into Flight values (422, 1, 'D', 'United Airlines 101', '2006-04-11 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2006-04-11 17:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (422, 2, 'R', 'United Airlines 121', '2006-04-18 17:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2006-04-18 23:10:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');
insert into Flight values (455, 1, 'D', 'Scand Airlines 8823', '2005-05-13 09:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-05-13 17:30:00.00', 'Arlanda-Stockholm, Sweden (ARN)', 'Scandinavian Airlines', 'Confirmed');
insert into Flight values (455, 2, 'R', 'Scand Airlines 8823', '2005-05-20 17:00:00.00', 'Arlanda-Stockholm, Sweden (ARN)', '2005-05-20 23:10:00.00', 'San Francisco Intl Arpt (SFO)', 'Scandinavian Airlines', 'Confirmed');

insert into Flight values (592, 1, 'D', 'AERO Airlines 001', '2005-06-16 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2005-06-16 17:30:00.00', 'Novosibirsk Regional Arpt (NVI)', 'AEROFLOT Airlines', 'Confirmed');
insert into Flight values (592, 2, 'R', 'AERO Airlines 101', '2005-06-23 17:00:00.00', 'Novosibirsk Regional Arpt (NVI)', '2005-06-23 23:10:00.00', 'San Jose Intl Arpt (SJC)', 'AEROFLOT Airlines', 'Confirmed');
insert into Flight values (201, 1, 'D', 'United Airlines23', '2005-07-01 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2005-07-13 01:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (201, 2, 'R', 'United Airlines 88', '2005-07-08 17:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2005-07-08 20:10:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');
insert into Flight values (590, 1, 'D', 'Delta Airlines 201', '2005-08-11 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2005-08-11 17:30:00.00', 'Orlando Intl Arpt (ORL)', 'Delta Airlines', 'Confirmed');
insert into Flight values (590, 2, 'R', 'Delta Airlines 202', '2005-08-18 17:00:00.00', 'Orlando Intl Arpt (ORL)', '2005-08-18 23:10:00.00', 'San Jose Intl Arpt (SJC)', 'Delta Airlines', 'Confirmed');
insert into Flight values (380, 1, 'D', 'United Airlines 23', '2006-10-23 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2006-10-23 01:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (380, 2, 'R', 'United Airlines 88', '2006-10-30 17:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2006-10-30 20:10:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');
insert into Flight values (421, 1, 'D', 'United Airlines 23', '2006-11-09 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2006-11-09 01:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (421, 2, 'R', 'United Airlines 88', '2006-11-16 17:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2006-11-16 20:10:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');

insert into Flight values (100, 1, 'D', 'United Airlines 709', '2005-05-01 09:00:00.00', 'Aspen Arpt (ASE)', '2005-05-01 01:30:00.00', 'San Francisco Intl Arpt (SFO)', 'United Airlines', 'Confirmed');
insert into Flight values (100, 2, 'R', 'United Airlines 880', '2005-05-08 17:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-05-08 20:10:00.00', 'Aspen Arpt (ASE)', 'United Airlines', 'Confirmed');
insert into Flight values (159, 1, 'D', 'American Airlines 03', '2005-09-01 09:00:00.00', 'Aspen Regional Arpt (ASE)', '2005-09-01 01:30:00.00', 'Park City Regional Arpt (PRK)', 'American Airlines', 'Confirmed');
insert into Flight values (159, 2, 'R', 'American Airlines 08', '2005-09-08 17:00:00.00', 'Park City Regional Arpt (PRK)', '2005-09-08 20:10:00.00', 'Aspen Regional Arpt (ASE)', 'American Airlines', 'Confirmed');
insert into Flight values (252, 1, 'D', 'United Airlines 23', '2005-11-01 09:00:00.00', 'Aspen Regional Arpt (ASE)', '2005-11-01 01:30:00.00', 'Chicago OHare Intl Arpt (ORD)', 'United Airlines', 'Confirmed');
insert into Flight values (252, 2, 'R', 'United Airlines 88', '2005-11-08 17:00:00.00', 'Chicago OHare Intl Arpt (ORD)', '2005-11-08 20:10:00.00', 'Aspen Regional Arpt (ASE)', 'United Airlines', 'Confirmed');
insert into Flight values (359, 1, 'D', 'Delta Airlines 23', '2006-01-26 09:00:00.00', 'Aspen Regional Arpt (ASE)', '2006-01-26 01:30:00.00', 'Los Angeles Intl Arpt (LAX)', 'Delta Airlines', 'Confirmed');
insert into Flight values (359, 2, 'R', 'Delta Airlines 88', '2006-01-28 17:00:00.00', 'Los Angeles Intl Arpt (LAX)', '2006-01-28 20:10:00.00', 'Aspen Regional Arpt (ASE)', 'Delta Airlines', 'Confirmed');
insert into Flight values (460, 1, 'D', 'United Airlines 709', '2006-05-06 09:00:00.00', 'Aspen Arpt (ASE)', '2005-05-06 01:30:00.00', 'San Francisco Intl Arpt (SFO)', 'United Airlines', 'Confirmed');
insert into Flight values (460, 2, 'R', 'United Airlines 880', '2006-05-13 17:00:00.00', 'San Francisco Intl Arpt (SFO)', '2005-05-13 20:10:00.00', 'Aspen Arpt (ASE)', 'United Airlines', 'Confirmed');
insert into Flight values (463, 1, 'D', 'United Airlines 23', '2006-06-11 09:00:00.00', 'Glenwood Springs (GSS)', '2006-06-11 01:30:00.00', 'Philadelphia Intl Arpt (PHL)', 'United Airlines', 'Confirmed');
insert into Flight values (463, 2, 'R', 'Bankrupt Air 3266', '2006-06-18 17:00:00.00', 'Philadelphia Intl Arpt (PHL)', null, 'Glenwood Springs (GSS)', 'Bankrupt Airlines', null);

insert into Flight values (198, 1, 'D', 'United Airlines 23', '2005-06-11 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2005-06-11 01:30:00.00', 'Grenoble Arpt (GRN)', 'United Airlines', 'Confirmed');
insert into Flight values (198, 2, 'R', 'United Airlines 88', '2005-06-18 17:00:00.00', 'Grenoble Arpt (GRN)', '2005-06-18 20:10:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');
insert into Flight values (208, 1, 'D', 'United Airlines 23', '2005-06-21 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2005-06-21 01:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (208, 2, 'R', 'United Airlines 23', '2005-06-28 09:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2005-06-28 01:30:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');
insert into Flight values (383, 1, 'D', 'Air France 902', '2006-10-23 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2006-10-23 01:30:00.00', 'Grenoble Arpt (GRN)', 'Air France', 'Confirmed');
insert into Flight values (383, 2, 'R', 'Air France 902', '2006-10-30 17:00:00.00', 'Grenoble Arpt (GRN)', '2006-10-30 20:10:00.00', 'San Jose Intl Arpt (SJC)', 'Air France', 'Confirmed');
insert into Flight values (420, 1, 'D', 'United Airlines 23', '2006-11-09 09:00:00.00', 'San Jose Intl Arpt (SJC)', '2006-11-09 01:30:00.00', 'Washington Dulles Intl Arpt (IAD)', 'United Airlines', 'Confirmed');
insert into Flight values (420, 2, 'R', 'United Airlines 88', '2006-11-16 17:00:00.00', 'Washington Dulles Intl Arpt (IAD)', '2006-11-16 20:10:00.00', 'San Jose Intl Arpt (SJC)', 'United Airlines', 'Confirmed');

 create table CarRental(
     TripID INTEGER NOT NULL,
     CarRentalID INTEGER NOT NULL,
     Provider VARCHAR(35),
     City VARCHAR(32),
     PickupDate DATE,
     ReturnDate DATE,
     CarType VARCHAR(30),
     Rate DECIMAL(10,2),
     BookingStatus VARCHAR(20),
     primary key (TripID, CarRentalID)
 );
 
 alter table CarRental add constraint CarRentalFK FOREIGN KEY (TripID) REFERENCES 
                Trip (TripID) on delete cascade ;
 
 //CarRental
 insert into CarRental values (128, 1, 'Avis', 'New York', '2005-06-16', '2005-06-23', 'Economy', 89.90, 'Confirmed');
 insert into CarRental values (199, 1, 'Avis', 'New York', '2005-09-14', '2005-09-17', 'Hybrid', 89.90, 'Confirmed');
 insert into CarRental values (202, 1, 'Hertz', 'Toronto, ON', '2005-10-22', '2005-10-24', 'Sub Compact', 44.99 , 'Confirmed');
 insert into CarRental values (203, 1, 'Hertz', 'Tokyo, JP', '2005-10-23', '2005-10-30', 'Economy', 59.90, null);
 insert into CarRental values (367, 1, 'National', 'Chicago, IL', '2005-10-12', '2005-10-19', 'Economy', null, 'Confirmed');
 insert into CarRental values (200, 1, 'Budget', 'Washington, DC', '2005-06-11', '2005-06-18', 'Economy', 52.50, 'Confirmed');
 insert into CarRental values (310, 1, 'Budget', 'Washington, DC', '2005-08-03', '2005-08-08', 'SUV', 52.50, 'Confirmed');
 insert into CarRental values (333, 1, 'Hertz', 'Tokyo, JP', '2006-02-03', '2005-02-09', null, 55.50, 'Confirmed');
 insert into CarRental values (422, 1, 'Budget', 'Washington, DC', '2006-04-11', '2005-04-18', null, null, null);
 insert into CarRental values (455, 1, 'Budget', 'Stockholm, SE', '2005-05-13', '2005-05-20', 'Economy', 50.50, 'Confirmed');
 insert into CarRental values (201, 1, 'Budget', 'Washington, DC', '2005-07-01', '2005-07-08', 'Economy', null, null);
 insert into CarRental values (590, 1, 'Budget', 'Orlando, FL', '2005-08-11', '2005-08-18', 'Economy', 50.50, 'Confirmed');
 insert into CarRental values (380, 1, 'Budget', 'Washington, DC', '2006-10-23', '2006-10-30', 'Economy', 50.50, 'Confirmed');
 insert into CarRental values (421, 1, 'Budget', 'Washington, DC', '2006-11-09', '2006-11-16', 'Luxury', 69.50, 'Confirmed');
 insert into CarRental values (100, 1, 'Hertz', 'San Francisco, CA', '2005-05-01', '2005-05-08', null, 35.50, 'Confirmed');
 insert into CarRental values (159, 1, 'SkiShuttle', 'Park City, UT', '2005-09-01', '2005-09-08', 'Wagon', 59.500, 'Confirmed');
 insert into CarRental values (359, 1, 'Budget', 'Los Angeles, CA', '2006-01-26', '2006-01-28', 'Economy', 50.50, 'Confirmed');
 insert into CarRental values (460, 1, 'Hertz', 'San Francisco, CA', '2006-05-06', '2005-05-13', 'Economy', 69.69, 'Confirmed');
 insert into CarRental values (208, 1, 'Budget', 'Washington, DC', '2005-06-21', '2005-06-28', 'Economy', 60.50, 'Confirmed');
 insert into CarRental values (383, 1, 'Eurocar', 'Grenoble, FR', '2006-10-23', '2006-10-30', 'Compact', 60.50, 'Confirmed');
 insert into CarRental values (463, 1, 'RentAWreck', 'Washington, DC', '2006-06-11', '2006-06-18', '4 Wheels', 12.99, null);
 
 create table Hotel(
     TripID INTEGER NOT NULL,
     HotelID INTEGER NOT NULL,
     HotelName VARCHAR(35),
     CheckinDate DATE,
     CheckoutDate DATE,
     Guests INTEGER,
     BookingStatus VARCHAR(20),
     primary key (TripID, HotelID) 
 ) ;
 
 alter table Hotel add constraint HotelFK FOREIGN KEY (TripID) REFERENCES 
                 Trip (TripID) on delete cascade ;
 
insert into Hotel values (128, 1, 'New York Marriott Financial Center', '2005-06-16', '2005-06-23', 1, 'Confirmed');
insert into Hotel values (199, 1, 'New York Marriott Financial Center', '2005-09-14', '2005-09-17', 1, 'Confirmed');
insert into Hotel values (202, 1, 'Carl E. Quinn Hotel and Resort', '2005-10-22', '2005-10-24', 1, 'Confirmed');
insert into Hotel values (203, 1, 'Mikimoto Plaza', '2005-10-23', '2005-10-30', 1, 'Confirmed');
insert into Hotel values (367, 1, 'The Drake', '2005-12-12', '2005-12-19', 1, 'Confirmed');
insert into Hotel values (200, 1, 'Sheraton Washington', '2005-06-11', '2005-06-18', 1, 'Confirmed');
insert into Hotel values (310, 1, 'Sheraton Washington', '2005-08-03', '2005-08-03', 1, 'Confirmed');
insert into Hotel values (333, 1, 'Tokyo Marriott', '2006-02-03', '2006-02-09', 1, 'Confirmed');
insert into Hotel values (422, 1, 'Sheraton Washington', '2006-04-11', '2006-04-18', 1, 'Confirmed');
insert into Hotel values (455, 1, 'Octavian Spa Stockholm', '2005-05-13', '2005-05-20', 1, 'Confirmed');
insert into Hotel values (592, 1, 'Best Eastern Vostok S Hotel', '2005-05-16', '2005-06-23', 1, 'Confirmed');
insert into Hotel values (201, 1, 'Sheraton Washington', '2005-07-01', '2005-07-08', 1, 'Confirmed');
insert into Hotel values (590, 1, 'Mickeys Hideaway', '2005-08-11', '2005-08-18', 1, 'Confirmed');
insert into Hotel values (380, 1, 'Sheraton Washington', '2006-10-23', '2006-10-30', 1, 'Confirmed');
insert into Hotel values (421, 1, 'Mayflower Hotel', '2006-11-09', '2006-11-16', 1, 'Confirmed');
insert into Hotel values (100, 1, 'Hyatt San Francisco', '2005-05-01', '2005-05-08', 1, 'Confirmed');
insert into Hotel values (159, 1, 'Park City Ski Lodge', '2005-09-01', '2005-09-08', 1, 'Confirmed');
insert into Hotel values (252, 1, 'Omni Chicago', '2005-11-01', '2005-11-08', 1, 'Confirmed');
insert into Hotel values (359, 1, 'Sheraton Los Angeles Airport', '2006-01-26', '2006-01-28', 1, 'Confirmed');
insert into Hotel values (460, 1, 'Hyatt San Francisco', '2006-05-06', '2005-05-13', 1, 'Confirmed');
insert into Hotel values (208, 1, 'Sheraton Washington', '2005-06-21', '2005-06-28', 1, 'Confirmed');
insert into Hotel values (383, 1, 'Grenoble Garden Chateau', '2006-10-23', '2006-10-30', 1, 'Confirmed');
insert into Hotel values (463, 1, 'Mounment Hostel', '2006-6-11', '2006-06-18', 1, null);

create table  NoRelation (
Col1 INTEGER NOT NULL, Col2 VARCHAR(15) )
;



create view 
PersonTrip as select TripID, Name from Trip, Person where Trip.PersonID = Person.PersonID
;