INDIAN INSTITUTE
INFORMATION OF
TECHNOLOGY
Submitted By: Nilesh kumar kamal
Roll no-2021UG1060
Branch: CSE
Group: A
Semester: V
Course: DBMS
Course Code: CS 3001
Submitted To: Dr. Nidhi Kushwaha
DBMS LAB ASSIGNMENT 3
Creating Table Employee
CREATE TABLE Employee (
Employee_Id NUMBER(5) PRIMARY KEY,
First_Name VARCHAR2(50),
Last_Name VARCHAR2(50),
Email VARCHAR2(50),
Phone_No VARCHAR2(15),
Hire_Date DATE,
Job_Id VARCHAR2(20),
Salary NUMBER(10,2),
Manager_Id NUMBER(5),
Department_Id NUMBER(5)
);
INSERT INTO Employee VALUES (100, 'Ritik', 'Raj', 'RITRAJ', '3456286544',
TO_DATE('1987-06-17', 'YYYY-MM-DD'), 'AD_PRES', 24000.00, 0, 90);
INSERT INTO Employee VALUES (101, 'Yash Kumar', 'YASKUM', '2748922645',
TO_DATE('1987-06-18', 'YYYY-MM-DD'), 'AD_VP', 17000.00, 100, 90);
INSERT INTO Employee VALUES (102, 'David', 'Roy', 'DAVROY', '2745987567',
TO_DATE('1987-06-19', 'YYYY-MM-DD'), 'AD_VP', 17000.00, 100, 60);
INSERT INTO Employee VALUES (103, 'Suraj Singh', 'SURSIN', '4328769801',
TO_DATE('1987-06-20', 'YYYY-MM-DD'), 'IT_PROG', 9000.00, 102, 60);
INSERT INTO Employee VALUES (104, 'Bruce', 'Jha', 'BRUJHA', '7634981746',
TO_DATE('1987-06-21', 'YYYY-MM-DD'), 'IT_PROG', 6000.00, 103, 60);
INSERT INTO Employee VALUES (105, 'Nancy', 'Rai', 'NANRAI', '9918387658',
TO_DATE('1987-06-22', 'YYYY-MM-DD'), 'IT_PROG', 4800.00, 103, 60);
INSERT INTO Employee VALUES (106, 'Yug', 'Patel', 'YUGPAT', '1264839275',
TO_DATE('1987-06-23', 'YYYY-MM-DD'), 'IT_PROG', 4800.00, 103, 60);
INSERT INTO Employee VALUES (107, 'Nayan Kumar', 'NAYKUM', '7498275689',
TO_DATE('1987-06-24', 'YYYY-MM-DD'), 'IT_PROG', 4200.00, 103, 100);
INSERT INTO Employee VALUES (108, 'Harshit Austin', 'HARAUS', '8746018731',
TO_DATE('1987-06-25', 'YYYY-MM-DD'), 'FI_MGR', 12000.00, 101, 100),
INSERT INTO Employee VALUES (109, 'Jiya Kumari', 'JIYKUM', '3198376549',
TO_DATE('1987-06-26', 'YYYY-MM-DD'), 'FI_ACCOUNT', 9000.00, 108, 100);
• Write a PL/SQL block to calculate the incentive of an employee whose ID is 100.
DECLARE
v_employee_id NUMBER := 100;
v_salary NUMBER;
v_incentive NUMBER;
BEGIN
-- Get the salary of the employee
SELECT Salary INTO v_salary FROM Employee WHERE Employee_Id =
v_employee_id;
-- Calculate incentive (you can customize the incentive calculation logic)
v_incentive := v_salary * 0.1; -- 10% of salary as incentive
-- Display or use the incentive value as needed
DBMS_OUTPUT.PUT_LINE('Incentive for Employee ' || v_employee_id || ': ' ||
v_incentive);
END;
/
• Write a code in PL/SQL TO create a trigger that automatically updates a
'last_modified' timestamp whenever a row in a specific table is updated.
Alter TABLE employee Add
last_modified TIMESTAMP;
CREATE OR REPLACE TRIGGER update_last_modified
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
:NEW.last_modified := SYSTIMESTAMP;
END;
/
• Write a code in PL/SQL to create a trigger that checks for different values in a
specific column and raises an exception if found.
CREATE OR REPLACE TRIGGER check_last_name
BEFORE INSERT OR UPDATE ON Employee
FOR EACH ROW
BEGIN
-- Check for specific values in the 'last_name' column
IF :NEW.last_name= 'INVALID' THEN
-- Raise an exception if 'INVALID' values are found
RAISE_APPLICATION_ERROR(-20001, 'Invalid value in the last_name column: '
|| :NEW.last_name);
END IF;
END;
• Write a code in PL/SQL to create a trigger that checks for duplicate values in a
specific column and raises an exception if found.
CREATE OR REPLACE TRIGGER prevent_duplicates
BEFORE INSERT ON Employee
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
-- Check if the new first_name already exists
SELECT COUNT(*) INTO v_count FROM Employee WHERE first_name
= :NEW.first_name;
-- If duplicate value found, raise an error
IF v_count> 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'first name already exists.');
END IF;
END;
/
• Create a trigger that checks if "value" is greater than 1000 for any new row inserted
to "Test" table, then insert a row to Audit table?
CREATE TABLE Test
(
test_id INTEGER,
value NUMBER,
test_date DATE
);
CREATE TABLE Test_Audit
(
test_id INTEGER,
value NUMBER,
test_date DATE
);
CREATE OR REPLACE TRIGGER check_and_insert_audit
BEFORE INSERT ON Test
FOR EACH ROW
BEGIN
-- Check if the value is greater than 1000
IF :NEW.value > 1000 THEN
-- Insert a corresponding row into Test_audit
INSERT INTO Test_audit (test_id, value, test_date)
VALUES (:NEW.test_id, :NEW.value, :NEW.test_date);
END IF;
END;
/
• Write a PL/SQL function to return the maximum bit length of the Last_Names in
the Employees table.
DECLARE
v_bit_length NUMBER;
v_max_bit_length NUMBER := 0;
BEGIN
FOR emp IN (SELECT Last_Name FROM Employee) LOOP
v_bit_length := LENGTHB(trim(emp.Last_Name)) * 8;
IF v_bit_length > v_max_bit_length THEN
v_max_bit_length := v_bit_length;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Maximum Bit Length: ' || v_max_bit_length);
END;
/
• Write a PL/SQL block to calculate the bit length of the employee's first name in the
employees table for all records.
DECLARE
v_bit_length NUMBER;
BEGIN
FOR emp IN (SELECT Last_Name FROM Employee) LOOP
v_bit_length := LENGTHB(trim(emp.Last_Name)) * 8;
DBMS_OUTPUT.PUT_LINE('Bit length of ' || emp.Last_Name || ': ' || v_bit_length);
END LOOP;
END;
/
• Write a PL/SQL block to display the character representation of each ASCII value
in the range of 65 to 90.
DECLARE
v_ascii_value NUMBER;
v_character CHAR(1);
BEGIN
FOR v_ascii_value IN 65..90 LOOP
-- Convert ASCII value to character
v_character := CHR(v_ascii_value);
-- Display ASCII value and its character representation
DBMS_OUTPUT.PUT_LINE('ASCII Value: ' || v_ascii_value || ', Character: ' ||
v_character);
END LOOP;
END;
/
• Write a PL/SQL block to convert the last name of each employee in the employees
table to lowercase and display the result.
DECLARE
v_lower_last_name VARCHAR2(50);
BEGIN
FOR emp IN (SELECT Last_Name FROM Employee) LOOP
-- Convert the last name to lowercase
v_lower_last_name := LOWER(emp.Last_Name);
-- Display the original and lowercase last names
DBMS_OUTPUT.PUT_LINE('Original Last Name: ' || emp.Last_Name || ',
Lowercase Last Name: ' || v_lower_last_name);
END LOOP;
END;
/
• Write a PL/SQL block that replaces all occurrences of the substring 'IT_PROG'
with ‘IT PROGRAMMER' in the job titles of employees in the employees table.
Display the updated job titles.
DECLARE
v_job_title Employee.Job_Id%TYPE;
BEGIN
FOR emp IN (SELECT Job_Id FROM Employee) LOOP
-- Check if the current job_title is 'IT_PROG'
IF emp.Job_Id = 'IT_PROG' THEN
-- Update job_title to 'IT_PROGRAMMER'
v_job_title := 'IT_PROGRAMMER';
ELSE
-- Keep the existing job_title if not 'IT_PROG'
v_job_title := emp.Job_Id;
END IF;
-- Display the updated job_title
DBMS_OUTPUT.PUT_LINE('Employee Job Title: ' || emp.Job_Id || ', Updated Job
Title: ' || v_job_title);
END LOOP;
END;
/
• Write a program in PL/SQL to find the number of rows effected by the use of SQL
%ROWCOUNT attributes of an implicit cursor.
CREATE TABLE emp_temp AS
SELECT Employee_Id, First_Name, Last_Name,Email
FROM Employee;
BEGIN
UPDATE emp_temp
SET email = 'not available'
WHERE first_name LIKE 'B%';
dbms_output.Put_line('Number of record updated: '
||To_char(SQL%rowcount));
END;
/
• Write a program in PL/SQL to create a table-based record using the %ROWTYPE
attribute.
DECLARE
v_employee_record Employee_Data%ROWTYPE;
BEGIN
-- Initialize the record with values
v_employee_record.Employee_Id := 101;
v_employee_record.First_Name := 'John';
v_employee_record.Last_Name := 'Doe';
-- Assign values to other columns as needed
-- Display the values in the record
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_record.Employee_Id);
DBMS_OUTPUT.PUT_LINE('First Name: ' || v_employee_record.First_Name);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || v_employee_record.Last_Name);
-- Display other columns as needed
END;
/
• Write a program in PL/SQL to create an implicit cursor with for loop.
DECLARE
v_employee_salary NUMBER;
BEGIN
-- Implicit cursor using a FOR loop
FOR emp IN (SELECT Salary FROM Employee) LOOP
v_employee_salary := emp.Salary;
-- You can perform operations on v_employee_salary or display it as needed
DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_employee_salary);
END LOOP;
END;
/
• Write a program to find the minimum of two values. Here, the procedure takes two
numbers using the IN mode and returns their minimum using the OUT parameters.
-- Create a PL/SQL procedure to find the minimum of two values
CREATE OR REPLACE PROCEDURE find_minimum(
p_number1 IN NUMBER,
p_number2 IN NUMBER,
p_minimum OUT NUMBER
) AS
BEGIN
-- Check for the minimum of the two values
IF p_number1 < p_number2 THEN
p_minimum := p_number1;
ELSE
p_minimum := p_number2;
END IF;
END;
/
-- Example of calling the procedure
DECLARE
v_result NUMBER;
BEGIN
-- Call the procedure with two values
find_minimum(15, 25, v_result);
-- Display the result
DBMS_OUTPUT.PUT_LINE('Minimum value: ' || v_result);
END;
/
• Write a program in which procedure computes the square of value of a passed
value. This example shows how we can use the same parameter to accept a value
and then return another result.
-- Create a PL/SQL procedure to compute the square of a value
CREATE OR REPLACE PROCEDURE compute_square(
p_value IN OUT NUMBER
) AS
BEGIN
-- Compute the square of the passed value
p_value := p_value * p_value;
END;
/
-- Example of calling the procedure
DECLARE
v_number NUMBER := 5; -- Initial value
BEGIN
-- Call the procedure with the initial value
DBMS_OUTPUT.PUT_LINE('Original value: ' || v_number);
-- Call the procedure to compute the square (passing and returning the same parameter)
compute_square(v_number);
-- Display the squared result
DBMS_OUTPUT.PUT_LINE('Squared value: ' || v_number);
END;
/
• Write a program in PL/SQL using function to calculate the factorial of a number
-- Create a PL/SQL function to calculate the factorial of a number
CREATE OR REPLACE FUNCTION calculate_factorial(
p_number IN NUMBER
) RETURN NUMBER IS
v_result NUMBER := 1;
BEGIN
-- Check if the input number is non-negative
IF p_number < 0 THEN
-- Return -1 for invalid input (factorial is not defined for negative numbers)
RETURN -1;
END IF;
-- Calculate the factorial
FOR i IN 1..p_number LOOP
v_result := v_result * i;
END LOOP;
-- Return the factorial result
RETURN v_result;
END;
/
-- Example of calling the function
DECLARE
v_input_number NUMBER := 5; -- Change this to the desired number
v_factorial_result NUMBER;
BEGIN
-- Call the function to calculate the factorial
v_factorial_result := calculate_factorial(v_input_number);
-- Display the result
IF v_factorial_result = -1 THEN
DBMS_OUTPUT.PUT_LINE('Factorial is not defined for negative numbers.');
ELSE
DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_input_number || ' is: ' ||
v_factorial_result);
END IF;
END;
/
• Create the CUSTOMER table.
CREATE TABLE Customer (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
AGE NUMBER,
ADDRESS VARCHAR2(100),
SALARY NUMBER
);
INSERT INTO Customer VALUES (1, 'Ram', 23, 'Allahabad', 12000);
INSERT INTO Customer VALUES (2, 'Suresh', 22, 'Kanpur', 12200);
INSERT INTO Customer VALUES (3, 'Mahi', 24, 'Ghaziabad', 24000);
INSERT INTO Customer VALUES (4, 'Chandan', 25, 'Noida', 26000);
INSERT INTO Customer VALUES (5, 'Aakash', 21, 'Paris', 28000);
INSERT INTO Customer VALUES (6, 'Sulekha', 20, 'Delhi', 30000);
• Write a program to update the CUSTOMER table and increase the the salary of
each customer by 1000. Here, SQL%ROWCOUNT attribute is used to determine
the number of rows affected.
DECLARE
v_updated_rows NUMBER;
BEGIN
-- Update the salary for each customer
UPDATE Customer
SET SALARY = SALARY + 1000;
-- Get the number of rows affected
v_updated_rows := SQL%ROWCOUNT;
-- Display the number of rows affected
DBMS_OUTPUT.PUT_LINE('Number of rows updated: ' || v_updated_rows);
END;
/
• Using explicit cursor write a program to retrieve the customer name and address
from CUSTOMER table.
DECLARE
-- Declare variables to store customer data
v_customer_name Customer.NAME%TYPE;
v_customer_address Customer.ADDRESS%TYPE;
-- Declare an explicit cursor
CURSOR customer_cursor IS
SELECT NAME, ADDRESS
FROM Customer;
BEGIN
-- Open the cursor
OPEN customer_cursor;
-- Fetch data from the cursor
LOOP
FETCH customer_cursor INTO v_customer_name, v_customer_address;
-- Exit the loop if no more rows
EXIT WHEN customer_cursor%NOTFOUND;
-- Display customer name and address
DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name || ', Address: '
|| v_customer_address);
END LOOP;
-- Close the cursor
CLOSE customer_cursor;
END;
/
