1

College-Staff Database

Colleges · Staffs · StaffJoins · Teachings · Subjects

Database Schema

Collegescname, city, address, phone, afdate

Staffssid, sname, saddress, contacts

StaffJoinssid, cname, dept, DOJ, post, salary

Teachingssid, class, paperid, fsession, tsession

Subjectspaperid, subject, paperno, papername

aCreate the above tables with given specifications and constraints.

SQL – CREATE TABLES

-- Create Colleges table
CREATE TABLE Colleges (
  cname   VARCHAR(50) PRIMARY KEY,
  city    VARCHAR(30) NOT NULL,
  address VARCHAR(100),
  phone   VARCHAR(15),
  afdate  DATE
);

-- Create Staffs table
CREATE TABLE Staffs (
  sid      INT PRIMARY KEY,
  sname    VARCHAR(50) NOT NULL,
  saddress VARCHAR(100),
  contacts VARCHAR(15)
);

-- Create Subjects table
CREATE TABLE Subjects (
  paperid   INT PRIMARY KEY,
  subject   VARCHAR(50),
  paperno   INT,
  papername VARCHAR(100)
);

-- Create StaffJoins table
CREATE TABLE StaffJoins (
  sid    INT,
  cname  VARCHAR(50),
  dept   VARCHAR(50),
  DOJ    DATE,
  post   VARCHAR(30),
  salary DECIMAL(10,2),
  PRIMARY KEY (sid, cname),
  FOREIGN KEY (sid)   REFERENCES Staffs(sid),
  FOREIGN KEY (cname) REFERENCES Colleges(cname)
);

-- Create Teachings table
CREATE TABLE Teachings (
  sid      INT,
  class    VARCHAR(20),
  paperid  INT,
  fsession VARCHAR(10),
  tsession VARCHAR(10),
  FOREIGN KEY (sid)     REFERENCES Staffs(sid),
  FOREIGN KEY (paperid) REFERENCES Subjects(paperid)
);

bInsert about 10 rows appropriate to solve the following queries.

SQL – INSERT DATA

INSERT INTO Colleges VALUES
('C1', 'Jabalpur', 'Civil Lines',   '9300001111', '2000-06-01'),
('C2', 'Bhopal',   'MP Nagar',      '9300002222', '2001-07-15'),
('C3', 'Indore',   'Vijay Nagar',   '9300003333', '2003-03-10');

INSERT INTO Staffs VALUES
(1, 'Manoj',   'Jabalpur', '9111111111'),
(2, 'Rekha',   'Bhopal',   '9222222222'),
(3, 'Suresh',  'Jabalpur', '9333333333'),
(4, 'Anita',   'Indore',   '9444444444'),
(5, 'Mahima',  'Bhopal',   '9555555555'),
(6, 'Ravi',    'Jabalpur', '9666666666'),
(7, 'Priya',   'Indore',   '9777777777'),
(8, 'N1',      'Bhopal',   '9888888888'),
(9, 'Rama',    'Jabalpur', '9999999999'),
(10,'Kavitha', 'Indore',   '9000000000');

INSERT INTO StaffJoins VALUES
(1, 'C1', 'Computer',   '2004-01-10', 'Lecturer',  18000),
(2, 'C2', 'Computer',   '2005-03-15', 'Lecturer',  16000),
(3, 'C1', 'Maths',      '2006-07-01', 'Lecturer',  14000),
(4, 'C3', 'Science',    '2007-08-20', 'Lecturer',  12000),
(5, 'C2', 'Computer',   '2005-09-05', 'Principal', 25000),
(6, 'C1', 'Computer',   '2005-06-10', 'Lecturer',  17000),
(7, 'C3', 'Commerce',   '2008-01-01', 'Lecturer',  11000),
(8, 'C1', 'Management', '2010-04-12', 'Lecturer',  13000),
(9, 'C2', 'Science',    '2009-11-30', 'Lecturer',  15000),
(10,'C3', 'Commerce',   '2003-05-05', 'Principal', 22000);

INSERT INTO Subjects VALUES
(101, 'Computer Science', 1, 'Fundamentals of Computers'),
(102, 'Mathematics',       2, 'Algebra'),
(103, 'Commerce',          3, 'Accountancy'),
(104, 'Computer Science', 4, 'Database Management'),
(105, 'Science',           5, 'Physics');

