André Restivo

Clinic SQL Solutions

  1. List the physicians working in the clinic? (name)
    SELECT name
    FROM physician
    
  2. List the names and addresses of the patients? (name, address)
    SELECT name, address
    FROM patient
    
  3. List the dates of all appointments of patient 12345? (date)
    SELECT DISTINCT date
    FROM appointment
    WHERE code = '12345'
    
  4. What are the existing conditions in the database in alphabetical order? (designation)
    SELECT designation
    FROM condition
    ORDER BY designation
    
  5. 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'
    
  6. What conditions were diagnosed in appointment number 456? (designation)
    SELECT designation
    FROM diagnosed JOIN condition USING (ref)
    WHERE num = 456
    
  7. How many appointments took place on January 1, 2007? (number)
    SELECT COUNT(*)
    FROM appointment
    WHERE date = '2007-01-01'
    
  8. How many times was each room used? (room, number) ~~~sql SELECT room, COUNT(*) FROM appointment GROUP BY room~~~
  9. How many times was each room used by the physician with number 99030? (room, number)
    
    
  10. 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
    
  11. 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
    
  12. 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
    )
    
  13. 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'
    
  14. 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'
    )