queries.txt

//Create all Tables:
CREATE TABLE AIRPORT(
AIRPORT_NAME VARCHAR(20) NOT NULL,
    	CITY VARCHAR(20),
   	STATE VARCHAR(20),
	PRIMARY KEY (AIRPORT_NAME)
);
CREATE TABLE AIRLINE(
	AIRLINE_ID INT NOT NULL,
	AIRLINE_NAME VARCHAR(10),
	AIRPORT_NAME VARCHAR(10),
	FOREIGN KEY (AIRPORT_NAME) REFERENCES AIRPORT(AIRPORT_NAME)
);
CREATE TABLE CONTAINS(
	AIRPORT_NAME VARCHAR(20),
    	AIRLINE_ID INT,
	FOREIGN KEY (AIRPORT_NAME) REFERENCES AIRPORT(AIRPORT_NAME),
	FOREIGN KEY (AIRLINE_ID) REFERENCES AIRLINE(AIRLINE_ID)
);
 
CREATE TABLE FLIGHT(
	FLIGHT_NO INT  NOT NULL,
	SOURCE VARCHAR(20) NOT NULL,
DESTINATION VARCHAR(20) NOT NULL,
D_TIME TIME,
A_TIME TIME,
DURATION INT NOT NULL,
AIRLINE_ID INT,
FOREIGN KEY (AIRLINE_ID) REFERENCES AIRLINE(AIRLINE_ID)
);
CREATE TABLE EMPLOYEE(
E_ID INT,
    	NAME VARCHAR(20),
    	ADDRESS VARCHAR(20),
    	SEX VARCHAR(10),
    	SALARY INT,
    	AGE INT,
    	AIRPORT_NAME VARCHAR(20),
	FOREIGN KEY (AIRPORT_NAME) REFERENCES    AIRPORT(AIRPORT_NAME)
);
CREATE TABLE EMP_PHONE_NO(
    	E_ID INT,
    	PHONE_NO VARCHAR(11),
	FOREIGN KEY (E_ID) REFERENCES   EMPLOYEE(E_ID)
);
 
 
CREATE TABLE PASSENGER(
	PASSPORT_NO VARCHAR(20) NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	ADDRESS VARCHAR(20),
	SEX VARCHAR(5),
	DOB DATE,
	AGE INT,
	FLIGHT_NO INT,
	TICKET_NO VARCHAR(20),
	FOREIGN KEY (FLIGHT_NO) REFERENCES   FLIGHT(FLIGHT_NO),
	FOREIGN KEY (TICKET_NO) REFERENCES   TICKET(TICKET_NO)
);
CREATE TABLE SERVES(
E_ID INT,
PASSPORT_NO VARCHAR(20),	
FOREIGN KEY (E_ID) REFERENCES   EMPLOYEE(E_ID),
FOREIGN KEY (PASSPORT_NO) REFERENCES   PASSENGER(PASSPORT_NO)
);
CREATE TABLE PASSENGER_PHONE_NO(
	PASSPORT_NO VARCHAR(20),
	PHONE_NO VARCHAR(11),
FOREIGN KEY (PASSPORT_NO) REFERENCES   PASSENGER(PASSPORT_NO)
);
 
CREATE TABLE TICKET(
	TICKET_NO VARCHAR(20) NOT NULL,
	AIRLINE_NAME VARCHAR(20) NOT NULL,
	PRICE INT NOT NULL,
	SEAT_NO VARCHAR(20),
	CLASS VARCHAR(5),
	D_TIME TIME,
	A_TIME TIME,
	DURATION INT,
	DESTINATION VARCHAR(20),
	SOURCE VARCHAR(20),
	PASSPORT_NO VARCHAR(20),
	PASSENGER_NAME VARCHAR(20),
FOREIGN KEY (PASSPORT_NO) REFERENCES   PASSENGER(PASSPORT_NO),
FOREIGN KEY (PASSENGER_NAME) REFERENCES   PASSENGER(NAME)
);
 
 
 
 
 
 
 
 
// EXP3 ALTER COMMANDS:
CREATE TABLE AIRPORT(
AIRPORT_NAME VARCHAR(20),
    	CITY VARCHAR(20),
	STATE VARCHAR(20)
);
CREATE TABLE AIRLINE(
	AIRLINE_ID INTEGER NOT NULL,
	AIRLINE_NAME VARCHAR(10),
	AIRPORT_NAME VARCHAR(10)
);
ALTER TABLE AIRPORT ADD PRIMARY KEY (AIRPORT_NAME);
ALTER TABLE AIRLINE ADD CONSTRAINT FK_NAME FOREIGN KEY (AIRPORT_NAME) REFERENCES AIRPORT(AIRPORT_NAME);
ALTER TABLE AIRPORT DROP COLUMN STATE;
ALTER TABLE AIRPORT ADD STATE VARCHAR(20);
ALTER TABLE AIRPORT MODIFY AIRPORT_NAME VARCHAR(20) NOT NULL;
ALTER TABLE AIRPORT RENAME COLUMN AIRPORT_NAME TO AIR_NAME;
ALTER TABLE AIRPORT RENAME TO AIRPORT_DETAILS;
ALTER TABLE AIRPORT_DETAILS RENAME TO AIRPORT;
ALTER TABLE AIRPORT ADD CHECK (CITY = ‘Mumbai’);
 
