Question

Disease Afflicts post sev pre sev --〈Takes Patient Drus SSN dosage duration

Write SQL queries to retrieve the requested information from the Drug Test database

(a) Find the names and ids of all patients who are afflicted with both depression and asthma.

(b) Find the names and ids of all patients who were completely cured by the end of their drug trial (as indicated by all post-treatment severity levels being 0).

Answer 1

Solutions:

here SQL tables are as follows :

1) Patient Table

Field patientName patientId SSN Type varchar (30) NULL varchar (30) NULL varchar (30) NULL

patientName patientId SSN □ XYZ 7 2 RAM *(NULL,) (NULL) (NULL)

2) Disease Table

Field PatientId DiseaseName Diseaseld Type varchar (30) NULL varchar (30) NULL varchar (30) NULL

DỊPatientId Diseasellame Disease!a depression1 asthma asthma 2 NULL) NULL)

3) Drug Table

Field DrugId DrugName PatientId Duration Type varchar (30) NULL varchar (30) NULL varchar (30) NULL int (10) NULL

DrugId DrugName PatientId Duration *「(NULL) |(NULL) (NULL) NULL)

a) Find the names and ids of all patients who are afflicted with both depression and asthma.

Query :

SELECT DISTINCT patient.patientName, patient.patientId
FROM patient INNER JOIN Disease
ON patient.patientId = Disease.PatientId AND Disease.DiseaseName IN ('depression' ,'asthma')

SELECT DISTINCT patient.patientName, patient.patientId FROM patient INNER JOIN Disease ON patient .patientId Disease. PatientId AND Disease. DiseaseName IN ('depression' ,' asthma')

Result :

  
1 Result 2 Profiler 3 Messages 4 Table Data5 Info l patientName patientId

B) Find the names and ids of all patients who were completely cured by the end of their drug trial

Query :

SELECT DISTINCT patient.patientName, patient.patientId

FROM patient INNER JOIN Drug

ON patient.patientId = Drug.PatientId AND Drug.duration = 0

  

SELECT DISTINCT patient, patientName, patient.patientId FROM patient INNER JOIN Drug ON patient, patientId = Drug. PatientId AND Drug, duration = q

Result :

  
1 Result 3 Mes 2 Profiler ▼ | patientName patientId ▼|乓鹵 (Read Only) □XYZ