ORACLE PROGRAMS

8. Employee Details Using DML

SQL> CREATE TABLE EMPLOYEE (EMPNO NUMBER (5)

PRIMARY KEY, ENAME VARCHAR2 (20), EDESG VARCHAR2 (15), GENDER
VARCHAR2 (6), EAGE NUMBER (2), EDOJ DATE, ESALARY NUMBER(10,2));
TABLE CREATED.

SQL> DESC EMPLOYEE;

Name Null? Type

EMPNO NOT NULL NUMBER(5)

ENAME VARCHAR2(20)
EDESG VARCHAR2(15)
GENDER VARCHAR2(6)
EAGE NUMBER(2)
EDOJ DATE
ESALARY NUMBER(10,2)

SQL> INSERT INTO EMPLOYEE VALUES

(&EMPNO,'&ENAME','&EDESG','&EGENDER',&EAGE,'&EDOJ',&ESALAR Y);

Enter value for empno : 1

Enter value for ename : KANCHANA

Enter value for edesg : ENGINEER

Enter value for egender : FEMALE

Enter value for eage : 21

Enter value for edoj : 01-JUNE-2022
Enter value for salary : 20000
old 1: INSERT INTO EMPLOYEE VALUES
(&EMPNO,'&ENAME','&EDESG','&EGENDER',&EAGE,'&EDOJ',&ESALARY)
 new 1: INSERT INTO EMPLOYEE VALUES
(1,'KANCHANA','ENGINEER','FEMALE',21,'01- JUNE-2022',20000)

1 row created.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME EDESG GENDER EAGE EDOJ

ESALARY

1 KANCHANA ENGINEER FEMALE 21 01-JUN-22

20000

2 SANTHOSHKUMAR DEVELOPER
MALE 21 01-JUL-22
21000

3 SRIJA DESIGNER FEMALE 21 01-JUN-22

22000

4 VIGNESHKUMAR ANALYST MALE 21 01-JUL-22

25000

5 SATHISHKUMAR ANALYST MALE 21 01-JUN-22

22000

6 GIFTY ENGINEER FEMALE 21 01-AUG-22

21000

7 DURGA OPERATOR FEMALE 22 01-FEB-22

25000

8 SATHYARUBA TESTER FEMALE 22 01-FEB-22

24000

9 DINESHBABU DEVELOPER MALE 23 01-FEB-22

21000

10 KARTHIK ANALYST MALE 22 01-JAN-22

20000
 COMAPRISION OPERATOR

SQL> SELECT * FROM EMPLOYEE WHERE ESALARY > 23000 AND

ESALARY <26000;

EMPNO ENAME EDESG GENDER EAGE EDOJ

ESALARY

4 VIGNESHKUMAR ANALYST MALE 21 01-JUL-22

25000

7 DURGA OPERATOR FEMALE 22 01-FEB-22

25000

8 SATHYARUBA TESTER FEMALE 22 01-FEB-22

24000

LOGICAL OPERATOR

SQL> SELECT * FROM EMPLOYEE WHERE ENAME LIKE 'S%';

EMPNO ENAME EDESG GENDER EAGE EDOJ

ESALARY

2 SANTHOSHKUMAR DEVELOPER MALE 21 01-JUL-22

21000
3 SRIJA DESIGNER FEMALE 21 01-JUN-22
22000

8 SATHYARUBA TESTER FEMALE 22 01-FEB-22

24000

10 KARTHIK ANALYST MALE 22 01-JAN-22

20000

SORTING OPERATOR

SQL> SELECT * FROM EMPLOYEE ORDER BY ENAME DESC;

EMPNO ENAME EDESG GENDER EAGE EDOJ

ESALARY

3 SRIJA DESIGNER FEMALE 21 01-JUN-22

22000
8 SATHYARUBA TESTER FEMALE 22 01-FEB-22
24000
10 KARTHIK ANALYST MALE 22 01-JAN-22
20000
2 SANTHOSHKUMAR DEVELOPER
MALE 21 01-JUL-22

