SQL දන්න අය සීටීනවාද ?

[paranavithana.123]

SQL දන්න අය සීටීනවාද ?

select c.Name, s.Name from Students as s
inner join Enrollment as e on e.SID = s.SID
inner join Course as c on e.CID = c.CID
having COUNT (e.CID) > 3;

Q1. Select the course, entrolled more than 3 students. Student name sholdbe desplayed

හදපු එකේ වැරැද්ද කුමක්ද ?

 

[Aruna119]

table tika koo ??
​

 

[paranavithana.123]

table tika koo ??
​

danna onida eka paper ekene thiyenne ?

 

[mtmadushanka]

 

[Sword_Fish]

Tables

Student

Student_Id | Student_Name
-------------------------------
1 | Student_1
2 | Student_2
3 | Student_3
4 | Student_4
5 | Student_5
6 | Student_6
7 | Student_7
8 | Student_8
9 | Student_9
10 | Student_10

Course

Course_Id | Course_Name
-------------------------------
1 | Course 1
2 | Course 2
3 | Course 3

Enrollment

Enrollmet_Id | Enrollmet_date | Student_Id | Course_Id
-------------------------------------------------------------
1 | 21-JUL-2018 | 1 | 1
2 | 21-JUL-2018 | 2 | 2
3 | 21-JUL-2018 | 3 | 2
4 | 21-JUL-2018 | 4 | 2
5 | 21-JUL-2018 | 5 | 2
6 | 21-JUL-2018 | 6 | 3
7 | 21-JUL-2018 | 7 | 3
8 | 21-JUL-2018 | 8 | 3
9 | 21-JUL-2018 | 9 | 3
10 | 21-JUL-2018 | 10 | 3

Query to get Students count for each course

SELECT C.COURSE_ID, C.COURSE_NAME, COUNT(C.COURSE_ID) ENROLLED_STUDENTS
FROM STUDENT s
INNER JOIN ENROLLMENT e ON S.STUDENT_ID = E.STUDENT_ID
INNER JOIN COURSE c ON E.COURSE_ID = C.COURSE_ID
GROUP BY C.COURSE_ID, C.COURSE_NAME

Output :

Course_Id | Course_Name | Enrolled_Students
-------------------------------------------------
1 | Course 1 | 1
2 | Course 2 | 4
3 | Course 3 | 5

Query to get Students count for each course > 3

SELECT C.COURSE_ID, C.COURSE_NAME, COUNT(C.COURSE_ID) ENROLLED_STUDENTS
FROM STUDENT s
INNER JOIN ENROLLMENT e ON S.STUDENT_ID = E.STUDENT_ID
INNER JOIN COURSE c ON E.COURSE_ID = C.COURSE_ID
GROUP BY C.COURSE_ID, C.COURSE_NAME
HAVING COUNT(C.COURSE_NAME) > 3;

Output :

Course_Id | Course_Name | Enrolled_Students
-------------------------------------------------
2 | Course 2 | 4
3 | Course 3 | 5

Query to get Students names for each course > 3

SELECT C.COURSE_ID, C.COURSE_NAME, LISTAGG(S.STUDENT_NAME, ', ') WITHIN GROUP (ORDER BY S.STUDENT_NAME) STUDENTS
FROM STUDENT s
INNER JOIN ENROLLMENT e ON S.STUDENT_ID = E.STUDENT_ID
INNER JOIN COURSE c ON E.COURSE_ID = C.COURSE_ID
GROUP BY C.COURSE_ID, C.COURSE_NAME
HAVING COUNT(C.COURSE_ID) > 3;

Output :

Course_Id | Course_Name | Students
------------------------------------------------------------------------------------
2 | Course 2 | Student_2, Student_3, Student_4, Student_5
3 | Course 3 | Student_10, Student_6, Student_7, Student_8, Student_9