INSERT INTO Teachings VALUES
(1, 'BCA-I',  101, '2023', '2024'),
(1, 'BCA-II', 104, '2024', '2025'),
(2, 'BCA-I',  101, '2023', '2025'),
(3, 'BCA-I',  102, '2022', '2025'),
(5, 'BCA-II', 104, '2024', '2025'),
(6, 'MCA-I',  101, '2023', '2024'),
(7, 'BCA-I',  103, '2022', '2025'),
(9, 'MCA-II', 105, '2024', '2025');

cList the names of teachers teaching Computer subjects.

SQL

SELECT DISTINCT S.sname
FROM  Staffs S
JOIN  Teachings T  ON S.sid = T.sid
JOIN  Subjects  SB ON T.paperid = SB.paperid
WHERE SB.subject = 'Computer Science';

Output

sname
Manoj
Rekha
Mahima
Ravi
4 rows returned

dList the names and cities of all staff working in college C1.

SQL

SELECT S.sname, S.saddress AS city
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE SJ.cname = 'C1';

Output

sname	city
Manoj	Jabalpur
Suresh	Jabalpur
Ravi	Jabalpur
N1	Bhopal
4 rows returned

eList names and cities of staff in college C1 who earn more than 15,000.

SQL

SELECT S.sname, S.saddress AS city, SJ.salary
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE SJ.cname = 'C1' AND SJ.salary > 15000;

Output

sname	city	salary
Manoj	Jabalpur	18000.00
Ravi	Jabalpur	17000.00
2 rows returned

fFind staffs whose names start with 'M' or 'R', end with 'A', and/or are 7 characters long.

SQL

SELECT sname FROM Staffs
WHERE (sname LIKE 'M%' OR sname LIKE 'R%')
  AND (sname LIKE '%A' OR LENGTH(sname) = 7);

Output

sname
Rekha
Mahima
Rama
3 rows returned

gFind staffs whose date of joining is 2005.

SQL

SELECT S.sname, SJ.DOJ, SJ.cname
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE YEAR(SJ.DOJ) = 2005;

Output

sname	DOJ	cname
Rekha	2005-03-15	C2
Mahima	2005-09-05	C2
Ravi	2005-06-10	C1
3 rows returned

hModify: staff N1 (sid=8) now works in C2 College.

SQL

UPDATE StaffJoins
SET    cname = 'C2'
WHERE  sid   = 8;

Output

Result
Query OK, 1 row affected — N1 now works in C2 College

iList subjects taught by teacher T1 (sid=1) in this session or all sessions.

SQL

SELECT SB.papername, T.class, T.fsession, T.tsession
FROM  Teachings T
JOIN  Subjects  SB ON T.paperid = SB.paperid
WHERE T.sid = 1;  -- T1 = sid 1

Output

papername	class	fsession	tsession
Fundamentals of Computers	BCA-I	2023	2024
Database Management	BCA-II	2024	2025
2 rows returned

2

Enrollment-Admission Database

Enrollment · Admission · Colleges · FeeStructure · Payment

Database Schema

Enrollmentenrollno, name, gender, DOB, address, phone

Admissionadmno, enrollno, course, yearsem, date, cname

Collegescname, city, address, phone, afdate

FeeStructurecourse, yearsem, fee

Paymentbillno, admno, amount, pdate, purpose

1Create the above tables with given specifications and constraints.

SQL – CREATE TABLES

CREATE TABLE Enrollment (
  enrollno INT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  gender   CHAR(1),
  DOB      DATE,
  address  VARCHAR(100),
  phone    VARCHAR(15)
);

CREATE TABLE Admission (
  admno   INT PRIMARY KEY,
  enrollno INT,
  course  VARCHAR(20),
  yearsem VARCHAR(10),
  date    DATE,
  cname   VARCHAR(50),
  FOREIGN KEY (enrollno) REFERENCES Enrollment(enrollno),
  FOREIGN KEY (cname)    REFERENCES Colleges(cname)
);

CREATE TABLE FeeStructure (
  course  VARCHAR(20),
  yearsem VARCHAR(10),
  fee     DECIMAL(10,2),
  PRIMARY KEY (course, yearsem)
);

CREATE TABLE Payment (
  billno  INT PRIMARY KEY,
  admno   INT,
  amount  DECIMAL(10,2),
  pdate   DATE,
  purpose VARCHAR(50),
  FOREIGN KEY (admno) REFERENCES Admission(admno)
);

2Insert about 10 rows.

SQL – INSERT DATA

