- List the physicians working in the clinic? (name)
SELECT name
FROM physician
- List the names and addresses of the patients? (name, address)
SELECT name, address
FROM patient
- List the dates of all appointments of patient 12345? (date)
SELECT DISTINCT date
FROM appointment
WHERE code = '12345'
- What are the existing conditions in the database in alphabetical order? (designation)
SELECT designation
FROM condition
ORDER BY designation
- What patients were seen on January 1, 2007? (number, name)
SELECT DISTINCT code, name
FROM appointment JOIN patient USING (code)
WHERE date = '2007-01-01'
- What conditions were diagnosed in appointment number 456? (designation)
SELECT designation
FROM diagnosed JOIN condition USING (ref)
WHERE num = 456
- How many appointments took place on January 1, 2007? (number)
SELECT COUNT(*)
FROM appointment
WHERE date = '2007-01-01'
- How many times was each room used? (room, number)
~~~sql
SELECT room, COUNT(*)
FROM appointment
GROUP BY room~~~
- How many times was each room used by the physician with number 99030? (room, number)
- How many times was each room used by the physician Luca Moore? (room, number)
SELECT room, COUNT(*)
FROM appointment
WHERE number = '99030'
GROUP BY room
- What rooms were used more than twice on 1 January 2007? (room)
SELECT room
FROM appointment
WHERE date = '2007-01-01'
GROUP BY room
HAVING COUNT(*) > 2
- What are the three most used rooms in that same day? (room)
SELECT room
FROM appointment
WHERE date = '2007-01-01'
GROUP BY room
HAVING COUNT(*) IN (
SELECT COUNT(*)
FROM appointment
WHERE date = '2007-01-01'
GROUP BY room
ORDER BY COUNT(*) DESC
LIMIT 3
)
- What conditions have been diagnosed for patient 12345? (designation)
SELECT DISTINCT designation
FROM appointment JOIN diagnosed USING(num) JOIN condition USING (ref)
WHERE code = '12345'
- What patients have been diagnosed conditions that have also been diagnosed for patient 12345? (name)
SELECT DISTINCT name
FROM appointment JOIN diagnosed USING(num) JOIN patient USING (code)
WHERE ref IN (
SELECT ref
FROM appointment JOIN diagnosed USING(num)
WHERE code = '12345'
)