EX 1: CREATION OF DATABASE TABLE

AIM:
To create a database table, add constraints (primary key, unique, check, Not null),
insert rows,update and delete rows using SQL DDL and DML commands.

QUERIES:

Q1)Create a table called EMP with the following structure.

Name Type
---------- ----------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
DEPTNO NUMBER(3)
SAL NUMBER(7,2)
Allow NULL for all columns except ename and job.

SQL> CREATE TABLE EMP1(EMPNO NUMBER(6),ENAME VARCHAR2(20),JOB

VARCHAR2(10),DEPTNO NUMBER(3), SAL NUMBER(7,2));

OUTPUT:
Table created.

SQL> DESC EMP1

OUTPUT:

Name Null? Type

----------------------------------------- -------- ----------------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
DEPTNO NUMBER(3)
SAL NUMBER(7,2)

SQL> CREATE TABLE EMP2(EMPNO NUMBER(6) NOT NULL, ENAME

VARCHAR2(20), JOB VARCHAR2(20), DEPTNO NUMBER(3) NOT NULL, SAL
NUMBER(7,2) NOT NULL);

OUTPUT:
Table created.

SQL> DESC EMP2

OUTPUT:

Name Null? Type

----------------------------------------- --------------- ----------------------------
EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(20)
DEPTNO NOT NULL NUMBER(3)
SAL NOT NULL NUMBER(7,2)

Q2)Add a column experience to the emp table. Experience numeric null is allowed.

SQL> ALTER TABLE EMP2

ADD (EXPERIENCE NUMBER(6) NOT NULL);

OUTPUT:
Table altered.

SQL> DESC EMP2

OUTPUT:

Name Null? Type

----------------------------------------- --------------- ----------------------------
EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(20)
DEPTNO NOT NULL NUMBER(3)
SAL NOT NULL NUMBER(7,2)
EXPERIENCE NOT NULL NUMBER(6)

Q3)Modify the column width of the job field of emp table.

SQL> ALTER TABLE EMP2

MODIFY (JOB VARCHAR2(15));

OUTPUT:
Table altered.

SQL> DESC EMP2

OUTPUT:

Name Null? Type

----------------------------------------- -------------- ----------------------------
EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(15)
DEPTNO NOT NULL NUMBER(3)
SAL NOT NULL NUMBER(7,2)
EXPERIENCE NOT NULL NUMBER(6)

Q4)Create dept table with the following structure.

Name Type
------------ ---------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(10)
LOC VARCHAR2(10)
Deptno as the primary key

SQL> CREATE TABLE DEPT(DEPTNO NUMBER(2),

2 DNAME VARCHAR2(10),
3 LOC VARCHAR2(10));

OUTPUT:
Table created.

SQL> DESC DEPT

OUTPUT:

Name Null? Type

----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(10)
LOC VARCHAR2(10)

Q5)Create the emp1 table with ename and empno, add constraints to check whether the
empno value while entering is greater than 100 (i.e) empno >100.

SQL> CREATE TABLE EMP4(EMPNO NUMBER(6),CHECK (EMPNO >100),ENAME

VARCHAR2(20));

OUTPUT:
Table created.

SQL> DESC EMP4

OUTPUT:

Name Null? Type

----------------------------------------- -------- ----------------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)

Q6)Drop a column experience from the emp table

SQL> ALTER TABLE EMP2
2 DROP COLUMN EXPERIENCE;

OUTPUT:
Table altered.

SQL> DESC EMP2

OUTPUT:

Name Null? Type

----------------------------------------- --------------- ----------------------------
EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(15)
DEPTNO NOT NULL NUMBER(3)
SAL NOT NULL NUMBER(7,2)

Q7)Truncate the emp table

SQL> TRUNCATE TABLE EMP2;

OUTPUT:
Table truncated.

SQL> DESC EMP2

OUTPUT:

Name Null? Type

------------------------------------- --------------- ----------------------------
EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(15)
DEPTNO NOT NULL NUMBER(3)
SAL NOT NULL NUMBER(7,2)

Q8)Insert more records into the employee table.

SQL>INSERT INTO EMP3 (EMPNO, ENAME, JOB,SAL)

VALUES (1001, 'RAVI', 'SE', 3000.00);

OUTPUT:
1 row created.

SQL>INSERT INTO EMP3 (EMPNO, ENAME, JOB, SAL)

VALUES (1002, 'AFSHEEN', 'LECTURER', 4500.00);
OUTPUT:
1 row created.

SQL> SELECT *FROM EMP3;

OUTPUT:

EMPNO ENAME JOB SAL

---------- -------------------- ------- --------
1001 RAVI SE 3000.00
1002 AFSHEEN LECTURER 4500.00

Q9) Insert a single record into dept table.

SQL>INSERT INTO DEPT (DEPTNO, DNAME, LOC)

VALUES (10, 'SALES', 'NY');

OUTPUT:
1 row created.

SQL> SELECT *FROM DEPT;

OUTPUT:
DEPTNO DNAME LOC
------------- ------------ --------
10 SALES NY

Q10)Update the emp table to set the salary of all employees to Rs 20,000

SQL>UPDATE EMP3
SET SAL = 20000;

OUTPUT:
1 row updated.

SQL> SELECT *FROM EMP3;

OUTPUT:

EMPNO ENAME JOB SAL

---------- -------------------- ---------------- --------
1001 RAVI SE 20000
1002 AFSHEEN LECTURER 20000

Q11)Delete only those who are working as “Lecturer”

SQL>DELETE FROM EMP3

WHERE JOB = 'LECTURER';

SQL> SELECT *FROM EMP3;

OUTPUT:

EMPNO ENAME JOB SAL

---------- -------------------- ------- --------
1001 RAVI SE 20000

RESULT:

Thus to create a database table, add constraints (primary key, unique, check, Not
null), insert rows, update and delete rows using SQL DDL and DML commands are written
and the output is verified.
EX-2 : CREATING A SET OF TABLES, ADD FOREIGN KEY CONSTRAINTS AND
INCORPORATE REFERENTIAL INTEGRITY

AIM:
To Create a set of tables, add foreign key constraints and incorporate referential integrity

QUERIES:

Q1) A)Create a dept table(dno,dname) Set referential integrity for the same

SQL>CREATE TABLE DEPT1(DNO NUMBER(4) PRIMARY KEY, DNAME VARCHAR2(30));

Table created.

SQL>DESC DEPT1;
Name Null? Type
------------------------- ------------ ---------------------------
DNO NOT NULL NUMBER(4)
DNAME VARCHAR2(30)

SQL>INSERT INTO DEPT1 VALUES(1,'CSE');

1 row created.

SQL>INSERT INTO DEPT1 VALUES(2,'ECE');

1 row created.

SQL>INSERT INTO DEPT1 VALUES(3,'EEE');

1 row created.

SQL>INSERT INTO DEPT1 VALUES(4,'IT');

1 row created.

SQL>SELECT * FROM DEPT1;

DNO DNAME
---------- ------------------------------
1 CSE
2 ECE
3 EEE
4 IT

