Display the lowest and highest ages of patients treated by the doctor named "Joseph Lam'. Your answer Display the number of doctors in each specialty and the sum of their salaries. Your answer Display the number of hospitals in each city, and the sum of their capacities. Only display cities with more than 10 hospitals. Your answer
CREATE TABLE patient(patientNo text PRIMARY KEY, name text, phoneNo text, age number, doctor text);
INSERT INTO patient VALUES('P1','Akshay','234-876-5673',12,'D5');
INSERT INTO patient VALUES('P2','Bhanu','987-654-2345',20,'D1');
INSERT INTO patient VALUES('P3','Chandler','345-765-1234',15,'D4');
INSERT INTO patient VALUES('P4','Rachel','345-876-5634',22,'D8');
INSERT INTO patient VALUES('P5','Ross','567-432-9616',30,'D5');
INSERT INTO patient VALUES('P6','Minoca','678-567-4321',28,'D1');
INSERT INTO patient VALUES('P7','Phoebe','876-453-3852',18,'D5');
INSERT INTO patient VALUES('P8','Gunther','984-345-2876',13,'D6');
INSERT INTO patient VALUES('P9','Mark','809-501-2354',21,'D9');
INSERT INTO patient VALUES('P10','Joey','123=765-9254',25,'D5');
select min(age), max(age)
from doctor d join patient p
where d.doctorNo = p.doctor and
d.name = 'Joseph Lam';
CREATE TABLE doctor(doctorNo integer PRIMARY KEY, name text, speciality text, salary text, hospitalNo text);
INSERT INTO doctor VALUES(1,'Aman','cardiologist',120000,'H12');
INSERT INTO doctor VALUES(2,'Bhav','neurologist',200000,'H12');
INSERT INTO doctor VALUES(3,'Chan','cardiologist',150000,'H1');
INSERT INTO doctor VALUES(4,'Dimple','neurologist',225000,'H16');
INSERT INTO doctor VALUES(5,'Joseph Lam','dermitologist',155000,'H3');
INSERT INTO doctor VALUES(6,'Green','dermitologist',200000,'H6');
INSERT INTO doctor VALUES(7,'Gurpreet','dermitologist',185000,'H10');
INSERT INTO doctor VALUES(8,'Harpreet','gynocologist',135000,'H6');
INSERT INTO doctor VALUES(9,'Inder','cardiologist',215000,'H9');
INSERT INTO doctor VALUES(10,'Jass','neurologist',125000,'H15');
select speciality, count(speciality), sum(salary)
from doctor
group by speciality;
Step by step
Solved in 2 steps with 3 images
