Program16:
MYSQL QUERIES
Create a student table with following fields and Enter data as given in table
below:
FILED NAME TYPE SIZE
RegNo varchar 10
SName char 25
Age Int -
Class Varchar 15
Grade Float -
Data to be Entered
RegNo SName Age Class Grade
R101 Dayton 16 XII-Rose 95
R102 Santin 15 XII-Rose 96
D101 Dani 15 XII-Dahlia 92
T101 Emmanuel 16 XII-Tulip 89
L101 Adam 16 XII-Lily 90
L102 Bejoy 15 XII-Lily 86
L103 Rohith 16 XII-Lily 91
Then make the following Queries:
❖ Write an SQL query to add a new attribute Email (Varchar) to the student
table.
❖ Write an SQL query to add email addresses to the newly added Email
column for each student.
❖ Write an SQL query to display all records from the student table.
❖ Write an SQL query to display all data from the student table in
descending order by Grade.
❖ Write an SQL query to delete the student with RegNo T101 from the
student table.
❖ Write an SQL query to select all distinct classes from the student table.
❖ Write an SQL query to group the data in the student table by Class and
find the maximum of the Grade for each class.
❖ Write an SQL query to group the data in the student table by Class and
find the count of students (RegNo) for each class.
❖ Write an SQL query to group the data in the student table by Class and
find the sum of the Grade for each class.
❖ Write an SQL query to list all the students of age 15 and older in the XII-
Rose class.
Aim:
The SQL exercises is to practice creating, modifying, and querying a
Student table in a MySQL database. This includes operations like
inserting data, updating records, and retrieving data based on various
conditions.
Creating Table Student:
CREATE TABLE Student (
RegNo INT PRIMARY KEY,
SName VARCHAR(50),
Age INT,
Class VARCHAR(10),
Grade FLOAT );
Inserting Data Into Table:
INSERT INTO Student (RegNo, SName, Age, Class, Grade) VALUES
('R101', 'Dayton', 16, 'XII-Rose', 95),
('R102', 'Santin', 15, 'XII-Rose', 96),
('D101', 'Dani', 15, 'XII-Dahlia', 92),
('T101', 'Emmanuel', 16, 'XII-Tulip', 89),
('L101', 'Adam', 16, 'XII-Lily', 90),
('L102', 'Bejoy', 15, 'XII-Lily', 86),
('L103', 'Rohith', 16, 'XII-Lily', 91);
❖ Write an SQL query to add a new attribute Email (Varchar) to the student
table.
➢ ALTER TABLE Student ADD Email VARCHAR (100);
❖ Write an SQL query to add email addresses to the newly added Email
column for each student.
• UPDATE Student SET Email = 'dayton@[Link]' WHERE
RegNo = 'R101';
• UPDATE Student SET Email = 'santin@[Link]' WHERE
RegNo = 'R102';
• UPDATE Student SET Email = 'dani@[Link]' WHERE RegNo
= 'D101';
• UPDATE Student SET Email = 'emmanuel@[Link]' WHERE
RegNo = 'T101';
• UPDATE Student SET Email = 'adam@[Link]' WHERE
RegNo = 'L101';
• UPDATE Student SET Email = 'bejoy@[Link]' WHERE
RegNo = 'L102';
• UPDATE Student SET Email = 'rohith@[Link]' WHERE
RegNo = 'L103';
❖ To display all records from the student table.
SELECT * FROM Student;
❖ To display all data from the student table in descending order by Grade.
SELECT * FROM Student ORDER BY Grade DESC;
❖ To delete the student with RegNo T101 from the student table.
DELETE FROM Student WHERE RegNo = 'T101';
❖ To select all distinct classes from the student table.
SELECT DISTINCT Class FROM Student;
❖ To group the data in the student table by Class and find the maximum of
the Grade for each class.
SELECT Class, MAX(Grade) AS MaxGrade
FROM Student
GROUP BY Class;
❖ Group the data in the student table by Class and find the count of students
(RegNo) for each class.
SELECT Class, COUNT(RegNo) AS StudentCount
FROM Student
GROUP BY Class;
❖ Group the data in the student table by Class and find the sum of the Grade
for each class.
SELECT Class, SUM(Grade) AS TotalGrade
FROM Student
GROUP BY Class;
❖ SQL query to list all the students of age 15 and older in the XII-Rose
class.
SELECT * FROM Student
WHERE Age >= 15 AND Class = 'XII-Rose';
Result:
Thus, the queries to create the tables and execute the sql
commands was done successfully.