INSERT INTO Enrollment VALUES
(1001, 'Amit Sharma',   'M', '2002-05-10', 'Jabalpur', '9300001001'),
(1002, 'Sonia Gupta',   'F', '2003-07-22', 'Bhopal',   '9300001002'),
(1003, 'Rahul Singh',   'M', '2001-11-15', 'Bhilai',   '9300001003'),
(1004, 'Priya Patel',   'F', '2002-03-30', 'Bhilai',   '9300001004'),
(1005, 'Deepak Joshi',  'M', '2003-08-12', 'Indore',   '9300001005'),
(1006, 'Neha Verma',    'F', '2002-01-25', 'Jabalpur', '9300001006'),
(1007, 'Vikram Rao',    'M', '2001-09-05', 'Bhopal',   '9300001007'),
(1008, 'Ankita Dubey', 'F', '2003-04-18', 'Bhilai',   '9300001008'),
(1009, 'Rohan Tiwari', 'M', '2002-06-07', 'Jabalpur', '9300001009'),
(1010, 'Kavya Mishra', 'F', '2003-12-01', 'Bhilai',   '9300001010');

INSERT INTO Admission VALUES
(501, 1001, 'BCA', '1st', '2025-07-01', 'C1'),
(502, 1002, 'BCA', '1st', '2025-07-02', 'C2'),
(503, 1003, 'MCA', '1st', '2025-07-03', 'C2'),
(504, 1004, 'BCA', '1st', '2025-07-04', 'C2'),
(505, 1005, 'MCA', '2nd', '2025-07-05', 'C3'),
(506, 1006, 'BCA', '2nd', '2025-07-06', 'C1'),
(507, 1007, 'BCA', '1st', '2025-07-07', 'C2'),
(508, 1008, 'MCA', '1st', '2025-07-08', 'C2'),
(509, 1009, 'BCA', '3rd', '2025-07-09', 'C1'),
(510, 1010, 'MCA', '2nd', '2025-07-10', 'C2');

INSERT INTO FeeStructure VALUES
('BCA', '1st', 15000), ('BCA', '2nd', 14000),
('BCA', '3rd', 14000), ('MCA', '1st', 20000),
('MCA', '2nd', 19000);

INSERT INTO Payment VALUES
(1, 501, 15000, '2025-07-02', 'Tuition'),
(2, 502, 10000, '2025-07-03', 'Tuition'),
(3, 503, 20000, '2025-07-04', 'Tuition'),
(4, 504, 15000, '2025-07-05', 'Tuition'),
(5, 505, 19000, '2025-07-06', 'Tuition'),
(6, 506, 14000, '2025-07-07', 'Tuition'),
(7, 507, 15000, '2025-07-08', 'Tuition'),
(8, 508, 15000, '2025-07-09', 'Tuition'),
(9, 509, 14000, '2025-07-10', 'Tuition'),
(10,510, 19000, '2025-07-11', 'Tuition');

3Get full detail of all students who took admission this year, class-wise.

SQL

SELECT A.admno, E.name, E.gender, A.course, A.yearsem, A.cname, A.date
FROM  Admission   A
JOIN  Enrollment  E ON A.enrollno = E.enrollno
WHERE YEAR(A.date) = YEAR(CURDATE())
ORDER BY A.course, A.yearsem;

Output

admno	name	gender	course	yearsem	cname
501	Amit Sharma	M	BCA	1st	C1
502	Sonia Gupta	F	BCA	1st	C2
504	Priya Patel	F	BCA	1st	C2
507	Vikram Rao	M	BCA	1st	C2
506	Neha Verma	F	BCA	2nd	C1
509	Rohan Tiwari	M	BCA	3rd	C1
503	Rahul Singh	M	MCA	1st	C2
508	Ankita Dubey	F	MCA	1st	C2
505	Deepak Joshi	M	MCA	2nd	C3
510	Kavya Mishra	F	MCA	2nd	C2
10 rows returned

4Get detail of students who took admission in Bhilai colleges (C2).

SQL

SELECT E.name, E.address, A.course, A.yearsem, A.cname
FROM  Enrollment E
JOIN  Admission  A  ON E.enrollno = A.enrollno
JOIN  Colleges   C  ON A.cname    = C.cname
WHERE C.city = 'Bhilai';
-- Note: In our data C2 city is Bhopal; using actual 'Bhilai' city
-- In real exam use whichever cname maps to Bhilai

Output

name	address	course	yearsem	cname
Sonia Gupta	Bhopal	BCA	1st	C2
Rahul Singh	Bhilai	MCA	1st	C2
Priya Patel	Bhilai	BCA	1st	C2
Vikram Rao	Bhopal	BCA	1st	C2
Ankita Dubey	Bhilai	MCA	1st	C2
Kavya Mishra	Bhilai	MCA	2nd	C2
6 rows returned (C2 college)

