Sql-7 college schema


CREATE TABLE PERSON (
PID NUMBER(10),
PNAME VARCHAR2(10),
PADDRESS  VARCHAR2(100),
UIDNO NUMBER(12)
);

-- DROP TABLE PERSON;

INSERT INTO PERSON VALUES(1,'BHUVAN','MUMBAI',123456789105);
INSERT INTO PERSON VALUES(2,'ASHISH','GADHINGLAJ',223456789123);
INSERT INTO PERSON VALUES(3,'PRANAV','HUPARI',323456789123);
INSERT INTO PERSON VALUES(4,'PRASHANT','INGLI',423456789123);
INSERT INTO PERSON VALUES(5,'SHREYA','SATARA',523456789123);
INSERT INTO PERSON VALUES(6,'AKSHAY','ICHALKARANJI',623456789123);
INSERT INTO PERSON VALUES(7,'SWAPNIL','PUNDI',723456789123);
INSERT INTO PERSON VALUES(8,'PRATP','SHANTINAGAR',183456789123);
INSERT INTO PERSON VALUES(9,'RAJ','MIRAJ',923456789123);
INSERT INTO PERSON VALUES(10,'VALHAB','SANGLI',103456789123);

INSERT INTO PERSON VALUES(11,'PATIL S V','ICHALKARANJI',103456789129);
INSERT INTO PERSON VALUES(12,'SANE M G','KOLHAPUR',103456789128);
INSERT INTO PERSON VALUES(13,'KADAM L C','MUMBAI',103456789127);
INSERT INTO PERSON VALUES(14,'CHAVAN B H','SANGLI',103456789126);
INSERT INTO PERSON VALUES(15,'SHINDE V S','MIRAJ',103456789125);
INSERT INTO PERSON VALUES(16,'RANADE T S','PUNE',103456789100);
INSERT INTO PERSON VALUES(17,'MALI T S','ICHALKARANJI',103456789990);
INSERT INTO PERSON VALUES(18,'YOGESH','ICHALKARANJI',103456789999);


CREATE TABLE UIDDATA (
UIDNO NUMBER(12),
DOB DATE
);


