---กิจกรรมที่ 5.1------------
-- 2.DML
/*
update,delete,select
*/
select * from customer;
--การแก้ใขข้อมูลในตาราง
update customer set cus_lname='pothong' where cus_code='10010';
update employee set salary=salart*1.5;
select * from pilot;
select * from employee;
--การลบข้อมูลในตาราง
delete from employee where emp_fname like 'k%';
----------------------------------------------------
--5.1
--select * from employee where emp_fname='jitrlada';
delete from employee where emp_fname='Jitrlada' AND emp_lname='Polnimit';
delete from employee where emp_num='106';
--5.2
delete from customer where cus_code='10018';
--5.3
update customer set cus_phone='988-2588' where cus_fname='Jittinan' AND cus_lname='pothong';
--5.4
update customer set cus_balance=45650.75 where cus_fname='Pong' AND cus_lname='Smith';
--5.5
update pilot set PIL_MED_TYPE='2' where pilot.emp_num=employee.emp_num AND employee.emp_fname='George';
update pilot set PIL_MED_TYPE='1' where pilot.emp_num = (select emp_num from employee where employee.emp_fname='George');
create database aircraft52
--5.2 select
select * from employee;
select emp_num,emp_fname,emp_lname from employee order by emp_fname;
select emp_num,emp_fname,emp_lname from employee order by 2;
select emp_num,emp_fname,emp_lname from employee order by 2 desc; --เรียงจากมากไปน้อย
select cus_fname,cus_lname,cus_balance from customer where cus_balance > 30000 order by 3 desc;
select cus_fname,cus_lname,cus_balance from customer
where cus_balance between 20000 and 30000 order by 3 desc;
--5.2.3
/* แสดงหมายเลขสัญญาเช่า จุหมายปลายทางของเครื่องบินที่ไปลงที่ atlanta และจำนวนชั่วโมงที่พักเครื่อง
มากกว่าหรือเท่ากับ 3.5
*/
select char_num,char_destination from charter
where char_destination = 'ATL' and char_hours_wait >=3.5;
--5.2.1
select * from charter order by char_date desc;
--5.2.2
select * from charter where char_date between '02/13/2003' and '02/14/2003' ;
--5.2.3
select char_date,char_destination,char_distance,char_hours from charter
where ac_num='2778V';
--5.2.4
select cus_fname,cus_lname,cus_phone,cus_balance from customer
where cus_balance > 0;
--5.2.5
select emp_title,emp_fname,emp_lname from employee,pilot
where pilot.emp_num=employee.emp_num and pilot.pil_license='COM' ;
/************************
ให้แสดง หมายเลขสัญญาเช่า ชื่อลูกค้า ของลูกค้าที่ชื่อ jittinan
*************************/
select char_num,cus_fname from charter,customer
where charter.cus_code=customer.cus_code and cus_fname = 'jittinan';
--5.2.6
select char_date,char_destination,ac_num,cus_fname,cus_lname,cus_areacode,cus_phone from charter,customer
where charter.ac_num='2778v' and charter.cus_code=customer.cus_code;
--5.2.7
select cus_fname,cus_lname,cus_areacode,cus_phone from customer,charter
where customer.cus_code=charter.cus_code and charter.ac_num='2778V' and customer.cus_balance > 30000;
--5.2.8
select char_date,cus_fname,cus_lname,cus_areacode,cus_phone from charter,customer
where charter.cus_code=customer.cus_code and cus_fname like '_i%';
--5.2.9
select char_num,char_date,char_destination,char_hours,mod_name,charter.ac_num from charter,aircraft,model
where charter.ac_num = aircraft.ac_num and aircraft.mod_code=model.mod_code and co_pil_num <> '';
-- 5.2.10
select char_date,ac_num,emp_fname,emp_lname from charter,employee,pilot
where charter.ac_num='2778V' and charter.pil_num=pilot.pil_num and pilot.emp_num=employee.emp_num;
-- 5.2.11
select char_date,char_num,charter.ac_num,mod_name from charter,aircraft,model
where charter.ac_num = aircraft.ac_num and aircraft.mod_code=model.mod_code and char_date >= '02/15/2003';
--5.2.12
select char_date,char_destination,charter.ac_num,mod_chg_mile,char_distance,cus_fname,cus_lname
from charter,customer,aircraft,model
where char_destination='ATL' and charter.ac_num = aircraft.ac_num and aircraft.mod_code=model.mod_code
and charter.cus_code=customer.cus_code;
--5.2.13
select char_date,charter.ac_num,emp_fname,char_hours,char_fuel,charter.char_fuel/char_hours as fuelPerHours from charter,pilot,employee,aircraft
where char_date >= '02/15/2003' and charter.ac_num=aircraft.ac_num
and charter.pil_num=pilot.pil_num and pilot.emp_num=employee.emp_num
--5.2.14
select char_date,char_distance,mod_chg_mile,mod_chg_mile*char_distance as mileageChange from charter,model,aircraft
where char_date >='02/14/2003' and charter.ac_num=aircraft.ac_num and aircraft.mod_code=model.mod_code;
--5.2.15 *******
select char_date,char_distance
from charter,customer
where char_date >='02/16/2003' and charter.cus_code=customer.cus_code;
--5.2.16
select avg(cus_balance) as SumOfBalance,
min(cus_balance) as MinBalance,
max(cus_balance) as MaxBalance,
sum(cus_balance) as SumBalance
from customer;
--5.2.17
select ac_num,sum(char_distance)as'Number of Flow',
count(ac_num) as 'Total Distanct',sum(char_hours)as 'Total Hours'
from charter
group by ac_num ;
--5.2.18
select char_destination,count(char_destination) as 'charter amout',min(char_hours)as'Minimum Hours'
from charter
group by char_destination order by count(char_destination) desc ;
--5.2.19
select ac_num,ac_ttel,mod_name,mod_chg_mile as 'change per mile'
from aircraft,model
where mod_chg_mile < 100 and aircraft.mod_code=model.mod_code ;
--5.2.20
select ac_num,count(ac_num)as 'Number of Flow',max(char_hours)as 'MaxIMUM HOURS'
from charter
group by ac_num having count(ac_num) > 4;