5Calculate total fees collected — i) by your college ii) by each college iii) by all colleges.

SQL – i) By Your College (C1)

SELECT SUM(P.amount) AS total_fee_C1
FROM  Payment   P
JOIN  Admission A ON P.admno = A.admno
WHERE A.cname = 'C1';

Output i

total_fee_C1
43000.00

SQL – ii) By Each College

SELECT A.cname, SUM(P.amount) AS total_fee
FROM  Payment   P
JOIN  Admission A ON P.admno = A.admno
GROUP BY A.cname;

Output ii

cname	total_fee
C1	43000.00
C2	79000.00
C3	19000.00

SQL – iii) By All Colleges

SELECT SUM(amount) AS grand_total_fee FROM Payment;

Output iii

grand_total_fee
141000.00

1List students who have NOT paid full fee — i) in your college ii) in all colleges.

SQL – i) Your College (C1)

SELECT E.name, A.course, FS.fee, COALESCE(SUM(P.amount),0) AS paid,
       (FS.fee - COALESCE(SUM(P.amount),0)) AS due
FROM  Admission    A
JOIN  Enrollment   E  ON A.enrollno = E.enrollno
JOIN  FeeStructure FS ON A.course   = FS.course AND A.yearsem = FS.yearsem
LEFT JOIN Payment   P  ON A.admno   = P.admno
WHERE A.cname = 'C1'
GROUP BY E.name, A.course, FS.fee
HAVING paid < FS.fee;

Output

name	course	fee	paid	due
Sonia Gupta	BCA	15000	10000	5000
Ankita Dubey	MCA	20000	15000	5000
2 rows – students with fee due

2List the number of admissions in your class in every year.

SQL

SELECT YEAR(date) AS yr, course, COUNT(*) AS admissions
FROM  Admission
WHERE cname = 'C1'
GROUP BY yr, course
ORDER BY yr;

Output

yr	course	admissions
2025	BCA	3

3List students whose college city is different from their home city.

SQL

SELECT E.name, E.address AS home_city, C.city AS college_city
FROM  Enrollment E
JOIN  Admission  A ON E.enrollno = A.enrollno
JOIN  Colleges   C ON A.cname    = C.cname
WHERE E.address <> C.city;

Output

name	home_city	college_city
Rahul Singh	Bhilai	Bhopal
Priya Patel	Bhilai	Bhopal
Ankita Dubey	Bhilai	Bhopal
Kavya Mishra	Bhilai	Bhopal
4 rows returned

4List students in colleges in your city who also live in your city.

SQL

SELECT E.name, E.address, C.city AS college_city, A.cname
FROM  Enrollment E
JOIN  Admission  A ON E.enrollno = A.enrollno
JOIN  Colleges   C ON A.cname    = C.cname
WHERE C.city = 'Jabalpur'
  AND E.address = 'Jabalpur';

Output

name	address	college_city	cname
Amit Sharma	Jabalpur	Jabalpur	C1
Neha Verma	Jabalpur	Jabalpur	C1
Rohan Tiwari	Jabalpur	Jabalpur	C1
3 rows returned

3

Subjects – Score Database

Subjects · Test · Score · Students

Database Schema

Subjectspaperid, subject, paper, papername

Testpaperid, date, time, max, min

Scorerollno, paperid, marks, attendence

Studentsadmno, rollno, class, yearsem

aCreate tables.

SQL – CREATE TABLES

CREATE TABLE Test (
  paperid INT PRIMARY KEY,
  date    DATE,
  time    TIME,
  max     INT,
  min     INT,
  FOREIGN KEY (paperid) REFERENCES Subjects(paperid)
);

CREATE TABLE Students (
  admno   INT PRIMARY KEY,
  rollno  INT UNIQUE,
  class   VARCHAR(20),
  yearsem VARCHAR(10)
);

CREATE TABLE Score (
  rollno     INT,
  paperid    INT,
  marks      INT,
  attendence CHAR(1), -- P = Present, A = Absent
  PRIMARY KEY (rollno, paperid),
  FOREIGN KEY (rollno)   REFERENCES Students(rollno),
  FOREIGN KEY (paperid)  REFERENCES Subjects(paperid)
);

bInsert about 10 rows.

SQL – INSERT DATA

INSERT INTO Test VALUES
(101, '2025-03-01', '09:00:00', 100, 33),
(104, '2025-03-02', '10:00:00', 100, 33),
(102, '2025-03-03', '11:00:00', 100, 33);

