ADVANCE QUERY.sql

-- 1) Show info of EMP_ID, Doctor Info and Clinic in the same table
SELECT e.EMP_ID,NAME_TITLE,FNAME_TH,LNAME_TH,FNAME_EN,LNAME_EN,
c.CLINIC_ID,CLINIC_TITLE
FROM EMPLOYEE e
INNER JOIN DOCTOR d
ON e.EMP_ID = d.EMP_ID
INNER JOIN CLINIC c
ON d.CLINIC_ID = c.CLINIC_ID

-- 2) Show info of ICD_10 and Diagnosis Result
SELECT i.ICD10_CODE, ICD10_DESCRIPTION, DIAG_DESCRIPTION
FROM ICD_10 i
INNER JOIN DIAGNOSIS_RESULT d
ON i.ICD10_CODE = d.ICD10_CODE


-- 3) Show the patinet how does not come according to the visit
-- - Left Join
-- - Subqueries งงในงง
SELECT p.PAT_ID,NAME_TITLE,FNAME_TH,LNAME_TH,FNAME_EN,LNAME_EN,VISIT.VISIT_ID,VISIT.VISIT_DATETIME
FROM PATIENT p
LEFT JOIN VISIT
ON p.PAT_ID = VISIT.PAT_ID
WHERE p.PAT_ID IS NULL;


-- 4) list of doctors who have treated the most number of patients
WITH Z (EMP_ID, COUNT_PAT) AS (
    SELECT EMP_ID, COUNT(PAT_ID)
    FROM VISIT
    GROUP BY EMP_ID
)
SELECT Z.COUNT_PAT, Z.EMP_ID, e.NAME_TITLE, e.FNAME_EN, e.LNAME_EN
FROM Z
INNER JOIN EMPLOYEE  e
ON e.EMP_ID = Z.EMP_ID
WHERE Z.COUNT_PAT = (SELECT MAX(COUNT_PAT) FROM Z)


-- 5) get the number of patients each doctor have treated
WITH Z (EMP_ID, COUNT_PAT) AS (
    SELECT EMP_ID, COUNT(PAT_ID)
    FROM VISIT
    GROUP BY EMP_ID
)
SELECT Z.EMP_ID, e.NAME_TITLE, e.FNAME_EN, e.LNAME_EN, Z.COUNT_PAT
FROM Z
INNER JOIN EMPLOYEE  e
ON e.EMP_ID = Z.EMP_ID


-- 6) which patient has the most number of visit
WITH Z (PAT_ID, COUNT_VISIT) AS(
    SELECT PAT_ID, COUNT(VISIT_ID)
    FROM VISIT
    GROUP BY PAT_ID
)
SELECT Z.COUNT_VISIT, Z.PAT_ID, P.NAME_TITLE, P.FNAME_EN, P.LNAME_EN
FROM Z
INNER JOIN PATIENT P
ON Z.PAT_ID = P.PAT_ID
WHERE Z.COUNT_VISIT = (SELECT MAX(COUNT_VISIT) FROM Z)


-- 7) List of the aggregated sum of money each clinic makes
WITH Z (EMP_ID, SUM_MONEY) AS (
    SELECT EMP_ID, SUM(PRICE)
    FROM MEDICAL_ORDER_ENTRY
    GROUP BY EMP_ID
),
Y (CLINIC_ID, CLINIC_TITLE, SUM_MONEY) AS (
    SELECT C.CLINIC_ID, C.CLINIC_TITLE, SUM(Z.SUM_MONEY)
    FROM Z
    JOIN DOCTOR D ON Z.EMP_ID = D.EMP_ID
    JOIN CLINIC C ON C.CLINIC_ID = D.CLINIC_ID
    GROUP BY C.CLINIC_ID, C.CLINIC_TITLE
)
SELECT * 
FROM Y 
ORDER BY SUM_MONEY DESC


-- 8) the disease that most patients have
WITH Z (ICD10_CODE, MAX_ICD10) AS (
    SELECT ICD10_CODE, COUNT(ICD10_CODE) 
    FROM DIAGNOSIS_RESULT 
    GROUP BY ICD10_CODE
)
SELECT z.ICD10_CODE, z.MAX_ICD10, I.ICD10_DESCRIPTION
FROM Z
INNER JOIN ICD_10 I
ON I.ICD10_CODE = Z.ICD10_CODE
WHERE MAX_ICD10 = (SELECT MAX(MAX_ICD10) FROM Z)


-- 9) medicine that makes the most money
WITH Z (MED_CODE, SUM_PRICE) AS (
    SELECT MED_CODE, SUM(PRICE)
    FROM MEDICAL_ORDER_ENTRY
    GROUP BY MED_CODE
) 
SELECT Z.MED_CODE, M.MED_NAME, M.MED_GENERAL_NAME, Z.SUM_PRICE
FROM Z
INNER JOIN MEDICINE M
ON M.MED_CODE = Z.MED_CODE
WHERE Z.SUM_PRICE = (SELECT MAX(SUM_PRICE) FROM Z)

-- 10) List of patients who have the highest medical bill
SELECT p.PAT_ID,FNAME_EN,LNAME_EN,TOTAL
FROM PATIENT p
INNER JOIN MEDICAL_BILL m
ON m.PAT_ID = p.PAT_ID
ORDER BY TOTAL DESC