21000
5 SATHISHKUMAR ANALYST MALE 21 01-JUN-22
22000
7 DURGA OPERATORFEMALE 22 01-FEB-22
25000
1 KANCHANA ENGINEER FEMALE 21 01-JUN-22
20000
9 DINESHBABU DEVELOPER MALE 23 01-FEB-22
21000
4 VIGNESHKUMAR ANALYST MALE 21 01-JUL-22
25000
6 GIFTY ENGINEER FEMALE 21 01-AUG-22

21000
10 rows selected.

GROUPING OPERATORS

SQL> SELECT ENAME, MIN(ESALARY) FROM EMPLOYEE GROUP BY

ENAME;

ENAME MIN(ESALARY)

GIFTY 21000
VIGNESHKUMAR 25000
JOTHIRAJAN 21000
KANCHANA 20000
DURGA 25000
SATHISHKUMAR 22000
SANTHOSHKUMAR 21000
KARTHIK 20000
SATHYARUBA 24000
SRIJA 22000

10 rows selected.

SET OPERATOR
SQL> CREATE TABLE EMPLOYEE1 AS SELECT * FROM EMPLOYEE;

Table created.

SQL> SELECT * FROM EMPLOYEE UNION SELECT * FROM

EMPLOYEE1;

EMPNO ENAME EDESG GENDER EAGE EDOJ

ESALARY

1 KANCHANA ENGINEER FEMALE 21 01-JUN-22

20000
2 SANTHOSHKUMAR DEVELOPER
MALE 21 01-JUL-22
21000

3 SRIJA DESIGNER FEMALE 21 01-JUN-22

22000

4 VIGNESHKUMAR ANALYST MALE 21 01-JUL-22

25000

5 SATHISHKUMAR ANALYST MALE 21 01-JUN-22

22000

6 GIFTY ENGINEER FEMALE 21 01-AUG-22

21000
7 DURGA OPERATOR FEMALE 22 01-FEB-22
25000

8 SATHYARUBA TESTER FEMALE 22 01-FEB-22

24000

9 DINESHBABU DEVELOPER MALE 23 01-FEB-22

21000

10 KARTHIK ANALYST MALE 22 01-JAN-22

20000

10 rows selected.

SQL> SELECT * FROM EMPLOYEE MINUS SELECT * FROM

EMPLOYEE1;

no rows selected
 9. Inventory Details and Updation

SQL> CREATE TABLE INVENTORY ( PRONO NUMBER(5,3),

PRONAME VARCHAR2(15), PRORATE
NUMBER(5,2));

Table created.

SQL> INSERT INTO INVENTORY VALUES(&PRONO,'&PRONAME',&PRORATE);

Enter value for proid : 1
Enter value for proname : APPLE
Enter value for prorate : 15
old 1: INSERT INTO INVENTORY
VALUES(&PRONO,'&PRONAME',&PRORATE)
new 1: INSERT INTO INVENTORY VALUES(1,'APPLE',15)

1 row created.

SQL> SELECT * FROM INVENTORY;

PRONO PRONAME PRORATE

1 APPLE 15
2 ORANGE 10
3 MANGO 9
4 BANANA 5
5 PINEAPPLE 20
SQL> ED PRG9

DECLARE
I NUMBER;
CURSOR C IS SELECT * FROM INVENTORY; BEGIN
FOR I IN C LOOP

I.PRORATE:=I.PRORATE*(20/100) + I.PRORATE;
UPDATE INVENTORY SET PRORATE = I.PRORATE WHERE PRONO=I.PRONO; END

LOOP;

END;

/
SQL> @PRG9.SQL;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM INVENTORY;

PRONO PRONAME PRORATE

1 APPLE 18
2 ORANGE 12
3 MANGO 10.8
4 BANANA 6
5 PINEAPPLE 24

SQL> ALTER TABLE INVENTORY ADD (NUMOFITEM NUMBER (3));