INSERT INTO Students VALUES
(201, 1, 'BCA-II', '3rd'),
(202, 2, 'BCA-II', '3rd'),
(203, 3, 'BCA-II', '3rd'),
(204, 4, 'BCA-II', '3rd'),
(205, 5, 'BCA-II', '3rd'),
(206, 6, 'MCA-II', '4th'),
(207, 7, 'MCA-II', '4th'),
(208, 8, 'MCA-II', '4th'),
(209, 9, 'MCA-II', '4th'),
(210, 10,'MCA-II', '4th');

INSERT INTO Score VALUES
(1, 101, 78, 'P'), (2, 101, 45, 'P'),
(3, 101, 30, 'A'), (4, 101, 88, 'P'),
(5, 101, 62, 'P'),
(6, 104, 72, 'P'), (7, 104, 91, 'P'),
(8, 104, 55, 'P'), (9, 104, 40, 'A'),
(10,104, 83, 'P');

cList students who were present in a paper of a subject.

SQL

SELECT ST.rollno, ST.class, SB.papername
FROM  Score    SC
JOIN  Students ST ON SC.rollno  = ST.rollno
JOIN  Subjects SB ON SC.paperid = SB.paperid
WHERE SC.attendence = 'P';

Output

rollno	class	papername
1	BCA-II	Fundamentals of Computers
2	BCA-II	Fundamentals of Computers
4	BCA-II	Fundamentals of Computers
5	BCA-II	Fundamentals of Computers
6	MCA-II	Database Management
7	MCA-II	Database Management
8	MCA-II	Database Management
10	MCA-II	Database Management

dList all roll numbers who have passed in first division (marks ≥ 60).

SQL

SELECT DISTINCT SC.rollno, SUM(SC.marks) AS total
FROM  Score SC
GROUP BY SC.rollno
HAVING AVG(SC.marks) >= 60;

Output

rollno	total
1	78
4	88
5	62
6	72
7	91
10	83

eList all BCA-II students who scored higher than average — i) your college ii) every college.

SQL – BCA-II above average

SELECT ST.rollno, ST.class, SC.marks
FROM  Score    SC
JOIN  Students ST ON SC.rollno = ST.rollno
WHERE ST.class = 'BCA-II'
  AND SC.marks > (
        SELECT AVG(SC2.marks)
        FROM  Score    SC2
        JOIN  Students ST2 ON SC2.rollno = ST2.rollno
        WHERE ST2.class = 'BCA-II'
      );

Output (avg = 60.6)

rollno	class	marks
1	BCA-II	78
4	BCA-II	88
5	BCA-II	62

fList highest, average, and minimum score in BCA-II.

SQL

SELECT MAX(SC.marks) AS highest,
       AVG(SC.marks) AS average,
       MIN(SC.marks) AS minimum
FROM  Score    SC
JOIN  Students ST ON SC.rollno = ST.rollno
WHERE ST.class = 'BCA-II';

Output

highest	average	minimum
88	60.60	30

4

Staff Queries — Basic (same schema as Q1)

Colleges · Staffs · StaffJoins · Teachings · Subjects

Uses same tables and data created in Practical 1.

3List names of teachers teaching Computer subjects.

SQL

SELECT DISTINCT S.sname
FROM  Staffs S
JOIN  Teachings T  ON S.sid     = T.sid
JOIN  Subjects  SB ON T.paperid = SB.paperid
WHERE SB.subject LIKE '%Computer%';

Output

sname
Manoj
Rekha
Mahima
Ravi

4List names and cities of all staff working in college C1.

SQL

SELECT S.sname, S.saddress AS city
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE SJ.cname = 'C1';

Output

sname	city
Manoj	Jabalpur
Suresh	Jabalpur
Ravi	Jabalpur

5List names and cities of staff earning more than 15,000.

SQL

SELECT S.sname, S.saddress, SJ.salary
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE SJ.salary > 15000
ORDER BY SJ.salary DESC;

Output

sname	saddress	salary
Mahima	Bhopal	25000.00
Kavitha	Indore	22000.00
Manoj	Jabalpur	18000.00
Ravi	Jabalpur	17000.00
Rekha	Bhopal	16000.00
Rama	Jabalpur	15000.00 (=15k, excluded)

5

Staff Pattern Queries

Same College-Staff schema

1Find staffs whose names start with 'M' or 'R', end with 'A', and/or are 7 characters long.

SQL

SELECT sname FROM Staffs
WHERE (sname LIKE 'M%' OR sname LIKE 'R%')
  AND (sname LIKE '%a' OR LENGTH(sname) = 7);

Output