INSERT INTO AIRPORT VALUES (‘Shivaji’, ‘Mumbai’, ‘Maharashtra’);
INSERT INTO AIRPORT VALUES (‘Shivaji’, ‘Delhi’, ‘Maharashtra’);
 
SELECT * FROM AIRPORT;
TRUNCATE TABLE AIRPORT;
DROP TABLE AIRPORT;
DROP TABLE AIRLINE;
 
// EXP 4 INSERT INSTRUCTIONS: 
INSERT INTO EMPLOYEE VALUES(1,'parth','mumbai','M','40000','19','mumbai')
INSERT INTO EMPLOYEE VALUES(1,'ramesh','mumbai','M','30000','20','mumbai')
INSERT INTO EMPLOYEE VALUES(2,'shikha','pune','F','30000','19','pune')
INSERT INTO EMPLOYEE VALUES(2,'raj','pune','M','25000','20','pune')
INSERT INTO EMPLOYEE VALUES(3,'ram','delhi','M','20000','23','delhi')
INSERT INTO EMPLOYEE VALUES(3,'rani','delhi','F','22000','19','delhi')
 
select * from EMPLOYEE
 
 
INSERT INTO EMP_PHONE_NO VALUES(1,'1111111111');
INSERT INTO EMP_PHONE_NO VALUES(3,'3333333333');
INSERT INTO EMP_PHONE_NO VALUES(6,'6666666666');
 
 
INSERT INTO AIRPORT VALUES('mumbai', 'mumbai');
INSERT INTO AIRPORT VALUES('delhi', 'delhi');
INSERT INTO AIRPORT VALUES('pune', 'pune');
 
 
 
 
 
INSERT INTO AIRPORT VALUES('mumbai', 'mumbai');
INSERT INTO AIRPORT VALUES('delhi', 'delhi');
INSERT INTO AIRPORT VALUES('pune', 'pune'); 
 
 
 
 
 
 
// EXP 6 NESTED QUERIES (PLEASE CHECK)
CREATE TABLE AIRPORT(
    AIRPORT_NAME VARCHAR(20) NOT NULL,
    CITY VARCHAR(20),
   	STATE VARCHAR(20),
	PRIMARY KEY (AIRPORT_NAME),
 
);
 
INSERT INTO AIRPORT VALUES('mumbai', 'mumbai', 'maharashtra');
INSERT INTO AIRPORT VALUES('delhi', 'delhi', 'delhi');
INSERT INTO AIRPORT VALUES('pune', 'pune',  'maharashtra'); 
 
CREATE TABLE AIRLINE(
    AIRLINE_ID INT NOT NULL,
	AIRLINE_NAME VARCHAR(20),
	AIRPORT_NAME VARCHAR(20),
	PRIMARY KEY (AIRLINE_ID),
	FOREIGN KEY (AIRPORT_NAME) REFERENCES AIRPORT(AIRPORT_NAME)
);
 
INSERT INTO AIRLINE VALUES(123,'Mumbai Airlines','mumbai');
INSERT INTO AIRLINE VALUES(124,'Delhi Airlines','delhi');
 
CREATE TABLE FLIGHT(
	FLIGHT_NO VARCHAR(10)  NOT NULL,
	F_SOURCE VARCHAR(20) NOT NULL,
    DESTINATION VARCHAR(20) NOT NULL,
    D_TIME VARCHAR(5),
    A_TIME VARCHAR(5),
    F_DURATION INTEGER NOT NULL,
    AIRLINE_ID INT NOT NULL,
    PRIMARY KEY (FLIGHT_NO),
    FOREIGN KEY (AIRLINE_ID) REFERENCES AIRLINE(AIRLINE_ID)
);
 