B)Create a student table (rollno,name, marks,dno) Set referential integrity for the same

SQL>CREATE TABLE STUDENT1(ROLLNO VARCHAR2(12),NAME VARCHAR2(30),MARKS

NUMBER(3),DNO NUMBER(4), FOREIGN KEY(DNO) REFERENCES DEPT1(DNO));

Table created.

SQL>INSERT INTO STUDENT1 VALUES('311123104001','AMY',92,1);

1 row created.

SQL>INSERT INTO STUDENT1 VALUES('311123104002','RIMY',87,2);

1 row created.

SQL>INSERT INTO STUDENT1 VALUES('311123104003','JIMMY',85,3);

1 row created.

SQL>INSERT INTO STUDENT1 VALUES('311123104004','MOLLY',81,4);

1 row created.

SQL>SELECT * FROM STUDENT1;

ROLLNO NAME MARKS DNO

------------ ------------------------------ ---------- ----------
311123104001 AMY 92 1
311123104002 RIMY 87 2
311123104003 JIMMY 85 3
311123104004 MOLLY 81 4

Q2) A) Customer (Cust_id, Cust_name, Addr, ph_no,pan_no)

SQL>CREATE TABLE CUSTOMER(CUST_ID NUMBER(4) PRIMARY KEY,CUST_N

VARCHAR2(30),ADDRESS VARCHAR2(30),PH_NO VARCHAR2(10),
PAN_NOVARCHAR2(12));
Table created.

SQL>DESC CUSTOMER;
Name Null? Type
------------------ --------------- ----------------------------
CUST_ID NOT NULL NUMBER(4)
CUST_N VARCHAR2(30)
ADDRESS VARCHAR2(30)
PH_NO VARCHAR2(10)
PAN_NO VARCHAR2(12)

SQL>INSERT INTO CUSTOMER VALUES(101,'MANOJ','Gandhi street','1234567890','1

12233');
1 row created.

SQL>SELECT * FROM CUSTOMER;

CUST_ID CUST_N ADDRESS PH_NO PAN_NO

---------- --------- ---------------------- —---------------- —-------------------------
101 MANOJ Gandhi street 1234567890 112233
102 KRISH Nethaji street 1234567891 112244
103 MANASA ECR 1234567892 112255
104 VIDYA OMR 1234567893 112266

B) Loan (Loan_id, Amount. Interest, Cust_Id)

SQL>CREATE TABLE LOAN1(LOAN_ID NUMBER(4),AMT NUMBER(6),INTEREST

NUMBER(6),C
UST_ID NUMBER(4), FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER(CUST_ID));
Table created.

SQL>DESC LOAN1;
Name Null? Type
-------------------------- ------- ----------------------------
LOAN_ID NUMBER(4)
AMT NUMBER(6)
INTEREST NUMBER(6)
CUST_ID NUMBER(4)

SQL>INSERT INTO LOAN1 VALUES(1010,1000,250,101);

1 row created.

SQL>INSERT INTO LOAN1 VALUES(1011,2000,260,102);

1 row created.

SQL>INSERT INTO LOAN1 VALUES(1012,3000,280,103);

1 row created.

SQL>INSERT INTO LOAN1 VALUES(1013,4000,300,104);

1 row created.

SQL>SELECT * FROM LOAN1;

LOAN_ID AMT INTEREST CUST_ID

---------------- ------- ------------- ----------
1010 1000 250 101
1011 2000 260 102
1012 3000 280 103
1013 4000 300 104

Q3) Create the following table with the mapping given below:
A)stu_details(reg_no, stu_name, DOB, address, city)

SQL>CREATE TABLE STUDENT3(REG_NO NUMBER(5) PRIMARY KEY,

STU_NAME VARCHAR2(30), DOB VARCHAR2(15), ADDRESS VARCHAR(30), CITY
VARCHAR(20));
Table created.

SQL>INSERT INTO STUDENT3 VALUES(10001,'EMILY','1.1.2005','Gandhi street','

chennai');
1 row created.

SQL>INSERT INTO STUDENT3 VALUES(10002,'LILLY','3.5.2005','Nethaji street','

Madurai');
1 row created.

SQL>SELECT * FROM STUDENT3;

 REG_NO STU_NAME DOB ADDRESS CITY

---------- ------------------------------ ----------- ------------------------------ --------------------

10001 EMILY 1.1.2005 Gandhi street Chennai
10002 LILLY 3.5.2005 Nethaji street Madurai

B)mark_details(reg_no, mark1, mark2, mark3, total)

SQL>CREATE TABLE MARKS(REG_NO NUMBER(5), FOREIGN KEY(REG_NO)

REFERENCES STUDENT3(REG_NO),MARK1 NUMBER(3),MARK2 NUMBER(3), MARK3
NUMBER(3), TOTAL NUMBER(3));
Table created.

SQL>INSERT INTO MARKS VALUES(10001,60,70,80,210);

1 row created.

SQL>INSERT INTO MARKS VALUES(10002,70,70,80,220);

1 row created.

SQL>SELECT * FROM MARKS;

REG_NO MARK1 MARK2 MARK3 TOTAL

--------------- ---------- ---------- ---------- ----------
10001 60 70 80 210
10002 70 70 80 220

Q4) Create the following tables

A)CUSTOMER(custno, custname, city, phone)

SQL>CREATE TABLE CUSTOMER5(CUSTNO NUMBER(4) PRIMARY

KEY,CUSTNAME VARCHAR2(30),CITY VARCHAR2(20),PHONE VARCHAR2(15));
Table created.

SQL>INSERT INTO CUSTOMER5 VALUES(101,'ARUN','Chennai', '1234567890');

1 row created.

SQL>INSERT INTO CUSTOMER5 VALUES(102,'ARUNA','Madurai', '1234567891');

1 row created.

SQL>INSERT INTO CUSTOMER5 VALUES(103,'Rimy','Theni', '1234567892');

1 row created.

SQL>SELECT * FROM CUSTOMER5;

CUSTNO CUSTNAME CITY PHONE

---------- ------------------ -------------- ---------------
101 ARUN Chennai 1234567890
102 ARUNA Madurai 1234567891
103 Rimy Theni 1234567892
B)ITEM(itemno, itemname, itemprice, quantity)

SQL>CREATE TABLE ITEM(ITEMNO NUMBER(4) PRIMARY KEY,ITEMNAME

VARCHAR2(30),IT
EMPRICE NUMBER(10,2),QUANTITY NUMBER(5));
Table created.

SQL>INSERT INTO ITEM VALUES(1011,'MILK',60,2);

1 row created.

SQL>INSERT INTO ITEM VALUES(1012,'CHOCOLATES',240,4);

1 row created.

SQL>INSERT INTO ITEM VALUES(1013,'MAIDA',270,3);

1 row created.

SQL>SELECT * FROM ITEM;

ITEMNO ITEMNAME ITEMPRICE QUANTITY
---------- ----------------------- --------------- —------------
1011 MILK 60 2
1012 CHOCOLATES 240 4
1013 MAIDA 270 3

C)INVOICE(invno, invdate, custno)

