Hello, Friends...
In this blog we will created some important SQL database and performed query on it. So read it carefully and comment if anything is missing.
Our first database is - Student management system(sms)
Creating database sms
Syntax:
CREATE DATABASE sms;
use sms;
Now we will creating databases tables
CREATE TABLE student
(
sid char(4) primary key,
sname varchar(20),
sdob date,
scity varchar(20),
squal varchar(20),
semail varchar(30),
sphone varchar(20)
);
CREATE TABLE course
(
courseid char(4) primary key,
coursename varchar(40),
coursecategory varchar(20),
coursefees decimal(10,2),
courseduration int
);
CREATE TABLE batch
(
batchid char(4) primary key,
bsdate datetime ,
bstrength int,
courseid char(4),
foreign key(courseid) references course(courseid)
);
CREATE TABLE enrollment
(
batchid char(4),
sid char(4) ,
edate date,
primary key(batchid,sid),
foreign key(sid) references student(sid),
foreign key(batchid) references batch(batchid)
);
Now inserting record into database sms
insert into student values('s001','rajesh','1980-12-17','kolkata','graduate','rajesh@gmail.com','09830978900');
insert into student values('s002','john','1949-1-7','hyderabad','postgraduate','john@yahoo.com','9833978933');
insert into student values('s003','kunal','1967-2-3','pune','postgraduate','kunal@gmail.com','09830922900');
insert into student values('s004','maya','1990-12-17','kolkata','graduate','maya.com','09830765900');
insert into student values('s005','jadeja','1940-1-23','kolkata','postgraduate','jadeja@yahoo.com','09837865432');
insert into student values('s006','suman','1995-6-17','kolkata','undergraduate','suman@gmail.com','0983097890');
insert into student values('s007','soha','1990-7-17','mumbai','undergraduate',null,null);
insert into student values('s008','thapa','1980-8-17','assam','graduate','thapa@gmail.com','19830978900');
insert into student values('s009','hira','1954-9-17','mumbai','postgraduate','hira@gmail.com','09234097890');
insert into student values('s010','akash','1977-1-27','kolkata','postgraduate','akash@gmail.com',null);
insert into student values('s011','amir','1992-1-1','delhi','undergraduate','amirgmail.com','09831118900');
insert into student values('s012','ramesh','1980-12-17','kolkata','graduate','ramesh@yahoo.com','09830918900');
insert into student values('s013','suresh','1980-3-22','kolkata','graduate','suresh@gmail.com','09830978912');
insert into student values('s014','amir','1945-1-13','delhi','postgraduate','amir123@rediffmail.com','29830978900');
insert into student values('s015','esha','1981-10-30','mumbai','graduate','esha@gmail.com','09831378900');
insert into student values('s016','gopichand','1966-5-7','assam','postgraduate','gopi@gmail.com','09831918100');
insert into student values('s017','sonali','1995-11-11','mumbai','undergraduate','sonali@gmail.com','09855978900');
insert into student values('s018','lisa','1983-1-31','delhi','graduate','lisa@gmail.com','09832978923');
insert into student values('s019','smith','1980-12-17','pune','graduate','smith@yahoo.com','09831111900');
insert into student values('s020','rajesh','1994-7-8','pune','graduate','rajesh@gmail.com','09830978900');
insert into course values('c001','sql server','compsc',1000,40);
insert into course values('c002','compmat','civileng',3000,120);
insert into course values('c003','biomaths','biotech',4000,160);
insert into course values('c004','word','compsc',500,8);
insert into course values('c005','photo','compsc',800,8);
insert into batch values('b001','2013-02-01 09:30' ,10, 'c001');
insert into batch values('b002','2013-03-01 09:30' ,10, 'c002');
insert into batch values('b003','2013-01-01 09:30' ,10, 'c003');
insert into batch values('b004','2013-03-31 09:30' ,10, 'c003');
insert into batch values('b005','2013-04-04 09:30' ,10, 'c005');
insert into batch values('b006','2013-01-27 09:30' ,10, 'c002');
insert into batch values('b007','2012-11-30 09:30' ,10, 'c004');
insert into batch values('b008','2013-01-28 09:30' ,10, 'c002');
insert into batch values('b009','2013-02-16 09:30' ,10,'c001');
insert into batch values('b010','2012-12-12 09:30' ,10, 'c003');
insert into enrollment values('b001','s001','2013-01-01');
insert into enrollment values('b001','s002','2013-01-31');
insert into enrollment values('b001','s003','2013-01-11');
insert into enrollment values('b001','s004','2013-02-02');
insert into enrollment values('b001','s005','2013-01-01');
insert into enrollment values('b001','s006','2013-01-01');
insert into enrollment values('b001','s007','2013-01-01');
insert into enrollment values('b001','s008','2013-01-01');
insert into enrollment values('b001','s009','2013-01-01');
insert into enrollment values('b002','s010','2013-02-01');
insert into enrollment values('b002','s012','2013-02-27');
insert into enrollment values('b002','s014','2013-01-21');
insert into enrollment values('b002','s016','2013-01-12');
insert into enrollment values('b002','s017','2013-02-15');
insert into enrollment values('b003','s018','2013-12-11');
insert into enrollment values('b003','s019','2013-02-27');
insert into enrollment values('b003','s020','2013-01-21');
insert into enrollment values('b003','s013','2013-01-01');
insert into enrollment values('b003','s007','2013-12-15');
insert into enrollment values('b003','s008','2013-11-25');
insert into enrollment values('b004','s001','2013-02-11');
insert into enrollment values('b004','s003','2013-02-27');
insert into enrollment values('b004','s006','2013-01-21');
insert into enrollment values('b004','s009','2013-03-01');
insert into enrollment values('b005','s001','2013-02-11');
insert into enrollment values('b005','s003','2013-02-27');
insert into enrollment values('b005','s006','2013-03-21');
insert into enrollment values('b005','s009','2013-04-01');
insert into enrollment values('b006','s001','2013-01-11');
insert into enrollment values('b006','s003','2012-12-27');
insert into enrollment values('b006','s006','2013-01-11');
insert into enrollment values('b006','s009','2013-01-01');
insert into enrollment values('b006','s007','2013-01-13');
insert into enrollment values('b006','s002','2012-12-17');
insert into enrollment values('b006','s008','2013-01-21');
insert into enrollment values('b006','s005','2013-01-01');
insert into enrollment values('b007','s001','2012-11-11');
insert into enrollment values('b007','s002','2012-11-11');
insert into enrollment values('b007','s003','2012-11-21');
insert into enrollment values('b007','s004','2012-11-13');
insert into enrollment values('b007','s007','2012-10-13');
insert into enrollment values('b007','s010','2012-10-17');
insert into enrollment values('b007','s009','2012-12-01');
insert into enrollment values('b008','s011','2012-11-11');
insert into enrollment values('b008','s012','2012-11-11');
insert into enrollment values('b008','s013','2012-11-21');
insert into enrollment values('b008','s014','2012-11-13');
insert into enrollment values('b008','s017','2012-10-13');
insert into enrollment values('b008','s020','2012-10-17');
insert into enrollment values('b008','s019','2012-12-01');
insert into enrollment values('b009','s001','2012-11-11');
insert into enrollment values('b009','s012','2012-11-11');
insert into enrollment values('b009','s013','2012-11-21');
insert into enrollment values('b009','s004','2012-11-13');
insert into enrollment values('b009','s007','2012-10-13');
insert into enrollment values('b009','s010','2012-10-17');
insert into enrollment values('b009','s009','2012-12-01');
insert into enrollment values('b010','s011','2012-11-11');
insert into enrollment values('b010','s002','2012-11-11');
insert into enrollment values('b010','s003','2012-11-21');
insert into enrollment values('b010','s014','2012-11-13');
insert into enrollment values('b010','s017','2012-10-13');
insert into enrollment values('b010','s010','2012-10-17');
insert into enrollment values('b010','s009','2012-12-01');
Querying and their answers
Problem 1>>> Display all undergraduate student whose name starts with ‘S’ and is of length between 5 and 20.
solution:
select sname from student
where sname like 's%' and length(sname) between 5 and 20 and squal='undergraduate';
Problem 2>>> Display the student who are senior citizen (>=60).
solution:
select sname from student where round(datediff(current_date,sdob)/365)>=60;
Problem 3>>> Display student who were born after 1st of June 1980
solution:
select sname from student where sdob>'1980-06-01';
Problem 4>>> The student are suppose to only provide mobile numbers .All mobile numbers should start with zero followed by 10 digits. Display student name having invalid phone numbers.
solution:
select sname from student
where sphone not like '0%' or length(sphone)!=11;
Problem 5>>> All emails should have “@” anywhere after the first character and should end with “.com”. Display count of students having invalid email id.
solution:
select count(sname) from student
where semail not like '_%@%.com';
Problem 6>>> Display the name and email of student who have a Gmail account.
solution:
select sname,semail from student
where semail like '_%@gmail.com';
Problem 7 >>> Display the qualification and the total number of students registered based on their qualifications.
(Alias use “totalStud” for total number of students)
solution:
select squal,count(sid) totalstud from student
group by squal;
Problem 8 >>> Display the full name of the month and the total number of students who are having their birthday in that month. (Alias use “Month” and “Total”)
solution:
select date_format(sdob,'%M') month,count(sid) from student
group by month;
Problem 9 >>> Display the student name that was born in a leap year ordering by student name and year of birth.
solution:
select sname from student
where year(sdob)%4=0;
Problem 10 >>> Display student whose city is Kolkata as “HomeStudent ” and others as “DistanceStudent” under a column “Remarks”. Also display the name and city of the student.
solution:
select sname,scity,if(scity='kolkata','HomeStudent','DistanceStudent') Remarks from student;
Problem 11>>> Display batchid, coursename, batch start date, batch end date for all batches. (batch end date=batch start date +course duration).
solution:
select b.batchid,c.coursename,date_format(b.bsdate,'%Y-%m-%d') batch_start_date,date_format(b.bsdate+interval c.courseduration day,'%Y-%m-%d') end_date_of_batch
from batch b
join course c on c.courseid=b.courseid;
Problem 12>>>Display all batchid having a difference of 10 hours and less between its starting and ending date.
solution:
select b.batch_id from batch b
join coure_id c
on c.course_id=b.course_id
where c.course_duration >10 ;
Problem 13 >>>Display all batches having similar start date and strength.
solution:
select batch_id from batch b1
bs.date=
(select bs.date from batch2 where b1.bstrenth=b2.bstrenth and b1.bs.date=b2.b.sdate)
Problem 14>>> Display student who enrolled for the batch after its start date.
solution:
select s.sname from student s
join enrollment e on s.sid=e.sid
join batch b on b.batchid=e.batchid
where e.edate>b.bsdate;
Problem 15>>> Display the studentid, studentname , totalfees for all student.
solution:
select s1.sid,s1.sname,s2.totalfees from student s1 join
(select s.sid id,sum(c.coursefees) totalfees from student s
join enrollment e on s.sid=e.sid
join batch b on b.batchid=e.batchid
join course c
on c.courseid=b.courseid group by s.sid) s2
on s1.sid=s2.id;
Problem 16>>> Display courses which are not being taught currently along with courses which are being taught.
Also display the batchid for the courses currently running and null for non executing courses.
solution:
select distinct c.coursename
from course c
where c.courseid not in (select courseid
from batch
group by courseid);
Problem 17>>> Display count of students having no contact information. (Either email or phone).
solution:
select count(sid) from student
where semail is null or sphone is null;
Problem 18>>> Display coursename having above average fees.
solution:
select coursename from course
where coursefees>(select avg(coursefees) from course);
Problem 19>>>Display coursename where fees are less than the average fees of its category.
solution:
select c1.coursename from course c1
join (select coursecategory,avg(coursefees) average
from course
group by coursecategory) c2
on c1.coursecategory=c2.coursecategory
where c1.coursefees<c2.average;
Problem 20>>> Display the coursename having the highest enrollment.
solution:
select c.courseid from enrollment e
join batch b on b.batchid=e.batchid
join course c on c.courseid=b.courseid
group by c.courseid
having count(e.sid)>=all
(select count(e.sid)from enrollment e
join batch b on b.batchid=e.batchid
join course c on c.courseid=b.courseid group by c.courseid) ;
Problem 21>>> Display student name having duplicate email ids.
solution:
select s1.sid,s1.sname from student s1
join (select semail,count(sid) from student
group by semail
having count(semail)>1)s2 on s1.semail=s2.semail;
Problem 22>>> Display student name having similar name but different email ids.
solution:
select s1.sid,s1.sname from student s1
join (select sname from student
group by sname
having count(sid)>1)s2 on s1.sname=s2.sname
join (select semail from student
group by semail
having count(sid)=1)s3 on s1.semail=s3.semail;
Thanks, for read it, If you like Codersarts blog and looking for Programming Assignment Help Service,Database Development Service,Web development service,Mobile App Development, Project help, Hire Software Developer,Programming tutors help and suggestion you can send mail at contact@codersarts.com.
Please write your suggestion in comment section below if you find anything incorrect in this blog post
List of Other Codersarts Assignment Expert Help Services
Also visit codersarts top rated programming Help services
For given latitude value check the location on equator in python
Calculate distance between two points on Earth the latitute and longitude given in degrees
Write a program of Rational Numbers Addition,Subtraction, Multiplication, Division in python
Design and implement a "Golf Club" program that records information about golfers and tournaments
Concatenate First, mid and last character of String in Python
A set of utilities to manipulate polynomials, Addition, subtraction, Multiplication and evaluation