INSERT INTO FLIGHT VALUES('12345','Mumbai','Delhi', '1500', '2100', 360,123);
INSERT INTO FLIGHT VALUES('12356','Delhi', 'Mumbai', '1800', '2100', 180,124);
INSERT INTO FLIGHT VALUES('12389','Delhi', 'Mumbai', '0100', '0330', 150,124);
 
CREATE TABLE EMPLOYEE(
    E_ID INT,
	NAME VARCHAR(20),
    ADDRESS VARCHAR(20),
    SEX VARCHAR(10),
    SALARY INT,
    AGE INT,
    AIRPORT_NAME VARCHAR(20),
	FOREIGN KEY (AIRPORT_NAME) REFERENCES AIRPORT(AIRPORT_NAME),
	PRIMARY KEY (E_ID)
);
INSERT INTO EMPLOYEE VALUES(1,'parth','mumbai','M','40000','19','mumbai');
INSERT INTO EMPLOYEE VALUES(2,'ramesh','mumbai','M','30000','20','mumbai');
INSERT INTO EMPLOYEE VALUES(3,'shikha','pune','F','30000','19','pune');
INSERT INTO EMPLOYEE VALUES(4,'raj','pune','M','25000','20','pune');
INSERT INTO EMPLOYEE VALUES(5,'ram','delhi','M','20000','23','delhi');
INSERT INTO EMPLOYEE VALUES(6,'rani','delhi','F','22000','19','delhi');
 
CREATE TABLE EMP_PHONE_NO(
    E_ID INT,
    PHONE_NO VARCHAR(11),
	FOREIGN KEY (E_ID) REFERENCES   EMPLOYEE(E_ID)
);
SELECT * FROM EMP_PHONE_NO;
INSERT INTO EMP_PHONE_NO VALUES(1,'1111111111');
INSERT INTO EMP_PHONE_NO VALUES(1,'3333333333');
INSERT INTO EMP_PHONE_NO VALUES(6,'6666666666');
INSERT INTO EMP_PHONE_NO VALUES(5,'5555555555');
 
 
CREATE TABLE TICKET(
	TICKET_NO INT NOT NULL,
	AIRLINE_NAME VARCHAR(20) NOT NULL,
	PRICE INT NOT NULL,
	SEAT_NO VARCHAR(20),
	CLASS VARCHAR(20),
	D_TIME VARCHAR(5),
	A_TIME VARCHAR(5),
	DURATION INT,
	DESTINATION VARCHAR(20),
	SOURCE VARCHAR(20),
	PRIMARY KEY(TICKET_NO)
);
INSERT INTO TICKET VALUES(786, 'SpiceJet', 6000, 'A-2' , 'First Class', '1500', '2100',  360, 'Delhi', 'Mumbai');
INSERT INTO TICKET VALUES(777, 'Mumbai Airlines', 4000, 'H-42' , 'Second Class','1800', '2100', 180, 'Delhi', 'Mumbai');
 
CREATE TABLE PASSENGER(
	PASSPORT_NO INT NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	ADDRESS VARCHAR(20),
	SEX VARCHAR(5),
	DOB DATE,
	AGE INT,
	FLIGHT_NO VARCHAR(20) NOT NULL,
	TICKET_NO INT NOT NULL,
	FOREIGN KEY (FLIGHT_NO) REFERENCES FLIGHT(FLIGHT_NO),
	FOREIGN KEY (TICKET_NO) REFERENCES TICKET(TICKET_NO),
	PRIMARY KEY (PASSPORT_NO)
);
INSERT INTO PASSENGER VALUES(12346,'Parth','A22','Male',date'2002-03-28',5,'12356',786);
INSERT INTO PASSENGER VALUES(8965, 'Tushar', 'B22', 'Male', date'2001-08-12', 19, '12345', 777);
INSERT INTO PASSENGER VALUES(4928, 'Raj', 'D45', 'Male', date'1972-06-13', 64, '12345', 786);
 
 
CREATE TABLE PASSENGER_PHONE_NO(
	PASSPORT_NO int,
	PHONE_NO VARCHAR(11),
    FOREIGN KEY (PASSPORT_NO) REFERENCES   PASSENGER(PASSPORT_NO)
);
INSERT INTO PASSENGER_PHONE_NO VALUES(12346,'1111111111');
INSERT INTO  PASSENGER_PHONE_NO VALUES(12346,'3333333333');
INSERT INTO  PASSENGER_PHONE_NO VALUES(8965,'6666666666');
 
 
 
1. SELECT NAME AND SALARY OF EMPLOYEE HAVING PHONE NUMBER:
 
SELECT E.NAME, E.SALARY
FROM EMPLOYEE E
WHERE E_ID IN
(SELECT EP.E_ID
FROM EMP_PHONE_NO EP
WHERE EP.E_ID=E.E_ID);
 
2. SELECT NAME AND SALARY AND ID IN WHICH E_ID  IS BETWEEN 1 TO 5 having phone number:
 
select E.E_ID, E.NAME, E.SALARY
from EMPLOYEE E
where E.E_ID in
(select EP.E_ID
from EMP_PHONE_NO EP
where EP.E_ID in (1,5));
 
 
3. SELECT NAME BASED ON AIRPORT_NAME WHERE CITY IS MUMBAI:
 
select E.NAME, E.E_ID
from EMPLOYEE E
where E.AIRPORT_NAME in 
(select A.AIRPORT_NAME
from AIRPORT A
where A.CITY='mumbai');
 
 
4. SELECT NAME AND SEX WHERE EMPLOYEE ID IS GREATER THAN 3 AND HAVING PHONE NUMBER
 
SELECT E.E_ID, E.NAME, E.SEX
FROM EMPLOYEE E
WHERE E.E_ID IN (SELECT EP.E_ID
FROM EMP_PHONE_NO EP
WHERE E.E_ID = EP.E_ID AND EP.E_ID>=3);
 
5.SELECT EMPLOYEE NAME, ID HAVING MULTIPLE PHONE NUMBERS:
 
SELECT E.NAME, E.E_ID 
FROM EMPLOYEE E
WHERE E.E_ID IN 
(SELECT EP.E_ID
FROM EMP_PHONE_NO EP
GROUP BY EP.E_ID
HAVING COUNT(*) > 1);
 
6. SELECT NAME, ADDRESS, AGE, SEX WHERE CITY IS Mumbai or Delhi
 
SELECT E.NAME, E.ADDRESS, E.AGE, E.SEX
FROM EMPLOYEE E
WHERE E.AIRPORT_NAME IN 
(SELECT A.AIRPORT_NAME
FROM AIRPORT A
WHERE A.CITY in ('mumbai', 'delhi'));
 
7. SELECT NAME, PASSPORT NUMBER OF PASSENGERS WHERE AIRLINE_NAME IS Mumbai Airlines 
 
SELECT P.NAME, P.PASSPORT_NO 
FROM PASSENGER P
WHERE P.TICKET_NO IN 
(SELECT T.TICKET_NO 
FROM TICKET T 
WHERE T.AIRLINE_NAME = 'Mumbai Airlines');
 
8. SELECT NAME, PASSPORT NUMBER OF PASSENGERS WHERE CLASS IS First Class  
 
SELECT P.NAME, P.PASSPORT_NO 
FROM PASSENGER P
WHERE P.TICKET_NO IN
(SELECT T.TICKET_NO 
FROM TICKET T 
WHERE T.CLASS = 'First Class');
 
 
 
9. SELECT PASSENGER NAME HAVING MULTIPLE PHONE NUMBERS:
 
SELECT P.NAME 
FROM PASSENGER P
WHERE P.PASSPORT_NO IN 
(SELECT PPN.PASSPORT_NO 
FROM PASSENGER_PHONE_NO PPN
GROUP BY PPN.PASSPORT_NO
HAVING COUNT(*)>1);
 
 
 
10. SELECT PASSENGER NAME, SEX, TICKET NUMBER HAVING FLIGHT DESTINATION AS Delhi
 
SELECT P.NAME, P.SEX, P.TICKET_NO
FROM PASSENGER P 
WHERE P.FLIGHT_NO IN
(SELECT F.FLIGHT_NO 
FROM FLIGHT F
WHERE F.DESTINATION = 'Delhi');
 
 
//COMPLEX QURIES:
1. SELECT EMPLOYEE ID, PHONE NUMBER, AND AIRPORT NAME WHERE CITY IS Delhi
 