SQL>CREATE TABLE INVOICE(INVNO NUMBER(4) PRIMARY KEY,INVDATE

VARCHAR2(15),CUSTNO NUMBER(4),FOREIGN KEY(CUSTNO)REFERENCES
CUSTOMER5(CUSTNO));
Table created.

SQL>INSERT INTO INVOICE VALUES(1,'12.01.2025',101);

1 row created.

SQL>INSERT INTO INVOICE VALUES(2,'13.01.2025',102);

1 row created.

SQL>INSERT INTO INVOICE VALUES(3,'14.01.2025',103);

1 row created.

SQL>SELECT * FROM INVOICE;

INVNO INVDATE CUSTNO

---------- --------------- ----------
1 12.01.2025 101
2 13.01.2025 102
3 14.01.2025 103

D)INVITEM(invno, itemno, quantity)

SQL>CREATE TABLE INVITEM(INVNO NUMBER(4) NOT NULL,ITEMNO NUMBER(4) NOT
NULL
,QUANTITY NUMBER(5), PRIMARY KEY(INVNO,ITEMNO));
Table created.

SQL>INSERT INTO INVITEM VALUES(1,1011,2);

1 row created.

SQL>INSERT INTO INVITEM VALUES(2,1012,4);

1 row created.

SQL>SELECT * FROM INVITEM;

INVNO ITEMNO QUANTITY

---------- ---------- --------------
1 1011 2
2 1012 4
3 1013 3

Q5) Consider the following relational schema for a Product Sales database application
A) Product (Prodid, Prodesc, Price, Stock)

SQL>CREATE TABLE PRODUCT(PRODID NUMBER(4) PRIMARY KEY,PRODESC

VARCHAR2(20),PRICE NUMBER(5),STOCK NUMBER(10));
Table created.

SQL>INSERT INTO PRODUCT VALUES(1001,'GROCERY',10000,520);

1 row created.

SQL>INSERT INTO PRODUCT VALUES(1002,'ELECTRONICS',40000,320);

1 row created.

SQL>INSERT INTO PRODUCT VALUES(1003,'FASHION',20000,100);

1 row created.

SQL>SELECT * FROM PRODUCT;

PRODID PRODESC PRICE STOCK

---------- ----------------------- ---------- ----------
1001 GROCERY 10000 520
1002 ELECTRONICS 40000 320
1003 FASHION 20000 100

B) Purchase (Purid, Proid, qty, supplierName)

SQL>CREATE TABLE PURCHASE(PURID NUMBER(4) PRIMARY KEY,PRODID

NUMBER(4),FOREIGN KEY(PRODID)REFERENCES PRODUCT(PRODID),QUANTITY
NUMBER(4),SUPPLIERNAME VAVARCHAR2(40));
Table created.
SQL>INSERT INTO PURCHASE VALUES(2001,1001,20,'PRIYA ENTERPRISES');
1 row created.

SQL>INSERT INTO PURCHASE VALUES(2002,1002,30,'JOHNSON ENTERPRISES');

1 row created.

SQL>INSERT INTO PURCHASE VALUES(2003,1003,40,'THOMSON ENTERPRISES');

1 row created.

SQL>SELECT * FROM PURCHASE;

PURID PRODID QUANTITY SUPPLIERNAME

---------- ---------- ---------- ----------------------------------------
2001 1001 20 PRIYA ENTERPRISES
2002 1002 30 JOHNSON ENTERPRISES
2003 1003 40 THOMSON ENTERPRISES

C)Sales (Saleid, Proid, qty, custname)

SQL>CREATE TABLE SALES(SALEID NUMBER(2) PRIMARY KEY,PRODID

NUMBER(4),FOREIGN KEY(PRODID)REFERENCES PRODUCT(PRODID),QUANTITY
NUMBER(4),CUSTNAME VARCHAR2(30));
Table created.

SQL>INSERT INTO SALES VALUES(1,1001,20,'JOHNNY');

1 row created.

SQL>INSERT INTO SALES VALUES(2,1002,30,'JIMMY');

1 row created.

SQL>INSERT INTO SALES VALUES(3,1003,40,'JAMES');

1 row created.

SQL>SELECT * FROM SALES;

SALEID PRODID QUANTITY CUSTNAME
---------- ---------- ---------- ------------------------------
1 1001 20 JOHNNY
2 1002 30 JIMMY
3 1003 40 JAMES

RESULT:

Thus, the set of Tables created with the given constraints and Referential integrity is
incorporated
and the output is verified.
 EX-3 : WHERE CLAUSE AND AGGREGATE FUNCTIONS

AIM:
To write SQL Queries for the given questions using WHERE clause and aggregate functions.

1)Create a table for employee with empid varchar(10), empname varchar(20)

SQL> CREATE TABLE EMPLOY1(EMPID VARCHAR2(10) PRIMARY KEY,EMPNAME

VARCHAR2(30));
Table created.

2)Add a column to this table as DateOfJoining

SQL>ALTER TABLE EMPLOY1 ADD DOJ VARCHAR(12);

Table altered.

3)Insert records into this table using Insert Command

SQL> INSERT INTO EMPLOY1 VALUES('A001','KRISH','15.01.2025');

1 row created.

SQL> INSERT INTO EMPLOY1 VALUES('A002','MANOJ','16.01.2025');

1 row created.

SQL> INSERT INTO EMPLOY1 VALUES('A003','VIMAL','18.01.2025');

1 row created.

SQL> SELECT * FROM EMPLOY1;

EMPID EMPNAME DOJ

---------- ------------------------------ ------------
A001 KRISH 15.01.2025
A002 MANOJ 16.01.2025
A003 VIMAL 18.01.2025

4)Create a pseudo table employee with the same structure as the table emp and insert rows into
the table using select clauses.

SQL> CREATE TABLE EMPLOY2 AS SELECT * FROM EMPLOY1;

Table created.

5)List the records in the emp table in ascending order of empid

SQL> SELECT * FROM EMPLOY1 ORDER BY EMPID ASC;

EMPID EMPNAME DOJ

---------- ------------------------------ ------------
A001 KRISH 15.01.2025
A002 MANOJ 16.01.2025
A003 VIMAL 18.01.2025

6)Display only those employees whose empid is A001.

SQL> SELECT * FROM EMPLOY1 WHERE EMPID='A001';

EMPID EMPNAME DOJ

---------- ------------------------------ ------------
A001 KRISH 15.01.2025

7)Display all the details of the records whose employee name does not starts with “A”

SQL> SELECT * FROM EMPLOY1 WHERE EMPNAME NOT LIKE 'A%';

EMPID EMPNAME DOJ

---------- ------------------------------ ------------
A001 KRISH 15.01.2025
A002 MANOJ 16.01.2025
A003 VIMAL 18.01.2025

8)Display the employees whose salary ranges from 15000 to 30000.

SQL> ALTER TABLE EMPLOY1 ADD SALARY NUMBER(8);
Table altered.

SQL> UPDATE EMPLOY1 SET SALARY=16000 WHERE EMPID='A001';

1 row updated.

SQL> UPDATE EMPLOY1 SET SALARY=10000 WHERE EMPID='A002';

1 row updated.

SQL> UPDATE EMPLOY1 SET SALARY=20000 WHERE EMPID='A003';

1 row updated.
SQL> SELECT * FROM EMPLOY1;

EMPID EMPNAME DOJ SALARY

---------- ------------------------------ ------------ ----------
A001 KRISH 15.01.2025 16000
A002 MANOJ 16.01.2025 10000
A003 VIMAL 18.01.2025 20000

SQL> SELECT * FROM EMPLOY1 WHERE SALARY BETWEEN 15000 AND 30000;

EMPID EMPNAME DOJ SALARY

---------- ------------------ ------------ ----------
A001 KRISH 15.01.2025 16000
A003 VIMAL 18.01.2025 20000

9)Calculate the total and average salary amount of the emp table.
SQL> SELECT SUM(SALARY) AS TOT_SAL, AVG(SALARY) AS AVG_SAL FROM EMPLOY1;

TOT_SAL AVG_SAL
---------- ----------
46000 15333.3333

10)Count the total records in the emp table

SQL> SELECT COUNT(*) AS TOTAL_RECORDS FROM EMPLOY1;

TOTAL_RECORDS
-------------------------
3

11)Determine the maximum and minimum salary and rename the column as max_salary and
min_salary.

SQL> SELECT MAX(SALARY) AS MAX_SALARY, MIN(SALARY) AS MIN_SALARY FROM

EMPLOY1;

MAX_SALARY MIN_SALARY
--------------------- ---------------------
20000 10000

12)Find the experience of the employee in months(Find the difference between the current date and the
DOJ) and display it for all the employees.

SQL> CREATE TABLE EMPLO1(EMPID VARCHAR2(10) PRIMARY KEY,EMPNAME

VARCHAR2(30),DOJ DATE, SALARY NUMBER(6));
Table created.

SQL> INSERT INTO EMPLO1 VALUES('A001','KRISH',TO_DATE('2024-08-15','YYYY-MM-

DD'),16000);
1 row created.

SQL> INSERT INTO EMPLO1 VALUES('A002','MANOJ',TO_DATE('2024-06-15','YYYY-MM-

DD'),10000);
1 row created.

SQL> INSERT INTO EMPLO1 VALUES('A003','VIMAL',TO_DATE('2024-03-15','YYYY-MM-

DD'),20000);
1 row created.

SQL> SELECT * FROM EMPLO1;

EMPID EMPNAME DOJ SALARY
---------- ------------------------------ --------- --------
A001 KRISH 15-AUG-24 16000
A002 MANOJ 15-JUN-24 10000
A003 VIMAL 15-MAR-24 20000
SQL> SELECT EMPID,EMPNAME, FLOOR(MONTHS_BETWEEN(SYSDATE, DOJ)) AS
EXPERIENCE_IN_MONTHS FROM EMPLO1;

EMPID EMPNAME EXPERIENCE_IN_MONTHS

---------- ------------------------------ --------------------
A001 KRISH 5
A002 MANOJ 7
A003 VIMAL 10

13)Find how many job titles are available in the employee table.

SQL>ALTER TABLE EMPLO1 ADD JOB_TITLE VARCHAR2(30);

Table altered.

SQL> UPDATE EMPLO1 SET JOB_TITLE='DEVELOPER' WHERE EMPID='A001';

1 row updated.

SQL> UPDATE EMPLO1 SET JOB_TITLE='HR' WHERE EMPID='A002';

1 row updated.

SQL> UPDATE EMPLO1 SET JOB_TITLE='DEVELOPER' WHERE EMPID='A003';

1 row updated.

SQL> SELECT * FROM EMPLO1;

EMPID EMPNAME DOJ SALARY JOB_TITLE
----------- ----------------- --------- ---------- ------------------------------
A001 KRISH 15-AUG-24 16000 DEVELOPER
A002 MANOJ 15-JUN-24 10000 HR
A003 VIMAL 15-MAR-24 20000 DEVELOPER

SQL> SELECT COUNT(DISTINCT JOB_TITLE) AS JOB_TITLE_COUNT FROM EMPLO1;

JOB_TITLE_COUNT
----------------------------
2

14)What is the difference between maximum and minimum salaries of employees in the
organization?
SQL> SELECT MAX(SALARY) - MIN(SALARY) AS SALARY_DIFF FROM EMPLO1;

SALARY_DIFF
---------------------
10000

15)Add a default constraint to the salary and the set the default value as Rs.5000.

SQL> ALTER TABLE EMPLO1 MODIFY SALARY DECIMAL(10,2) DEFAULT 5000;

Table altered.
16)Find the total amount of salary spent by each department using GROUP BY clause.

SQL> ALTER TABLE EMPLO1 ADD DEPT_ID VARCHAR2(4);

Table altered.

SQL> UPDATE EMPLO1 SET DEPT_ID='101' WHERE EMPID='A001';

1 row updated.

SQL> UPDATE EMPLO1 SET DEPT_ID='102' WHERE EMPID='A002';

1 row updated.

SQL> UPDATE EMPLO1 SET DEPT_ID='101' WHERE EMPID='A003';

1 row updated.

SQL> SELECT * FROM EMPLO1;

EMPID EMPNAME DOJ SALARY JOB_TITLE DEPT
---------- ------------------ -------------- ---------- —-------------- —-----------
A001 KRISH 15-AUG-24 16000 DEVELOPER 101
A002 MANOJ 15-JUN-24 10000 HR 102
A003 VIMAL 15-MAR-24 20000 DEVELOPER 101
DEVELOPER 101

SQL> SELECT DEPT_ID, SUM(SALARY) AS TOTAL_SALARY FROM EMPLO1 GROUP BY

DEPT_ID;
DEPT TOTAL_SALARY
—------- -----------
101 36000
102 10000

17)Display the department that has total salary paid for its employees more than 25000
SQL> SELECT DEPT_ID FROM EMPLO1 GROUP BY DEPT_ID HAVING SUM(SALARY)>25000;

DEPT
—------
101

18)Display the ENAME as first name from the employees

SQL> SELECT EMPID,EMPNAME, SUBSTR(EMPNAME, 1, INSTR(EMPNAME, ' ') -1) AS FIR
ST_NAME FROM EMPLO1;
EMPID EMPNAME FIRST_NAME
---------- ----------------- —----------------
A001 KRISH
A002 MANOJ
A003 VIMAL

RESULT:
Thus, the queries have been created and the output is verified.
EX 4: DATABASE QUERYING – SIMPLE QUERIES, NESTED QUERIES, SUB QUERIES AND
JOINS

AIM:
To create Database queries for simple, nested and sub queries and also use the joins.

1)Create the following tables

emp (empno,empname ,job,deptno,salary )
Department(dname,dno,id)
Grades()rollno,gpa)
Student(sname,rollno,regno)

i) emp (empno,empname ,job,deptno,salary )

SQL> CREATE TABLE E(EMPNO NUMBER(4),EMPNAME VARCHAR(30),JOB

VARCHAR(30),DEPTNO NUMBER(4),SALARY NUMBER(6));
Table created.

