HI
in the final project of SQL in DATA SCIENCE path,
I really don’t understand what is " rv " at the 9th and 15th line
I searched the web and found nothing relevant . Does anybody have any idea about this mysterious thing?
SELECT
active_patients.Name AS Name,
round(LDL) AS LDL,
active_patients.Address AS Address,
active_patients.Phone AS Phone
FROM
panels,
(SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv
JOIN active_patients ON panels.patient_ID = active_patients.ID
GROUP BY
LDL,
active_patients.Name
HAVING LDL >= rv.Borderline_High
ORDER BY LDL DESC;
SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL" will output a table (the right name for this table is an intermediate table if i am not mistaken.), this “table” needs to have a name, you/the project decide the name for this table is rv.
edit: it seems i am mistaken, rv is a derived table. But please feel free to correct me if i am wrong in what kind of table rv is
well, actually the following code (SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv
equals the ‘Borderline_High’ column from ‘recommended_values’ Table as the one of rv
ie rv.Borderline_High is equal to 'recommended_values.Borderline_High"
But rv is not grammtically a derived table because no ‘AS’ is used in the line (SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv
i am not sure what as gives you, but (SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv is certainly a derived table, i checked the documentation. Maybe both are derived tables, that is possible. would have to check that in documentation
To keep it simple, although it might vary between flavors of SQL engines, “AS” is basically optional for the most part when used to rename for an ALIAS.
The above is a DERIVED table, and in order for SQL to know where to get it’s data from it has to be given an name; in this case as covered we ALIAS’ed this as “rv”.
We see this in action when we look at the predicate in the HAVING clause that explicitly calls out to select the field “Borderline_High” from object reference “rv”.
HAVING LDL >= rv.Borderline_High
Example: Build the Schema on the LEFT first, then Run SQL on the right after. This uses TSQL.
