Institute Database

Tables Structure

COURSES Table

BATCHES table

STUDENTS Table

PAYMENTS Table

FOLLOWING QUERIES ARE BASED ON TABLES IN SCOTT ACCOUNT

SELECT * FROM EMP
WHERE DEPTNO = 20 AND MGR = 7488;

SELECT EMPNO,ENAME,SAL,SAL*0.3 HRA,SAL*0.2 DA,JOB FROM EMP
WHERE JOB = 'CLERK';

SELECT * FROM EMP WHERE SAL < 2000 AND DEPTNO IN (20,30);

SELECT * FROM EMP WHERE ENAME LIKE '%M%' AND ENAME LIKE '%E%';

SELECT * FROM EMP WHERE COMM IS NULL AND SAL>3000; 

SELECT * FROM EMP ORDER BY SAL ,HIREDATE DESC; 
DISPLAY DETAILS OF EMPLOYES WHO JOINED IN 1981 AND DRAWING LESS THAN 3000 
AND WORKING FOR DEPT 20.
SELECT * FROM EMP WHERE  HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1981'
AND SAL < 3000  AND DEPTNO =20;

DISPLAY ENAME,SAL,HIREDATE AND NET SALERY,NET SALERY INCLUDES SAL+HRA(30%)-PF(5%).
SELECT ENAME,SAL,HIREDATE,SAL+(SAL*0.3)-(SAL*0.05) NETSALERY  FROM EMP. 

FOLLOWING QUERIES ARE BASED ON INSTITUTE DATABASE 

DISPLAY THE LAST NAME OF STUDENTS 
SELECT SUBSTR(SNAME,INSTR(SNAME,''))  FROM STUDENTS;

DISPLAY DETAILS OF BATCHES THAT STARTED IN YEAR 2002.

SELECT * FROM BATCHES WHERE STDDATE BETWEEN '01-JAN-2002' AND '31-DEC-2002';
DISPLAY DETAILS OF STUDENTS WHERE NAME CONTAINS LETTER 'B' AFTER  5th POSITION.
SELECT * FROM STUDENTS  WHERE INSTR(SNAME,'B',5) > 5; 
DISPLAY THE DETAILS OF PAYMENTS  THAT ARE MADE ON MONDAY OF THE CURRENT WEEK. 
SELECT * FROM PAYMENTS WHERE  TRUNC(NEXT_DAY(SYSDATE-7,'MON')) = TRUNC(DP); 
DISPLAY DETAILS OF COURSES THAT WERE INTRODUCED  MORE THAN ONE YEAR BACK. 
SELECT * FROM COURSES WHERE MONTHS_BETWEEN(SYSDATE,DI) > 12; 
DISPLAY DETAILS OF STUDENTS WHERE THE EMAIL IS IN YAHOO.COM AND STUDENT
NAME  CONTAINS LESS THAN 15 CHARECTERS.
SELECT *  FROM STUDENTS 
WHERE EMAIL LIKE '%YAHOO.COM' AND LENGTH(SNAME) < 15; 
DISPLAY THE BCODE AND NUMBER OF STUDENTS IN THE BATCH 
SELECT  BCODE,COUNT(*) 
FROM STUDENTS
GROUP BY BCODE;

DISPLAY BCODE,HIGHEST ROLLNO 
SELECT BCODE,MAX(ROLLNO) 
FROM STUDENTS
GROUP BY BCODE; 

DISPLAY BCODE,NUMBER OF STUDENTS TAKEN LAB FACILITY 
SELECT BCODE,COUNT(ROLLNO) 
FROM STUDENTS
WHERE LAB = 'Y' 
GROUP BY BCODE;


