View difference between Paste ID: fyNHGFhJ and jAA2GM8r

 CREATE DATABASE syit2;
 
 CREATE TABLE Students(
     student_id INT PRIMARY KEY,
     student_name VARCHAR(20),
     email VARCHAR(30),
     enrollment_date DATE);
 
 CREATE TABLE Courses(
     course_id INT PRIMARY KEY,
     course_name VARCHAR(20),
     instructor VARCHAR(20),
     start_date DATE,
     end_date DATE);
 
 CREATE TABLE Progress(
     progress_id INT PRIMARY KEY,
     student_id INT FOREIGN KEY REFERENCES Students,
     course_id INT FOREIGN KEY REFERENCES Courses,
     completed_modules INT,
     total_modules INT);
 
 SELECT * FROM Students
 
 SELECT * FROM Courses
 
 SELECT * FROM Progress
 
 INSERT INTO Students VALUES(1,'John Smith','[email protected]','2023-01-15')
 INSERT INTO Students VALUES(2,'Jane Doe','[email protected]','2023-02-01')
 INSERT INTO Students VALUES(3,'Alex Johnson','[email protected]','2023-03-10')
 INSERT INTO Students VALUES(4,'Emily Brown','[email protected]','2023-03-25')
 INSERT INTO Students VALUES(5,'Michael Lee','[email protected]','2023-04-05')
 
 INSERT INTO Courses VALUES(101,'Introduction to SQL','Prof. Anderson','2023-02-15','2023-03-30')
 INSERT INTO Courses VALUES(102,'Web Development','Prof. Johnson','2023-03-01','2023-05-15')
 INSERT INTO Courses VALUES(103,'Data Science Basics','Prof. Williams','2023-04-10','2023-06-30')
 INSERT INTO Courses VALUES(104,'Python for Beginners','Prof. Brown','2023-05-01','2023-06-15')
 INSERT INTO Courses VALUES(105,'Machine Learning','Prof. Thompson','2023-06-15','2023-08-31')
 
 INSERT INTO Progress VALUES (1,1,101,8,10),
                             (2,1,102,12,15),
                             (3,2,101,6,10),
                             (4,3,102,10,15),
                             (5,4,103,4,12),
                             (6,2,105,7,20),
                             (7,3,104,6,10),
                             (8,5,102,3,15),
                             (9,1,104,8,10)
 /*
 Drop table Students;
 Drop table Courses;
 Drop table Progress;
 */
 
 --Q1
 SELECT COUNT(*) 'No. of Students' FROM Students
 --Q2
 SELECT course_name,instructor FROM Courses
 --Q3
 SELECT avg(completed_modules) FROM Progress
 --Q4
 SELECT student_name FROM Students
     WHERE student_id=
         (SELECT student_id FROM Progress
             WHERE completed_modules=
                 (SELECT MAX(completed_modules) FROM Progress)
         )
 --Q5
 SELECT course_name FROM Courses
     WHERE course_id IN
         (SELECT course_id FROM Progress
             GROUP BY course_id
                 HAVING COUNT(*)>=
                     ALL(SELECT COUNT(*) FROM progress
                             GROUP BY course_id)
         )
 --Q6
 SELECT course_name,Percentage
     FROM (SELECT course_id,(SUM(completed_modules) *100/SUM(total_modules)) "Percentage" FROM Progress
             GROUP BY course_id) AS PC, Courses c
     WHERE c.course_id=PC.course_id
 
 --Q7
 SELECT student_name FROM Students
     WHERE student_id IN
         (SELECT student_id FROM Progress
             WHERE ((completed_modules*100)/(total_modules)) IN
                 (SELECT MAX((completed_modules*100)/(total_modules)) FROM Progress))
 
 --Q8
 SELECT course_name FROM Courses
     WHERE start_date>GETDATE()
 
 --Q9
 SELECT student_name FROM Students
     WHERE student_id IN
         (SELECT student_id FROM Progress
             WHERE completed_modules<>total_modules
         )
 
 --Q10
 SELECT instructor, avg_completion_rate FROM
     (SELECT course_id,avg((completed_modules*100)/(total_modules)) "avg_completion_rate" FROM Progress
         GROUP BY course_id) AS ACR, Courses c
             WHERE ACR.course_id=c.course_id
 
 --Q11
 SELECT student_name,"Courses_enrolled" FROM Students s,
     (SELECT student_id,COUNT(course_id) "Courses_enrolled" FROM Progress
         GROUP BY student_id) AS CE
     WHERE s.student_id=CE.student_id
 
 --Q12
 SELECT student_name,email FROM Students
     WHERE student_id IN
         (SELECT student_id FROM Progress
             WHERE completed_modules<>total_modules
         )
 
 --Q13
 SELECT avg(DATEDIFF(DAY,start_date,end_date)) "Average Course Duration (in days)" FROM Courses
 
 --Q14
 SELECT student_name from Students
 	where student_id=(
 		Select student_id from progress
 			where completed_modules=total_modules)
 
 --Q15
 Select c.course_name, s.student_name from Courses c, Students s, Progress p
 	where c.course_id=p.course_id and s.student_id=p.student_id and p.completed_modules<=p.total_modules
 	order by ((p.completed_modules*100)/(total_modules)) desc
 
 --Q16
 with sq as (select course_id, avg((completed_modules * 100) / total_modules) "rate"
-	Select c.instructor, avg(SumPerc.completion_rate) "max_completion_rate" from Courses c,
+									from Progress
-		(Select course_id,(sum(completed_modules*100)/sum(total_modules)) "completion_rate"  from Progress
+									group by course_id
-			group by course_id) as SumPerc
+									)
-		where c.course_id=SumPerc.course_id and avg(SumPerc.completion_rate)>= All((Select c.instructor, avg(SumPerc.completion_rate) "max_completion_rate" from Courses c,
+select Courses.instructor, rate from sq, Courses
-		(Select course_id,(sum(completed_modules*100)/sum(total_modules)) "completion_rate"  from Progress
+	where rate = (select max(s.rate) from sq s) and (sq.course_id = Courses.course_id)
-			group by course_id) as SumPerc)
+
-		group by c.instructor)
+
 --Q17
 (select sum(completed_modules) "Total completed modules" from progress)
 
 --Q18
 
 
 --19
 
 /*select MONTH(s.enrollment_date), count(*) from Courses c, Progress p, Student s
 	where p.student_id = s.student_id and p.course_id = c.course_id
 	group by MONTH(s.enrollment_date)*/