select E.E_ID,P.PHONE_NO,A.AIRPORT_NAME
from EMPLOYEE E,EMP_PHONE_NO P,AIRPORT A
where E.E_ID=P.E_ID 
and E.AIRPORT_NAME=A.AIRPORT_NAME 
and A.CITY in ('delhi');
 
2. Select Flight Number, Airline ID, Airline Name, Source, Destination whose Duration is greater than 3 hours, and Destination is Delhi or Mumbai.
 
select F.FLIGHT_NO, AL.AIRLINE_ID, F.F_SOURCE, F.DESTINATION
from FLIGHT F, AIRLINE AL
where (F.F_DURATION >= 3*60) AND F.DESTINATION in ('Mumbai','Delhi') AND AL.AIRLINE_ID = F.AIRLINE_ID;
 
3. Select children (with age 10 or less) Senior Citizens (with age 60 or greater) - Passport Number, Name, Age, Flight Number, Ticket Number, travelling from Delhi.
Method 1
select P.PASSPORT_NO, P.NAME, P.AGE, P.FLIGHT_NO, T.TICKET_NO
from PASSENGER P, TICKET T
where T.SOURCE in ('Delhi') and (P.AGE >= 60 or P.AGE <= 10);
 
Method 2
select P.PASSPORT_NO, P.NAME, P.AGE, P.FLIGHT_NO, T.TICKET_NO
from PASSENGER P, TICKET T
where (P.AGE >= 60 or P.AGE <= 10) and T.SOURCE in 
(select T.SOURCE 
from TICKET T
where T.SOURCE = 'Delhi');
 
4. Select Senior Citizens (with age 60 or greater) - Passport Number, Name, Age, Flight Number, Ticket Number, Seat number travelling to Delhi, with the SpiceJet Airline
 
select P.PASSPORT_NO, P.NAME, P.AGE, P.FLIGHT_NO, T.TICKET_NO, T.SEAT_NO
from PASSENGER P, TICKET T
where (T.DESTINATION) in ('Delhi') and (P.AGE >= 60) and T.AIRLINE_NAME in 
(select AIRLINE_NAME
from TICKET
where AIRLINE_NAME = 'SpiceJet');
 
 
 
// Views in DBMS
CREATE TABLE tblAirport(
AIRPORT_NAME VARCHAR(20) NOT NULL,
            CITY VARCHAR(20),
           STATE VARCHAR(20),
PRIMARY KEY (AIRPORT_NAME)
)
 
describe tblAIRPORT
 
CREATE TABLE tblAirline(
AIRLINE_ID INT NOT NULL,
AIRLINE_NAME VARCHAR(10),
AP_NAME VARCHAR(10),
PRIMARY KEY (AIRLINE_ID)
)
 
CREATE TABLE FLIGHT(
FLIGHT_NO INT  NOT NULL,
SOURCE VARCHAR(20) NOT NULL,
	DESTINATION VARCHAR(20) NOT NULL,
	D_TIME INT,
	A_TIME INT,
	DURATION INT NOT NULL,
	A_ID INT,
	FOREIGN KEY (A_ID) REFERENCES tblAirline(AIRLINE_ID)
);
 
 
Insert into tblAirport values ('Mum','Mumbai','Maharashtra')
 
Insert into tblAirport values ('Del','Delhi','Haryana')
 
Insert into tblAirport values ('Kol','Kolkata','West Bengal')
 
Insert into tblAirport values ('Ban','Bangalore','Karnataka')
 
 
Insert into tblAirline values (1,'Indigo','Mum')
 
Insert into tblAirline values (2,'Spicejet','Del')
 
Insert into tblAirline values (3,'Air India','Kol')
 
Insert into tblAirline values (4,'Air India','Ban')
 
 
Insert into FLIGHT values (11,'Mum','Del',2,4,2,1)
 
Insert into FLIGHT values (22,'Del','Kol',3,4,1,2)
 
Insert into FLIGHT values (33,'Kol','Che',11,2,3,3)
 
Insert into FLIGHT values (44,'Ban','Mum',2,3,1,4)
 
 
update tblAirport set AIRPORT_NAME='Mum', CITY='Mumbai',STATE='Maharashtra' where STATE='Karnataka'
 
Select AIRLINE_ID, AIRLINE_NAME, AP_NAME
 
from tblAirline
 
join FLIGHT
 
on tblAirline.AIRLINE_ID = FLIGHT.A_ID
 
 
Create view vWAirportByAirline
 
