Chapter 1 Querying and SQL Functions
PRATICAL 1
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
-> );
PRATICAL 2
Problem statement: In the table ‘student’ created in practical 1, 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);
PRATICAL 3
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;
PRATICAL 4
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;
PRATICAL 5
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;
PRATICAL 6
Problem statement: for the given table ‘Hospital’ write SQL command to display name all patient
admitted in month of May.
Solution:
Source Code:
select * from hospital where monthname(admitdate) = 'May';
PRATICAL 7
Problem statement: for the given table ‘Hospital’ write SQL command to Display patient name in
upper case with year of admission.
Solution:
Source Code:
Select UPPER(pname) as ‘patient name’, YEAR(admitdate) as ‘admit year’ From hospital;
PRATICAL 8
Problem statement: For given string “techtipnow computer education”, Write SQL command to
display the position of “education”.
Solution:
Source Code:
Select INSTR(“techtipnow computer education”, “education”);