Identify Students Below Cutoff Marks Using SQL Queries
"In this video, we dive into a practical SQL scenario where we help an instructor identify students who scored below the cutoff of 120 marks. We'll explore a query that uses DISTINCT, JOIN, GROUP BY, and HAVING clauses to find students needing extra attention. Whether you're preparing for interviews or sharpening your SQL skills, this example is perfect for understanding how to filter and group data effectively."
--A Coaching instructor, Vijay conducted an exam. now he's curious to know about the students who did not clear
the cutoff of 120 marks as he will have to arrange extra classes for them with his teaching assistant, rehana.
can you help him by writing a query for this?
-- Create table for student details
drop table Students
CREATE TABLE Students (
sid INT PRIMARY KEY,
sname VARCHAR(100)
);
-- Create table for student marks
drop table Marks
CREATE TABLE Marks (
sid INT,
sname VARCHAR(100),
marks INT,
FOREIGN KEY (sid) REFERENCES Students(sid)
);
-- Insert sample data into Students table
INSERT INTO Students (sid, sname) VALUES
(1, 'Rahul'),
(2, 'Priya'),
(3, 'Amit'),
(4, 'Sneha'),
(5, 'Vikas');
-- Inserting records into Marks table
INSERT INTO Marks VALUES (1, 'Maths', 35);
INSERT INTO Marks VALUES (1, 'English', 40);
INSERT INTO Marks VALUES (1, 'Science', 30);
INSERT INTO Marks VALUES (2, 'Maths', 50);
INSERT INTO Marks VALUES (2, 'English', 45);
INSERT INTO Marks VALUES (2, 'Science', 40);
INSERT INTO Marks VALUES (3, 'Maths', 60);
INSERT INTO Marks VALUES (3, 'English', 55);
INSERT INTO Marks VALUES (3, 'Science', 70);
INSERT INTO Marks VALUES (4, 'Maths', 80);
INSERT INTO Marks VALUES (4, 'English', 75);
INSERT INTO Marks VALUES (4, 'Science', 85);
INSERT INTO Marks VALUES (5, 'Maths', 90);
INSERT INTO Marks VALUES (5, 'English', 95);
INSERT INTO Marks VALUES (5, 'Science', 85);
select * from Marks
select * from Students
-- Query to find students who did not clear the cutoff of 120 marks
select distinct s.sname as student_name
from Students s join Marks M on s.sid=m.sid where s.sid in (
select sid
from Marks group by sid
having sum(marks)(lessthan)120)
SQL Interview Problem: Splitting Full Names into First, Middle, and Last Names
• SQL Interview Problem: Splitting Full Name...
Top SQL Interview Questions: RANK, DENSE_RANK, ROW_NUMBER Explained
• Top SQL Interview Questions: RANK, DENSE_R...
Top 3 Products by Sales & Employees by Salary | SQL Ranking Functions Explained
• Top 3 Products by Sales & Employees by Sal...
SQL Interview Challenge: Find Hidden Likes in Your Friends' Favorites!
• SQL Interview Challenge: Find Hidden Likes...
SQL Magic: Aggregating Marks & Delivery Dates with Advanced Queries!
• SQL Magic: Aggregating Marks & Delivery Da...
Top 20% vs Bottom 20% of Students in SQL: NTILE vs TOP PERCENT
• Top 20% vs Bottom 20% of Students in SQL: ...
SQL Tricks: Find the 3rd Lowest Salary in Each Department with Two Powerful Methods!
• SQL Tricks: Find the 3rd Lowest Salary in ...
SQL Tutorial: Find Employees Who Joined Before Their Managers
• SQL Tutorial: Find Employees Who Joined Be...
SQL Tutorial: Retrieve Specific Rows with OFFSET & ROW_NUMBER - Top 2 Methods Compared!
• SQL Tutorial: Retrieve Specific Rows with ...
How to Build a Location-Based Hierarchy in SQL | Recursive Query Tutorial
• How to Build a Location-Based Hierarchy in...
How to Count Weekends in Any Month Using SQL Server: A Step-by-Step Guide
• How to Count Weekends in Any Month Using S...
SQL Date Magic: Find & Format the Last Day of the Previous Month
• SQL Date Magic: Find & Format the Last Day...
How to Count Workdays in SQL: Two Effective Methods Explained!
• How to Count Workdays in SQL: Two Effectiv...
How to Find Employees Earning More Than Their Managers in SQL
• How to Find Employees Earning More Than Th...
SQL Join Techniques: Filtering Data with WHERE vs. ON Clause
• SQL Join Techniques: Filtering Data with W...
Retrieve Specific Rows in SQL: OFFSET-FETCH vs. ROW_NUMBER!
• Retrieve Specific Rows in SQL: OFFSET-FETC...
SQL Query to Sum Comma-Separated Values in a Column
• SQL Query to Sum Comma-Separated Values in...
#SQLHAVINGClause
#SQLGroupBy
#SQLQueryforMarks
#SQLStudentMarksExample
#SQLDataFiltering
#SQLAggregateFunctions
#SQLEducation