MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table facility (
`name` varchar(30) primary key,
address varchar(30), 
phone varchar(30), 
`type` varchar(30), 
capacity int
)
insert into facility (name, capacity, address, phone, `type`) values
('wevac', 69, 'xxx', '79 72 04', 'private'), 
('finalcare', 4, 'venice', '00 00 04', 'morgue')

Records: 2  Duplicates: 0  Warnings: 0


create table healthcare_worker (
facility_name varchar(30),
person_id int
)
insert into healthcare_worker values 
('wevac', 1),('wevac', 2),('wevac', 3), 
('finalcare', 4),('finalcare', 5),('finalcare', 6)

Records: 6  Duplicates: 0  Warnings: 0


create table vaccination (
location varchar(30),
dose int
);
insert into vaccination values 
('wevac', 1),('wevac', 2),('wevac', 3), 
('finalcare', 4),('finalcare', 5),('finalcare', 6)

Records: 6  Duplicates: 0  Warnings: 0


create table booking (
facility_name varchar(30),
booking_id int
)
insert into booking values 
('wevac', 1),('wevac', 2),('wevac', 3), 
('finalcare', 4),('finalcare', 5),('finalcare', 6)

Records: 6  Duplicates: 0  Warnings: 0


SELECT `name`, address, phone, `type`, capacity
, ( SELECT COUNT(person_id)
    FROM healthcare_worker
    WHERE fac.`name`= healthcare_worker.facility_name
  ) AS totalWorkers
, ( SELECT SUM(dose)
    FROM vaccination
    WHERE fac.`name`= vaccination.location
  ) AS totalDose
, ( SELECT COUNT(booking_id)
    FROM booking
    WHERE fac.`name`= booking.facility_name
  ) AS totalBookings
FROM facility AS fac


name
address
phone
type
capacity
totalWorkers
totalDose
totalBookings


finalcare
venice
00 00 04
morgue
4
3
15
3

wevac
xxx
79 72 04
private
69
3
6
3

name	address	phone	type	capacity	totalWorkers	totalDose	totalBookings
finalcare	venice	00 00 04	morgue	4	3	15	3
wevac	xxx	79 72 04	private	69	3	6	3