MySQL
Practicals
PRATICAL 26
Problem statement: Create a student table with the student id, name, and marks as
attributes where the student id is the primary key.
Solution:
Source Code:
create table student
( -> studid int primary key,
-> name varchar(30),
-> marks int
-> );
Screenshot:
PRATICAL 27
Problem statement: In the table ‘student’ created in practical 26, insert the details of new
students.
Solution:
Source Code:
insert into student values(1, 'sanjay', 67);
mysql> insert into student values(2, 'surendra', 88);
mysql> insert into student values(3, 'Jamil', 74);
mysql> insert into student values(4, 'Rahul', 92);
mysql> insert into student values(5, 'Prakash', 78);
Screenshot:
PRATICAL 28
Problem statement: Write SQL command to get the details of the students with marks
more than 80.
Solution:
Source Code:
select * from student where marks >=80;
Screenshot:
PRATICAL 29
Problem statement: Write SQL command to Find the min, max, sum, and average of the
marks in a student marks table..
Solution:
Source Code:
select min(marks) as Min_marks, max(marks) as Max_Marks, sum(marks) as
Total_Marks, avg(marks) as Average_Marks from student;
Screenshot:
PRATICAL 30
Problem statement: Delete the details of a student table created in Practical 26.
Solution:
Source Code:
Delete from student;
Screenshot:
PRATICAL 31
Problem statement: Find the total number of customers from each country in the table
(customer ID, customer Name, country) using group by.
Solution:
Source Code:
select country, count(cname) as 'Total_Customers' from customer
group by country;
Screenshot:
PRATICAL 32
Problem statement: Write a SQL query to order the (student ID, marks) table in descending
order of the marks.
Solution:
Source Code:
select * from student Order By name DESC;
Screenshot:
PRATICAL 33
Problem statement: for the given table ‘Hospital’ write SQL command to display name all
patient admitted in month of May.
PID PNAME ADMITDATE DEPT FEES
AP/PT/001 Rahil Khan 21/04/2019 ENT 250
AP/PT/002 Jitendal Pal 12/05/2019 Cardio 400
AP/PT/003 Suman Lakra 19/05/2019 Cardio 400
AP/PT/004 Chandumal Jain 24/06/2019 Neuro 600
.
Solution:
Source Code:
select * from hospital where monthname(admitdate) = 'May';
Screenshot:
PRATICAL 34
Problem statement: for the given table ‘Hospital’ write SQL command to Display patient
name in upper case with year of admission.
PID PNAME ADMITDATE DEPT FEES
AP/PT/001 Rahil Khan 21/04/2019 ENT 250
AP/PT/002 Jitendal Pal 12/05/2019 Cardio 400
AP/PT/003 Suman Lakra 19/05/2019 Cardio 400
AP/PT/004 Chandumal Jain 24/06/2019 Neuro 600
.
Solution:
Source Code:
Select UPPER(pname) as ‘patient name’, YEAR(admitdate) as ‘admit year’
From hospital;
Screenshot:
PRATICAL 35
Problem statement: for the given table ‘Hospital’ Create sql query to display first four
letters of the patient name along with length of their name who admitted before may.
PID PNAME ADMITDATE DEPT FEES
AP/PT/001 Rahil Khan 21/04/2019 ENT 250
AP/PT/002 Jitendal Pal 12/05/2019 Cardio 400
AP/PT/003 Suman Lakra 19/05/2019 Cardio 400
AP/PT/004 Chandumal Jain 24/06/2019 Neuro 600
.
Solution:
Source Code:
Select LEFT(pname,4) as ‘pname’, length(pname) as ‘length’
From hospital
Where month(admitdate) < 5;
Screenshot:
PRATICAL 36
Problem statement: To display student id, Name, DOB, Marks, Email of those male students
in ascending order of their names.
STUDENT ID NAME DOB MARKS EMAIL
SCS101101 Rahil Khan 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal Jain 24/06/2002 489 jain@gmail.com
Solution:
Source Code:
SELECT STUDENT_ID, NAME, DOB, MARKS, EMAIL FROM STUDENT WHERE GENDER='M'
ORDER BY NAME;
Screenshot:
PRATICAL 37
Problem statement: Describe the below table
STUDENT ID NAME DOB MARKS EMAIL
SCS101101 Rahil Khan 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal Jain 24/06/2002 489 jain@gmail.com
Solution:
Source Code:
DESC STUDENT;
Screenshot:
PRATICAL 38
Problem statement: Display student name whose marks above 470.
STUDENT ID NAME DOB MARKS EMAIL
SCS101101 Rahil Khan 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal Jain 24/06/2002 489 jain@gmail.com
Solution:
Source Code:
SELECT NAME FROM STUDENT WHERE MARKS>470;
Screenshot:
PRATICAL 39
Problem statement: To display student id, Name, DOB of those students who are born
between ‘2000- 04-21’ and ‘2002-06-24’.
STUDENT ID NAME DOB MARKS EMAIL
SCS101101 Rahil Khan 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal Jain 24/06/2002 489 jain@gmail.com
Solution:
Source Code:
SELECT STUDENT_ID, NAME, DOB FROM STUDENT WHERE DOB BETWEEN '2000-04-21'
AND '2002-06-24';
Screenshot:
PRATICAL 40
Problem statement: To display student id, Gender, Name, DOB, Marks, Email in descending
order of their marks.
STUDENT NAME GENDER DOB MARKS EMAIL
ID
SCS101101 Rahil Khan M 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal M 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra M 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal Jain M 24/06/2002 489 jain@gmail.com
Solution:
Source Code:
SELECT STUDENT_ID, GENDER, NAME, DOB, MARKS, EMAIL FROM STUDENT ORDER BY
MARKS DESC;
Screenshot:
