DBMS Code

Página 1

*********Creating a Table with the help of Default Keyword:-

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

employee_name VARCHAR(50),

department VARCHAR(50) DEFAULT 'Unknown'

);

***********Inserting the value using DEFAULT values:-

-- No department specified, so it will default to 'Unknown'

INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John Doe');

-- Specifying department explicitly

INSERT INTO employees (employee_id, employee_name, department) VALUES (2, 'Jane Smith', 'HR');

**********Altering a Table to add the VAlues using the DEFAULT values:-

-- Add a default value to an existing column

ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'Unknown';

**********Removing the DEFAULT Values:-

-- Remove default value from a column

ALTER TABLE employees ALTER COLUMN department DROP DEFAULT;

**********Viewing the DEFAULT values:-

-- Querying system tables to view default value constraint

SELECT column_name, column_default

FROM information_schema.columns

WHERE table_name = 'employees' AND column_name = 'department';

Página 2

# If you intend to set the same fee for all students, you can use a simple UPDATE statement like this:

BEGIN

EXECUTE IMMEDIATE 'UPDATE student2 SET fees = :feeAmount';

END;

/

# Replace :feeAmount with the desired fee amount. For example, if the fee is 5000 for every student, the code would be:

BEGIN

EXECUTE IMMEDIATE 'UPDATE student2 SET fees = 5000';

END;

/

#If the fee varies from student to student, you'll need a more detailed approach. Here are two common scenarios and how to handle them:

1. If the fees are determined by some condition (e.g., class, program):

You can use conditional logic in your UPDATE statement. For example, if students in class '616 B' have a different fee than those in '816 A':

BEGIN

EXECUTE IMMEDIATE 'UPDATE student2 SET fees = CASE WHEN class = ''616 B'' THEN 6000 WHEN class = ''816 A'' THEN 7000 ELSE 5000 END';

END;

/

# Updating specific column using WHERE Clause

BEGIN

EXECUTE IMMEDIATE 'UPDATE student2 SET fees = 6000 WHERE id = 1001';

EXECUTE IMMEDIATE 'UPDATE student2 SET fees = 7000 WHERE id = 1002';

-- And so on for each specific case

END;

/

****************---------------------------------*****************

// Creating a Procedure and with the help of procedure entering data into it --- 18/04/2024

CREATE TABLE employee_9 (

EMP_ID NUMBER PRIMARY KEY,

emp_name VARCHAR2(100),

emp_Number VARCHAR2(20),

emp_Salary NUMBER,

emp_joining_date DATE,

EMP_mailID VARCHAR2(100)

);

INSERT INTO employee_9 (EMP_ID, emp_name, emp_Number, emp_Salary ,emp_joining_date, EMP_mailID) VALUES (1001, 'Shiven SHarma', 'E123', 97000 ,TO_DATE('2024-04-18', 'YYYY-MM-DD'), 'sharmashiven997@gmail.com');

INSERT INTO employee_9 (EMP_ID, emp_name, emp_Number, emp_joining_date, EMP_mailID) VALUES (1002, 'Jatin Ghanghas', 'E456', 89000 ,TO_DATE('2024-04-18', 'YYYY-MM-DD'), 'ghanghasjatin13@gmail.com.com');

INSERT INTO employee_9 (EMP_ID , emp_name , emp_Number , emp_Salary ,emp_joining_date , EMP_mailID) VALUES (1003 'Arnav Kumar', 'E534', 85000 ,TO_DATE('2024-03-16', 'YYYY-MM-DD'), 'kumararnav99@gmail.com');

INSERT INTO employee_9 (EMP_ID, emp_name, emp_Number, emp_joining_date, EMP_mailID) VALUES (1004, 'Sahil Sharma', 'E656', 80000 ,TO_DATE('2024-04-18', 'YYYY-MM-DD'), 'sharmasahil17@gmail.com.com');

CREATE OR REPLACE PROCEDURE add_employee(

p_emp_id IN NUMBER,

p_emp_name IN VARCHAR2,

p_emp_number IN VARCHAR2,

p_emp_salary IN NUMBER,

p_emp_joining_date IN DATE,

p_emp_mail_id IN VARCHAR2

)

IS

BEGIN

INSERT INTO employee_9 (EMP_ID, emp_name, emp_Number, emp_Salary, emp_joining_date, EMP_mailID)

VALUES (p_emp_id, p_emp_name, p_emp_number, p_emp_salary, p_emp_joining_date, p_emp_mail_id);

COMMIT;

DBMS_OUTPUT.PUT_LINE('Employee added successfully.');

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

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

END;

/

DBMS_OUTPUT.PUT_LINE('EMPID :'|| p_emp_id);

BEGIN

add_employee(1005, 'Sahil Phogat', 'E789', 95000, TO_DATE('2024-04-18', 'YYYY-MM-DD'),'phogatsahil98@gmail.com');

END;

/

