doctor, patient, treat

create table Patient(
  Pid int primary key,
  Pname varchar(25),
  DOB date,
  Address varchar(20)
);
insert into Patient values('p_100', 'Adhi', '12-07-1999', 'cvvd');
insert into Patient values('p_101', 'Bosco', '02-03-2000', 'uythk');
insert into Patient values('p_102', 'Dylan', '18-09-0989', 'njkl');
insert into Patient values('p_103', 'Elma', '22-01-2000', 'mkjgx');
insert into Patient values('p_106', 'Vimal', '14-09-1998', 'liepm');
insert into Patient values('p_104', 'Cathy', '23-07-1987', 'qwerty');

create table Doctor(
  Did int primary key,
  Dname varchar(25),
  specialization varchar(20) default 'general',
  Adhar_no varchar(12) unique,
  consultation_fee numeric(5,2) constraint c1 check(consultation_fee > 50)
);
insert into Doctor values('D_100', 'XYZ', 'ENT', 12334, 150);
insert into Doctor values('D_101', 'PQR', 'ORTHO', 12455, 200);
insert into Doctor values('D_102', 'LMN', 'ENT', 12256, 150);
insert into Doctor values('D_103', 'ABC', 'GEN.MED', null, 150);
insert into Doctor values('D_106', 'KLM', 'PEDIATRIC', 12987, 200);
insert into Doctor values('D_104', 'JACK', 'CARDIAC', 13567, 200);

create table Treat(
  Did int references Doctor(Did) on delete cascade,
  Pid int references Patient (Pid) on delete set null,
  Diagnosis varchar(30) not null,
  treat_id int,
  treat_date date
);
insert into Treat values('D_100', 'p_100', 'aaa', 1, '03-11-2000');
insert into Treat values('D_101', 'p_101', 'bbb', 2, '04-11-2010');
insert into Treat values('D_102', 'p_102', 'ccc', 3, '05-11-2009');
insert into Treat values('D_103', 'p_103', 'ddd', 4, '06-11-2005');
insert into Treat values('D_106', 'p_106', 'eee', 5, '07-11-2002');
insert into Treat values('D_104', 'p_104', 'fff', 6, '08-11-2001');

select count(Pid) from Treat inner join Doctor
where treat_id = 1;