SQL cumulative project

Projects
1

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;
2

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

1 Like
3

@stetim94
Thank you very much for your attention.

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

:frowning:

4

no, given you select two columns:

Borderline_High, High

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

1 Like
5

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.

http://sqlfiddle.com/#!18/42e69/2

2 Likes
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.