Develop Your Sql Database, Queries And Database Projects



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

306 views0 comments

Recent Posts

See All