SQL: Student Database and Computing Results

 -- Exer20 Create a student database and compute the results.

-- create a database create database student; -- use / open the database just created use student; -- Create a table in the database with the name class. CREATE TABLE `student`.`class` ( `rollno` int NOT NULL, `sname` VARCHAR(45) NULL, `sub1` Decimal(5,2) NULL, `sub2` Decimal(5,2) NULL, `sub3` Decimal(5,2) NULL, `sub4` Decimal(5,2) NULL, `sub5` Decimal(5,2) NULL, `sub6` Decimal(5,2) NULL, PRIMARY KEY (`rollno`)); -- insert / add rows to the table just created. INSERT INTO CLASS VALUES (1401, 'PAWAN', 56, 36, 56, 78, 44, 67); INSERT INTO CLASS VALUES (1411, 'RAJESH', 100,100,96,100,100,100); INSERT INTO CLASS VALUES (1412, 'KARAN', 60,30,45,45,36,49); INSERT INTO CLASS VALUES (1403, 'SACHIN', 56,60,72,57,78,67); INSERT INTO CLASS VALUES (1410, 'PRAKASH', 96,99,97,90,78,100); INSERT INTO CLASS VALUES (1402, 'POOJA', 30,45,39,20,33,56); INSERT INTO CLASS VALUES (1405, 'ASHWINI', 79,65,79,70,89,88); INSERT INTO CLASS VALUES (1406, 'PRAJWAL', 100,90,100,89,90,100); INSERT INTO CLASS VALUES (1404, 'BALU', 35,30,78,23,44,70); INSERT INTO CLASS VALUES (1407, 'ESHWAR', 100,100,100,98,99,100); -- Describe the structure of the TABLE describe class; -- Add the columns / fields: total, percentage and result to the table class alter table class add column ( total decimal(5,2), percentage decimal(5,2), result varchar(45)); -- Calculate total marks by updating total fields update class set total = sub1 + sub2 + sub3 + sub4 + sub5 + sub6; -- calculate percentage by updating percentage fields. update class set percentage = total/6; -- Compute "pass" or "Fail" update class set result = "pass" where (sub1 >= 35 and sub2 >= 35 and sub3 >= 35 and sub4 >= 35 and sub5 >= 35 and sub6 >= 35); update class set result = "fail" where (sub1 < 35 or sub2 < 35 or sub3 < 35 or sub4 < 35 or sub5 < 35 or sub6 < 35); -- List the content of the TABLE select * from class; -- Display only rollno and student name select rollno, sname from class; -- Show the students who are "pass" select * from class where result = "pass"; -- show the students who are "fail" select * from class where result = "fail"; -- find out how many students are pass select count(*) from class where result = "pass"; -- find out how many students are fail select count(*) from class where result = "fail"; -- list the students whose percentage is 60 or above. select * from class where percentage >= 60; -- Sort the table according to the student name; select * from class order by sname; -- end of exercise.