as
 
Select AIRLINE_ID, AIRLINE_NAME, AP_NAME
 
from tblAirline
 
join FLIGHT
 
on tblAirline.AIRLINE_ID = FLIGHT.A_ID
 
 
Create or replace View vWAirportByAirline
 
as
 
Select AIRPORT_NAME, CITY, STATE
 
from tblAirport
 
join tblAirline
 
on tblAirport.AIRPORT_NAME = tblAirline.AP_NAME
 
 
SELECT * from vWAirportByAirline
 
select * from tblAirport
 
select * from tblAirline
 
select * from FLIGHT
  
 
 
alter table tblAirport drop primary key;
 
 
 
ALTER table tblAirport
 
ADD CONSTRAINT air
 
primary key (AIRPORT_NAME) ;
 
 
 
ALTER VIEW vWAirportByAirline
 
ADD CONSTRAINT emp_view_unq
 
UNIQUE (Airline_ID) DISABLE NOVALIDATE
 
 
ALTER VIEW vWAirportByAirline
 
ADD CONSTRAINT emp_view_unq1
 
UNIQUE (name) DISABLE NOVALIDATE
 
 
ALTER VIEW vWAirportByAirline
 
ADD CONSTRAINT emp_view_unq2
 
primary key (id) DISABLE NOVALIDATE
 
 
insert into vWAirportByAirline values (6,'Spicejet','Che')
 
insert into vWAirportByAirline (CITY, STATE) values ('Chennai','TamilNadu')
 
insert into vWAirportByAirline (STATE) values ('Madras')
 
 
 
select * from vWAirportByAirline
 
select * from tblAirport
 
alter view vWAirportByAirline
 
 
Create View vWithAirline_Airport
 
as
 
Select AIRPORT_NAME, CITY, STATE
 
from tblAirport
 
join tblAirline
 
on tblAirport.AIRPORT_NAME = tblAirline.AP_NAME
 
where tblAirline.AIRLINE_NAME = 'Spicejet'
 
select * from vWithAirline_Airport
 
 
Create View vWAirportNonConfidentialData
 
as
 
Select AIRPORT_NAME, CITY, STATE
 
from tblAirport
 
join tblAirline
 
on tblAirport.AIRPORT_NAME = tblAirline.AP_NAME
 
select * from vWAirportNonConfidentialData
 
 
Create View vWAirportCountByAirline
 
as
 
Select AIRLINE_NAME, COUNT(A_ID) as TotalCount
 
from tblAirline
 
join FLIGHT
 
on tblAirline.AIRLINE_ID = FLIGHT.A_ID
 
select * from vWAirportCountByAirline
 
 
 
SELECT column_name, updatable
 
FROM user_updatable_columns
 
WHERE table_name = 'vWAirportCountByAirline'
 
 
//TRIGGERS OUTPUT
 
 
CREATE TABLE AIRPORT(
    AIRPORT_NAME VARCHAR(20) NOT NULL,
    CITY VARCHAR(20),
   	STATE VARCHAR(20),
	PRIMARY KEY (AIRPORT_NAME)
);
 
INSERT INTO AIRPORT VALUES('mumbai', 'mumbai', 'maharashtra');
INSERT INTO AIRPORT VALUES('delhi', 'delhi', 'delhi');
INSERT INTO AIRPORT VALUES('pune', 'pune',  'maharashtra'); 
 
SELECT * FROM AIRPORT;
 
CREATE OR REPLACE TRIGGER AFTER_INSERT_TR 
AFTER INSERT ON AIRPORT
FOR EACH ROW
BEGIN
dbms_output.put_line('insert successfully');
END;
 
INSERT INTO AIRPORT VALUES('A', 'A', 'A'); 
 
DROP TRIGGER AFTER_INSERT_TR;
 
CREATE OR REPLACE TRIGGER BEFORE_INSERT_TR  
BEFORE INSERT ON AIRPORT  
FOR EACH ROW  
BEGIN
  :new.AIRPORT_NAME := TRIM(:new.AIRPORT_NAME);
  :new.CITY := TRIM(:new.CITY);
  :new.STATE := TRIM(:new.STATE);
END; 
 
INSERT INTO AIRPORT VALUES('B   ','B    ','B   ');
 
SELECT * FROM AIRPORT;
 
DROP TRIGGER BEFORE_INSERT_TR;