DBMS Practical 1 Extended(q15)

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','john.smith@example.com','2023-01-15')
INSERT INTO Students VALUES(2,'Jane Doe','jane.doe@example.com','2023-02-01')
INSERT INTO Students VALUES(3,'Alex Johnson','alex.johnson@example.com','2023-03-10')
INSERT INTO Students VALUES(4,'Emily Brown','emily.brown@example.com','2023-03-25')
INSERT INTO Students VALUES(5,'Michael Lee','michael.lee@example.com','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"
                                    FROM Progress
                                    GROUP BY course_id
                                    )
SELECT Courses.instructor, rate FROM sq, Courses
    WHERE rate = (SELECT MAX(s.rate) FROM sq s) AND (sq.course_id = Courses.course_id)


--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)*/