SQL> INSERT INTO E VALUES(1001,'MANOJ','MANAGER',101,10000);

1 row created.

SQL> INSERT INTO E VALUES(1002,'MARY','MARKETING ROLE',102,20000);

1 row created.

SQL> INSERT INTO E VALUES(1003,'AMY','HR',103,30000);

1 row created.

SQL> SELECT * FROM E;

EMPNO EMPNAME JOB DEPTNO SALARY

---------------- ------------------------- ------------------------------ ------------ ----------
 1001 MANOJ MANAGER 101 10000
1002 MARY MARKETING ROLE 102 20000
1003 AMY HR 103 30000

ii) Department(dname,dno,id)
SQL> CREATE TABLE DEP(DNAME VARCHAR(10),DEPTNO NUMBER(4),ID NUMBER(4));
Table created.

SQL> INSERT INTO DEP VALUES('MEDIA',101,1);

1 row created.

SQL> INSERT INTO DEP VALUES('EVENT',102,2);

1 row created.

SQL> INSERT INTO DEP VALUES('MARKETING',103,3);

1 row created.

SQL> SELECT * FROM DEP;

DNAME DNO ID
----------------- ---------- ----------
MEDIA 101 1
EVENT 102 2
MARKETING 103 3

iii) Grades(rollno,gpa)
SQL> CREATE TABLE GRADE(ROLLNO NUMBER(10),GPA NUMBER(2));
Table created.

SQL> INSERT INTO GRADE VALUES(3111231041,7);

1 row created.
SQL> INSERT INTO GRADE VALUES(3111231042,8);
1 row created.

SQL> INSERT INTO GRADE VALUES(3111231043,9);

1 row created.

SQL> SELECT * FROM GRADE;

ROLLNO GPA
----------------- ----------
3111231041 7
3111231042 8
3111231043 9

iv) Student(sname,rollno,regno)
SQL> CREATE TABLE STU(SNAME VARCHAR(20),ROLLNO NUMBER(10),REGNO
NUMBER(2));
Table created.

SQL> INSERT INTO STU VALUES('RAHUL',3111231041,11);

1 row created.

SQL> INSERT INTO STU VALUES('AJAY',3111231042,12);

1 row created.

SQL> INSERT INTO STU VALUES('SAI',3111231043,13);

1 row created.

SQL> SELECT * FROM STU;

SNAME ROLLNO REGNO
-------------- ---------- ----------
RAHUL 3111231041 11
AJAY 3111231042 12
SAI 3111231043 13

A) Display all employee names and salary whose salary is greater than minimum salary of the
company and job title starts with ‘M’
SQL> SELECT EMPNAME,SALARY FROM E WHERE SALARY>(SELECT MIN(SALARY) FROM
E) AND JOB LIKE 'M%';

EMPNAME SALARY
----------------- ----------
MARY 20000

B)Issue a query to find all the employees who work in the same job as AMY
SQL> SELECT EMPNAME, JOB FROM E WHERE JOB=(SELECT JOB FROM E WHERE
EMPNAME ='AMY');

EMPNAME JOB
----------------- ----------------
AMY HR

C)Issue a query to display information about employees who earn more than any employee in dept
102.
SQL> SELECT * FROM E WHERE SALARY > ALL(SELECT SALARY FROM E WHERE
DEPTNO=102);

EMPNO EMPNAME JOB DEPTNO SALARY

---------- ------------------- ----------------------- ---------- ------------
1003 AMY HR 103 30000
D)Display the employee details, departments that the departments are same in both the emp and
dept.
SQL> SELECT [Link],[Link],[Link],[Link],[Link],[Link] FROM E T JOIN
DEP D ON [Link]=[Link];

EMPNO EMPNAME JOB DEPTNO SALARY DEPTNO

---------- ------------------ ------------------------------ ---------- ---------- ----------
1001 MANOJ MANAGER 101 10000 101
1002 MARY MARKETING ROLE 102 20000 102
1003 AMY HR 103 30000 103

E)Display the Student name and grade by implementing a left outer join.
SQL> SELECT [Link],[Link] FROM STU S LEFT OUTER JOIN GRADE G ON
[Link]=[Link];

SNAME GPA
-------------- ----------
RAHUL 7
AJAY 8
SAI 9

F)Display the Student name, register no, and result by implementing a right outer join.
SQL> SELECT [Link],[Link],[Link] FROM STU S RIGHT OUTER JOIN GRADE G ON S.R
OLLNO=[Link];

SNAME REGNO GPA

------------ ---------- ----------
RAHUL 11 7
AJAY 12 8
SAI 13 9
G)Display the Student name register no by implementing a full outer join.
SQL> SELECT [Link],[Link] FROM STU S FULL OUTER JOIN GRADE G ON
[Link]=[Link];

SNAME REGNO
------------- ----------
RAHUL 11
AJAY 12
SAI 13

2)Create the following tables and insert rows:

Product (maker, model, type)
PC(mode1, speed, ram, hd, rd, price)
Laptop(mode1, speed, ram, hd, screen, price)
Printer(mode1,color,type,price)

Product (maker, model, type)

SQL> CREATE TABLE PROD(MAKER VARCHAR(5),MODEL VARCHAR(6),TYPE
VARCHAR(10));
Table created.

SQL> INSERT INTO PROD VALUES('A','10A1','LAPTOP');

1 row created.

SQL> INSERT INTO PROD VALUES('B','10A2','PC');

1 row created.

SQL> INSERT INTO PROD VALUES('A','10A3','PRINTER');

1 row created.
SQL> INSERT INTO PROD VALUES('C','10A4','PRINTER');
1 row created.

SQL> SELECT * FROM PROD;

MAKER MODEL TYPE

---------- —-------- ----------
A 10A1 LAPTOP
B 10A2 PC
A 10A3 PRINTER
C 10A4 PRINTER

PC(mode1, speed, ram, hd, rd, price)

SQL> CREATE TABLE PC(MODEL VARCHAR(6),SPEED NUMBER(5),RAM NUMBER(5),HD

NUMBER(5),RD NUMBER(5),PRICE NUMBER(6));
Table created.

SQL> INSERT INTO PC VALUES('10A1',3000,8,500,500,1500);

1 row created.

SQL> INSERT INTO PC VALUES('10A2',2800,16,1000,1000,2500);

1 row created.

SQL> INSERT INTO PC VALUES('10A3',3500,8,500,500,1800);

1 row created.

SQL> INSERT INTO PC VALUES('10A4',3500,6,600,1500,1900);

1 row created.

SQL> SELECT * FROM PC;

MODEL SPEED RAM HD RD PRICE
------ ---------- ------ ------ ------- ----------
10A1 3000 8 500 500 1500
10A2 2800 16 1000 1000 2500
10A3 3500 8 500 500 1800
10A4 3500 6 600 1500 1900

Laptop(mode1, speed, ram, hd, screen, price)

SQL> CREATE TABLE LAPTOP(MODEL VARCHAR(5),SPEED NUMBER(5),RAM

NUMBER(5),HD NUMBER(5),SCREEN NUMBER(5),PRICE NUMBER(6));
Table created.