Página 3

// Creating a new Table with the help of packages we will insert table into it and create the packages which help to gather the information of all the procdeures and the function in it.

-- Creating the Employee_10 table

CREATE TABLE Employee_10 (

employee_id NUMBER PRIMARY KEY,

employee_name VARCHAR2(100),

employee_salary NUMBER

);

-- Inserting sample data into the Employee_10 table

INSERT INTO Employee_10 (employee_id, employee_name, employee_salary)

VALUES (1, 'John Doe', 50000);

INSERT INTO Employee_10 (employee_id, employee_name, employee_salary)

VALUES (2, 'Jane Smith', 60000);

INSERT INTO Employee_10 (employee_id, employee_name, employee_salary)

VALUES (3, 'Michael Johnson', 55000);

-- Committing the changes

COMMIT;

-- Creating the package specification

CREATE OR REPLACE PACKAGE Employee_Package AS

PROCEDURE Insert_Employee(

p_id IN NUMBER,

p_name IN VARCHAR2,

p_salary IN NUMBER

);

END Employee_Package;

/

-- Creating the package body

CREATE OR REPLACE PACKAGE BODY Employee_Package AS

PROCEDURE Insert_Employee(

p_id IN NUMBER,

p_name IN VARCHAR2,

p_salary IN NUMBER

) AS

BEGIN

INSERT INTO Employee_10 (employee_id, employee_name, employee_salary)

VALUES (p_id, p_name, p_salary);

COMMIT;

END Insert_Employee;

END Employee_Package;

/

BEGIN

Employee_Package.Insert_Employee(1005, 'Sahil Phogat', 89000);

END;

/

-- Creating a PL/SQL script to print the contents of the Employee_10 table

SET SERVEROUTPUT ON; -- This enables the printing of output

DECLARE

CURSOR emp_cursor IS

SELECT * FROM Employee_10;

emp_record Employee_10%ROWTYPE;

BEGIN

-- Open the cursor

OPEN emp_cursor;

-- Fetch and print each record

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' || emp_record.employee_name || ', Salary: ' || emp_record.employee_salary);

END LOOP;

-- Close the cursor

CLOSE emp_cursor;

END;

/

Página 4

-- Creating Package

CREATE OR REPLACE PACKAGE Employee_Package AS

PROCEDURE AddEmployee(p_employee_id NUMBER, p_name VARCHAR2, p_salary NUMBER);

PROCEDURE UpdateSalary(p_employee_id NUMBER, p_new_salary NUMBER);

PROCEDURE GetEmployeeDetails(p_employee_id NUMBER, o_name OUT VARCHAR2, o_salary OUT NUMBER);

END Employee_Package;

/

-- Creating Package Body

CREATE OR REPLACE PACKAGE BODY Employee_Package AS

PROCEDURE AddEmployee(p_employee_id NUMBER, p_name VARCHAR2, p_salary NUMBER) AS

BEGIN

INSERT INTO employees(employee_id, name, salary) VALUES (p_employee_id, p_name, p_salary);

COMMIT;

END AddEmployee;

PROCEDURE UpdateSalary(p_employee_id NUMBER, p_new_salary NUMBER) AS

BEGIN

UPDATE employees

SET salary = p_new_salary

WHERE employee_id = p_employee_id;

COMMIT;

END UpdateSalary;

-- Getting Feasible Output :-

PROCEDURE GetEmployeeDetails(p_employee_id NUMBER, o_name OUT VARCHAR2, o_salary OUT NUMBER) AS

BEGIN

SELECT name, salary INTO o_name, o_salary FROM employees WHERE employee_id = p_employee_id;

END GetEmployeeDetails;

END Employee_Package;

/

-- Creating Table

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

name VARCHAR2(100),

salary NUMBER

);

-- inserting Values

INSERT INTO employees (employee_id, name, salary) VALUES (1001, 'Jatin Ghanghas', 90000);

INSERT INTO employees (employee_id, name, salary) VALUES (1002, 'Shiven Sharma', 89000);

INSERT INTO employees (employee_id, name, salary) VALUES (1003, 'Arnav Kumar', 79000);

INSERT INTO employees (employee_id, name, salary) VALUES (1004, 'Sahil Sharma', 69000);

-- Updating Values :

DECLARE

v_name VARCHAR2(100);

v_salary NUMBER;

BEGIN

Employee_Package.AddEmployee(1006, 'Phogat Sahil', 95000);

/*Employee_Package.GetEmployeeDetails(3, v_name, v_salary);

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name || ', Salary: ' || v_salary);

*/

Employee_Package.UpdateSalary(1005, 89000);

Employee_Package.GetEmployeeDetails(1005, v_name, v_salary);

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name || ', Salary: ' || v_salary);

END;

/

-- Inserting new Entries into Table

INSERT INTO employees (employee_id, name, salary) VALUES (1007, 'Anshul Sheoran', 80000);