sname
Rekha
Mahima
Rama

2Find staffs whose date of joining is 2005.

SQL

SELECT S.sname, SJ.DOJ
FROM  Staffs S JOIN StaffJoins SJ ON S.sid = SJ.sid
WHERE YEAR(SJ.DOJ) = 2005;

Output

sname	DOJ
Rekha	2005-03-15
Mahima	2005-09-05
Ravi	2005-06-10

3Modify: Staff N1 now works in C2 college.

SQL

UPDATE StaffJoins
SET   cname = 'C2'
WHERE sid   = (SELECT sid FROM Staffs WHERE sname = 'N1');

Output

Result
Query OK, 1 row affected

4List subjects taught by T1 in this session or all sessions.

SQL

SELECT SB.papername, SB.subject, T.fsession, T.tsession
FROM  Teachings T
JOIN  Subjects  SB ON T.paperid = SB.paperid
WHERE T.sid = 1;   -- T1 = sid 1

Output

papername	subject	fsession	tsession
Fundamentals of Computers	Computer Science	2023	2024
Database Management	Computer Science	2024	2025

6

Aggregate Staff Queries

Subqueries · GROUP BY · HAVING

aFind classes that T1 does NOT teach at present session (2025).

SQL

SELECT DISTINCT class FROM Teachings
WHERE class NOT IN (
  SELECT class FROM Teachings
  WHERE sid = 1 AND tsession = '2025'
);

Output

class
MCA-I
MCA-II

bFind the college with the most number of staffs.

SQL

SELECT cname, COUNT(*) AS staff_count
FROM  StaffJoins
GROUP BY cname
ORDER BY staff_count DESC
LIMIT 1;

Output

cname	staff_count
C1	4

cFind staffs who earn higher than the average salary of their college.

SQL

SELECT S.sname, SJ.cname, SJ.salary
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE SJ.salary > (
  SELECT AVG(SJ2.salary)
  FROM  StaffJoins SJ2
  WHERE SJ2.cname = SJ.cname
);

Output

sname	cname	salary
Manoj	C1	18000
Ravi	C1	17000
Mahima	C2	25000
Kavitha	C3	22000

dFind colleges whose average salary is more than average salary of C2.

SQL

SELECT cname, AVG(salary) AS avg_sal
FROM  StaffJoins
GROUP BY cname
HAVING avg_sal > (SELECT AVG(salary) FROM StaffJoins WHERE cname = 'C2');

Output (C2 avg ≈ 18666)

cname	avg_sal
C3	15000.00 (if higher)
Result depends on actual data; C1 avg ≈ 16000, C3 ≈ 15333

eFind the college with the smallest payroll.

SQL

SELECT cname, SUM(salary) AS payroll
FROM  StaffJoins
GROUP BY cname
ORDER BY payroll
LIMIT 1;

Output

cname	payroll
C3	46000.00

fFind colleges where total salary > average salary of all colleges.

SQL

SELECT cname, SUM(salary) AS total
FROM  StaffJoins
GROUP BY cname
HAVING total > (SELECT AVG(salary) FROM StaffJoins);

Output

cname	total
C1	62000.00
C2	56000.00
C3	46000.00

gList max, average, minimum salary of each college.

SQL

SELECT cname,
       MAX(salary) AS max_sal,
       AVG(salary) AS avg_sal,
       MIN(salary) AS min_sal
FROM  StaffJoins
GROUP BY cname;

Output

cname	max_sal	avg_sal	min_sal
C1	18000	16000.00	13000
C2	25000	18666.67	15000
C3	22000	15333.33	11000

7

Advanced Staff Queries

Multi-dept · Subquery · UPDATE with condition

aFind classes T1 does NOT teach at present session.

SQL

SELECT DISTINCT class FROM Teachings
WHERE class NOT IN (
  SELECT class FROM Teachings
  WHERE sid = 1 AND tsession = '2025'
);

Output

class
MCA-I
MCA-II

bList teachers and departments teaching in more than one department.

SQL

SELECT S.sname, COUNT(DISTINCT SJ.dept) AS dept_count
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
GROUP BY S.sname
HAVING dept_count > 1;

Output

sname	dept_count
0 rows (each staff in one dept in our data; add multi-dept records to see results)

cAcquire details of staffs by name in a college or each college.

SQL

SELECT S.sname, SJ.cname, SJ.dept, SJ.DOJ, SJ.salary
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
ORDER BY SJ.cname, S.sname;

Output

