Assignment – 1

Query 1 :

CREATE TABLE tblBranch(branchID varchar(5) NOT NULL PRIMARY KEY,branchName varchar(50) NOT
NULL,building char(10),other char(10));

Output :

Query 2 :

CREATE TABLE tblSubject(subjectID varchar(5) NOT NULL PRIMARY KEY,subName varchar(0) NOT
NULL,thHours int(4),tuHours int(4),prHours int(4),credit int(4));

Output :
Query 3 :

CREATE TABLE tblStudent(studentID int(4) NOT NULL PRIMARY KEY,fName varchar(20) NOT
NULL,mName varchar(20),lName varchar(20),address varchar(30),area varchar(20),city
varchar(20),state varchar(20),country varchar(20),pincode int(6),phoneNo int(10), birthDate
date,gender varchar(20) NOT NULL,regNo int(5), branchID char(20) NOT NULL, FOREIGN KEY
(branchID) REFERENCES tblBranch(branchID),admyear int(10),emailID varchar(20));

Output :
Query 4 :

CREATE TABLE tblFaculty(facID varchar(5) NOT NULL PRIMARY KEY,fName varchar(20) NOT
NULL,mName varchar(20),lName varchar(20),address varchar(20),area char(10),city char(10),state
char(10),country char(10),pincode int(10),phoneNo int(15),emailID varchar(20),joinDate date,gender
char(10) NOT NULL,qualification varchar(20),designation varchar(20),deptID char(10) NOT
NULL,addDuties char(20), FOREIGN KEY (deptID) REFERENCES tblBranch(branchID));

Output :
Query 5 :

CREATE TABLE tblCourse(courseID varchar(5) NOT NULL PRIMARY KEY,subID varchar(5) NOT
NULL,facID varchar(5) NOT NULL,semester int(2) NOT NULL,branchID char(5),FOREIGN KEY(subID)
REFERENCES tblSubject(subjectID),FOREIGN KEY (facID) REFERENCES tblFaculty(facID),FOREIGN KEY
(branchID) REFERENCES tblBranch(branchID));

Output :

Query 6 :

CREATE TABLE tblStudentCourse(stuCouID varchar(5) NOT NULL PRIMARY KEY,stuID int(5) NOT
NULL,courseID varchar(5) NOT NULL,month char(10),year int(5),FOREIGN KEY(stuID) REFERENCES
tblStudent(studentID),FOREIGN KEY(courseID) REFERENCES tblCourse(courseID));

Output :
Query 7 :

CREATE TABLE tblStudentResult(ID varchar(5) NOT NULL PRIMARY KEY,stuCouID varchar(5) NOT
NULL,creditObtained int(5) NOT NULL,isCleared char(5),yearApp int(5),FOREIGN KEY (stuCouID)
REFERENCES tblStudentCourse(StuCouID));

Output :

Query 8 :

CREATE TABLE tblOneTimeFees(ID varchar(10) NOT NULL PRIMARY KEY,stuID int(5) NOT
NULL,particular varchar(20),amount int(10),dateSub date,isRefundable varchar(5),FOREIGN KEY
(stuID) REFERENCES tblStudent(studentID));

Output :
Query 9 :

CREATE TABLE tblTuitionFees(ID varchar(5) NOT NULL PRIMARY KEY,stuID int(5) NOT NULL,semester
int(5),amount int(10),dateSub date,FOREIGN KEY (stuID) REFERENCES tblStudent(studentID));

Output :

Query 10 :

CREATE TABLE tblExamFees(ID varchar(5) NOT NULL PRIMARY KEY,stuID int(5) NOT NULL,couID
varchar(5) NOT NULL,amount int(10),dateSub date,FOREIGN KEY(stuID) REFERENCES
tblStudent(studentID), FOREIGN KEY (couID) REFERENCES tblCourse(courseID));

Output :
 Assignment – 2

Query 1 :

INSERT INTO tblBranch VALUES('CO','Computer','D1','First building after Admin Building');

INSERT INTO tblBranch VALUES('EC','Electronics & Communication','D2','Next to D1');

INSERT INTO tblBranch VALUES('EL','Electrical','D3','Opposite Site Office');

INSERT INTO tblBranch VALUES('ME','Mechanical','D7','Next to D6');

INSERT INTO tblBranch VALUES('CI','Civil','D6','Building ,next to Canteen');

