Student_Management.sql

CREATE DATABASE Students;
use students;

#CREATE TABLE
create table studentdetails 
(sid int(10) primary key, 
f_name varchar(20) not null,
l_name varchar(20) not null,
branch varchar(5),
percentage decimal(4,2));

#INSERTING VALUES
insert into studentdetails 
values
(121,'k','Kavya','cse',64.6),
(122,'k','Devika','cse',84.6),
(123,'p','darshini','ise',95.67),
(124,'b','Ayesha','aiml',34.00),
(125,'F','smith','ise',87.6),
(126,'s','suhas','cse',75.67),
(127,'b','maha','AIDS',76.00),
(128,'e','jones','AIML',87.6),
(129,'s','suDEEP','AIDS',75.67),
(130,'S','VISHU','CSE',99.00);


#DISPLAYING TABLE
SELECT * FROM STUDENTDETAILS;

SET SQL_SAFE_UPDATES=0;
#MODIFYING DATA TO  IN PROPER WAY
UPDATE STUDENTDETAILS SET
F_NAME=UPPER(F_NAME),
L_NAME=UPPER(L_NAME),
 BRANCH=UPPER(BRANCH); 
 SET SQL_SAFE_UPDATES=1;
 
 #ADDING NEW COLUMN
 ALTER TABLE STUDENTDETAILS
 ADD GRADE VARCHAR(1);

 # INSERTING DATA TO NEW COLUMN USING CASE FUNCTION
SET SQL_SAFE_UPDATES=0;
 UPDATE studentdetails
SET grade = 
    CASE 
        WHEN percentage >= 85 THEN 'A'
        WHEN percentage >= 70 AND percentage < 85 THEN 'B'
        WHEN percentage >= 60 AND percentage < 70 THEN 'C'
        WHEN percentage >= 35 AND percentage < 60 THEN 'D'
        ELSE 
        'F'
END;


# ADDING STUDENT FULNAME COLUMN, ADDING DATA INTO IT AND DELETEING FIRST AND LAST NAME COLUMNS
SET SQL_SAFE_UPDATES=0;
ALTER TABLE STUDENTDETAILS
ADD SNAME VARCHAR(30) NOT NULL
AFTER SID;
SET SQL_SAFE_UPDATES=0;
UPDATE STUDENTDETAILS SET
SNAME=CONCAT(F_NAME,' ',L_NAME);


ALTER TABLE STUDENTDETAILS
DROP F_NAME,
DROP L_NAME;


#DISPLAYING TABLE
SELECT * FROM STUDENTDETAILS;

#count of total students
SELECT COUNT(*) FROM STUDENTDETAILS;

#Display highest percentage
SELECT MAX(PERCENTAGE) 
FROM STUDENTDETAILS;

# DISPLAY LOWEST PERCENTAGE
SELECT MIN(PERCENTAGE)
FROM STUDENTDETAILS;

#QUERY TO DISPLAY STUDENTS INFORMATION  BASED ON PERCENTAGE USING ORDER BY CLAUSE 
SELECT * 
FROM STUDENTDETAILS
ORDER BY PERCENTAGE DESC;

# DISPLAY  STUDENTS ID AND NAME HAVING 'A' GRADE
SELECT SID,SNAME
FROM STUDENTDETAILS
WHERE GRADE='A';

# DISPLAY GRADE AND NO OF  NUMBER STUDENTS 
SELECT GRADE,COUNT(SID)
FROM STUDENTDETAILS
GROUP BY GRADE
ORDER BY GRADE;

#DISPLAY NUMBER OF STUDENTS IN EACH BRANCH
SELECT BRANCH,COUNT(SID)
FROM STUDENTDETAILS
GROUP BY BRANCH;


# INFORMATION OF THE TOP STUDENT
# USING LIMIT
SELECT * FROM STUDENTDETAILS
ORDER BY PERCENTAGE DESC
LIMIT 1; 

#  USING SUBQUERY
SELECT * FROM STUDENTDETAILS
WHERE PERCENTAGE = (SELECT MAX(PERCENTAGE) FROM STUDENTDETAILS);
  
# CORELATED SUBQUERY
SELECT * FROM STUDENTDETAILS S1 WHERE 
(SELECT COUNT(DISTINCT S2.PERCENTAGE)
 FROM STUDENTDETAILS S2 
 WHERE S2.PERCENTAGE>=S1.PERCENTAGE)=1;
 
 # RANK STUDENT USING WINDOW FUNCTIONS
 
 SELECT S.* ,DENSE_RANK() OVER(ORDER BY PERCENTAGE DESC) AS RANK_POSITION 
 FROM STUDENTDETAILS AS S;
 
 #SECOND HIGEST PERCENTAGE STUDENT DETAILS
 SELECT * FROM STUDENTDETAILS WHERE PERCENTAGE=
 (SELECT MAX(PERCENTAGE) FROM STUDENTDETAILS
 WHERE PERCENTAGE < (SELECT MAX(PERCENTAGE) FROM STUDENTDETAILS));