Difference between revisions of "SQL Examples"
Jump to navigation
Jump to search
| Line 21: | Line 21: | ||
=== Join Query including Unit-Of-Measurement === | === Join Query including Unit-Of-Measurement === | ||
[[File:ExampleLab.jpg| | [[File:ExampleLab.jpg|frameless|left]] | ||
SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID; | SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID; | ||
Revision as of 10:02, 2 November 2022
While the SICdb dataset is provided as csv raw data, it is designed to be used in a a relational database management system. Refer to Get Started for further information building up the database and corresponding software. Our software also includes one-click SQL codes for hundreds of fields like creatinine on day one/two/..., max/min/Average lactate and much more.
Using Referenced Fields
One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.
Simple Join Query
SELECT CaseID, d_references.ReferenceValue as Survived FROM `cases` LEFT JOIN d_references ON d_references.ReferenceGlobalID=cases.HospitalDischargeType;
Join Query including Unit-Of-Measurement
SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;
Subqueries to select select
SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases