Task 1: Create student table in database and insert the data
CREATE TABLE STUDENTS(
StudentName varchar2(50),
StudentRollNo varchar2(15) ,
S_Class varchar2(50),
Age number(2),
FathersName varchar2(50),
CONSTRAINT s_pk PRIMARY KEY (StudentRollNo)
);
INSERT INTO STUDENTS VALUES (‘JYOSNA’,’MLKSP01’,'X',19, ‘CHANDRA’);
INSERT INTO STUDENTS VALUES (‘Srusti MISHRA','MLKSP02','XI',18,'SURESH MISHRA');
INSERT INTO STUDENTS VALUES ('VAMSI', 'MLKSP03','IX',17,’PITTA’);
INSERT INTO STUDENTS VALUES ('MONISHA', 'MLKSP05','XII',18,’VENKAT');
INSERT INTO STUDENTS VALUES ('SUMITA SHARMA', 'MLKSP06','IV',14,'AJAY SHARMA');
INSERT INTO STUDENTS VALUES ('NANDINI GUPTA', 'MLKSP07','VII',18,'RAM GUPTA');
INSERT INTO STUDENTS VALUES ('RAM KUMAR', 'MLKSP08','X',17,'RAMAN KUMAR');
INSERT INTO STUDENTS VALUES ('LAKSHMI M', 'MLKSP09','VII',18,'SUBRAMANYAM');
INSERT INTO STUDENTS VALUES (‘LAKSHMI M’, 'MLKSP4','X',19, 'SUBRAMANYAM');
Task 2 : Write sql query to find out total number of students in each class
SELECT S_CLASS, COUNT(StudentRollNo) AS TOTAL_STUDENTS from STUDENTS
GROUP BY S_CLASS ORDER BY TOTAL_STUDENTS
Task 3- Write sql query to find out average age of students in each class.
SELECT S_CLASS, AVG(Age) AS AVERAGE_AGE_PER_CLASS from STUDENTS
GROUP BY S_CLASS;
Task 4- Write sql query to find out the number of students having same roll no.
SELECT COUNT(StudentRollNo) from STUDENTS
GROUP BY StudentRollNo HAVING COUNT(StudentRollNo) > 1 ;
Task 5 : Write sql query to view the duplicate name of a student from the table
SELECT StudentName from STUDENTS
GROUP BY StudentName HAVING COUNT(StudentName) > 1 ;
