adbms 0, Sql Server

adbms 0

create table EMPLOYEE(
    e_id int,
    e_name varchar(20) not null,
    address varchar(30),
    gender varchar (10),
    ph_number int,
    doj varchar(20),
    salary int );
    
 insert into employee(e_id,e_name,address,gender,ph_number,doj,salary)
 values('1','Alex','211 delhi','M','88362416','11/08/10','30000'),
       ('2','Bradly','306 gurugram','M','88721616','14/05/18','45000'),
       ('3','Dorey','363 faridabad','F','22426156','20/12/16','20000'),
       ('4','Andy','241 delhi','F','34186756','16/01/01','45000'),
       ('5','James','521 gurugram','M','21345214','20/09/12','50000');
  
 select * from employee;

create table PASSENGER(
    p_id int,
    p_name varchar(10),
    seat_no int,
    gender varchar(30),
    ph_number int,
    e_id int,
    res_status varchar(60));

insert into PASSENGER(p_id,p_name,seat_no,gender,ph_number,e_id,res_status)
values('1','Rahul','109','M','89201234','2','waitlist'),
      ('2','Ayush','201','M','76125678','4','confirmed'),
      ('3','Tanu','302','F','56782345','2','confirmed'),
      ('4','Mohit','122','M','78906578','3','waitlist'),
      ('5','Anjali','244','F','88902389','4','confirmed');

select * from PASSENGER;

create table STATION(
     station_id int,
     station_name varchar(20),
     no_of_lines int,
     no_of_platforms int);

insert into STATION(station_id,station_name,no_of_lines,no_of_platforms)
values('1','Faridabad','15','6'),
      ('2','New Delhi','20','12'),
      ('3','Mumbai','15','10'),
      ('4','Dadar','11','20'),
      ('5','Chennai','17','12');

select * from STATION;

create table TRAIN(
      train_id int,
      station_id int,
      trn_name varchar(30));

insert into TRAIN(train_id,station_id,trn_name)
values('2506','4','Northern express'),
      ('2408','3','Amritsar express'),
      ('1987','4','Eastern express'),
      ('2398','2','Amritsar express'),
      ('1934','1','Southern express');

select * from TRAIN;

create table TICKET(
    ticket_no int,
    source varchar(20),
    destination varchar(30),
    class_id int,
    fare int,
    train_id int);

insert into TICKET(ticket_no,source,destination,class_id,fare,train_id)
values('2004','New Delhi','Gurugram','2431','50','1934'),
      ('2003','Chandigarh','Mumbai','2339','500','1987'),
      ('2004','Chennai','Gurugram','2556','1000','1878'),
      ('2005','Noida','Jamshedpur','2431','500','1987'),
      ('2007','New Delhi','Cuttack','2332','2050','1934');

select * from TICKET;

create table FARE(
      recipt_no int,
      train_id int, 
      source varchar(20),
      destination varchar(40),
      class varchar(30),
      fare int,
      ticket_no int);
 
insert into FARE(recipt_no,train_id,source,destination,class,fare,ticket_no)
values('101','1878','Chennai','Gurugram','First class','1000','2004'),
      ('103','1934','New Delhi','Gurugram','Business class','50','2004'),
      ('105','1987','Noida','Jamshedpur','First class','500','2005'),
      ('101','1934','New Delhi','Cuttack','Second class','2050','2007'),
      ('104','1987','Chandigarh','Mumbai','Second class','500','2003');

select * from FARE;

create table CLASS(
   class varchar(20),
   journey_date varchar(30),
   no_of_seats int,
   train_id int);
 
insert into CLASS(class,journey_date,no_of_seats,train_id)
values('First class','10/09/20','7','1934'),
      ('Business class','30/02/19','10','1934'),
      ('Second class','10/09/20','5','1878'),
      ('Second class','14/04/19','10','1987'),
      ('Business class','01/08/20','6','1934');

select * from CLASS;

create table TIME(
    ref_no int,
    dep_time varchar(20),
    arr_time varchar(30),
    train_id int,
    station_id int);
    
insert into TIME(ref_no,dep_time,arr_time,train_id,station_id)
values('21','8.30','8.00','1878','4'),
      ('23','13.10','12.45','1934','2'),
      ('24','8.30','7.45','1987','1'),
      ('23','9.00','7.45','2506','4'),
      ('25','15.15','15.00','2398','3');
 
select * from TIME;

create table ROUTE(
     arr_time varchar(30),
     dep_time varchar(20),
     stop_no int);

insert into ROUTE(arr_time,dep_time,stop_no)
values('12.45','13.10','3'),
      ('7.45','8.30','5'),
      ('15.00','15.15','2'),
      ('8.00','8.30','5'),
      ('7.45','9.00','1');

select * from ROUTE;

select p_name,gender,res_status,e_id from PASSENGER where e_id = 2;

select e_name,gender from employee where gender ='M' and salary < 45000;

select train_id,source,destination,class,fare from FARE where source ='New Delhi' and fare > 20;

select train_id,source,destination,class,fare from FARE where source = 'New Delhi' and fare= (select max(fare)from FARE where source ='New Delhi');

select station_name,no_of_lines,no_of_platforms from STATION where no_of_lines >= 15 and no_of_platforms >=10;

select train_id,trn_name,station_id from TRAIN where not station_id = '4';


run \| edit \| history \| help	0