SQL Cumulative Project - Task 8


#1

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


#2

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 SELECTs the max (maximum or biggest) value in the ID column FROM all of the rows in the active_patients table.


#3

@louwding thanks a million!