Hey! I am working on the SQL cumulative project, and stuck at task 8 - I don’t quite understand the solution that they are suggesting in the hints - and why would I need to DELETE the column row? Below is the code they’re suggesting - may someone break it down for me so I get a better understanding? Thank you!
DELETE FROM active_patients
WHERE Name IN
(SELECT Name FROM active_patients LIMIT 1);
SELECT * FROM active_patients WHERE ID = (SELECT max(ID) from active_patients);
I’ll try to explain it to the best of my ability. (my wording might be a bit off, but I will try to make it understandable)
Firstly the ; separates the statements, so there are 2 in your example.
The first statement:
DELETE FROM active_patients
WHERE Name IN (
SELECT Name
FROM active_patients
LIMIT 1);
This block will DELETE
rows FROM
the active_patients table WHERE
the Name value comes forth in the results of the following statement:
SELECT Name
FROM active_patients
LIMIT 1
What the above statement does is SELECT
the Name column for all of the records in the active_patients table and LIMIT
it to the first applicable row. So in other words, this will return only 1 record (or the first row).
The second statement:
SELECT *
FROM active_patients
WHERE ID = (
SELECT max(ID)
FROM active_patients);
When this is executed it will SELECT
all of the columns (That is what the “*” indicates) for the rows in the active_patients table WHERE
the ID value of the row is equal to the result of the following statement:
SELECT max(ID)
FROM active_patients
This SELECT
s the max
(maximum or biggest) value in the ID column FROM
all of the rows in the active_patients table.
@louwding thanks a million!