SQL> INSERT INTO LAPTOP VALUES ('10A1',2500,8,500,15,3000);

1 row created.

SQL> INSERT INTO LAPTOP VALUES ('10A2',2200,16,1000,14,2500);

1 row created.

SQL> INSERT INTO LAPTOP VALUES ('10A3',1200,13,100,12,2300);

1 row created.

SQL> INSERT INTO LAPTOP VALUES ('10A4',1300,11,120,20,3300);

1 row created.

SQL> SELECT * FROM LAPTOP;

MODEL SPEED RAM HD SCREEN PRICE

------ -------- ------ ------- ---------- ----------
10A1 2500 8 500 15 3000
10A2 2200 16 1000 14 2500
10A3 1200 13 100 12 2300
10A4 1300 11 120 20 3300

Printer(mode1,color,type,price)

SQL> CREATE TABLE PRINTER(MODEL VARCHAR(5),COLOR VARCHAR(10),TYPE

VARCHAR(10), PRICE NUMBER(5));
Table created.

SQL> INSERT INTO PRINTER VALUES('10A1','BLACK','LAPTOP',3000);

1 row created.

SQL> INSERT INTO PRINTER VALUES('10A2','GREY','PC',2500);

1 row created.

SQL> INSERT INTO PRINTER VALUES('10A3','GREY','PRINTER',2300);

1 row created.

SQL> INSERT INTO PRINTER VALUES('10A4','WHITE','PRINTER',3300);

1 row created.

SQL> SELECT * FROM PRINTER;

MODEL COLOR TYPE PRICE

—---- ---------- ---------- ---------
10A1 BLACK LAPTOP 3000
10A2 GREY PC 2500
10A3 GREY PRINTER 2300
10A4 WHITE PRINTER 3300
A)Find the average speed of PC’s.
SQL> SELECT AVG(SPEED) AS AVG_SPEED_PC FROM PC;

AVG_SPEED_PC
-----------------------
3200

B)Find the average speed of laptops costing over Rs. 2000.

SQL> SELECT AVG(SPEED) AS AVG_SPEED FROM LAPTOP WHERE PRICE>2000;

AVG_SPEED
------------------
1800

C)Find the average price of PC made by a manufacturer A

SQL> SELECT AVG(PRICE) AS AVG_PRICE_PC_MANUFACTURER_A FROM PC P JOIN PROD
PR ON [Link]=[Link] WHERE [Link]='A';

AVG_PRICE_PC_MANUFACTURED_A
----------------------------------------------------
1650

D)Find for each different speed the average price of PC

SQL> SELECT SPEED,AVG(PRICE) AS AVG_PRICE FROM PC GROUP BY SPEED;

SPEED AVG_PRICE
-------------- ----------------
3500 1850
3000 1500
2800 2500
E)Find for each manufacturer, the average screen size of its laptops.
SQL> SELECT [Link],AVG([Link]) AS AVG_SCREEN_SIZE FROM LAPTOP L JOIN
PROD PR ON [Link]=[Link] GROUP BY [Link];

MAKER AVG_SCREEN_SIZE
—------- ----------------------------
A 13.5
B 14
C 20

F)Find the manufacturers that make at least three different models of PC

SQL> SELECT MAKER FROM PROD WHERE TYPE='PC' GROUP BY MAKER HAVING
COUNT(DISTINCT MODEL) >=3;
no rows selected

G)Find , for each speed of PC above 800, the average price

SQL> SELECT SPEED,AVG(PRICE) AS AVG_PRICE_PC FROM PC WHERE SPEED >800 GROUP
BY SPEED;

SPEED AVG_PRICE_PC
---------- -------------------
3500 1850
3000 1500
2800 2500

RESULT:

Thus, the queries, sub queries, nested queries and joins are created for the given tables and
the output is verified.
 EX-5 : USER DEFINED FUNCTIONS AND STORED PROCEDURES

AIM: To implement the user defined functions and stored procedures.

USER DEFINED FUNCTION:

SYNTAX:

CREATE FUNCTION function_name

(parameter1 datatype, parameter2 datatype, …)
RETURNS return_datatype
AS
DECLARE local_variable1 datatype;
DECLARE local_variable2 datatype;
BEGIN
--Function body with SQL statements to determine the Function behaviour

RETURN value;

END;

PROCEDURE:

CREATE OR REPLACE PROCEDURE proc_name (IN param datatype OUT param datatype)

{IS | AS}

-- declaration section (local variables)

BEGIN

-- Procedure body /executable logic

EXCEPTION

-- optional error handlers

END;

//Set serveroutput on to execute put_line() builtin function.

1)Write a PLSQL program to add 2 numbers.

declare

a number;

b number;
c number;

begin

a:=20;

b:=10;

c:=a+b;

dbms_output.put_line('ADDITION OF A AND B IS:'||c);

end;

SQL> @"C:\Users\A12-18\Desktop\[Link]";

ADDITION OF A AND B IS:30

2)Write a PL SQL program to swap 2 numbers.

declare

a number;

b number;

c number;

begin

a:=10;

b:=20;

c:=a;

a:=b;

b:=c;

dbms_output.put_line('After Swapping:a='||a);

dbms_output.put_line('After Swapping:b='||b);

end;

SQL> @"C:\Users\A12-18\Desktop\[Link]";
After Swapping:a=20

After Swapping:b=10

Method-2(Swapping without third variable):

declare

a number;

b number;

begin

a:=&a;

b:=&b;

a:=a+b;

b:=a-b;

a:=a-b;

dbms_output.put_line('After Swapping:a='||a);

dbms_output.put_line('After Swapping:b='||b);

end;

SQL> @"C:\Users\A12-18\Desktop\[Link]";

Enter value for a: 2

old 5: a:=&a;

new 5: a:=2;

Enter value for b: 3

old 6: b:=&b;

new 6: b:=3;

After Swapping:a=3

After Swapping:b=2
3)Create a procedure Sal_Get to get the salary of an employee, passing an empid as an input parameter
( Create employees table with columns like EmpID, Fname,Lname, Age, Salary. Insert 5 rows in the
table)

SQL> CREATE TABLE EMP_TABLE(EMPID NUMBER(5),FNAME VARCHAR(20),LNAME

VARCHAR(20), AGE NUMBER(3), SALARY NUMBER(10,2));

Table created.

SQL> INSERT INTO EMP_TABLE VALUES(101,'AMY','JOHN',25,50000);

1 row created.

SQL> INSERT INTO EMP_TABLE VALUES(102,'RIMMY','JOHN',25,60000);

1 row created.

SQL> INSERT INTO EMP_TABLE VALUES(103,'PAUL','MEJOY',28,55000);

1 row created.

SQL> INSERT INTO EMP_TABLE VALUES(104,'LIJO','ARUN',27,65000);

1 row created.

SQL> INSERT INTO EMP_TABLE VALUES(105,'JENY','MATHEW',30,76000);

1 row created.

SQL> SELECT * FROM EMP_TABLE;

CODE:

