Geek Answers Handbook

Improve my SQL Select statement to select students who have not fully completed the section.

My SQL skills are pretty limited. I am in my second year in computer science at a technical college. I am creating a Windows Forms application that will allow the BAS Director at my college to track students and their progress across all courses. I have full control over the design of the database, so if you are thinking about how to help me find a solution that will include setting up the database, which is an opportunity.

I am trying to select all Studentsthat do not have EnrollmentStatusout of 3 in all Coursesthat have CreditSectionof 1. There are 12 courses with CreditSectionof1

The tables I use are as follows: Schema

, SQL:

SELECT * FROM Students WHERE each student has 12 entries in CourseEnrollment AND
CourseEnrollment.EnrollmentStatus = 3 AND Courses.CreditSection = 1


SELECT * FROM Students WHERE Courses.CourseID 1 thru 12 EXIST in
CourseEnrollment for each student AND CourseEnrollment.EnrollmentStatus = 3

, , , , 4 , , , .

, , :

SELECT DISTINCT s.* FROM Students s
WHERE s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 1 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 2 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 3 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 4 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 5 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 6 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 7 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 8 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 9 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 10 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 11 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 12 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 13 AND ce.EnrollmentStatus = 3)

- , SQL, LINQ, , . - , .

LINQ :

var query =
                from student in datStudents.Students.AsEnumerable<dsStudentManager.StudentsRow>()
                where !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 1 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 2 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 3 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 4 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 5 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 6 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 7 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 8 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 9 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 10 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 11 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 12 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 13 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID)
                select new
                {
                    id = student.StudentID,
                    rtcid = student.RTCStudentID,
                    firstname = student.FirstName,
                    lastname = student.LastName,
                    phone = student.Phone,
                    studentemail = student.StudentEmail,
                    personalemail = student.PersonalEmail,
                    address = student.Address,
                    city = student.City,
                    state = student.State,
                    zip = student.Zip,
                    birthdate = student.BirthDate,
                    gender = student.Gender,
                    notes = student.Notes,
                    studentdocumentslocation = student.StudentDocumentsLocation
                };
+4
3

, - :

 SELECT * FROM Students WHERE StudentID NOT IN (
      SELECT s.StudentID FROM Students s 
        JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
        JOIN Courses c ON ce.CourseID = c.CourseID
        WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
        GROUP BY s.StudentID
        HAVING COUNT(*) = 12
      )

, "HAVING COUNT (*) = 12" , 12 . , .

 SELECT * FROM Students WHERE StudentID NOT IN (
      SELECT s.StudentID FROM Students s 
        JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
        JOIN Courses c ON ce.CourseID = c.CourseID
        WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
        AND c.CourseID IN (1,2,3,4,5,6,7,8)
        GROUP BY s.StudentID
        HAVING COUNT(*) = 8 -- Number of courses in the ID in clause
      )

, .

+4

- , , .

SELECT * FROM Students s
	inner join (select StudentID, count(0) as CrsEnrollCount
					from CourseEnrollment ce 
					inner join Courses c 
					on ce.CourseID = c.CourseID
					where ce.EnrollmentStatus = 3 AND Courses.CreditSection = 1) cnt
	on cnt.StudentID = s.StudentID
where CrsEnrollCount < 12
Hide result
+2

To write this T-SQL:

SELECT * FROM Students WHERE StudentID NOT IN (
  SELECT s.StudentID FROM Students s 
    JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
    JOIN Courses c ON ce.CourseID = c.CourseID
    WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
    GROUP BY s.StudentID
    HAVING COUNT(*) = 12
  )

Becomes this LINQ:

var query = 
            from student in datStudents.Students.AsEnumerable<dsStudentManager.StudentsRow>()
            where !(from s2 in datStudents.Students.AsEnumerable<dsStudentManager.StudentsRow>()
                        join ce in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() on student.StudentID equals ce.StudentID
                        join c in datStudents.Courses.AsEnumerable<dsStudentManager.CoursesRow>() on ce.CourseID equals c.CourseID
                        where ce.EnrollmentStatus == 3 && c.CreditSection == 1
                        group s2 by s2.StudentID into s3
                        where s3.Count() == 12
                        select s3.Key).Contains<int>(student.StudentID)
            select new
            {
                id = student.StudentID,
                rtcid = student.RTCStudentID,
                firstname = student.FirstName,
                lastname = student.LastName,
                phone = student.Phone,
                studentemail = student.StudentEmail,
                personalemail = student.PersonalEmail,
                address = student.Address,
                city = student.City,
                state = student.State,
                zip = student.Zip,
                birthdate = student.BirthDate,
                gender = student.Gender,
                notes = student.Notes,
                studentdocumentslocation = student.StudentDocumentsLocation
            };

As far as I can tell, that’s right. It still gives the desired results.

0
source

More articles:


All Articles