T-SQL sub query to find out the name of students who have taken minimum number of subjects-CodePudding

I have following table and data in my SQL database, and I Need to find out the name of students who have taken minimum number of subjects by using T-SQL sub query.

EXPECTED RESULT IMAGE LINK: Expected Result

Table 01 Name (tblSubject)

CREATE TABLE tblSubject
(
    SubjectID INT PRIMARY KEY NOT NULL,
    SubjectName NVARCHAR (50) NOT NULL
)
GO

Table 02 Name (tblSemester)

CREATE TABLE tblSemester 
(
    SemesterID INT PRIMARY KEY NOT NULL,
    SemesterName NVARCHAR (50) NOT NULL
)
GO

Table 03 Name (tblTeacher)

CREATE TABLE tblTeacher
(
    TeacherID INT PRIMARY KEY NOT NULL IDENTITY,
    TeacherName NVARCHAR (50) NOT NULL
)
GO

Table 04 Name (tblTeacher_Details)

CREATE TABLE tblTeacher_Details
(
    TeacherID INT REFERENCES tblTeacher (TeacherID) NOT NULL,
    SemesterID INT REFERENCES tblSemester (SemesterID) NOT NULL,
    SubjectID INT REFERENCES tblSubject (SubjectID) NOT NULL
    PRIMARY KEY (TeacherID, SemesterID, SubjectID )
)
GO

Table 05 Name (tblStudent)

CREATE TABLE tblStudent
(
    StudentID INT PRIMARY KEY IDENTITY NOT NULL,
    StudentName NVARCHAR (50) NOT NULL
)
GO

Table 06 Name (tblStudent_Details)

CREATE TABLE tblStudent_Details
(
    StudentID INT REFERENCES tblStudent (StudentID) NOT NULL,
    SemesterID INT REFERENCES tblSemester (SemesterID) NOT NULL,
    SubjectID INT REFERENCES tblSubject (SubjectID) NOT NULL,
    TeacherID INT REFERENCES tblTeacher (TeacherID) NOT NULL
    PRIMARY KEY (StudentID, SemesterID, SubjectID, TeacherID )
)
GO

INSERT Data INTO Table Name (tblSubject)

INSERT INTO tblSubject
VALUES 
    (1, 'C#'),
    (2, 'Data Base'),
    (3, 'Web Design'),
    (4, 'MIS'),
    (5, 'PHP'),
    (6, 'Project Management'),
    (7, 'PCL'),
    (8, 'Software Engineering'),
    (9, 'Data Minin')
GO

INSERT Data INTO Table Name (tblSemester)

INSERT INTO tblSemester
VALUES 
    (1, 'Spring'),
    (2, 'Summer'),
    (3, 'Fall'),
    (4, 'Winter')
GO

INSERT Data INTO Table Name (tblTeacher)

INSERT INTO tblTeacher
VALUES
    ('A'),
    ('B'),
    ('C')
GO

INSERT Data INTO Table Name (tblTeacher_Details)

INSERT INTO tblTeacher_Details
([TeacherID], [SemesterID], [SubjectID])
VALUES
    (1,1,1),
    (2,1,2),
    (3,1,3),
    (1,2,1),
    (2,2,9),
    (3,2,4),
    (1,3,5),
    (2,3,2),
    (3,3,6),
    (1,4,7),
    (2,4,8),
    (3,4,3)
GO

INSERT Data INTO Table Name (tblStudent)

INSERT INTO tblStudent
VALUES
    ('AA'),
    ('BB'),
    ('DD'),
    ('EE'),
    ('HH'),
    ('GG'),
    ('FF'),
    ('CC'),
    ('II')
GO

INSERT Data INTO Table Name (tblStudent_Details)

INSERT INTO tblStudent_Details
([StudentID], [SemesterID], [SubjectID], [TeacherID])
VALUES
    (1,1,1,1),
    (2,1,1,1),
    (3,1,1,1),
    (1,1,2,2),
    (8,1,2,2),
    (9,1,2,2),
    (4,1,3,3),
    (5,1,3,3),
    (2,1,3,3),
    (6,1,3,3),
    (4,2,1,1),
    (6,2,1,1),
    (2,2,9,2),
    (7,2,9,2),
    (5,2,9,2),
    (1,2,4,3),
    (2,2,4,3),
    (8,2,4,3),
    (5,3,5,1),
    (9,3,5,1),
    (4,3,2,2),
    (6,3,2,2),
    (3,3,2,2),
    (9,3,6,3),
    (3,3,6,3),
    (7,3,6,3),
    (8,4,7,1),
    (7,4,7,1),
    (1,4,8,2),
    (2,4,8,2),
    (6,4,8,2),
    (1,4,3,3),
    (8,4,3,3),
    (3,4,3,3)
GO

CodePudding user response：

You can use this query:

select StudentName, count(1) [Number Of Subject] 
from tblStudent_Details d left join tblStudent s on s.StudentID=d.StudentID
group by StudentName
having count(1) = (select top 1 count(1) from tblStudent_Details group by StudentID order by 1)
order by StudentName

/* Output:
StudentName Number Of Subject
EE          3
FF          3
HH          3
II          3
*/

CodePudding user response：

try it :

select StudentName,count(*) from   tblStudent s
join   tblStudent_Details d on s.StudentID = d.StudentID
group by StudentName
order by 2