Question

Given the following relational tables TEACHER (TeacherID. FirstName LastName Department) COURSE Course CourseName Year, Semester, Main Teacherib. Language) STUDENT (StudentIDFirstName, LastName. BirthDate, Nationality) STUDENT COURSE-ENROLLMENT (Coursel. Studenti, EnrollmentYear) STREAMING-VIDEOLECTURE VideolecturelD Coursel. Topic Date, DurationInMinutes. Teacher) STREAMING-VIDEOLECTURE-ATTENDANCE (Studenti Videolecture. Coursel. AttendanceInMinutes) Write the following query in SQL language: Select the student 1o. first name, last name and nationalty of each student who has never attended a streaming video-lecture longer than 10% of the video-lecture duration Assignment for the exercise Use the text box below to provide your soluton E.

Answer 1

This query can be Done Using Not Exists Operator

1. We need to make query which extract all the students who have attended video lecture longer than 10% by Joining both the VideoLecture and Video-lecture-Attendance table

2 Then return only those that are not in the students id returned by above SQL

So here we are use nested query connected via Not Exists Operator

Lets first Build the Step 1

Select StudentID

From STREAMING-VIDEOLECTURE JOIN STREAMING-VIDEOLECTURE-ATTENDANCE

ON STREAMING-VIDEOLECTURE .VideoLectureID = STREAMING-VIDEOLECTURE-ATTENDANCE. VideoLectureID  

Where AttendanceInMinutes > (0.1 * DurationInMinutes )

Now connected it to outer query

So Final Query is

Select StudentID ,FirstName ,LastName , Nationality

From STUDENT

Where NOT EXISTS (

Select StudentID

From STREAMING-VIDEOLECTURE JOIN STREAMING-VIDEOLECTURE-ATTENDANCE

ON STREAMING-VIDEOLECTURE .VideoLectureID = STREAMING-VIDEOLECTURE-ATTENDANCE. VideoLectureID  

Where AttendanceInMinutes > (0.1 * DurationInMinutes )

)

Working of Not Exists

It will return True and False according to inner Query result .If inner Query is True then False is Returned and outer query didn't return Anything .

So it will return simply opposite of inner query Result.

So this how using NOT EXISTS we can get those students who have never attended video lecture more then 10% of their duration

IF u like the answer give it a Thumbs Up and Have any doubt comment it