bank

CREATE TABLE Customer (
CustomerSSNID NUMBER PRIMARY KEY,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
Email VARCHAR2(100),
DateOfBirth DATE,
Address VARCHAR2(100),
ContactNumber VARCHAR2(10)
);
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1001, 'Rahul', 'Sharma', '[email protected]', TO_DATE('1990-01-01', 'YYYY-MM-DD'), '123 Main St, Delhi', '9876543210');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1002, 'Anita', 'Verma', '[email protected]', TO_DATE('1985-02-15', 'YYYY-MM-DD'), '456 Market St, Mumbai', '8765432109');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1003, 'Suresh', 'Patel', '[email protected]', TO_DATE('1992-03-20', 'YYYY-MM-DD'), '789 Industrial Area, Bangalore', '7654321098');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1004, 'Priya', 'Kumar', '[email protected]', TO_DATE('1988-04-25', 'YYYY-MM-DD'), '321 Park Lane, Kolkata', '6543210987');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1005, 'Vijay', 'Reddy', '[email protected]', TO_DATE('1991-05-30', 'YYYY-MM-DD'), '654 Lake Rd, Hyderabad', '5432109876');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1006, 'Neha', 'Mishra', '[email protected]', TO_DATE('1993-06-10', 'YYYY-MM-DD'), '987 Green St, Pune', '4321098765');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1007, 'Ravi', 'Gupta', '[email protected]', TO_DATE('1987-07-15', 'YYYY-MM-DD'), '159 Silver St, Chennai', '3210987654');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1008, 'Sunita', 'Desai', '[email protected]', TO_DATE('1989-08-20', 'YYYY-MM-DD'), '753 Gold St, Ahmedabad', '2109876543');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1009, 'Amit', 'Naik', '[email protected]', TO_DATE('1986-09-25', 'YYYY-MM-DD'), '852 Diamond St, Surat', '1098765432');
INSERT INTO Customer (CustomerSSNID, FirstName, LastName, Email, DateOfBirth, Address, ContactNumber) VALUES
(1010, 'Meera', 'Kapoor', '[email protected]', TO_DATE('1994-10-30', 'YYYY-MM-DD'), '951 Pearl St, Chandigarh', '0198765432');

select * from customer;

-- FOR RETERIVE CUSTOMER INFROMATION

SELECT
CustomerSSNID AS "Customer ID",
FirstName AS "First Name",
LastName AS "Last Name",
Email AS "Email",
DateOfBirth AS "Date of Birth",
Address AS "Address",
ContactNumber AS "Contact Number"
FROM
Customer
WHERE
CustomerSSNID = 1001;

-- FOR UPDATE CUTOMER INFORMATION

UPDATE Customer
SET
Email = '[email protected]',
Address = '456 New Address, City',
ContactNumber = '1234567890'
WHERE
CustomerSSNID = 1001;

-- CREATE AND INSERT DATA IN EMPLOYEE TABLE WITH PL-SQL

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE Employee (
EmployeeID NUMBER PRIMARY KEY,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
Email VARCHAR2(100),
ContactNumber VARCHAR2(10),
Designation VARCHAR2(50),
Salary NUMBER(10, 2)
)';
END;
/

select * from Employee;

BEGIN
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(1, 'Rohit', 'Kumar', '[email protected]', '9876543210', 'Clerk', 25000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(2, 'Anjali', 'Sharma', '[email protected]', '8765432109', 'Manager', 75000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(3, 'Vikram', 'Singh', '[email protected]', '7654321098', 'Accountant', 55000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(4, 'Priya', 'Verma', '[email protected]', '6543210987', 'Clerk', 26000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(5, 'Amit', 'Patel', '[email protected]', '5432109876', 'Manager', 80000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(6, 'Suresh', 'Naik', '[email protected]', '4321098765', 'Accountant', 58000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(7, 'Neha', 'Gupta', '[email protected]', '3210987654', 'Clerk', 25500);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(8, 'Ravi', 'Desai', '[email protected]', '2109876543', 'Manager', 78000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(9, 'Sunita', 'Joshi', '[email protected]', '1098765432', 'Accountant', 60000);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, ContactNumber, Designation, Salary) VALUES
(10, 'Rajesh', 'Mehta', '[email protected]', '0198765432', 'Clerk', 24000);

COMMIT;

END;
/

-- UPDATE SALARY WITH DESIGNATION WISE

BEGIN
UPDATE Employee
SET Salary = Salary + 1000
WHERE Designation = 'Clerk';

COMMIT;

FOR rec IN (SELECT FirstName || ' ' || LastName AS Name, Designation, Salary
            FROM Employee
            WHERE Designation = 'Clerk') LOOP
    DBMS_OUTPUT.PUT_LINE('Name: ' || rec.Name || ', Designation: ' || rec.Designation || ', New Salary: ' || rec.Salary);
END LOOP;

END;
/

BEGIN
-- Update salary with 2% commission for employees with salary >= 50000
UPDATE Employee
SET Salary = Salary + (Salary * 0.02)
WHERE Salary >= 50000;

-- Commit the changes
COMMIT;

-- Display the updated records
FOR rec IN (SELECT FirstName || ' ' || LastName AS Name, Designation, Salary AS NewSalary
            FROM Employee
            WHERE Salary >= 50000) LOOP
    DBMS_OUTPUT.PUT_LINE('Name: ' || rec.Name || ', Designation: ' || rec.Designation || ', New Salary: ' || rec.NewSalary);
END LOOP;

END;
/