create database USICT;
use USICT;
create table student (
    stu_id int,
    stu_name varchar(255)
);
desc student;
create table faculty (
    fac_name varchar(255),
    fac_id int
);

create table stream (
    CSE varchar(255),
    IT varchar(255),
    ECE varchar(255)
);


alter table student add column (
    DOB varchar(255)
);


alter table student modify column DOB date;
delete from student where city='Mumbai';
alter table student drop column state;
select * from student where stu_name='Dhiraj' or stu_id=0841641216;
alter table stream add column (
    EEE varchar(255)
);
select * from faculty where fac_salary >= 55000;
select * from faculty order by fac_salary asc;
select * from faculty where fac_name like 'r%';
select * from faculty where fac_city in (select fac_city from faculty where fac_name='Manoj');

alter table faculty add column (fac_subjects varchar(255));
select * from faculty where fac_subjects <> 'DBMS' and  fac_subjects <> 'AI';
select faculty.fac_name,faculty.fac_salary,faculty.fac_city from faculty
inner join stream on stream.CSE=faculty.fac_name
where faculty.fac_subjects='DBMS';



create table employee(
    Sno int primary key not null auto_increment,
    emp_id bigint,
    emp_name varchar(255),
    emp_dept varchar(255),
    emp_salary int,
    emp_city varchar(255)
);
select * from employee where emp_city = 'Noida' or emp_city = 'New Delhi';
update employee set emp_salary = emp_salary + emp_salary*0.2 where emp_city = 'Greater Noida';
select * from employee where emp_name = 'Akash' or emp_id = 12116491214;
select * from employee where emp_salary in (select max(emp_salary) from employee where emp_dept = 'ECE') and emp_dept = 'ECE';
select * from employee where emp_city <> 'New Delhi' and emp_city <> 'Gurgaon';





create table aviation_details(
    passenger_name varchar(255),
    flight_no int,
    departure_airport varchar(255),
    arrival_airport varchar(255),
    flight_date date,
    reservation_status varchar(255),
    flight_fare int,
    airlines varchar(255)
);
insert into aviation_details values('Harsh Aggarwal',23335,'New Delhi','Bengaluru','2022-01-31','full',5260,'Vistara'),
('Dhruv Sachdeva',27690,'New Delhi','Mumbai','2022-02-01','vacant',4560,'IndiGo'),
('Ujjwal Gautam',52609,'Patna','Kanpur','2022-02-19','full',4789,'Spice Jet'),
('Arjun Rathore',62555,'New Delhi','Hyderabad','2022-03-02','full',5487,'Air India');


select passenger_name as Passenger_Name, flight_no as Flight_Number from aviation_details;
select flight_no as 'Flight Number', departure_airport as 'Departure Airport',
arrival_airport as 'Arrival Airport', airlines as 'Airline Vendor'
from aviation_details;
select flight_no as 'Flight Number', airlines as 'Airline Vendor', reservation_status as 'Reservation Status' from aviation_details where flight_no=52609;


create table train_details(
    train_no int,
    train_src varchar(255),
    train_dest varchar(255),
    train_arr_time time,
    train_dep_time time,
    train_seat_total_seats int,
    train_seats_booked int,
    train_seats_vacant int
);

insert into train_details values(22547,'Kanya Kumari','Jammu','19:20:00','19:30:00',380,80,300);

select * from train_details where train_src='Delhi' and train_dest='Varanasi';
select * from train_details where train_src='Kanya Kumari' and train_dest='Jammu';
select train_no,train_src from train_details where train_dest='Delhi';
select * from train_details where train_dest<>'Gaziabad';







insert into stream values('Manoj','Rachita Rana','Reena Gupta','Anuj Sharma');
insert into employee(emp_id,emp_name,emp_dept,emp_salary,emp_city)values(12116491221,'Meenal Dahiya','IT',120000,'Greater Noida');
update faculty set fac_subjects='Data Structures' where fac_name='Manoj';
insert into student values (0841641220,'Dhiraj','1999-07-13','Varanasi');
insert into faculty values ('Manoj',1009,165000,'Mumbai');
alter table faculty rename column city to fac_city;
alter table employee modify column emp_id bigint;
delete from student where DOB='0000-00-00';
delete from train_details where train_no='12628';
drop table aviation_details;
drop table train_no;
desc student;
desc stream;
desc faculty;
desc stream;
select * from student;
select * from faculty;
select * from employee;
select * from aviation_details;
select * from train_details;
select * from stream;
desc employee;
desc aviation_details;