Winners and Solution – DELETE Qualified Rows From Multiple Tables

Earlier this week, I had announced a contest to get Recorded PASS of my extremely popular training SQL Server Performance Tuning Practical Workshop – Recorded Classes. As the price of this class high, I wanted to keep my contest difficult as well. Today we will see the winners and solutions.

Winners and Solution - DELETE Qualified Rows From Multiple Tables del-puz0-800x305

You can read this blog post to understand what was the actual puzzle and conditions. Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class. Here I am reproducing the puzzle in the simple words –

Due to some reason, in the school, the class of English was canceled during the summer. All the students who had enrolled in the English class were affected. Here are two scenarios for us for the students who had taken English class.

Scenario 1: If a student has enrolled in a single class of English – Remove from the Student’s list and Remove from the StudentClass list.
Scenario 2: If a student has enrolled in multiple class with English class – Remove from the StudentClass list.

Original Setup

Winners and Solution - DELETE Qualified Rows From Multiple Tables del-puz1

What we want to do

Winners and Solution - DELETE Qualified Rows From Multiple Tables del-puz2

Final Outcome

Winners and Solution - DELETE Qualified Rows From Multiple Tables del-puz3

To active the final outcome from the original setup, we needed to write a query which takes the least amount of the reads. I had given a sample query which was taking 19 logical reads and that was way higher. Your task was to come up with the solution which takes lease amount of the logical reads to accomplish my tasks.

I was very much delighted that I have got many good answers to this DIFFICULT puzzle.

Let us see first the solution of Harsh who has won the FREE Access to the SQL Server Performance Tuning Practical Workshop – Recorded Classes.

Solution by Harsh – Winner

Only 4 logical reads.

SET STATISTICS IO ON
DECLARE @ClassID AS INT
SELECT @ClassID=ID FROM Class WHERE ClassName='English'
--
DECLARE @StudentID AS VARCHAR(MAX)
SELECT @StudentID= COALESCE(@StudentID , ',') + CAST(StudentID AS VARCHAR(20)) + ','
FROM StudentClass
GROUP BY StudentID
HAVING COUNT(*) = 1 AND MIN(ClassID)=@ClassID
--
DELETE FROM Student WHERE @StudentID LIKE '%,' + CAST(ID AS VARCHAR(20)) + ',%'
DELETE FROM StudentClass WHERE ClassID=@ClassID
GO

Harsh, send me email to get your FREE access as you are winner. Here is the link to his solution.

Notable Winners

Here are other participants who also provided a valid answer and also eligible for 50% off for my recorded course.

  • sabin
  • Calunoth Allerton
  • Andrew Wait
  • Nandakumar
  • Sanjay Monpara
  • Bryan Rebok
  • Saranyasc
  • Nitin Dhiman

If you can think of the better solution, you can always post them here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – SQL Agent Job and Backslash – Strange Behavior
Next Post
SQL Puzzle – Schema and Table Creation – Answer Without Running Code

Related Posts

2 Comments. Leave new

  • HI Pinal,

    i am new to this blog, i have one question.

    Q: Why we need to have this much big query ? To achieve this, why can’t we delete data where className = ‘English’ !

    when i have perform delete operation on table logical reads 1 and physical reads are 0.

    Please refer below Message,

    Table ‘EMp’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (3 row(s) affected)

    Correct me if i am missing anything !

    Regards,
    Patan

    Reply
  • with cte
    as
    (
    select * from (giventablename)
    )
    delete from cte
    where classname=’english’

    Reply

Leave a Reply

Menu