Assignment 4(pl block)
<------As.1-------->
<----------As.2----------->
<----------As.3----------------->
<-----------As.4-------------->
<---------As.5---------------->
<-----------As.6----------->
<------------As.7----------->
<------------As.8----------->
<--------As.9------------>
DROP TABLE
STUDENT;
CREATE
TABLE STUDENT
(
NO NUMBER(3),
NAME VARCHAR2(10),
MARK1 NUMBER(3),
MARK2 NUMBER(3),
MARK3 NUMBER(3),
TOTAL NUMBER(5),
AVERAGE NUMBER(5),
CLASS VARCHAR2(5)
);
INSERT
INTO STUDENT(NO,NAME,MARK1,MARK2,MARK3) VALUES(1,'Amisha',40,50,75);
INSERT
INTO STUDENT(NO,NAME,MARK1,MARK2,MARK3) VALUES(2,'Vishakha',40,50,75);
INSERT
INTO STUDENT(NO,NAME,MARK1,MARK2,MARK3)VALUES(3,'Drashti',40,50,75);
INSERT
INTO STUDENT(NO,NAME,MARK1,MARK2,MARK3)VALUES(4,'Nikita’,40,50,75);
INSERT
INTO STUDENT(NO,NAME,MARK1,MARK2,MARK3) VALUES(5,'Dhvani',40,50,75);
DROP TABLE
STUD_RESULT;
CREATE
TABLE STUD_RESULT
(
SNO NUMBER(3),
SNAME VARCHAR2(10),
SMARK1 NUMBER(3),
SMARK2 NUMBER(3),
SMARK3 NUMBER(3),
STOTAL NUMBER(5),
SAVERAGE NUMBER(5),
CLASS VARCHAR2(5)
);
DECLARE
RNO STUDENT.NO%TYPE;
A
STUDENT.AVERAGE%TYPE;
T
STUDENT.TOTAL%TYPE;
C
STUDENT.CLASS%TYPE;
N
STUDENT.NAME%TYPE;
P
NUMBER;
M1
NUMBER;
M2
NUMBER;
M3
NUMBER;
BEGIN
RNO:=&Roll_no;
BEGIN
SELECT MARK1,MARK2,MARK3,NAME INTO
M1,M2,M3,N FROM STUDENT WHERE NO=RNO;
EXCEPTION
WHEN
NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('STUDENT
NUMBER IS NOT FOUND');
GOTO
XYZ;
END;
SELECT
(M1+M2+M3) INTO T FROM DUAL;
--T:=M1+M2+M3;
SELECT
(T/3) INTO A FROM DUAL;
P:=T*100/300;
IF
P>60 THEN
C:='1ST';
ELSIF
P>50 THEN
C:='2ND';
ELSIF
P>35 THEN
C:='PASS';
ELSE
C:='FAIL';
END
IF;
UPDATE STUDENT SET
TOTAL=T,AVERAGE=A,CLASS=C WHERE NO=RNO;
INSERT
INTO STUD_RESULT VALUES(RNO,N,M1,M2,M3,T,A,C);
DBMS_OUTPUT.PUT_LINE(C);
<<XYZ>>
DBMS_OUTPUT.PUT_LINE('GOOD
BYE');
END;
/<----------As.2----------->
DROP TABLE
EMP;
DROP TABLE
DEPT;
CREATE
TABLE DEPT
(
DEPTNO VARCHAR2(5) PRIMARY KEY,
NAME VARCHAR2(20)
);
INSERT
INTO DEPT VALUES('D1','SALES');
INSERT
INTO DEPT VALUES('D2','ACCOUNT');
INSERT
INTO DEPT VALUES('D3','SERVICE');
CREATE
TABLE EMP
(
EMPNO VARCHAR2(5),
NAME VARCHAR2(20),
SALARY NUMBER(10),
DEPTNO VARCHAR2(5),
CONSTRAINT FK1 FOREIGN KEY(DEPTNO)
REFERENCES DEPT(DEPTNO)
);
INSERT
INTO EMP VALUES('E1','Ankit',2000,'D1');
INSERT
INTO EMP VALUES('E2','Meet',3000,'D1');
INSERT
INTO EMP VALUES('E3','Pritesh',4000,'D2');
INSERT
INTO EMP VALUES('E4','Vishal',2000,'D2');
INSERT
INTO EMP VALUES('E5','Parth',5000,'D3');
SELECT *
FROM DEPT;
SELECT *
FROM EMP;
SET
SERVEROUTPUT ON;
DECLARE
CURSOR D1 IS SELECT * FROM DEPT;
CURSOR
E2 IS SELECT * FROM EMP;
MAXVAL NUMBER(10);
NAM EMP.NAME%TYPE;
BEGIN
FOR
REC1 IN D1
LOOP
MAXVAL:=0;
FOR
REC2 IN E2
LOOP
IF
REC1.DEPTNO = REC2.DEPTNO THEN
IF REC2.SALARY > MAXVAL THEN
MAXVAL:=REC2.SALARY;
NAM:=REC2.NAME;
END IF;
END
IF;
END
LOOP;
DBMS_OUTPUT.PUT_LINE(REC1.NAME||'
'||NAM||' '||MAXVAL);
END
LOOP;
END;
/
<----------As.3----------------->
DROP
TABLE DEPARTMENT;
CREATE
TABLE DEPARTMENT
(D_NO
NUMBER(3),
D_NAME
VARCHAR2(20),
D_LOCATION
VARCHAR2(15));
INSERT
INTO DEPARTMENT VALUES(101,'HR',’SURAT’);
INSERT
INTO DEPARTMENT VALUES(102,’ACCOUNTING’,’SURAT’);
INSERT
INTO DEPARTMENT VALUES(103,’MARKETING’,’AHEMDABAD’);
INSERT
INTO DEPARTMENT VALUES(104,’MANAGING’,’VADODARA’);
INSERT
INTO DEPARTMENT VALUES(105,’HR’,’BOMBAY’);
DECLARE
CURSOR DCURSOR IS SELECT *FROM DEPARTMENT;
DREC DCURSOR%ROWTYPE;
BEGIN
OPEN DCURSOR;
LOOP
FETCH DCURSOR INTO
DREC;
EXIT WHEN DCURSOR
%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(DREC.D_NO
|| ' ' || DREC.D_NAME || ' ' || DREC.D_LOCATION);
END LOOP;
CLOSE
DCURSOR;
END;
/<-----------As.4-------------->
CREATE TABLE DEPARTMENT2(DEPTNO NUMBER(5)PRIMARY KEY,
DEPTNAME VARCHAR(20));
CREATE TABLE EMPLOYEE1(EMPNO NUMBER(5)PRIMARY KEY,
EMPNAME VARCHAR(20),
SALARY NUMBER(10),
DEPTNO NUMBER(5),
CONSTRAINT FK1_DEPTNO FOREIGN KEY(DEPTNO)REFERENCES
DEPARTMENT2(DEPTNO));
CREATE TABLE EMPLOYEESDEPT(DEPTNO NUMBER(5),
DEPTNAME VARCHAR(20),
EMPNO NUMBER(5),
EMPNAME VARCHAR(20),
SALARY NUMBER(10));
INSERT INTO DEPARTMENT2 VALUES(1,'MANAGER');
INSERT INTO DEPARTMENT2 VALUES(2,'OFFICER');
INSERT INTO DEPARTMENT2 VALUES(3,'FINANCIAL');
INSERT INTO DEPARTMENT2 VALUES(4,'MANAGER');
INSERT INTO DEPARTMENT2 VALUES(5,'OFFICER');
INSERT INTO EMPLOYEE1 VALUES(101,'Ankit',20000,2);
INSERT INTO EMPLOYEE1 VALUES(102,'Shubham',50000,1);
INSERT INTO EMPLOYEE1 VALUES(103,'RUT',20500,1);
INSERT INTO EMPLOYEE1 VALUES(104,'ABHI',80000,3);
INSERT INTO EMPLOYEE1 VALUES(105,'CHINTAN',12000,2);
SET SERVEROUTPUT ON;
DECLARE
CURSOR C_DEPT IS SELECT*FROM DEPARTMENT2;
CURSOR C_EMP(VDEPTNO DEPARTMENT2.DEPTNO%TYPE)
IS SELECT*FROM EMPLOYEE1 WHERE
DEPTNO=VDEPTNO;
BEGIN
FOR D1 IN C_DEPT
LOOP
FOR E1 IN C_EMP(D1.DEPTNO)
LOOP
INSERT INTO
EMPLOYEESDEPT(DEPTNO,DEPTNAME,EMPNO,EMPNAME,SALARY)
VALUES(D1.DEPTNO,D1.DEPTNAME,E1.EMPNO,E1.EMPNAME,E1.SALARY);
END LOOP;
END LOOP;
END;
/<---------As.5---------------->
DROP
TABLE EMP;
CREATE
TABLE EMP
(ENO
NUMBER(5)PRIMARY KEY,
NAME
VARCHAR2(20),
SALARY
NUMBER(15));
INSERT
INTO EMP VALUES(1,'Ankit',50000);
INSERT
INTO EMP VALUES(2,'Pritesh',3000);
INSERT
INTO EMP VALUES(3,'Parth',25000);
INSERT
INTO EMP VALUES(4,'Paras',8000);
INSERT
INTO EMP VALUES(5,'Vishakha',10000);
INSERT
INTO EMP VALUES(6,'Amisha’,5000);
INSERT
INTO EMP VALUES(7,'Drashti',40000);
SELECT
ROWNUM AS RANK,NAME,SALARY FROM (SELECT * FROM EMP ORDER BY SALARY DESC)
WHERE ROWNUM<=5;
CREATE
OR REPLACE PROCEDURE P1 IS
CURSOR C1 IS SELECT ROWNUM AS
RANK,NAME,SALARY FROM (SELECT * FROM EMP ORDER BY SALARY DESC) WHERE
ROWNUM<=5;
BEGIN
FOR REC IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(REC.RANK||'
'||REC.NAME||' '||REC.SALARY);
END LOOP;
END;
/<-----------As.6----------->
CREATE TABLE STUDENTS(ROLLNO NUMBER(5)PRIMARY KEY,
NAME VARCHAR(20),
STREAM VARCHAR(20),
YEAR VARCHAR(20),
SEM NUMBER(5),
PERCENTAGE NUMBER(5));
CREATE TABLE TOPPERS(ROLLNO NUMBER(5),
NAME VARCHAR(20),
STREAM VARCHAR(20),
YEAR VARCHAR(20),
SEM NUMBER(5),
CONSTRAINT FK1_ROLLNO FOREIGN KEY(ROLLNO)REFERENCES
STUDENTS(ROLLNO));
INSERT INTO STUDENTS VALUES(1,'Amisha','BCA','TYBCA',5,90);
INSERT INTO STUDENTS VALUES(2,'Krushna','BBA','SYBCA',3,80);
INSERT INTO STUDENTS VALUES(3,'Ashmita','BBA','SYBCA',3,70);
INSERT INTO STUDENTS VALUES(4,'Chandani','BCOM','TYBCA',5,65);
INSERT INTO STUDENTS VALUES(5,'Jigar','BCA','FYBCA',1,75);
SET SERVEROUTPUT ON;
CREATE OR REPLACE
PROCEDURE P6 IS
CURSOR C1 IS SELECT
STREAM,YEAR,SEM,MAX(PERCENTAGE)AS MAXIMUM FROM
STUDENTS GROUP BY
STREAM,YEAR,SEM;
CURSOR C2(VSTREAM
STUDENTS.STREAM%TYPE,VYEAR STUDENTS.YEAR%TYPE,
VSEM
STUDENTS.SEM%TYPE,VPERCENTAGE STUDENTS.PERCENTAGE%TYPE)IS
SELECT ROLLNO,NAME
FROM STUDENTS WHERE STREAM=VSTREAM AND YEAR=VYEAR
AND SEM=VSEM AND
PERCENTAGE=VPERCENTAGE;
BEGIN
FOR SREC IN C1
LOOP
FOR TREC IN
C2(SREC.STREAM,SREC.YEAR,SREC.SEM,SREC.MAXIMUM)
LOOP
INSERT INTO TOPPERS
VALUES(TREC.ROLLNO,TREC.NAME,SREC.STREAM,SREC.YEAR, SREC.SEM);
END LOOP;
END LOOP;
END;
/ <------------As.7----------->
DROP TABLE DISTRIBUTOR;
CREATE TABLE DISTRIBUTOR
(DNO NUMBER(5) PRIMARY KEY,
DNAME VARCHAR2(20),
PHNO NUMBER(10));
DROP TABLE ITEM;
CREATE TABLE ITEM
(INO NUMBER(5) PRIMARY KEY,
INAME VARCHAR2(20),
WEIGHT NUMBER(5));
DROP TABLE DIS_ITEM;
CREATE TABLE DIS_ITEM
(DNO NUMBER(5) REFERENCES DISTRIBUTOR(DNO),
INO NUMBER(5) REFERENCES ITEM(INO),
QTY NUMBER(5));
INSERT INTO DISTRIBUTOR VALUES (1,'ABC',9876543210);
INSERT INTO DISTRIBUTOR VALUES (2,'XYZ',8765432109);
INSERT INTO DISTRIBUTOR VALUES (3,'PQR',7654321098);
INSERT INTO DISTRIBUTOR VALUES (4,'MNO',6543210987);
INSERT INTO DISTRIBUTOR VALUES (5,'DEF',5432109876);
INSERT INTO ITEM VALUES(101,'PEN',65);
INSERT INTO ITEM VALUES(102,'PENCIL',25);
INSERT INTO ITEM VALUES(103,'BOOK',200);
INSERT INTO ITEM VALUES(104,'CHOCOLATE',300);
INSERT INTO ITEM VALUES(105,'CLOTHES',500);
INSERT INTO DIS_ITEM VALUES(1,101,15);
INSERT INTO DIS_ITEM VALUES(2,102,25);
INSERT INTO DIS_ITEM VALUES(3,103,20);
INSERT INTO DIS_ITEM VALUES(4,104,30);
INSERT INTO DIS_ITEM VALUES(5,105,50);
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION F1(N VARCHAR2)RETURN
VARCHAR2 IS
ITN VARCHAR2(20);
M NUMBER(5);
BEGIN
SELECT
INAME,MAX(QTY) INTO ITN,M FROM DISTRIBUTOR,ITEM,DIS_ITEM WHERE
DISTRIBUTOR.DNO=DIS_ITEM.DNO
AND ITEM.INO=DIS_ITEM.INO AND DNAME=N GROUP BY INAME;
RETURN ITN;
END;
/<------------As.8----------->
CREATE TABLE BILL(B_NO NUMBER(5)PRIMARY KEY,
T_NO NUMBER(10),
WAITER_NO NUMBER(10),
TOTAL_AMT
NUMBER(10),
TIP NUMBER(10),
DAY NUMBER(10));
CREATE TABLE MENU(DISH_NO NUMBER(10)PRIMARY KEY,
DISH_DIS NUMBER(10),
PRICE NUMBER(10));
CREATE TABLE ORDER1(B_NO NUMBER(5),
DISH_NO NUMBER(5),
QTY NUMBER(10),
CONSTRAINT FK1_BNO FOREIGN KEY(B_NO)REFERENCES BILL(B_NO),
CONSTRAINT FK2_DISHNO FOREIGN KEY(DISH_NO)REFERENCES
MENU(DISH_NO));
INSERT INTO BILL VALUES(101,1,201,1000,10,3);
INSERT INTO BILL VALUES(102,2,202,4000,11,6);
INSERT INTO BILL VALUES(103,3,202,3000,12,5);
INSERT INTO MENU VALUES(11,9,200);
INSERT INTO MENU VALUES(12,8,400);
INSERT INTO MENU VALUES(13,7,500);
INSERT INTO ORDER1 VALUES(101,11,5);
INSERT INTO ORDER1 VALUES(101,12,7);
INSERT INTO ORDER1 VALUES(101,13,4);
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION F8(B_NO IN NUMBER,DISH_NO IN
NUMBER)
RETURN NUMBER IS
MAXQTY NUMBER(10);
BEGIN
SELECT
MAX(QTY)INTO MAXQTY FROM
ORDER1 O,BILL B, MENU M WHERE O.B_NO=B.B_NO
AND O.DISH_NO=M.DISH_NO;
RETURN MAXQTY;
END;
/
FUNCTION CREATED.
DECLARE
B_NO
NUMBER(5):=&B_NO;
DISH_NO
NUMBER(5):=&DISH_NO;
MAXQTY
NUMBER(10);
BEGIN
MAXQTY:=F8(B_NO,DISH_NO);
DBMS_OUTPUT.PUT_LINE(‘MAXIMUM
QTY=’||MAXQTY);
END;
/<--------As.9------------>
CREATE TABLE DISTRIBUTOR(DNO NUMBER(5)PRIMARY KEY,
DNAME VARCHAR(20),
PHNO DECIMAL(12));
CREATE TABLE ITEM(INO NUMBER(5)PRIMARY KEY,
INAME VARCHAR(20),
COLOR VARCHAR(20),
WEIGHT NUMBER(20));
CREATE TABLE DIS_ITEM(DNO NUMBER(5),
INO NUMBER(5),
QTY NUMBER(10),
CONSTRAINT DFK2_DNO FOREIGN KEY(DNO)REFERENCES
DISTRIBUTOR(DNO),
CONSTRAINT IFK2_INO FOREIGN KEY(INO)REFERENCES ITEM(INO));
INSERT INTO DISTRIBUTOR VALUES(1,'Ankit',8877668877);
INSERT INTO DISTRIBUTOR VALUES (2,'Vishal',9988779988);
INSERT INTO DISTRIBUTOR VALUES (3,’Pritesh',7799778899);
INSERT INTO DISTRIBUTOR VALUES (4,'Paras',9595969798);
INSERT INTO DISTRIBUTOR VALUES(5,'Parth',9192939495);
INSERT INTO ITEM VALUES(101,'MOBILE','RED',51);
INSERT INTO ITEM VALUES (102,'VEGETABLES','YELLOW',100);
INSERT INTO ITEM VALUES (103,'OIL','GOLDEN',30);
INSERT INTO ITEM VALUES (104,'VEGETABLES','YELLOW',80);
INSERT INTO ITEM VALUES(105,'MOBILE','RED',20);
INSERT INTO DIS_ITEM VALUES(1,103,5);
INSERT INTO DIS_ITEM VALUES(2,102,6);
INSERT INTO DIS_ITEM VALUES(5,105,7);
INSERT INTO DIS_ITEM VALUES(1,103,4);
INSERT INTO DIS_ITEM VALUES(4,101,2);
INSERT INTO DIS_ITEM VALUES(2,104,9);
INSERT INTO DIS_ITEM VALUES(3,102,8);
SET SERVEROUTPUT ON;
CREATE OR REPLACE
FUNCTION RANGE(A IN NUMBER,B IN NUMBER,C IN VARCHAR)
RETURN NUMBER IS NO
NUMBER;
BEGIN
SELECT COUNT(*)INTO NO FROM(SELECT*FROM ITEM
WHERE COLOR=C AND
WEIGHT BETWEEN A AND B);
RETURN NO;
END;
/
FUNCTION CREATED.
SET SERVEROUTPUT ON;
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C VARCHAR(20):='&C';
N NUMBER(5);
BEGIN
N:=RANGE(A,B,C);
DBMS_OUTPUT.PUT_LINE('NO. OF ITEM:'|| N);
END;
/
<-----------As.10----------->
CREATE TABLE DISTRIBUTORS1(DNO NUMBER(5)PRIMARY KEY,
DNAME VARCHAR(20),
DADDRESS VARCHAR(30),
PHNO DECIMAL(12));
CREATE TABLE ITEMS1(INO NUMBER(5)PRIMARY KEY,
INAME VARCHAR(20),
COLOUR VARCHAR(20),
WEIGHT VARCHAR(20));
CREATE TABLE DIS_ITEM1(DNO NUMBER(5),
INO NUMBER(5),
QTY NUMBER(10),
CONSTRAINT DFK_DNO FOREIGN KEY(DNO)REFERENCES
DISTRIBUTORS1(DNO),
CONSTRAINT IFK_INO FOREIGN KEY(INO)REFERENCES ITEMS1(INO));
INSERT INTO DISTRIBUTORS1 VALUES(1,'Vishal','SHAKTINAGAR',8877668877);
INSERT INTO DISTRIBUTORS1 VALUES (2,'Paras','TAPSHIL',9988779988);
INSERT INTO DISTRIBUTORS1 VALUES (3,'Sinu','GAYTRIBAG',7799778899);
INSERT INTO DISTRIBUTORS1 VALUES (4,'Brijesh’,'SHRINAGAR',9595969798);
INSERT INTO DISTRIBUTORS1 VALUES(5,'Parth','LAKSHMIBAG',9192939495);
INSERT INTO ITEMS1 VALUES(101,'APPLE','RED','51KG');
INSERT INTO ITEMS1 VALUES (102,'BANANA','YELLOW','100KG');
INSERT INTO ITEMS1 VALUES (103,'OIL','GOLDEN','30KG');
INSERT INTO ITEMS1 VALUES (104,'MANGO','YELLOW','60KG');
INSERT INTO ITEMS1 VALUES(105,'ALMONDS','BROWN','20KG');
INSERT INTO DIS_ITEM1 VALUES(1,103,5);
INSERT INTO DIS_ITEM1 VALUES(2,102,6);
INSERT INTO DIS_ITEM1 VALUES(5,105,7);
INSERT INTO DIS_ITEM1 VALUES(1,103,4);
INSERT INTO DIS_ITEM1 VALUES(4,101,2);
INSERT INTO DIS_ITEM1 VALUES(2,104,9);
INSERT INTO DIS_ITEM1 VALUES(3,102,8);
SET SERVEROUTPUT ON;
CREATE OR REPLACE
PROCEDURE DISP_QTY
(VDNO IN
DIS_ITEM1.DNO%TYPE,VINO IN DIS_ITEM1.INO%TYPE)IS
VQTY
DIS_ITEM1.QTY%TYPE;
BEGIN
SELECT QTY INTO VQTY FROM DIS_ITEM1 WHERE
DNO=VDNO AND INO=VINO;
DBMS_OUTPUT.PUT_LINE(VQTY);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ITEM NOT FOUND');
END;
/
<---------As.11--------->
<---------As.12---------->
<----------As.13---------->
<---------As.14----------->
<----------As.15----------->
<-------As.16------->
<-----As.17------->
<-------As.19--------->
<---------As.11--------->
DROP TABLE
DEPT;
DROP TABLE
EMP;
CREATE
TABLE EMP
(
EMPNO VARCHAR2(5),
NAME VARCHAR2(20),
SALARY NUMBER(10),
DOB DATE
);
INSERT
INTO EMP VALUES('E1','Ankit',10000,'21-JAN-1986');
INSERT
INTO EMP VALUES('E2','Jay',15000,'01-FEB-1987');
INSERT
INTO EMP VALUES('E3','Vishal',40000,'04-MAR-1988');
INSERT
INTO EMP VALUES('E4','Paras',10000,'15-APR-1989');
INSERT
INTO EMP VALUES('E5','Pritesh',5000,'07-APR-1990');
CREATE OR
REPLACE PROCEDURE P1 IS
CURSOR C1 IS SELECT
EMPNO,NAME,ROUND((SYSDATE-DOB)/365,0) AS AGE,SALARY FROM EMP WHERE
SALARY<20000 AND ((SYSDATE-DOB)/365)>30;
BEGIN
FOR REC IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(REC.EMPNO||'
'||REC.NAME||' '||REC.AGE||' '||REC.SALARY);
END
LOOP;
END;
/<---------As.12---------->
CREATE
TABLE EMP
(EMPNO
VARCHAR2(5),
NAME VARCHAR2(20),
SALARY NUMBER(10),
DOB DATE);
INSERT
INTO EMP VALUES('E1','Brijesh',10000,'21-JAN-1986');
INSERT
INTO EMP VALUES('E2','Parth',15000,'01-FEB-1987');
INSERT
INTO EMP VALUES('E3','Pritesh',40000,'04-MAR-1988');
INSERT
INTO EMP VALUES('E4','Paras',10000,'15-APR-1989');
INSERT
INTO EMP VALUES('E5','Sinu',5000,'07-APR-1990');
SET SERVEROUTPUT ON;
CREATE OR REPLACE
FUNCTION F1
RETURN
VARCHAR2 IS
NAM
VARCHAR2(10);
BEGIN
SELECT
NAME INTO NAM FROM EMP WHERE DOB=(SELECT MAX(DOB) FROM EMP);
RETURN
NAM;
END;
/
FUNCTION CREATED.
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE(F1
||' IS YOUNGEST EMPLOYEE');
END;
/<----------As.13---------->
<---------As.14----------->
CREATE TABLE STUDENT(ROLLNO NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(20),
AGE NUMBER(10),
PHNO NUMBER(15),
MARKS NUMBER(5));
INSERT INTO STUDENT VALUES(1,'Ankit',20,9345261870,180);
INSERT INTO STUDENT VALUES(2,'Pritesh',25,9642053173,200);
INSERT INTO STUDENT VALUES(3,'Paras',18,8965200152,130);
INSERT INTO STUDENT VALUES(4,'Vishal',21,9853421703,260);
INSERT INTO STUDENT VALUES(5,'Sinu',23,7152304573,230);
SET SERVEROUTPUT ON;
CREATE OR REPLACE
PROCEDURE PRO14
(VROLLNO IN NUMBER,VNAME OUT VARCHAR2,VPHNO OUT NUMBER) AS
BEGIN
SELECT NAME,PHNO INTO VNAME,VPHNO FROM
STUDENT
WHERE ROLLNO=VROLLNO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
END;
/
PROCEDURE CREATED.
DECLARE
VROLLNO NUMBER(5):=&VROLLNO;
SNAME VARCHAR2(20);
SPHNO NUMBER(12);
BEGIN
PRO14(VROLLNO,SNAME,SPHNO);
DBMS_OUTPUT.PUT_LINE('STUDENT
NAME='||''||SNAME);
DBMS_OUTPUT.PUT_LINE('STUDENT
PHNO='||''||SPHNO);
END;
/<----------As.15----------->
CREATE TABLE EMP1(ENO NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(20),
SALARY NUMBER(10),
DNO NUMBER(5));
INSERT INTO EMP1 VALUES(1,'Ankit',70000,101);
INSERT INTO EMP1 VALUES(2,'Meet',60000,102);
INSERT INTO EMP1 VALUES(3,'Smit',25000,103);
INSERT INTO EMP1 VALUES(4,'Shubham',5000,105);
INSERT INTO EMP1 VALUES(5,'Pritesh',25000,105);
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION F15(VDNO IN NUMBER)
RETURN NUMBER IS
MAXSAL
NUMBER(5);
BEGIN
SELECT MAX(SALARY) INTO MAXSAL FROM EMP1
WHERE DNO=VDNO;
RETURN MAXSAL;
END;
/
FUNCTION CREATED.
DECLARE
VDNO NUMBER(5):=&VDNO;
MAXSAL NUMBER(5);
BEGIN
MAXSAL:=F15(VDNO);
DBMS_OUTPUT.PUT_LINE('MAXIMUM
SALARY='||''||MAXSAL);
END;
/<-------As.16------->
CREATE TABLE DEPARTMENT3(DEPTNO NUMBER(5)PRIMARY KEY,
DEPTNAME VARCHAR(20));
CREATE TABLE EMPLOYEE3(EMPNO NUMBER(5)PRIMARY KEY,
EMPNAME VARCHAR(20),
SALARY NUMBER(10),
DEPTNO NUMBER(5),
CONSTRAINT FK1_DEPTNO FOREIGN KEY(DEPTNO)REFERENCES
DEPARTMENT3(DEPTNO));
INSERT INTO DEPARTMENT3 VALUES(1,'MANAGER');
INSERT INTO DEPARTMENT3 VALUES(2,'OFFICER');
INSERT INTO DEPARTMENT3 VALUES(3,'FINANCIAL');
INSERT INTO DEPARTMENT3 VALUES(4,'OFFICER');
INSERT INTO EMPLOYEE3 VALUES(101,'Ankit',20000,2);
INSERT INTO EMPLOYEE3 VALUES(102,'Nik',50000,1);
INSERT INTO EMPLOYEE3 VALUES(103,'Meet',20500,1);
INSERT INTO EMPLOYEE3 VALUES(104,'Meshu',80000,3);
INSERT INTO EMPLOYEE3 VALUES(105,'Raj',12000,2);
SET SERVEROUTPUT ON;
CREATE OR REPLACE
FUNCTION DEPT_NAME(ENAME IN VARCHAR)
RETURN VARCHAR IS
DNAME VARCHAR(20);
BEGIN
SELECT D.DEPTNAME INTO DNAME FROM
EMPLOYEE3 E , DEPARTMENT3 D
WHERE E.DEPTNO=D.DEPTNO AND E.EMPNAME=ENAME;
RETURN DNAME;
END;
/
FUNCTION CREATED.
SET SERVEROUTPUT ON;
DECLARE
ENAME VARCHAR(20):='&ENAME';
D_NAME VARCHAR(20);
BEGIN
D_NAME:=DEPT_NAME(ENAME);
DBMS_OUTPUT.PUT_LINE(D_NAME);
END;
/<-----As.17------->
CREATE TABLE EMP(ENO DECIMAL(5)PRIMARY KEY,
NAME VARCHAR(20),
SALARY DECIMAL(10),
EXP NUMBER(5));
INSERT INTO EMP VALUES(1,'Nikita',70000,7);
INSERT INTO EMP VALUES(2,'Amisha',60000,4);
INSERT INTO EMP VALUES(3,'Vishakha',25000,2);
INSERT INTO EMP VALUES(4,'Drashti',5000,3);
INSERT INTO EMP VALUES(5,'Jagu',25000,1);
SET SERVEROUTPUT ON;
CREATE OR REPLACE
TRIGGER T17
BEFORE UPDATE ON EMP
FOR EACH ROW
DECLARE
Y NUMBER;
BEGIN
IF:OLD.EXP<3 THEN
RAISE_APPLICATION_ERROR(-20001,'EXP IS LESS
THAN 3');
END IF;
END;
/
<-----As.18-------->
CREATE TABLE DEPT3(DNO NUMBER(5)PRIMARY KEY,
DNAME VARCHAR(20),
DLOCATION VARCHAR(20));
INSERT INTO DEPT3 VALUES(1,'SALES','SURAT');
INSERT INTO DEPT3 VALUES(2,'PRODUCT','VADODARA');
INSERT INTO DEPT3 VALUES(3,'PURCHASE','AHMEDABAD');
CREATE TABLE EMP3(ENO NUMBER(5)PRIMARY KEY,
ENAME VARCHAR(20),
SALARY NUMBER(10),
DESIGNATION VARCHAR(20),
DNO NUMBER(5),
CONSTRAINT FK_DNO FOREIGN KEY(DNO)REFERENCES DEPT3(DNO));
INSERT INTO EMP3 VALUES(101,'Ankit',20000,'MANAGER',2);
INSERT INTO EMP3 VALUES(102,'Meet',50000,'OFFICER',1);
INSERT INTO EMP3 VALUES(103,’Parth',20500,'MANAGER',1);
INSERT INTO EMP3 VALUES(104,’Pritesh',80000,'MANAGER',3);
INSERT INTO EMP3 VALUES(105,'Vishal',12000,'OFFICER',2);
SET SERVEROUTPUT ON;
CREATE OR REPLACE TRIGGER
TRI18
BEFORE UPDATE ON EMP3
FOR EACH ROW
DECLARE
NAME VARCHAR(20);
BEGIN
SELECT D.DNAME INTO NAME FROM DEPT3 D,EMP3 E
WHERE E.DNO=D.DNO
AND E.ENO=:OLD.ENO;
IF NAME='SALES' THEN
RAISE_APPLICATION_ERROR(-20001,'UPDATE NOT
ALLOWED');
END IF;
END;
/<-------As.19--------->