fork download
copy 
  1. create table Patients_2022260153 (
  2.     PatientID number,
  3.     Name varchar(40),
  4.     gender varchar(2),
  5.     age number,
  6.     ContactNumber varchar(11),
  7.     primary key(PatientID)
  8. );
  9.  
  10. create table Doctors_2022260153 (
  11.     DoctorID number,
  12.     Name varchar(40),
  13.     Specialization varchar(40),
  14.     ContactNumber varchar(11),
  15.     YearsExperience number,
  16.     primary key(DoctorID)
  17. );
  18. create table Appointments_2022260153 (
  19.     AppointmentID number,
  20.     AppointmentDate date,
  21.     PatientID number,
  22.     DoctorID number,
  23.     AppoinmentStatus varchar(11),
  24.     check (AppoinmentStatus='completed' or AppoinmentStatus='pending' or AppoinmentStatus='confirmed'),
  25.     primary key(AppointmentID),
  26.     foreign key (DoctorID) references Doctors,
  27.     foreign key (PatientID) references Patients
  28. );
  29. create table Billing_2022260153(
  30.     BillID number,
  31.     AppointmentID number,
  32.     BillAmount number,
  33.     BillStatus varchar(10),
  34.     primary key(BillID),
  35.     foreign key (AppointmentID) references Appointments
  36. );
  37.  
  38. insert into Doctors_2022260153 values (1101,'Richard','Cardiology','11011101',22);
  39. insert into Doctors_2022260153 values (1102,'Bob','Cardiology','11021102',12);
  40. insert into Doctors_2022260153 values (1103,'Kumar','Oncology','11031103',9);
  41. insert into Doctors_2022260153 values (1104,'Samantha','Medicine','11041104',7);
  42. insert into Doctors_2022260153 values (1105,'Ali','Neurology','11051105',8);
  43. insert into Doctors_2022260153 values (1106,'zeng','Gynecology','11061106',6);
  44. insert into Doctors_2022260153 values (1107,'Luis','Gynecology','11071107',11);
  45.  
  46. insert into Patients_2022260153 values (5001,'Lucas','M',11,'50015001');
  47. insert into Patients_2022260153 values (5002,'Anthony','M',43,'50025002');
  48. insert into Patients_2022260153 values (5003,'Nick','M',26,'50035003');
  49. insert into Patients_2022260153 values (5004,'Rodri','M',28,'50045004');
  50. insert into Patients_2022260153 values (5005,'Viola','F',38,'50055005');
  51. insert into Patients_2022260153 values (5006,'Annabelle','F',9,'50065006');
  52. insert into Patients_2022260153 values (5007,'Bill','M',61,'50075007');
  53. insert into Patients_2022260153 values (5008,'Taylor','F',19,'50085008');
  54. insert into Patients_2022260153 values (5009,'Trevor','M',39,'50095009');
  55.  
  56. insert into Appointments_2022260153 values (910,'04-21-2024',5002,1101,'completed');
  57. insert into Appointments_2022260153 values (911,'04-24-2024',5007,1102,'confirmed');
  58. insert into Appointments_2022260153 values (912,'04-24-2024',5005,1103,'pending');
  59. insert into Appointments_2022260153 values (913,'04-19-2024',5006,1104,'confirmed');
  60. insert into Appointments_2022260153 values (914,'04-20-2024',5005,1102,'completed');
  61. insert into Appointments_2022260153 values (915,'04-21-2024',5008,1104,'pending');
  62. insert into Appointments_2022260153 values (916,'04-21-2024',5002,1101,'completed');
  63. insert into Appointments_2022260153 values (917,'04-21-2024',5009,1107,'completed');
  64.  
  65. insert into Billing_2022260153 values (9910,910,250,'paid');
  66. insert into Billing_2022260153 values (9911,911,200,'unpaid');
  67. insert into Billing_2022260153 values (9912,912,100,'unpaid');
  68. insert into Billing_2022260153 values (9913,913,100,'paid');
  69. insert into Billing_2022260153 values (9914,914,180,'paid');
  70. insert into Billing_2022260153 values (9915,915,120,'unpaid');
  71. insert into Billing_2022260153 values (9916,916,220,'unpaid');
  72. insert into Billing_2022260153 values (9917,917,120,'unpaid');
  73.  
  74. --a) 
  75. select name,contactnumber 
  76. from patients_2022260153 
  77. where age=(select max(age) from patients_2022260153);
  78.  
  79. --b)
  80. select name,doctorid,count(appointmentid) 
  81. from appointments_2022260153 natural join doctors_2022260153 
  82. where specialization='Oncology' and appointmentdate='04-24-2024' group by name,doctorid;
  83.  
  84. --c)
  85. select p.name,p.contactnumber 
  86. from patients_2022260153 p, appointments_2022260153 a, doctors_2022260153 d 
  87. where d.doctorid=a.doctorid and p.patientid=a.patientid and specialization='Cardiology'and appointmentdate='04-24-2024';
  88.  
  89. --d)
  90. select distinct name, contactnumber 
  91. from doctors_2022260153 natural join appointments_2022260153 
  92. where AppoinmentStatus = 'completed' and appointmentdate between '04-19-2024' and '04-21-2024' order by name;
  93.  
  94. --e)
  95. select doctorid, name from doctors_2022260153 natural left join appointments_2022260153 
  96. minus
  97. select  doctorid,name from doctors_2022260153 natural join appointments_2022260153; 
  98.  
  99. --f)
  100.  
  101. --g)
  102. select name,contactnumber,count(appointmentid) 
  103. from patients_2022260153 natural join appointments_2022260153 
  104. group by name,contactnumber having count(appointmentid)>=2;
  105.  
  106. --h)
  107. select specialization,avg(yearsexperience) from doctors_2022260153 group by specialization;
  108.  
  109. --i)
  110. select doctorid, name, count(appointmentid) 
  111. from doctors_2022260153 natural left join appointments_2022260153  
  112. where appoinmentstatus='completed' group by doctorid,name;
  113.  
  114. --j)
  115. update billing_2022260153
  116. set billamount = case
  117.                  when billamount < 1000 then billamount- (billAmount * 0.15)
  118.                  when billamount >= 1000 and billamount < 2500 then billamount-(billamount * 0.10)
  119.                  else billamount -(billamount * 0.05)
  120.                  end;
  121.  
  122. --k)
  123. alter table patients_2022260153 add adress varchar(40);
  124.  
  125. --l)
  126. create view gynecologyappointment as
  127. select p.name as patientname, d.name as doctorname, a.appointmentdate, a.appoinmentstatus
  128. from patients_2022260153 p, appointments_2022260153 a, doctors_2022260153 d 
  129. where d.doctorid=a.doctorid and p.patientid=a.patientid
  130. and specialization = 'Gynecology';
  131.  
  132. select * from gynecologyappointment;
  133.  
Success #stdin #stdout #stderr 0.01s 5280KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Bill|50075007
Kumar|1103|1
Bill|50075007
Bob|11021102
Luis|11071107
Richard|11011101
Anthony|50025002|2
Viola|50055005|2
Cardiology|17.0
Gynecology|8.5
Medicine|7.0
Neurology|8.0
Oncology|9.0
1101|Richard|2
1102|Bob|1
1107|Luis|1
Trevor|Luis|04-21-2024|completed
stderr
copy 
Error: near line 95: near "select": syntax error
https://ideone.com/fT4uYa
language:
SQLite (sqlite 3.27.2)
created:
17 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!