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.
Comments
Post a Comment