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
[Link]:=[Link]*(20/100) + [Link];
UPDATE INVENTORY SET PRORATE = [Link] WHERE PRONO=[Link]; END
LOOP;
END;
/
SQL> @[Link];
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 ((:[Link] <=0) OR (:[Link] > 300))
THEN RAISE_APPLICATION_ERROR (-
20011,'CHECK YOUR
QUANTITY');
ELSE DBMS_OUTPUT.PUT_LINE ('RECORD INSERTED');
F
END;
/
SQL> @[Link];
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 "[Link]", line 3
ORA-04088: error during execution of trigger '[Link]'
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.