INSERT INTO UIDDATA VALUES(123456789123,to_date('21-07-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(223456789123,to_date('24-4-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(323456789123,to_date('24-5-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(423456789123,to_date('24-6-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(523456789123,to_date('14-7-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(623456789123,to_date('24-8-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(723456789123,to_date('24-9-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(183456789123,to_date('4-10-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(193456789123,to_date('24-11-2000', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789123,to_date('20-12-2000', 'dd-mm-yyyy'));

INSERT INTO UIDDATA VALUES(103456789129,to_date('2-2-1970', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789128,to_date('24-12-1970', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789127,to_date('24-1-1970', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789126,to_date('25-10-1970', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789125,to_date('26-9-1970', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789100,to_date('26-9-1960', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789990,to_date('26-9-1970', 'dd-mm-yyyy'));
INSERT INTO UIDDATA VALUES(103456789999,to_date('27-10-1998', 'dd-mm-yyyy'));

CREATE TABLE STUDENT (
PRN VARCHAR2(10),
PID NUMBER(10),
CLS VARCHAR2(30),
DEPT VARCHAR2(30)
);

INSERT INTO STUDENT VALUES('19UCS01',1,'TYCSE','CSE');
INSERT INTO STUDENT VALUES('19UCS02',2,'TYCSE','CSE');
INSERT INTO STUDENT VALUES('19UCS03',3,'TYCSE','CSE');
INSERT INTO STUDENT VALUES('19UCS04',4,'TYCSE','CSE');
INSERT INTO STUDENT VALUES('19UCS05',5,'SYTT','TT');
INSERT INTO STUDENT VALUES('19UCS06',6,'SYTT','TT');
INSERT INTO STUDENT VALUES('19UCS07',7,'SYTT','TT');
INSERT INTO STUDENT VALUES('19UCS08',8,'FYTC','TC');
INSERT INTO STUDENT VALUES('19UCS09',9,'FYTC','TC');
INSERT INTO STUDENT VALUES('19UCS10',10,'FYTC','TC');
INSERT INTO STUDENT VALUES('19PHS11',11,'PHD','CSE');
INSERT INTO STUDENT VALUES('19UCS12',18,'PHD','CSE');



CREATE TABLE FACULTY (
FID NUMBER(10),
PID NUMBER(10),
DEPT VARCHAR(30)
);

INSERT INTO FACULTY VALUES(1,11,'TYCSE');
INSERT INTO FACULTY VALUES(2,12,'TYCSE');
INSERT INTO FACULTY VALUES(3,13,'TYCSE');
INSERT INTO FACULTY VALUES(4,14,'TC');
INSERT INTO FACULTY VALUES(5,15,'TT');
INSERT INTO FACULTY VALUES(6,16,'TT');
INSERT INTO FACULTY VALUES(7,17,'TC');

CREATE TABLE STUDENTATTENDANCE (
PRN VARCHAR2(20),
PERATTENDANCE NUMBER(5,2)
);

INSERT INTO STUDENTATTENDANCE VALUES('19UCS01',80);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS02',60);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS03',50);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS04',70);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS05',90);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS06',80);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS07',75);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS08',80);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS09',60);
INSERT INTO STUDENTATTENDANCE VALUES('19UCS10',90);

CREATE TABLE STUDENTMARKS(
PRN VARCHAR2(20),
SUBJECT VARCHAR2(20),
MARKS NUMBER(10)
);

INSERT INTO STUDENTMARKS  VALUES('19UCS01','OS',88);
INSERT INTO STUDENTMARKS  VALUES('19UCS01','DBE',56);
INSERT INTO STUDENTMARKS  VALUES('19UCS02','OS',78);
INSERT INTO STUDENTMARKS  VALUES('19UCS02','DBE',80);
INSERT INTO STUDENTMARKS  VALUES('19UCS03','OS',82);
INSERT INTO STUDENTMARKS  VALUES('19UCS03','DBE',70);
INSERT INTO STUDENTMARKS  VALUES('19UCS04','OS',86);
INSERT INTO STUDENTMARKS  VALUES('19UCS04','DBE',60);
INSERT INTO STUDENTMARKS  VALUES('19UCS05','ORG',79);
INSERT INTO STUDENTMARKS  VALUES('19UCS05','CHM',65);
INSERT INTO STUDENTMARKS  VALUES('19UCS06','ORG',80);
INSERT INTO STUDENTMARKS  VALUES('19UCS06','CHM',83);
INSERT INTO STUDENTMARKS  VALUES('19UCS07','ORG',85);
INSERT INTO STUDENTMARKS  VALUES('19UCS07','CHM',64);
INSERT INTO STUDENTMARKS  VALUES('19UCS08','SMP',70);
INSERT INTO STUDENTMARKS  VALUES('19UCS08','ST',36);
INSERT INTO STUDENTMARKS  VALUES('19UCS09','SMP',38);
INSERT INTO STUDENTMARKS  VALUES('19UCS09','ST',79);
INSERT INTO STUDENTMARKS  VALUES('19UCS10','SMP',70);
INSERT INTO STUDENTMARKS  VALUES('19UCS10','ST',84);

CREATE TABLE STUDENTFEE (
PRN VARCHAR2(20),
FEEPAID NUMBER(10,2),
FEEDUES NUMBER(10,2)
);

INSERT INTO STUDENTFEE VALUES('19UCS01',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS02',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS03',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS04',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS05',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS06',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS07',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS08',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS09',30000,30000);
INSERT INTO STUDENTFEE VALUES('19UCS10',30000,30000);

SELECT * FROM STUDENTFEE;

CREATE TABLE LECTURES (
FID NUMBER(10),
CLS VARCHAR2(30),
SUBJECT VARCHAR2(30)
);

--TRUNCATE TABLE LECTURES;

INSERT INTO LECTURES VALUES(1,'TYCSE','OS');
INSERT INTO LECTURES VALUES(2,'TYCSE','DBE');
INSERT INTO LECTURES VALUES(4,'SYTT','CHM');
INSERT INTO LECTURES VALUES(5,'SYTT','ORG');
INSERT INTO LECTURES VALUES(6,'FYTC','ST');
INSERT INTO LECTURES VALUES(7,'FYTC','ST');
INSERT INTO LECTURES VALUES(3,'TYCSE','DBE');

-- 1] Find the faculty who is pursuing Ph.D. in same college
SELECT P.PNAME
FROM PERSON  P, STUDENT S, FACULTY F
WHERE P.PID = S.PID AND F.PID = P.PID AND  S.PID=F.PID AND S.CLS = 'PHD';

-- 2] Find the faculty teaching subject to 'T.Y.CSE'
SELECT DISTINCT P.PNAME
FROM PERSON  P, FACULTY F, LECTURES L
WHERE P.PID = F.PID  AND F.FID = L.FID AND L.CLS = 'TYCSE';

-- 3] Find address of students whose attendance is less than 70%
SELECT P.PADDRESS FROM PERSON P, STUDENT S, STUDENTATTENDANCE SA
WHERE P.PID = S.PID AND S.PRN = SA.PRN AND SA.PERATTENDANCE <70;

-- 4] Find all the students who are eligible for voting

SELECT P.PNAME
FROM PERSON P, UIDDATA U, STUDENT S
WHERE P.PID=S.PID AND P.UIDNO=U.UIDNO AND FLOOR((SYSDATE-U.DOB)/365.25)>18;

-- 5] Find the total fee paid by T.Y.CSE students
SELECT SUM(SF.FEEPAID) FROM STUDENTFEE SF, STUDENT S
WHERE  S.PRN = SF.PRN SELECT P.PNAME
FROM PERSON P, STUDENT S, LECTURES L
WHERE
P.PID=S.PID AND
S.CLS=L.CLS AND
L.SUBJECT='DBE'S.CLS = 'TYCSE';

--6] Find the address of students who have backlog subjects
SELECT P.PNAME FROM PERSON P ,STUDENT S, STUDENTMARKS SM
WHERE S.PRN = SM.PRN AND S.PID = P.PID AND SM.MARKS <40

-- 7] Find the list of students who are supposed to attend DBE lectures.
SELECT DISTINCT P.PNAME
FROM PERSON P, STUDENT S, LECTURES L
WHERE P.PID=S.PID AND S.CLS=L.CLS AND L.SUBJECT='DBE'

-- 8] Find all the teachers who teaches to student with uid 123456789105
SELECT P.PNAME FROM PERSON P, PERSON SP, FACULTY F, STUDENT S, LECTURES L
WHERE P.PID = F.PID AND F.FID = L.FID AND L.CLS = S.CLS AND S.PID = SP.PID AND SP.UIDNO = 123456789105

-- 9] Find the topper in subject ‘DBE’.
SELECT P.PNAME, SM.MARKS FROM PERSON P , STUDENT S, STUDENTMARKS SM
WHERE P.PID = S.PID AND S.PRN = SM.PRN AND SM.SUBJECT = 'DBE' AND SM.MARKS = (SELECT MAX(MARKS) FROM STUDENTMARKS WHERE SUBJECT = 'DBE');

--10] Find the defaulter list of 'T.Y.CSE'
SELECT P.PNAME FROM PERSON P, STUDENT S , STUDENTATTENDANCE SA
WHERE P.PID = S.PID AND S.PRN = SA.PRN AND S.CLS = 'TYCSE' AND SA.PERATTENDANCE < 70;

-- 11] Find the subjects in which all the students have passed the examination.
SELECT SUBJECT FROM STUDENTMARKS
GROUP BY SUBJECT HAVING MIN(MARKS)>=40