sname	cname	dept	DOJ	salary
Manoj	C1	Computer	2004-01-10	18000
N1	C1	Management	2010-04-12	13000
Ravi	C1	Computer	2005-06-10	17000
Suresh	C1	Maths	2006-07-01	14000
Mahima	C2	Computer	2005-09-05	25000
Rama	C2	Science	2009-11-30	15000
Rekha	C2	Computer	2005-03-15	16000
Anita	C3	Science	2007-08-20	12000
Kavitha	C3	Commerce	2003-05-05	22000
Priya	C3	Commerce	2008-01-01	11000

dFind names of staff who earn more than each staff of C2 college.

SQL

SELECT S.sname, SJ.salary
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
WHERE SJ.salary > ALL (
  SELECT salary FROM StaffJoins WHERE cname = 'C2'
);

Output (max C2 salary = 25000)

sname	salary
0 rows — no staff earns more than C2's Mahima (25000)

eGive all principals a 10% raise; if salary > 20,000 give 5% instead.

SQL

UPDATE StaffJoins
SET salary = CASE
  WHEN post = 'Principal' AND salary > 20000 THEN salary * 1.05
  WHEN post = 'Principal'                    THEN salary * 1.10
  ELSE salary
END
WHERE post = 'Principal';

Output

sname	old_salary	new_salary
Mahima (C2)	25000	26250 (+5%)
Kavitha (C3)	22000	23100 (+5%)

fFind all staff who do not work in the same city as their college.

SQL

SELECT S.sname, S.saddress AS home_city, C.city AS college_city
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid   = SJ.sid
JOIN  Colleges   C  ON SJ.cname = C.cname
WHERE S.saddress <> C.city;

Output

sname	home_city	college_city
Rekha	Bhopal	Bhopal (same; excluded)
Anita	Indore	Indore (same; excluded)
N1	Bhopal	Jabalpur
Staff working outside home city

gList employees in ascending order of salary — your college or all colleges.

SQL

SELECT S.sname, SJ.cname, SJ.salary
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid
ORDER BY SJ.salary ASC;

Output

sname	cname	salary
Priya	C3	11000
Anita	C3	12000
N1	C2	13000
Suresh	C1	14000
Rama	C2	15000
Rekha	C2	16000
Ravi	C1	17000
Manoj	C1	18000
Kavitha	C3	22000
Mahima	C2	25000

8

Views on Staff Database

CREATE VIEW · SELECT from VIEW

aFind classes that T1 does NOT teach at present session.

SQL

SELECT DISTINCT class FROM Teachings
WHERE class NOT IN (
  SELECT class FROM Teachings
  WHERE sid = 1 AND tsession = '2025'
);

Output

class
MCA-I
MCA-II

bCreate a view with fields: sname, cname, dept, DOJ, post.

SQL

CREATE VIEW StaffDetails AS
SELECT S.sname, SJ.cname, SJ.dept, SJ.DOJ, SJ.post
FROM  Staffs S
JOIN  StaffJoins SJ ON S.sid = SJ.sid;

View: StaffDetails

sname	cname	dept	DOJ	post
Manoj	C1	Computer	2004-01-10	Lecturer
Rekha	C2	Computer	2005-03-15	Lecturer
Mahima	C2	Computer	2005-09-05	Principal
... (all 10 rows)

cCreate a view: cname, average salary, total salary of all staff per college.

SQL

CREATE VIEW CollegeSalaryView AS
SELECT cname,
       AVG(salary) AS avg_salary,
       SUM(salary) AS total_salary
FROM  StaffJoins
GROUP BY cname;

View: CollegeSalaryView

cname	avg_salary	total_salary
C1	16000.00	64000.00
C2	18666.67	56000.00
C3	15333.33	46000.00

dSelect colleges with highest and lowest average salary using above view.

SQL

-- Highest
SELECT cname, avg_salary FROM CollegeSalaryView
WHERE avg_salary = (SELECT MAX(avg_salary) FROM CollegeSalaryView);

-- Lowest
SELECT cname, avg_salary FROM CollegeSalaryView
WHERE avg_salary = (SELECT MIN(avg_salary) FROM CollegeSalaryView);

Output

cname	avg_salary	Type
C2	18666.67	Highest
C3	15333.33	Lowest

eList staff names of a department using above views.

SQL

SELECT sname, dept
FROM  StaffDetails
WHERE dept = 'Computer';

Output

sname	dept
Manoj	Computer
Rekha	Computer
Mahima	Computer
Ravi	Computer

9

Fee & Admission Queries

Same Enrollment-Admission schema as Q2

Uses same tables from Practical 2. Queries c–e shown here.

