create table flight(flno VARCHAR(10) NOT NULL, from_place VARCHAR(50) NOT NULL,
to_place VARCHAR(50) NOT NULL, distance_in_km INT NOT NULL, depart_date DATE NOT
NULL, depart_time TIME NOT NULL, arrive_date DATE NOT NULL, arrive_time TIME NOT
NULL, price DECIMAL(10,2) NOT NULL, PRIMARY KEY(flno));
create table aircraft(aid VARCHAR(10) NOT NULL PRIMARY KEY, aname VARCHAR(50) NOT
NULL, cruising_range_in_miles INT NOT NULL);
create table employee(empid INT NOT NULL PRIMARY KEY, fname VARCHAR(20) NOT NULL,
lname VARCHAR(20) NOT NULL, dob DATE NOT NULL, address VARCHAR(50) NOT NULL, salary
DECIMAL(10, 2) NOT NULL, phoneno VARCHAR(10));
create table certified(empid INT, aid VARCHAR(10), PRIMARY KEY (empid, aid),
FOREIGN KEY (empid) REFERENCES employee(empid), FOREIGN KEY (aid) REFERENCES
aircraft (aid));
insert into flight values('Z235', 'Shillong', 'Imphal', 1197, '2023/05/08',
'12:45:00', '2023/05/08', '16:00:00', 9500), ('J302', 'Guwahati', 'Delhi', 4449,
'2022/09/21', '08:00:00', '2022/09/21', '11:30:00', 25000), ('G231', 'Pune',
'Bangalore', 2325, '2022/09/23', '19:45:00', '2022/09/23', '23:15:00', 8000),
('F901', 'Shillong', 'Kolkata', 1550, '2022/07/26','9:30:00', '2022/07/26',
'11:15:00', 15000), ('ER23', 'Mumbai', 'Pune', 545, '2022/12/11', '13:45:00',
'2022/12/11', '15:30:00', 13000), ('T779', 'Guwahati', 'Kolkata', 1469,
'2023/03/11', '23:15:00', '2023/03/12', '02:30:00', 19000);
('A311', 'Srinagar', 'Chandigarh', 1745, '2022/01/30', '10:30:00', '2022/01/30',
'13:45:00', 28000),
insert into aircraft values('JWA101', 'Jettwings Airlines', 5000), ('AIA211', 'Air
India Airlines', 5500), ('BAL29', 'Bharat Airlines', 6000), ('UAL665', 'United
Airlines', 5800), ('SAL333', 'Southwest Airlines', 4700), ('JETA21', 'Jettblue
Airlines', 5200);
insert into employee values(101, 'Ramesh', 'Narayan', '1962/09/15', 'Dehradun',
18000, 9876543), (102, 'John', 'Smith', '1965/01/09', 'Bangalore', 25000, 7285163),
(103, 'Ahmad', 'Jabbar', '1969/03/29', 'Mysore', 20000, 4567541), (104, 'Oliver',
'Kyle', '1937/11/10', 'Pune', 25000, 5554622), (105, 'Kushal', 'Singh',
'1955/12/08', 'Agra', 13000, 4011823), (106, 'Mike', 'Johnson', '1998/08/06',
'Shillong', 23000, 9246722), (107, 'Jane', 'Smith', '1991/03/30', 'Kolkata', 19000,
3456781), (108, 'Luke', 'William', '2000/11/12', 'Imphal', 15000, 9011001), (109,
'Neha', 'Gupta', '1999/01/23', 'Bangalore', 24000, 2023926), (110, 'Rajesh',
'Kumar', '1995/10/19', 'New Delhi', 19000, 6968670), (111, 'Allan', 'Smith',
'1969/12/01', 'Shillong', 13500, 8111000), (112, 'Sonu', 'Gupta', '2001/01/01',
'New Delhi', 27000, 4300112);
insert into certified values(102, 'AIA211'), (103, 'BAL29'), (104, 'JETA21'), (106,
'JWA101'), (109, 'SAL333'), (112, 'UAL665');
Q.2
(b) select * from employee where empid IN(select empid from certified);
(c) select fname,lname from employee where empid IN(select empid from certified)
AND salary>(select MIN(price) from flight where from_place='Guwahati' AND
to_place='Delhi');
(d) select AVG(salary) from employee where empid NOT IN(select empid from
certified);
(e) delete from flight where price < 10000;
(f) select * from employee where phoneno LIKE '%22';
Q.3
(b) select * from flight where price > 10000;
(c) select * from employee where empid NOT IN(select empid from certified);
(d)update employee set salary = salary * 1.10 where empid IN (select empid from
certified); OR set salary=salary+(salary*10/100);
(e) select count(*) from flight where from_place='Shillong' AND
DATE(depart_date)='2022/07/26';
(f) select aircraft.aid,aircraft.aname,aircraft.cruising_range_in_miles,fname,lname
from employee,aircraft,certified where employee.empid=certified.empid AND
aircraft.aid=certified.aid ORDER BY aname;