CREATE OR REPLACE PROCEDURE SAL_GET (EMP_ID IN NUMBER)

IS

SAL NUMBER;

BEGIN
 SELECT SALARY INTO SAL FROM EMP_TABLE WHERE EMPID=EMP_ID;

DBMS_OUTPUT.PUT_LINE(SAL);

END;

SQL> @"C:\Users\A12-18\Desktop\[Link]";

Procedure created.

SQL> EXEC SAL_GET (101);

50000

4) Create a procedure to get the Annual Income of the employee. Employee ID is passed as an input
parameter and Annual Income is retrieved as output parameter.

Procedure:

CREATE OR REPLACE PROCEDURE Get_Annual_Income (

p_emp_id IN NUMBER,

p_annual_income OUT NUMBER)

IS

p_salary NUMBER;

BEGIN

SELECT SALARY INTO p_salary

FROM EMP_TABLE

WHERE EMPID = p_emp_id;

p_annual_income := p_salary * 12;

EXCEPTION

WHEN NO_DATA_FOUND THEN

p_annual_income := NULL;

DBMS_OUTPUT.PUT_LINE('Employee not found.');

WHEN OTHERS THEN

 p_annual_income := NULL;

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;

PROCEDURE CALL:

ACCEPT emp_id NUMBER PROMPT ‘ Enter Employee ID: ‘;

DECLARE

v_annual_income NUMBER;

BEGIN

Get_Annual_Income(p_emp_id => :emp_id, p_annual_income => v_annual_income);

DBMS_OUTPUT.PUT_LINE('Annual Income: ' || v_annual_income);

END;

OUTPUT:

Enter Employee ID: 103

Annual Income: 55000

(Or) Another way to call the procedure with in and out parameters:

DECLARE

v_annual_income NUMBER;

BEGIN

Get_Annual_Income(p_emp_id => 103, p_annual_income => v_annual_income);

DBMS_OUTPUT.PUT_LINE('Annual Income is : ' || v_annual_income);

END;
/

5) Create a function to get the salary of an employee when empid is passed as parameter. Use the
function in a query and return the result.

CODE:

CREATE OR REPLACE FUNCTION Get_Salary (p_emp_id IN NUMBER) RETURN NUMBER

IS

v_salary NUMBER;

BEGIN

SELECT SALARY INTO v_salary FROM EMP_TABLE WHERE EMPID = p_emp_id;

RETURN v_salary;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN NULL;

WHEN OTHERS THEN

RETURN NULL;

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

SQL> SELECT Get_Salary(103) AS SALARY FROM DUAL;

OUTPUT:

6)Create a function to display the FirstName and LastName as FirstName,LastName

CODE:

CREATE OR REPLACE FUNCTION Get_FullName (p_emp_id IN NUMBER) RETURN VARCHAR2

IS
 v_first_name VARCHAR2(6);

v_last_name VARCHAR2(6);

v_full_name VARCHAR2(6);

BEGIN

SELECT FNAME, LNAME INTO v_first_name, v_last_name

FROM EMP_TABLE WHERE EMPID = p_emp_id;

v_full_name := v_first_name || ', ' || v_last_name;

RETURN v_full_name;

END;

SQL> SELECT Get_FullName(103) AS FullName FROM DUAL;

RESULT:
Thus, the functions and procedures are created for the above questions and the output is verified.
EXERCISE 06: DCL AND TCL COMMANDS

AIM: To write SQL queries to illustrate DCL and TCL commands

COMMANDS:

SQL> CREATE TABLE employees (EmpID INT PRIMARY KEY,FirstName

VARCHAR(50),LastName VARCHAR(50),Salary DECIMAL(10, 2));

Table created.

SQL> INSERT INTO employees (EmpID, FirstName, LastName, Salary)VALUES (1,

'John', 'Doe', 50000);

1 row created.

SQL> INSERT INTO employees (EmpID, FirstName, LastName, Salary)VALUES (2, 'Jane',
'Smith', 55000);

1 row created.

SQL> INSERT INTO employees (EmpID, FirstName, LastName, Salary)VALUES (3,

'Alice', 'Johnson', 60000);

1 row created.

SQL> SAVEPOINT before_additional_inserts;

Savepoint created.

SQL> INSERT INTO employees (EmpID, FirstName, LastName, Salary)VALUES (4, 'Bob',
'Brown', 65000);

1 row created.

SQL> INSERT INTO employees (EmpID, FirstName, LastName, Salary)VALUES (5,

'Charlie', 'Davis', 70000);

1 row created.

SQL> ROLLBACK TO SAVEPOINT before_additional_inserts;

Rollback complete.

SQL> COMMIT;

Commit complete.
SQL> Select * from employees;

EMPID FIRSTNAME LASTNAME SALARY

--------- ----------------- ----------------- ------------

1 John Doe 50000

2 Jane Smith 55000

3 Alice Johnson 60000

SQL> GRANT SELECT, INSERT ON employees TO cs4050;

Grant succeeded.

SQL> REVOKE SELECT, INSERT ON employees FROM cs4050;

Revoke succeeded.

RESULT: Thus the various DCL and TCL commands are executed and output is verified.
7) SQL TRIGGERS FOR INSERT, DELETE, AND UPDATE OPERATIONS

AIM:
To create Triggers for Insert, Delete, and Update Operations.
QUERIES:
1)Create table sailor(sid,sname,age). Add trigger before insert to allow age greater than 18
SQL> CREATE TABLE SAILOR(SID INT PRIMARY KEY, SNAME VARCHAR(30),AGE INT);
Table created.
CODE FOR TRIGGER:
CREATE OR REPLACE TRIGGER check_age_before_insert
BEFORE INSERT ON SAILOR
FOR EACH ROW
BEGIN
IF :[Link] <= 18 THEN (OR) WHEN ([Link] <= 18)
RAISE_APPLICATION_ERROR(-20001, 'Age must be greater than 18.');
END IF;
END;
/
SQL> @"C:\Users\A12-31\Desktop\[Link]";
Trigger created.
SQL> INSERT INTO SAILOR VALUES(1,'JOHN',20);
1 row created.
SQL> INSERT INTO SAILOR VALUES(2,'MIKE',19);
1 row created.
SQL> INSERT INTO SAILOR VALUES(6,'RICHE',10);

2)Create table stock(pid ,stock). Add trigger before update to maintain a stock above 5 on
update.
SQL> CREATE TABLE STOCK(PID INT PRIMARY KEY, STOCK INT);
Table created.
CODE FOR TRIGGER:
CREATE OR REPLACE TRIGGER maintain_min_stock
BEFORE UPDATE ON STOCK
FOR EACH ROW
BEGIN
IF :[Link] < 5 THEN
RAISE_APPLICATION_ERROR(-20002, 'Stock must be at least 5.');
END IF;
END;
/
SQL> @"C:\Users\A12-31\Desktop\[Link]";
Trigger created.
SQL> INSERT INTO STOCK VALUES(101,10);
1 row created.
SQL> INSERT INTO STOCK VALUES(102,7);
1 row created.
SQL> UPDATE STOCK SET STOCK=6 WHERE PID=101;
1 row updated.
SQL> UPDATE STOCK SET STOCK=3 WHERE PID=102;