DISPLAY MONTH AND TOTAL PAYMENTS OF THE MONTH  FOR EACH MONTH OF CURRENT YEAR.
SELECT TO_CHAR(DP,'MM'),SUM(AMOUNT)
FROM PAYMENTS
WHERE TO_CHAR(DP,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY TO_CHAR(DP,'MM');

DISPLAY THE BCODE OF BATCHES EITHER WITH MORE THAN 20 STUDENTS OR
MORE THAN 30,000 TOTAL PAYMENTS.
SELECT BCODE FROM PAYMENTS 
GROUP BY BCODE
HAVING SUM(AMOUNT) > 30000  OR  COUNT (DISTINCT ROLLNO) >20;

DISPLAY MONTH AND NO. OF BATCHES STARTED IN LAST SIX MONTHS 

SELECT TO_CHAR(STDATE,'MON'), COUNT(*)
FROM BATCHES
WHERE  MONTHS_BETWEEN(SYSDATE,STDATE)<=6
GROUP BY  TO_CHAR(STDATE,'MON');


DISPLAY THE BATCHES IN WHICH WE HAVE MORE THAN 10 STUDENTS TAKING THE  LAB FACILITY. 

SELECT BCODE FROM STUDENTS 
WHERE LAB='Y'
GROUP BY BCODE
HAVING COUNT(*)>10;

DISPLAY THE COURSE FOR WHICH WE STARTED MORE THAN TWO BATCHES IN THE CURRENT  YEAR.
SELECT CCODE
FROM  BATCHES
WHERE TO_CHAR(STDATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY CCODE
HAVING COUNT(*) > 2;


DISPLAY THE COURSE FOR WHICH WE HAVE STARTED MORE THAN 1 BATCH IN THE SAME MONTH.

SELECT CCODE FROM BATCHES 
GROUP BY CCODE,TO_CHAR(STDATE,'MMYY')
HAVING COUNT(*) > 1;


DISPLAY THE HIGHEST THEORY COURSE 
SELECT MAX(TFEE) FROM COURSES;

DISPLAY SNAME,BCODE,DJ,REGISTRATION AMOUNT
SELECT  SNAME,S.BCODE, DJ,AMOUNT
FROM STUDENTS S, PAYMENTS P
WHERE S.BCODE = P.BCODE AND S.ROLLNO = P.ROLLNO 
AND  PTYPE= 'R';


DISPLAY SNAME,BCODE,AMOUNT FOR STUDENT WHO JOINED IN THE CURRENT MONTH. 
SELECT  SNAME,BCODE, AMOUNT 
FROM STUDENTS S, PAYMENTS P
WHER  S.BCODE = P.BCODE AND S.ROLLNO = P.ROLLNO 
AND  TO_CHAR(DJ,'MMYY') = TO_CHAR( SYSDATE,'MMYY');

DISPLAY CCODE,CNAME,BCODE,NO. OF MONTHS BETWEEN DATE OF STARTING AND  DATE OF INTRODUCTION.
SELECT  CCODE, CNAME, BCODE, MONTHS_BETWEEN(STDATE,DI)
FROM BATCHES B, COURSES C
WHERE B.CCODE = C.CCODE;

DISPLAY NO. OF STUDENTS WHO TOOK LAB FACILITY AND NO. OF STUDENTS WHO DIDN'T TAKE LAB FACILITY. 
SELECT SUM( DECODE(LAB,'Y',1,0) ) LAB, SUM( DECODE(LABL,'N',1,0)) NOLAB
FROM STUDENTS;



DISPLAY RCPTNO,SNAME,BCODE,ROLLNO,AMOUNT FOR PAYMENTS OF TODAY.
SELECT  RCPTNO,P.BCODE,P.ROLLNO,SNAME,AMOUNT
FROM STUDENTS S, PAYMENTS P
WHERE  TRUNC(DP) = TRUNC(SYSDATE)
AND  S.BCODE = P.BCODE AND S.ROLLNO = P.ROLLNO;


DISPLAY CCODE,CNAME,BCODE,TIME AND TOTAL FEE WHERE THE DURATION OF THE 
BATCH IS MORE THAN THE DURATION OF THE COURSE. 
SELECT  C.CCODE,CNAME,BCODE,TIMING, B.TFEE + B.LFEE TOTALFEE
FROM COURSES C, BATCHES B
WHERE  C.CCODE = B.CCODE AND  B.DURATION > C.DURATION;


DISPLAY BCODE,CNAME,SNAME FOR ON GOING BATCHES.
SELECT  B.BCODE,CNAME,SNAME 
FROM COURSES C, BATCHES B, STUDENTS S
WHERE  C.CCODE = B.BCODE AND S.BCODE = B.BCODE
AND  STDATE <= SYSDATE AND ENDDATE IS NULL;


DISPLAY CNAME AND NO. OF BATCHES CURRENTLY RUNNING FOR THE COURSE.
SELECT  CNAME,COUNT(*)
FROM  COURSES C, BATCHES B
WHERE  C.CCODE = B.CCODE AND  STDATE <= SYSDATE AND ENDDATE IS NULL
GROUP BY CNAME;


DISPLAY THE DETAILS OF COURSES WHERE THE DURATION OF THE COURSE IS LESS THAN  DURATION
OF ORACLE AND MORE THAN DURATION OF DS. 
SELECT  C1.*
FROM COURSES C1, COURSES C2, COURSES C3
WHERE  C2.CCODE = 'ORA' AND C2.CCODE = 'DS'
   AND  C1.DURATION < C2.DURATION AND C1.DURATION > C3.DURATION;


DISPALY SNAME,NO. OF PAYMENTS MADE BY STUDENT. 
SELECT  SNAME, COUNT(*)
FROM PAYMENTS P, STUDENTS S
WHERE  S.BCODE = P.BCODE AND S.ROLLNO = P.ROLLNO
GROUP BY SNAME;


DISPLAY THE DETAILS OF COURSES  WITH HIGHEST FEE.
SELECT  * FROM COURSES
WHERE  TFEE =
 ( SELECT MAX(TFEE) FROM COURSES );



DISPLAY THE BATCHES IN WHICH MORE THAN 10 STUDENTS  HAVE PAID THE TOTAL FEE. 
SELECT BCODE
FROM STUDENTS
WHERE 
  (BCODE,ROLLNO, DECODE(LAB,'Y',TFEE + LFEE, TFEE) )
    IN 
  (SELECT  BCODE,ROLLNO,SUM(AMOUNT)
    FROM PAYMENTS
    GROUP BY BCODE,ROLLNO
  );
GROUP BY BCODE
HAVING COUNT(*) > 10;


DISPLAY THE COURSES FOR WHICH WE HAVEN'T STARTED ANY BATCH IN THE CURRENT  MONTH.
SELECT * FROM COURSES
WHERE CCODE NOT IN
 ( SELECT  CCODE FROM BATCHES
   WHERE   TO_CHAR(STDATE,'MMYY') = TO_CHAR(STDATE,'MMYY')
 );


DISPLAY THE DETAILS OF STUDENTS EITHER   JOINED IN THE COURSES ORACLE OR ANY
COURSES WITH TFEE  MORE  THAN 1500.

SELECT * FROM STUDENTS 
WHERE  BCODE IN
(SELECT BCODE FROM BATCHES
 WHERE CCODE = 'ORA' OR TFEE > 1500);

DISPLAY THE DETAILS OF STUDENTS  WHO HAVE  PAID  LUMSUM (TOTAL AMOUNT AT A TIME). 
SELECT * FROM STUDENTS
WHERE (BCODE,ROLLNO)
IN
(SELECT  BCODE,ROLLNO
 FROM PAYMENTS P, STUDENT S, BATCHES B
 WHERE P.BCODE = S.BCODE AND P.ROLLNO = S.ROLLNO AND B.BCODE = S.BCODE
 AND AMOUNT = TFEE);

DISPLAY THE BATCHES OF ORACLE OR THE BATCHES  THAT STARTED IN THE CURRENT MONTH
WITH MORE THAN 10 STUDENTS.
SELECT * FROM BATCHES
WHERE CCODE = 'ORA'
     OR TO_CHAR(STDATE,'MMYY') = TO_CHAR(SYSDATE,'MMYY')
        AND BCODE IN
        (SELECT BCODE 
         FROM   STUDENTS
         GROUP  BY BCODE
         HAVING COUNT(*) > 10);


DISPLAY THE PAYMENTS MADE BY THE ORACLE STUDENTS.
SELECT * FROM PAYMENTS
WHERE BCODE
  IN
  (SELECT BCODE
   FROM   BATCHES B
   WHERE  CCODE = 'ORA');

CHANGE THE AMOUNT OF STUDENT 15 IN ORA150503 TO TFEE OF ORACLE. 
UPDATE PAYMENTS
    SET  AMOUNT = (SELECT TFEE FROM COURSES WHERE CCODE = 'ORA')
   WHERE BCODE = 'ORA150503' AND ROLLNO = 15;


DELETE  THE COURSES FOR WHICH WE HAVEN'T STARTED ANY  BATCH. 
DELETE FROM COURSES
WHERE  CCODE NOT IN
  (SELECT CCODE FROM BATCHES);


DISPLAY THE COURSES IN WHICH WE HAVE MORE THAN 150 STUDENTS TAKING THE LAB FACILITY.
SELECT  * FROM COURSES
WHERE CCODE IN
 (SELECT CCODE
  FROM   STUDENTS S, BATCHES B
  WHERE  S.BCODE = B.BCODE AND LAB = 'Y'
  GROUP BY CCODE
  HAVING COUNT(*) > 150);


PL/SQL 

INSERT A ROW INTO PAYMENTS TABLE WITH BALANCE AMOUNT FOR STUDENT 2 OF BATCH ORA150503.

DECLARE
  V_AMTPAID NUMBER(5);
  V_FEE     NUMBER(5);
BEGIN
   SELECT  SUM(AMOUNT) INTO V_AMTPAID
   FROM PAYMENTS
   WHERE BCODE = 'ORA150503' AND ROLLNO = 2;

   SELECT  DECODE(LAB,'Y',TFEE + LFEE, TFEE) INTO V_FEE
   FROM BATCHES B, STUDENTS S
   WHERE B.BCODE = S.BCODE AND S.BCODE = 'ORA150503' AND ROLLNO = 2;

   IF  V_FEE - V_AMTPAID > 0 THEN
    INSERT INTO PAYMENTS
     VALUES( ( SELECT MAX(RCPTNO) + 1 FROM PAYMENTS),'ORA150503',2,SYSDATE, V_FEE - V_AMTPAID);
   END IF;
END;


INCREASE THE TFEE OF JAVA BY 500 IF WE STARTED MORE THAN 5 BATCHES  IN LAST 6
MONTHS  OR WE GOT MORE THAN 20 STUDENTS IN THE MOST RECENT 2 BATCHES.

DECLARE
  V_STUDCOUNT  NUMBER(4);
  V_BATCHCOUNT NUMBER(2);
BEGIN
   -- HOW MANY JAVA BATCHES WERE STARTED IN LAST 6 MONTHS
   SELECT COUNT(*) INTO V_BATCHCOUNT
   FROM BATCHES
   WHERE  CCODE = 'JAVA' AND MONTHS_BETWEEN(SYSDATE,STDATE) <= 6;

   -- HOW MANY STDENTS JOINED INTO MOST RECENT TWO BATCHES
   SELECT COUNT(*) INTO V_STUDCOUNT
   FROM  STUDENTS 
   WHERE BCODE IN
          (SELECT BCODE FROM 
            (SELECT  BCODE FROM BATCHES WHERE CCODE = 'JAVA' ORDER BY STDATE DESC)
           WHERE  ROWNUM <=2);

   IF  V_BATCHCOUNT > 5 OR V_STUDCOUNT > 20 THEN
     UPDATE  COURSES SET TFEE = TFEE + 500 WHERE CCODE = 'JAVA';
   END IF;
END;


CREATE FUNCTION TO TAKE BATCHCODE AND RETURN THE NEXT ROLLNO. IT IS 1 IF NO  STUDENT
IS PRESENT IN THE BATCH OR FISRT MISSING ROLLNO IF A ROLLNO IS MISSING  OR
THE HIGHEST ROLLNO+1.

create or replace function getmissrno(pbcode varchar2) Return number is
    v_rno  number(3) := 1;
    v_c number(2);  
begin 
           while true
           loop
                select count(*) into v_c 
                from  students
                where bcode=pbcode and rollno=v_rno;

                if v_c=0  then 
                      return v_rno;
                end if;
                v_rno := v_rno+1;    
           end loop;
 
end;
/


CREATE A FUNCTION THAT TAKES THE CCODE AND RETURN THE BCODE IN WHICH WE GOT
           HIGHEST REVENUE
CREATE OR REPLACE FUNCTION  HIGHESTREVENUEBATCH(PCCODE VARCHAR2) RETURN VARCHAR2 IS
 V_BCODE VARCHAR2(15);
BEGIN

  SELECT BCODE INTO V_BCODE
  FROM  PAYMENTS P, BATCHES B
  WHERE P.BCODE= B.BCODE AND CCODE = PCCODE
  GROUP BY BCODE
  HAVING SUM(AMOUNT) = 

   (SELECT MAX( SUM(AMOUNT))
    FROM   PAYMENTS P , BATCHES B
    WHERE  P.BCODE = B.BCODE AND  CCODE = PCCODE
    GROUP BY P.BCODE)

  RETURN V_BCODE;

END;





CREATE A FUNCTION THAT RETURNS TRUE IF THE GIVEN STUDENT  NAME IS EXISTING 
IN AN ONGOING BATCH.
CREATE OR REPLACE FUNCTION  ISSTUDENTEXISTING( STUDNAME VARCHAR2) RETURN BOOLEAN IS
 V_C NUMBER(1);
BEGIN
    SELECT COUNT(*) INTO V_C
    FROM STUDENTS
    WHERE SNAME = STUDNAME
      AND BCODE IN
            (SELECT BCODE FROM BATCHES WHERE STDATE <= SYSDATE AND ENDDATE IS NULL);

    IF V_C = 0 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;



CREATE A PACKAGE CALLED STUDENT AND PROVIDE  FOLLOWING METHODS:
GETFEEPAID(BCODE,ROLLNO)
GETNAME(BCODE,ROLLNO)
CHANGEEMAIL(OLDEMAIL,NEWEMAIL)


create or replace package PKGSTUDENT
as
  procedure changeemail(oemAIL varchar2,nem varchar2);
  function  getfeepaid(pbcode varchar2, prOLLNO number) return number;
  function  getName( pbcode varchar2,prOLLno number) return varchar2; 
end;
/

create or replace package body student 
as
procedure changeemail(oeMAIL varchar2,nemAIL varchar2)
is
begin
 update students
 set  email=neMAIL
 where OEMAIL=email;
end;

function getfeepaid(pbcode varchar2,prOLLNO number) Return number is
 v_fee  number(4);
begin 
select 
  SELECT SUM(AMOUNT) INTO V_FEE
  FROM PAYMENTS
  WHERE BCODE = PBCODE AND ROLLNO = PROLLNO;

  RETURN  V_FEE;

end;

function  getname(pbcode varchar2,prOLLno number) return varchar2 is
 v_sname   varchar2(30);
begin
 select sname into v_sname from students
 where pbcode=bcode AND ROLLNO = PROLLNO;

 return  v_sname;
end;

end;
/


ENSURE IN THE SECOND PAYMENT STUDENT IS MAKING THE TOTAL PAYMENT. 
create or replace trigger  trig_2nd_pay
before insert on payments
for each row
declare
    v_c     number(2);
    v_amt   number(5);
    v_fee   number(5);
begin
    Select count(*),sum(amount) into v_c,v_amt  from payments
    where bcode= :new.bcode and rollno= :new.rollno;

    if v_c = 1  then 
         /*find out due amount*/
         select  decode(lab,'y',tfee+lfee,tfee) into v_fee 
         from students s,batches b
         Where s.bcode=b.bcode and s.bcode= :new.bcode and s.rollno= :new.rollno;

         if :new.amount != (v_fee - v_amt) then 
              raise_application_error(-20020,'invalid amount for 2nd installment');
         end if;
    end if;
end;

 
CREATE  A TRIGGER TO PREVENT CHANGES TO PAYMENTS TABLE ON SUNDAY. 
create or replace trigger  trg_pc_sun
before insert  or delete or update on payments
begin
    if  to_char(sysdate,'d')= 1 then 
                raise_application_error(-20020,'you can not modify data on sunday');
    end if;
end;

RESTRICT  THE NOR OF STUDENTS TO 15 IN A BATCH. 
create or replace trigger trg_resb_15st
before insert or update on students
for each row
declare
    v_c   number(3);
begin
    select  count(*) into v_c from students
    where bcode= :new.bcode ;
          
    if v_c >15 then 
           raise_application_error(-20020,'not allowed');
    end if;
end;