INSERT INTO tblBranch VALUES('AP','Humanities','D7','Applied Science Department');

Query 2 :

INSERT INTO tblSubject VALUES('ss101','DBMS',3,0,2,3);

INSERT INTO tblSubject VALUES('ss201','Communication System',3,1,2,4);

INSERT INTO tblSubject VALUES('ss202','Digital Electronics',3,1,2,4);

INSERT INTO tblSubject VALUES('ss301','Electrical Circuit Theory',3,0,0,2);

INSERT INTO tblSubject VALUES('ss401','Engineering Mechanics',2,1,2,3);

INSERT INTO tblSubject VALUES('ss701','Maths-III',3,0,0,2);

INSERT INTO tblSubject VALUES('ss601','Surveying & Leveling',2,1,1,3);

INSERT INTO tblSubject VALUES('ss702','Engineering Chemistry',2,0,2,2);

Query 3 :

INSERT INTO tblStudent VALUES('001','Parth','Mohan','Patel','5,Riverview

Heights','Vesu','Surat','Gujarat','India','395007','5671234589','1991-06-
25','Male','101256','CO','2006','parth@gmail.com');

INSERT INTO tblStudent VALUES('002','Mayur','Sahil','Rathod','10,Vraj Society','near Bus

Station','Baroda','Gujarat','India','123456','1245789638','1990-08-
24','Male','101289','CO','2006','mayur@gmail.com');

INSERT INTO tblStudent VALUES('003','Ronak','Ramesh','Panchal','80,Ramnagar

Colony','Bavdhan','Pune','Maharashtra','India','411021','5178965412','1990-11-
04','Male',205168,'CI',2006,'ronak@gmail.com');

INSERT INTO tblStudent VALUES('004','Chirag','Hasmukh','Mor','25,Opera residency','City

light','Ahmedabad','Gujarat','India','380015','4128763892','1991-08-
15','Male','200589','ME','2005','chirag@gmail.com');

INSERT INTO tblStudent VALUES('005','Prachi','Divesh','Dhimmar','58,White

House','Borivali','Mumbai','Maharashtra','India','400091','9845127851','1989-07-
14','Female','105456','EC','2004','prachi@gmail.com');

INSERT INTO tblStudent VALUES('006','Hetvi','Rahul','Kumar','84,Mharaja

society','Kalaghoda','Baroda','Gujarat','India','390005','8974125496','1989-08-
18','Female','104565','EC','2004','hetvi@gmail.com');

INSERT INTO tblStudent VALUES('007','Vipual','Mahesh','Patil','42,Sri Nivas Colony','Mahajan

Nagar','Nasik','Maharashtra','India','422010','7412586312','1988-08-
15','Male','200554','ME','2005','vipul@gmail.com');

INSERT INTO tblStudent VALUES('008','Vikas','Narottam','Ojha','56,Sundarbag','Limda

Chowk','Rajkot','Gujarat','India','364730','8796542132','1989-12-
21','Male','200458','EL','2005','vikas@gmail.com');

INSERT INTO tblStudent VALUES('009','Atula','Anil','Avasthi','23,Subhas

Nagar','Mandvi','Bhavnagar','Gujarat','India','364275','9874125235','1990-06-
05','Female','101054','CO','2006','atula@gmail.com');

INSERT INTO tblStudent VALUES('010','Mayuri','Rajnish','Ravat','39,Shukan

Bunglows','Varachha','Surat','Gujarat','India','365006','4578968972','1989-02-
28','Female','201024','EC','2004','mayuri@gmail.com');
Query 4 :

INSERT INTO tblFaculty VALUES('f101','Chetan','K','Solanki','A-204,Riverview resedency','Mota

Varachha','Surat','Gujarat','India','394101','9876523425','chetan@gmail.com','2004-07-
21','M','BE(Computer)','Lecturer','CO','Proxy Administrator');

INSERT INTO tblFaculty VALUES('f302','Milind','A','Trivedi','53,Avadh

heights','Virod','Surat','Gujarat','India','390022','7512364285','milind@gmail.com','2004-07-
21','M','BE(Electrical)','Lecturer','EL','Embedded Lab In Charge');

INSERT INTO tblFaculty

VALUES('f203','Vijayendra','A','Desai','45,Revera','Adajan','Surat','Gujarat','India','395009','99048770
88','vijayendra@gmail.com','2005-01-25','M','ME(EC)','Lecturer','EC','T & P(EC) In Charge');

INSERT INTO tblFaculty VALUES('f705','Kalpesh','D','Mania','D-404, Opera House','Parsi Agiyari

Chwok','Rajkot','Gujarat','India','360001','8805577557','kalpesh@gmail.com','2002-03-
02','M','ME(Mechanical)','Lecturer','ME','Examination Coordinator');

INSERT INTO tblFaculty VALUES('f801','Mitesh','S','Joshi','5, Anand Park

Society','Adajan','Surat','Gujarat','India','395009','7952304545','mitesh@gmail.com','2000-04-
30','M','MSc(Maths)','Lecturer','AP','Sports committee In Charge');

INSERT INTO tblFaculty VALUES('f811','Deepa','M','Kapoor','41, Shantivan

Socirty','Rander','Surat','Gujarat','India','395005','4523289545','deepa@gmail.com','1999-05-
06','F','PhD, MSc(Physics)','Assistant','AP','Physics Lab In Charge');

INSERT INTO tblFaculty VALUES('f605','Arun','T','Bharoniya','A-602, Palli Hills','Parle

Point','Surat','Gujarat','India','395007','8765542189','arun@gmail.com','2000-10-
11','M','ME(Civil)','Lecturer','CI','Main Lab In Charge');
Query 5 :

INSERT INTO tblCourse VALUES('c001','ss101','f101','7','CO');

INSERT INTO tblCourse VALUES('c002','ss201','f203','4','EC');

INSERT INTO tblCourse VALUES('c003','ss202','f203','4','EC');

INSERT INTO tblCourse VALUES('c004','ss301','f302','5','EL');

INSERT INTO tblCourse VALUES('c005','ss401','f705','2','ME');

INSERT INTO tblCourse VALUES('c006','ss701','f801','4','AP');

INSERT INTO tblCourse VALUES('c007','ss601','f605','1','CI');

INSERT INTO tblCourse VALUES('c008','ss702','f811','1','AP');

Query 6 :

INSERT INTO tblStudentCourse VALUES('Sc101','001','c001','July','2009');

INSERT INTO tblStudentCourse VALUES('Sc202','002','c002','January','2007');

INSERT INTO tblStudentCourse VALUES('Sc203','003','c003','January','2007');

INSERT INTO tblStudentCourse VALUES('Sc304','004','c004','July','2007');

INSERT INTO tblStudentCourse VALUES('Sc705','005','c005','January','2006');

INSERT INTO tblStudentCourse VALUES('Sc606','006','c006','January','2006');

INSERT INTO tblStudentCourse VALUES('Sc817','007','c007','July','2005');

INSERT INTO tblStudentCourse VALUES('Sc807','008','c008','July','2005');

INSERT INTO tblStudentCourse VALUES('Sc710','009','c005','January','2007');

INSERT INTO tblStudentCourse VALUES('Sc205','010','c003','January','2006');

Query 7 :

INSERT INTO tblStudentResult VALUES('r01','Sc101','2','Y','2009');

INSERT INTO tblStudentResult VALUES('r02','Sc202','3','Y','2007');

INSERT INTO tblStudentResult VALUES('r03','Sc203','1','N','2007');

INSERT INTO tblStudentResult VALUES('r04','Sc304','1','Y','2008');

INSERT INTO tblStudentResult VALUES('r05','Sc705','1','N','2006');

INSERT INTO tblStudentResult VALUES('r06','Sc606','2','Y','2006');

INSERT INTO tblStudentResult VALUES('r07','Sc817','1','N','2005');

INSERT INTO tblStudentResult VALUES('r08','Sc807','2','Y','2006');

INSERT INTO tblStudentResult VALUES('r09','Sc710','1','N','2007');

INSERT INTO tblStudentResult VALUES('r10','Sc205','3','Y','2006');

Query 8 :

INSERT INTO tblOneTimeFees VALUES('Of001','001','Library+Security Deposit','2500','2006-06-

15','Y');

INSERT INTO tblOneTimeFees VALUES('Of002','002','Library+Security Deposit','2500','2006-07-

12','Y');

INSERT INTO tblOneTimeFees VALUES('Of003','003','Library+Security Deposit','2500','2005-06-

15','Y');

INSERT INTO tblOneTimeFees VALUES('Of004','004','Library+Security Deposit','2500','2005-06-

15','Y');

INSERT INTO tblOneTimeFees VALUES('Of005','005','Library+Security Deposit','2500','2004-07-

12','Y');

INSERT INTO tblOneTimeFees VALUES('Of006','006','Library+Security Deposit','2500','2004-07-

12','Y');

INSERT INTO tblOneTimeFees VALUES('Of007','007','Library+Security Deposit','2500','2005-06-

15','Y');

INSERT INTO tblOneTimeFees VALUES('Of008','008','Library+Security Deposit','2500','2005-06-

15','Y');

INSERT INTO tblOneTimeFees VALUES('Of009','009','Library+Security Deposit','2500','2006-06-

15','Y');

INSERT INTO tblOneTimeFees VALUES('Of010','010','Library+Security Deposit','2500','2004-07-

12','Y');
Query 9 :

INSERT INTO tblTuitionFees VALUES('tf001','001','7','20000','2009-07-01');

INSERT INTO tblTuitionFees VALUES('tf002','002','4','20000','2007-01-01');

INSERT INTO tblTuitionFees VALUES('tf003','003','4','20000','2007-01-01');

INSERT INTO tblTuitionFees VALUES('tf004','004','5','20000','2007-07-01');

INSERT INTO tblTuitionFees VALUES('tf005','005','2','20000','2006-01-01');

INSERT INTO tblTuitionFees VALUES('tf006','006','4','20000','2006-01-01');

INSERT INTO tblTuitionFees VALUES('tf007','007','1','20000','2005-07-01');

INSERT INTO tblTuitionFees VALUES('tf008','008','1','20000','2005-07-01')

INSERT INTO tblTuitionFees VALUES('tf009','009','2','20000','2007-01-01');

INSERT INTO tblTuitionFees VALUES('tf010','010','4','20000','2006-01-01');

Query : 10

INSERT INTO tblExamFees VALUES('ef001','001','c001','200','2009-07-01');

INSERT INTO tblExamFees VALUES('ef002','002','c002','200','2007-01-01');

INSERT INTO tblExamFees VALUES('ef003','003','c003','200','2007-01-01');

INSERT INTO tblExamFees VALUES('ef004','004','c004','200','2008-07-01');

INSERT INTO tblExamFees VALUES('ef005','005','c005','200','2006-01-01');

INSERT INTO tblExamFees VALUES('ef006','006','c006','200','2006-01-01');

INSERT INTO tblExamFees VALUES('ef007','007','c007','200','2005-07-01');

INSERT INTO tblExamFees VALUES('ef008','008','c008','200','2006-07-01');

INSERT INTO tblExamFees VALUES('ef009','009','c005','200','2007-01-01');

INSERT INTO tblExamFees VALUES('ef010','010','c003','200','2006-01-01');

 Assignment – 3
Aim 1 : Select first names & last names of all the students. Also display the names (First and Last)
comma separated and space separate, along with the appropriate aliases.

Syntax : SELECT fName,lName FROM tblStudent;

SELECT CONCAT(fName , lName) as Name FROM tblStudent;

Output :
Aim 2 : Display the city names of all the students.

Syntax : SELECT City FROM tblStudent;

Output :

Aim 3 : Display the names and birth dates of all the students whose names start with either ‘M’ or
with ‘P’.

Syntax : SELECT fName "Name",birthDate FROM tblStudent WHERE fName LIKE 'M%' OR fName LIKE
'P%';

Output :
Aim 4 : Display the entire student IDs who are from ‘Maharashtra’.

Syntax : SELECT studentID FROM tblStudent WHERE state='Maharashtra';

Output :

Aim 5 : Display the entire student first and last names having age equal to or greater than 20.

Syntax : SELECT fName, lName FROM tblStudent WHERE (year(curdate())-year(birthDate))>=20;

Output :

Aim 6 : Display all the male student first names, last names and year of admission that are from
‘Surat’.

Syntax : SELECT fName, lName, admYear FROM tblStudent WHERE gender='Male' AND city='Surat';

Output :
Aim 7 : Display the student IDs and branch names of all the students.

Syntax : SELECT studentID, branchName FROM tblStudent, tblBranch WHERE tblStudent.branchID =

tblBranch.branchID;

Output :

Aim 8 : Display the first and last names of all the students who belong to D1 building.

Syntax : SELECt fName,lName FROM tblStudent s,tblBranch b WHERE s.branchID=b.branchID AND

b.Building='D1';

Output :

Aim 9 : Display all the student IDs and registration numbers that are registered for more than 4
years.

Syntax : SELECT studentID,regNo FROM tblStudent WHERE (year(curdate())-admYear)>4;

Output :
Aim 10 : Display the entire faculty first and last names that belong to D1 building.

Syntax : SELECT fName , lName FROM tblFaculty f,tblBranch b WHERE f.deptID=b.branchID AND
b.Building='D1';

Output :

Aim 11 : Display all the student IDs and cities in the descending order of their year of admission.

Syntax : SELECT studentID, City FROM tblStudent ORDER BY admYear DESC;

Output :
 Assignment – 4

Aim 1 : Display the total number of students and the department name for every department.

Syntax : SELECT COUNT(studentID), branchName FROM tblStudent s, tblBranch b WHERE

s.branchID=b.branchID GROUP BY s.branchID;

Output :

Aim 2 : Find and display the branch name with the maximum students enrolled to it.

Syntax : SELECT branchName , count(studentID) FROM tblStudent s , tblBranch b WHERE

s.branchID=b.branchID GROUP BY branchName;

Output :

Aim 3 : Display the entire student first and last names for which the faculty is ‘Chetan
Solanki’.

Syntax : SELECT s.fname , s.lname FROM tblStudent s, tblFaculty f WHERE s.branchID=f.deptID and
f.fname='Chetan' and f.lname='Solanki';

Output :
Aim 4 : Display the subject name and faculty name of branch ‘EC’.

Syntax : SELECT sb.subName , fName FROM tblSubject sb , tblCourse cb , tblFaculty f WHERE

cb.facID=f.facID and deptID='EC' and sb.subjectID=cb.subID;

Output :

Aim 5 : Display all the student IDs and faculty IDs who live in the same city.

Syntax : SELECT studentID , facID FROM tblStudent s , tblFaculty f WHERE s.branchID=f.deptId and
s.city =f.city;
Output :

Aim 6 : Display the faculty ID and name for the faculty who has joined most recently.

Syntax : SELECT facID, fName, mName, lName, datediff(curdate(),joinDate) "Days_after_Joining"

FROM tblFaculty GROUP BY facID order by Days_after_Joining limit 1;;

Output :

Aim 7 : Display the Subject Name and Semester No. for all the subjects up to 2nd year.

Syntax : SELECT subName , Semester FROM tblSubject s , tblCourse c WHERE s.subjectId=c.subID

and Semester <=2;

Output :
Aim 8 : Display One Time Fees’ ID, Tuition fees’ ID and Exam fees’ ID for all the students who are in
1st year(1st and 2nd sem).

Syntax : SELECT otf.ID, tf.ID,ef.ID FROM tblOneTimeFees otf ,tblTuitionFees tf, tblExamFee ef WHERE
otf.stuID=tf.stuID and otf.stuID=ef.stuID and tf.Semester <=2;

Output :

Aim 9 : Display total fees for all the ‘Computer’ branch students.

Syntax : SELECT otf.Amount+tf.Amount+ef.Amount as "Total_Fees" FROM tblOneTimeFees otf

,tblTuitionFees tf, tblExamFee ef , tblStudent s WHERE otf.stuID=tf.stuID and
otf.stuID=ef.stuID and s.studentID=tf.stuID and s.branchID="CO";
Output :

Aim 10 : SELECT otf.Amount+tf.Amount+ef.Amount as "Total_Fees" FROM tblOneTimeFees otf

,tblTutionFees tf, tblExamFees ef , tblStudent s WHERE otf.stuID=tf.stuID and
otf.stuID=ef.stuID and s.studentID=tf.stuID and s.branchID="CO";

Syntax : SELECT s.fName , rs.creditObtained FROM tblStudent s , tblStudentResult rs ,

tblStudentCourse sc ,tblCourse c WHERE sc.StuCouID= rs.StuCouID and sc.StuID=
s.studentID and rs.isCleared='y' and c.CourseID=sc.CourseID and c.Semester in(3,4);

Output :

Aim 11 : Count the average years of experience for all the faculties.

Syntax : SELECT Avg(year(curdate())-year(joinDate)) as "Avg_Experience" FROM tblFaculty;

Output :