3)Write a Trigger to carry out the following action: on deleting any records from the emp
table ,the same values must be inserted into the log table.
SQL> CREATE TABLE EMPL(EMP_ID INT PRIMARY KEY,EMP_NAME
VARCHAR(20),SALARY DECIMAL(10,2));
Table created.
SQL>CREATE TABLE LOG(EMP_ID INT,EMP_NAME VARCHAR(20),SALARY
DECIMAL(10,2),DELETED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Table created.
CODE FOR TRIGGER:
CREATE OR REPLACE TRIGGER log_emp_deletions
AFTER DELETE ON EMPL
FOR EACH ROW
BEGIN
INSERT INTO LOG (emp_id, emp_name, salary, deleted_at)
VALUES (:OLD.EMP_ID, :OLD.EMP_NAME, :[Link], SYSTIMESTAMP);
END;
/
SQL> @"C:\Users\A12-31\Desktop\[Link]";
Trigger created.
SQL> INSERT INTO EMPL VALUES(201,'ALICE',50000);
1 row created.
SQL> INSERT INTO EMPL VALUES(202,'BOB',60000);
1 row created.
SQL> DELETE FROM EMPL WHERE EMP_ID=201;
1 row deleted.
SQL> SELECT * FROM EMPL;

SQL> SELECT * FROM LOG;

RESULT:
Thus, the queries are created and the output is verified.
EXERCISE 8 VIEW AND INDEX

AIM:

To create a view from the employee table, create an index on employee name, check
execution time, and observe updates in the original table.

QUERIES:

[Link] a view containing the employee number and employee name from employee
table.

SQL>CREATE VIEW emp_view AS

SELECT emp_no, emp_name

FROM employee;

OUTPUT:

View created.

2. Create an index on employee name and then run the query. Use set timing on to check
for the elapsed time to execute the query.

SQL>CREATE INDEX idx_emp_name ON employee(emp_name);

OUTPUT:

Index created.

SQL>SET TIMING ON;

OUTPUT:

Timing enabled.

SQL>SELECT * FROM emp_view WHERE Fname = 'NOBI';

OUTPUT:

EmpID | Fname | Lname

-----------------------

102 | NOBI | TA

[Link] the employee name of an employee and record the observations. Check if the
value is reflected in the original table.
SQL>UPDATE emp_view

SET Fname = 'NOBITA'

WHERE EmpID = 102;

OUTPUT:

1 row updated.

SQL>SELECT * FROM employees WHERE EmpID = 102;

OUTPUT:

EmpID | Fname | Lname | Age | Salary

--------------------------------------

102 | NOBITA | TA | 15 | 20000

RESULT:

Thus, the queries for view and index have been created and the output has been
verified.

EX 9:Write PL/SQL code to trap a predefined and non-predefined Oracle Server error.
AIM:
To write PL/SQL code to trap a predefined and non-predefined Oracle Server error.

Trapping Predefined Exceptions:

Predefined exceptions are common Oracle errors that have been given specific names in
PL/SQL (e.g., NO_DATA_FOUND, ZERO_DIVIDE, TOO_MANY_ROWS). You can directly
reference these names in the EXCEPTION section.

Trapping Non-Predefined Exceptions:

Non-predefined exceptions are Oracle errors that do not have a predefined name in
PL/SQL. These are handled by associating a custom exception name with a specific Oracle
error code using the PRAGMA EXCEPTION_INIT directive.

SQL>CREATE TABLE employees (employee_id NUMBER(6) PRIMARY KEY, employee_name

VARCHAR2(100) NOT NULL);
SQL> INSERT INTO employees VALUES (1001, ' Anne ');
SQL> INSERT INTO employees VALUES (1002, 'Jane Smith');
SQL> INSERT INTO employees VALUES (1003, ' John Doe ');

Code for Trapping Predefined Exceptions:

DECLARE
v_employee_name VARCHAR2(50);
BEGIN
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = 1005;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);

EXCEPTION
 WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No employee found with the given ID.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Output:

Code for Trapping Non-Predefined Exceptions:

DECLARE
emp_not_found EXCEPTION;
v_employee_name VARCHAR2(100);
BEGIN
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id =
1005;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE emp_not_found;
END;
EXCEPTION
WHEN emp_not_found THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee not found with the given ID.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Output:
Result:

Thus PL/SQL code to trap a predefined and non-predefined Oracle Server error has
been created and validated.

EX 10: Create PL/SQL code to demonstrate the purpose of implicit and explicit cursors.
AIM:
To write PL/SQL code to demonstrate the purpose of implicit and explicit cursors.

Implicit Cursors

Implicit Cursor (Automatically Managed)

Oracle automatically creates an implicit cursor every time you run a single SQL statement
(like SELECT INTO, INSERT, UPDATE, or DELETE) inside PL/SQL. You don’t have to
open, fetch, or close it. Oracle does that for you automatically.
It used some attributes like FOUND, ROWCOUNT.
 SQL%FOUND: It is a Boolean attribute.
Returns TRUE if the last SQL statement affected one or more rows.
Returns FALSE if no rows were affected.
 SQL%ROWCOUNT: It is a Numeric attribute. Returns the number of rows affected
by the last SQL statement
Explicit Cursors
Explicit Cursor (Programmer-Controlled)
 Yourself declare a cursor using a name and a SELECT query in the declarative part of
your PL/SQL block .
 Steps you must follow:
1. OPEN the cursor: Oracle executes the query.
2. FETCH data from the cursor into variables (one row at a time).
3. CLOSE the cursor: freeing the memory and resources
 It has more control and flexibility, and good for: When the query returns multiple
rows and you want to loop over them.
SQL>CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR2(100),
marks INT);
SQL>INSERT INTO students VALUES (101, 'Alice', 75);
SQL>INSERT INTO students VALUES (102, 'Bob', 80);
SQL>INSERT INTO students VALUES (103, 'Charlie', 85);
SQL>SET SERVEROUTPUT ON;
Code for Implicit Cursors to Update the mark of particular student in Students table:

DECLARE
v_student_id NUMBER := 101;
BEGIN
UPDATE students
SET marks = marks + 5
WHERE student_id = v_student_id;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Student with ID ' || v_student_id || ' updated successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('No student found with ID ' || v_student_id);
END IF;
DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
END;
/
Code to Update the marks of all the students in Students table:
BEGIN
UPDATE students SET marks = marks + 5;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' students updated!');
ELSE
DBMS_OUTPUT.PUT_LINE('No students updated.');
END IF;
END;
/

Code for Explicit Cursors:

DECLARE
CURSOR students_cur IS SELECT name, marks FROM students;
v_name [Link]%TYPE;
v_marks [Link]%TYPE;
BEGIN
OPEN students_cur;
LOOP
FETCH students_cur INTO v_name, v_marks;
EXIT WHEN students_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_marks);
END LOOP;
CLOSE students_cur;
END;
/

Result:

Thus PL/SQL code to demonstrate the purpose of implicit and explicit cursors has
been created and validated.