Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class

It has been a while since we have had a puzzle on this blog post, so today we are going to see a very simple but brain teaser puzzle. This puzzle is about Deleting Qualified Rows From Multiple Tables. This question was actually asked to me by my client while working together on the Comprehensive Database Performance Health Check.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz0-800x305

Everyone who gives correct answers to the puzzle will get a 50% discount on SQL Server Performance Tuning Practical Workshop – Recorded Classes and One person will get FREE Access to it as well.

Puzzle Setup – Multiple Tables

The problem which I am going to describe happened with my one of the largest client who is working in the financial technology area (a private bank). However, due to privacy issues, I can’t write here the details about their table structure, I am going to use a sample database of the students and class.

The setup is very simple. We have three tables.

Table 1: Student – Containing student names
Table 2: Class – Containing the class name
Table 3: StudentClass- Containing the relationship between student and class

Here is the script to set up that up:

USE TempDB
GO
-- Set up environment
CREATE TABLE Student (ID INT, StudentName VARCHAR(100))
GO
CREATE TABLE Class (ID INT, ClassName VARCHAR(100))
GO
CREATE TABLE StudentClass (ID INT, ClassID INT, StudentID INT)
GO
-- Populate Data
INSERT INTO Student (ID, StudentName)
SELECT 1, 'Mark'
UNION
SELECT 2, 'John'
UNION
SELECT 3, 'Thomas'
GO
-- Populate Data
INSERT INTO Class (ID, ClassName)
SELECT 1, 'Maths'
UNION
SELECT 2, 'English'
UNION
SELECT 3, 'Science'
GO
-- Populate Data
INSERT INTO StudentClass (ID, ClassID, StudentID)
SELECT 1, 1, 1
UNION
SELECT 2, 2, 2
UNION
SELECT 3, 1, 3
UNION
SELECT 4, 2, 1
UNION
SELECT 5, 2, 3
UNION
SELECT 6, 3, 1
GO
-- Query to List all Students and Classes
SELECT DISTINCT s.StudentName,
		c.ClassName
FROM StudentClass sc
INNER JOIN Class c ON c.ID = sc.ClassID
INNER JOIN Student s ON s.ID = sc.StudentID
GO

Here is the output of the query which we just ran. It lists the name of the students with the class they are enrolled in.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz1

Puzzle Statement – Delete from Multiple Tables

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.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz2

Here is the initial script was written which satisfies both the scenarios.

SET STATISTICS IO ON 
-- Delete from Students Table
DELETE s
FROM Student s
INNER JOIN StudentClass sc ON s.ID = sc.StudentID 
INNER JOIN Class c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English' AND 
	sc.StudentID IN 
		(SELECT sc2.StudentID 
		FROM StudentClass sc2 
		GROUP BY sc2.StudentID 
		HAVING COUNT(*) = 1)
GO
-- Delete from StudentClass Table
DELETE sc
FROM StudentClass sc
INNER JOIN Class c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English'
GO

If you run the above query, you will get the following output.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz3

 

Additionally, when I go to messages, it shows the following output for multiple tables:

Table ‘Student’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Class’. 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.
Table ‘StudentClass’. Scan count 7, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)

Table ‘StudentClass’. 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.
Table ‘Class’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 rows affected)

Your Task:

Optimize the above DELETE statement. That’s it!

How to Participate

Once you figure out how to optimize the DELETE statement, post your answer in the comments section. Your answer will be invisible until July 20th. I will publish all the answers on July 20th.

I will check the performance of the DELETE statement by running above DELETE statement and your modified suggestion. If your suggestion takes less amount of total logical reads, you will be the winner.

You have complete freedom to do anything to optimize the DELETE statement. Here are a few ideas:

  • Re-write DELETE statement
  • Combine DELETE operations
  • USE CTE
  • USE TempTables
  • Create Indexes
  • Use Subqueries

Pretty much anything you can think of it, you can try. However, your answer should be correct and logical reads should be less. I will also test by passing the different subject in the WHERE clause and will see that your query follows both the scenarios mentioned above.

Everyone Gets to Win

Everyone who gives correct answers to the puzzle will get a 50% discount on SQL Server Performance Tuning Practical Workshop – Recorded Classes and One person will get FREE Access to it as well.

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

, ,
Previous Post
SQL Puzzle – Correct the Incorrect Query – Win Price Worth USD 1000 – Aggregate and Subquery
Next Post
SQL SERVER – SQL Agent Job and Backslash – Strange Behavior

Related Posts

48 Comments. Leave new

  • Muhammad Qaiser
    August 1, 2019 4:07 am

    Select @classid=Id from classs where classname =‘English’ will always pick one record , infact it should not run without using top 1. And I’m next query it is using Min(classid), seems not useful

    Reply
  • Declare @Students Table(StudentID Int Primary Key,StudentName VarChar(500))
    Declare @StudentClass Table(ClassID Int Identity(1,1),StudentID Int,ClassName VarChar(500));
    Declare @CLassName VarChar(500);

    Insert Into @Students
    Values(1,’John’),(2,’Mark’),(3,’Thomas’)

    Insert Into @StudentClass
    (StudentID,ClassName)
    Values(1,’English’),(2,’English’),(2,’Maths’),(2,’Science’),(3,’English’),(3,’Maths’)

    Set @ClassName=’English’

    –Method-1
    ;
    WITH CTE
    As
    (Select S.StudentID,Count(ClassID) As Cnt
    From @Students S Inner JOin @StudentClass SC On S.StudentID=SC.StudentID
    Where S.StudentID In(Select Distinct StudentID From @StudentClass Where ClassName=@CLassName)
    Group By S.StudentID)
    Delete S From @Students S Inner JOin CTE E On E.StudentID=S.StudentID
    Where Cnt=1

    Delete From @StudentClass Where ClassName=@ClassName

    Select S.StudentName,ClassName
    From @Students S Inner Join @StudentClass SC On S.StudentID=SC.StudentID

    –Method-1
    ;
    WITH CTE
    As
    (Select Distinct StudentID
    From @StudentClass Where ClassName=@ClassName),
    N
    As
    (Select S.StudentID,Count(*) As NoOfClass
    From @Students S Inner JOin @StudentClass SC On SC.StudentID=S.StudentID
    Inner JOin CTE E On E.StudentID=S.StudentID
    Group By S.StudentID)
    Delete S From @Students S Inner JOin N On N.StudentID=S.StudentID
    Where N.NoOfClass=1

    Delete From @StudentClass Where ClassName=@ClassName

    Select S.StudentName,ClassName
    From @Students S Inner JOin @StudentClass SC On SC.StudentID=S.STudentID

    Reply
  • USE TempDB
    GO

    SET STATISTICS IO ON

    DECLARE @ClassID INT

    SELECT @ClassID = ID
    FROM Class
    WHERE ClassName = ‘English’

    DELETE Student
    WHERE ID IN
    (SELECT sc2.StudentID
    FROM StudentClass sc2
    WHERE sc2.ClassID = @ClassID
    GROUP BY sc2.StudentID
    HAVING COUNT(*) = 1)

    DELETE StudentClass
    WHERE ClassID = @ClassID

    Reply
  • DELETE A FROM StudentClass A INNER JOIN Class B ON A.CLASSID=B.ID
    WHERE B.ClassName =’ENGLISH’

    DELETE FROM Student WHERE ID NOT IN (SELECT StudentID FROM StudentClass)

    Reply

Leave a Reply

Menu