Table altered.

SQL> SELECT * FROM INVENTORY;

PRONO PRONAME PRORATE NUMOFITEM

1 APPLE 18
2 ORANGE 12
3 MANGO 10.8
4 BANANA 6
5 PINEAPPLE 24

SQL> UPDATE INVENTORY SET NUMOFITEM=5 WHERE PRONO=1;

1 row updated.

SQL> SELECT * FROM INVENTORY;

PRONO PRONAME PRORATE NUMOFITEM

1 APPLE 18 5
2 ORANGE 12 10
3 MANGO 10.8 20
4 BANANA 6 7
5 PINEAPPLE 24 13
 10. Implementation of Triggers

SQL> CREATE TABLE MASINVENTORY (PID NUMBER (3),

PNAME VARCHAR2 (15),PQTY NUMBER(5));

Table created.

SQL> INSERT INTO MASINVENTORY VALUES (&PID,'&PNAME',&PQTY);

Enter valuefor pid : 1
Enter value for pname : APPLE
Enter value for pqty : 20
old 1: INSERT INTO MASINVENTORY VALUES (&PID,'&PNAME',&PQTY) new 1:
INSERT INTO MASINVENTORY VALUES (1,'APPLE',20)

1 row created.

SQL> SELECT * FROM MASINVENTORY;

PID PNAME PQTY

1 APPLE 20
2 BANANA 30
3 ORANGE 50
4 PINEAPPLE 40
5 MANGO 57

SQL>CREATE TABLE TRANSINVENTORY(PID NUMBER(3),PNAME

VARCHAR2(15),PQTY NUMBER(5));

Table created.
SQL>ED PRGTRIG

CREATE OR REPLACE TRIGGER CHECKVAL BEFORE INSERT ON

TRANSINVENTORY FOR EACH

BEGIN
IF ((:NEW.PQTY <=0) OR (:NEW.PQTY > 300))
THEN RAISE_APPLICATION_ERROR (-
20011,'CHECK YOUR
QUANTITY');

ELSE DBMS_OUTPUT.PUT_LINE ('RECORD INSERTED');

F
END;
/

SQL> @PRGTRIG.SQL;

Trigger created.

SQL> INSERT INTO TRANSINVENTORY VALUES (&PID,'&PNAME',&PQTY);

Enter valuefor pid : 1
Enter value for pname : APPLE
Enter value for pqty : 0
old 1: INSERT INTO TRANSINVENTORY VALUES (&PID,'&PNAME',&PQTY) new 1:
INSERT INTO TRANSINVENTORY VALUES (1,'APPLE',0)
 INSERT INTO TRANSINVENTORY VALUES (1,'APPLE',0)
*
ERROR at line 1:
ORA-20011: CHECK YOUR QUANTITY

ORA-06512: at "TEST01.CHECKVAL", line 3

ORA-04088: error during execution of trigger 'TEST01.CHECKVAL'

SQL> SELECT * FROM TRANSINVENTORY;

no rows selected
SQL> INSERT INTO TRANSINVENTORY VALUES
(&PID,'&PNAME',&PQTY);
Enter value for pid : 1
Enter value for pname : APPLE
Enter value for pqty : 20
old 1: INSERT INTO TRANSINVENTORY VALUES (&PID,'&PNAME',&PQTY) new 1:
INSERT INTO TRANSINVENTORY VALUES (1,'APPLE',20)
RECORD INSERTED

1 row created.

SQL> SELECT * FROM TRANSINVENTORY;

PID PNAME PQTY

1 APPLE 20
 11. Implementation of Procedures

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number,y IN number,z OUT number)IS
BEGIN
IF x<y THEN
z:=x;
ELSE
z:=y;
END IF;
END;
BEGIN
a:=23;
b:=45;
findMin(a,b,c);
DBMS_OUTPUT.PUT_LINE('Minimum of(23,45):'||c);
END;
/
Minimum of (23,45):23

PL/SQL procedure successfully completed.