BASIC QUERY.sql

-- 1) select the max of icd1_code in diagnosis_result
WITH Z (ICD10_CODE, MAX_ICD10) AS (SELECT ICD10_CODE, COUNT(ICD10_CODE) FROM DIAGNOSIS_RESULT GROUP BY ICD10_CODE)
SELECT ICD10_CODE, MAX_ICD10
FROM Z
WHERE MAX_ICD10 = (SELECT MAX(MAX_ICD10) FROM Z)

-- 2) select the min of icd1_code in diagnosis_result
WITH Z (ICD10_CODE, MIN_ICD10) AS (SELECT ICD10_CODE, COUNT(ICD10_CODE) FROM DIAGNOSIS_RESULT GROUP BY ICD10_CODE)
SELECT ICD10_CODE, MIN_ICD10
FROM Z
WHERE MIN_ICD10 = (SELECT MIN(MIN_ICD10) FROM Z)

-- 3) average spending of the patients
SELECT AVG(TOTAL) AS AVG_SPENDING FROM MEDICAL_BILL

-- 4) get the age of the patients 
SELECT PAT_ID, NAME_TITLE, FNAME_EN, LNAME_EN, FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) AS AGE 
FROM PATIENT  

-- 5) get the medicines in medical_order_entry
SELECT COUNT(MED_CODE) as COUNT, MED_CODE
FROM MEDICAL_ORDER_ENTRY
GROUP BY MED_CODE

-- 6) Get the date time of the visit 
SELECT VISIT_ID, PAT_ID, EMP_ID, VISIT_DATETIME 
FROM VISIT

-- 7) find accumulate price classify Payment_type: cash
SELECT SUM(TOTAL) as TOTAL_PRICE
FROM RECEIPT 
WHERE PAYMENT_TYPE LIKE '%Cash%'

-- 8) find accumulate  price classify Payment_type: credit card
SELECT SUM(TOTAL) as TOTAL_PRICE
FROM RECEIPT 
WHERE PAYMENT_TYPE LIKE '%Credit card%'

-- 9) find the maximum price from 2 payment_types
WITH Z (PAYMENT_TYPE, TOTAL_SUM) as (SELECT PAYMENT_TYPE, SUM(TOTAL) FROM RECEIPT GROUP BY PAYMENT_TYPE) 
SELECT TOTAL_SUM, PAYMENT_TYPE
FROM Z
WHERE TOTAL_SUM = (SELECT MAX(TOTAL_SUM) FROM Z)

-- 10) get the maximum number of medicines
WITH Z (MED_CODE, MED_COUNT) AS (SELECT MED_CODE, COUNT(MED_CODE) FROM MEDICAL_ORDER_ENTRY GROUP BY MED_CODE)
SELECT MED_CODE, MED_COUNT 
FROM Z 
WHERE MED_COUNT = (SELECT MAX(MED_COUNT) FROM Z)

-- 11) get thhe minimum number of medicines
WITH Z (MED_CODE, MED_COUNT) AS (SELECT MED_CODE, COUNT(MED_CODE) FROM MEDICAL_ORDER_ENTRY GROUP BY MED_CODE)
SELECT MED_CODE, MED_COUNT 
FROM Z 
WHERE MED_COUNT = (SELECT MIN(MED_COUNT) FROM Z)

-- 12) classify children
CREATE VIEW Children AS 
SELECT PAT_ID, NAME_TITLE, FNAME_EN, LNAME_EN, FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) AS AGE 
FROM PATIENT 
WHERE FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) < 13

SELECT * FROM Children

-- 13) classify teenager
CREATE VIEW Teen AS 
SELECT PAT_ID, NAME_TITLE, FNAME_EN, LNAME_EN, FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) AS AGE 
FROM PATIENT 
WHERE FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) BETWEEN 13 AND 19

SELECT * FROM Teen

-- 14) classify as adults
CREATE VIEW Adult AS 
SELECT PAT_ID, NAME_TITLE, FNAME_EN, LNAME_EN, FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) AS AGE 
FROM PATIENT 
WHERE FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) BETWEEN 20 AND 59

SELECT * FROM Adult

-- 15) classify as elder
CREATE VIEW Elder AS 
SELECT PAT_ID, NAME_TITLE, FNAME_EN, LNAME_EN, FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) AS AGE 
FROM PATIENT 
WHERE FLOOR(DATEDIFF(DAY, BIRTHDATE, Getdate()) / 365.25) > 59

SELECT * FROM Elder

-- 16) which clinic has the max number of doctor
WITH Z (CLINIC_ID, NUM_CLINIC) AS (SELECT CLINIC_ID, COUNT(CLINIC_ID) FROM DOCTOR GROUP BY CLINIC_ID)
SELECT CLINIC_ID, NUM_CLINIC
FROM Z
WHERE NUM_CLINIC = (SELECT MAX(NUM_CLINIC) FROM Z)

-- 17) which clinic has the least number of doctor
WITH Z (CLINIC_ID, NUM_CLINIC) AS (SELECT CLINIC_ID, COUNT(CLINIC_ID) FROM DOCTOR GROUP BY CLINIC_ID)
SELECT CLINIC_ID, NUM_CLINIC
FROM Z
WHERE NUM_CLINIC = (SELECT MIN(NUM_CLINIC) FROM Z)

-- 18) CLINIC HAVE HOW MANY DOCTOR
WITH Z (CLINIC_ID, NUM_CLINIC) AS (SELECT CLINIC_ID, COUNT(CLINIC_ID) FROM DOCTOR GROUP BY CLINIC_ID)
SELECT CLINIC_ID, NUM_CLINIC
FROM Z
ORDER BY NUM_CLINIC ASC