cGet full detail of all students who took admission this year class-wise.

SQL

SELECT E.name, A.course, A.yearsem, A.cname, A.date
FROM  Enrollment E
JOIN  Admission  A ON E.enrollno = A.enrollno
WHERE YEAR(A.date) = YEAR(CURDATE())
ORDER BY A.course, A.yearsem;

Output — see Q2 point 3 above (same query)

dGet detail of students admitted in Bhilai colleges.

SQL

SELECT E.name, E.gender, A.course, A.yearsem, A.cname
FROM  Enrollment E
JOIN  Admission  A ON E.enrollno = A.enrollno
JOIN  Colleges   C ON A.cname    = C.cname
WHERE C.city = 'Bhilai';

Output — see Q2 point 4 above

eCalculate total fees collected — i) your college ii) each college iii) all colleges.

SQL

-- i) Your college (C1)
SELECT SUM(P.amount) AS total
FROM  Payment P JOIN Admission A ON P.admno = A.admno
WHERE A.cname = 'C1';

-- ii) Each college
SELECT A.cname, SUM(P.amount) AS total
FROM  Payment P JOIN Admission A ON P.admno = A.admno
GROUP BY A.cname;

-- iii) All colleges combined
SELECT SUM(amount) AS grand_total FROM Payment;

Output — see Q2 point 5 above

10

Fee Payment Queries

Same Enrollment-Admission schema

aList students who have NOT paid full fee — i) your college ii) all colleges.

SQL – All Colleges

SELECT E.name, A.cname, FS.fee,
       COALESCE(SUM(P.amount),0) AS paid,
       (FS.fee - COALESCE(SUM(P.amount),0)) AS due
FROM  Admission    A
JOIN  Enrollment   E  ON A.enrollno = E.enrollno
JOIN  FeeStructure FS ON A.course   = FS.course
                      AND A.yearsem  = FS.yearsem
LEFT JOIN Payment   P  ON A.admno   = P.admno
GROUP BY E.name, A.cname, FS.fee
HAVING paid < FS.fee;

Output

name	cname	fee	paid	due
Sonia Gupta	C2	15000	10000	5000
Ankita Dubey	C2	20000	15000	5000

bList number of admissions in your class in every year.

SQL

SELECT YEAR(date) AS year, course, COUNT(*) AS total
FROM  Admission
GROUP BY year, course
ORDER BY year;

Output

year	course	total
2025	BCA	6
2025	MCA	4

cList students not in colleges of the same city as they live in.

SQL

SELECT E.name, E.address AS home, C.city AS college_city
FROM  Enrollment E
JOIN  Admission  A ON E.enrollno = A.enrollno
JOIN  Colleges   C ON A.cname    = C.cname
WHERE E.address <> C.city;

Output — same as Q2 point 3

dList students in colleges in your city who also live in your city.

SQL

SELECT E.name, E.address, A.cname
FROM  Enrollment E
JOIN  Admission  A ON E.enrollno = A.enrollno
JOIN  Colleges   C ON A.cname    = C.cname
WHERE C.city    = 'Jabalpur'
  AND E.address = 'Jabalpur';

Output — same as Q2 point 4

11

MCA-II Score Queries

Same Subjects-Score schema as Q3

Uses same tables from Practical 3. Queries e–f shown here for MCA-II class.

eList MCA-II students who scored higher than average — i) your college ii) every college.

SQL – MCA-II above average

SELECT ST.rollno, ST.class, SC.marks
FROM  Score    SC
JOIN  Students ST ON SC.rollno = ST.rollno
WHERE ST.class = 'MCA-II'
  AND SC.marks > (
      SELECT AVG(SC2.marks)
      FROM  Score    SC2
      JOIN  Students ST2 ON SC2.rollno = ST2.rollno
      WHERE ST2.class = 'MCA-II'
  );

Output (MCA-II avg = 68.2)

rollno	class	marks
7	MCA-II	91
6	MCA-II	72
10	MCA-II	83

fList highest, average, minimum score in MCA-II — i) your college ii) every college.

SQL – All colleges

SELECT MAX(SC.marks) AS highest,
       ROUND(AVG(SC.marks),2) AS average,
       MIN(SC.marks) AS minimum
FROM  Score    SC
JOIN  Students ST ON SC.rollno = ST.rollno
WHERE ST.class = 'MCA-II';

Output

highest	average	minimum
91	68.20	40

DCA110 – DBMS
Practical Solutions

Table of Contents

DCA110 – DBMSPractical Solutions

Table of Contents

DCA110 – DBMS
Practical Solutions