5.
3 Data Management
Prac 10 – Write the code in MySQL to
Q1. Create a database named ‘SMgmt’
Q2. Open this database
Q3. Create a table named ‘Student_Detail’ as per the understanding the structure of the table given as below
(Student_Id – primary key)
Student_Id Name D_O_B English Maths_Bio Phy_Acc Chem_BSt IP
101 Amitej 2000/02/01 98 78 87 77 98
102 Abhishek 2001/09/11 88 98 89 98 78
103 Bhavya 2000/12/12 88 89 88 89 88
104 Mansi 2001/07/11 83 85 87 85 83
105 Anushka 2000/02/12 77 79 77 79 81
106 Prakhar 2001/12/15 88 89 90 93 88
107 Shivam 2000/04/24 73 75 77 75 73
108 Raj Shekhar 2001/12/28 87 85 83 85 87
Q4. Insert the records as given above.
Q5. Show the average marks of each Subject.
Q6. Show the Maximum score in English.
Q7. Show the minimum score of the subject IP.
Q8. Add a new column named Scholarship and assign all the students with the constant amount 5000.00
Q9. Change the Scholarship amount to 10000.00 for all those students who have scored above 85 in
Phy_Acc and Chem_BSt.
Q10. Show the name of the students with 10% of the scholarship amount added for those students who have
scored above 90 in the Maths_Bio and the heading of this column should be Max Scholars . (** Not editing
the column just for the display purpose)
*************
Code 10
Statement1 CREATE DATABASE SMgmt;
Statement2 USE SMgmt;
Statement3
CREATE TABLE Student_Detail
( Student_Id INTEGER(4) PRIMARY KEY ,
Name VARCHAR(20) ,
D_O_B DATE ,
ENGLISH INTEGER(3) ,
Maths_Bio INTEGER(3) ,
Phy_Acc INTEGER(3) ,
Chem_BSt INTEGER(3) ,
IP INTEGER(3) ) ;
Statement 4 INSERT INTO Student_Detail
( Student_Id , Name , D_O_B , ENGLISH , Maths_Bio , Phy_Acc , Chem_BSt , IP)
VALUES ( 101, ‘Amitej’, ‘200/02/01’, 98, 78, 87, 77, 98 ),
(102, ‘Abhishek’, ‘2001/09/11’, 88, 98, 89, 98, 78),
(103, ‘Bhavya’, ‘2000/12/12’ , 88, 89, 88, 89 ,88),
);
Query 5 SELECT Student_Id, Name, AVG(English), AVG(Maths_Bio), AVG(Phy_Acc),
AVG(Chem_BSt), AVG(IP) FROM Student_Detail;
Query 6 SELECT MAX( English) FROM Student_Detail;
Query 7 SELECT MIN(IP) FROM Student_Detail;
Query 8 ALTER TABLE Student_Detail
ADD Scholarship DECIMAL(10,2)
DEFAULT 5000.00;
Query 9 UPDATE Student_Detail
SET Scholarship = 10000.00
WHERE Phy_Acc > 85 AND Chem_BSt >85;
Query 10 SELECT Name, Scholarship*0.1+Scholarship AS Max Scholars
FROM Student_Detail
WHERE Maths_Bio > 90;
************