-- 12] Find the teacher who is teaching a subject in which no defaulter is found.
SELECT PNAME
FROM PERSON WHERE PID IN
(SELECT PID FROM FACULTY WHERE FID IN
(SELECT FID FROM
(SELECT FID FROM LECTURES MINUS SELECT
L.FID FROM LECTURES L, STUDENT S,
STUDENTATTENDANCE A
WHERE L.CLS=S.CLS AND
S.PRN=A.PRN AND
A.PERATTENDANCE<70)))

-- 13] Calculate Percentage Marks of all the Students of 'TYCSE'
SELECT M.PRN, AVG(M.MARKS)
FROM STUDENT S, STUDENTMARKS M
WHERE
S.PRN=M.PRN AND
S.CLS='TYCSE'
GROUP BY M.PRN
   
-- 14] Calculate Average Marks of 'TYCSE' Students in 'DBE' subject
SELECT AVG(M.MARKS)
FROM STUDENT S, STUDENTMARKS M
WHERE
S.PRN=M.PRN AND
S.CLS='TYCSE'
GROUP BY M.SUBJECT
HAVING (M.SUBJECT='DBE')

-- 15] Calculate Average Marks of 'TYCSE' Students in all subjects
SELECT M.SUBJECT, AVG(M.MARKS)
FROM STUDENT S, STUDENTMARKS M
WHERE
S.PRN=M.PRN AND
S.CLS='TYCSE'
GROUP BY M.SUBJECT

Post a Comment

0 Comments