Set-13

1. PL/SQL Program to demonstrate Strings:
DECLARE
    str1 VARCHAR2(50) := 'Hello';
    str2 VARCHAR2(50) := 'World';
    concatenated_str VARCHAR2(100);
BEGIN
    -- Concatenation
    concatenated_str := str1 || ' ' || str2;
    DBMS_OUTPUT.PUT_LINE('Concatenated String: ' || concatenated_str);

    -- Length
    DBMS_OUTPUT.PUT_LINE('Length of str1: ' || LENGTH(str1));

    -- Substring
    DBMS_OUTPUT.PUT_LINE('Substring of concatenated_str: ' || SUBSTR(concatenated_str, 4, 5));

    -- Uppercase
    DBMS_OUTPUT.PUT_LINE('Uppercase of str1: ' || UPPER(str1));

    -- Lowercase
    DBMS_OUTPUT.PUT_LINE('Lowercase of str2: ' || LOWER(str2));
END;

2. DML Commands:
//INSERT: Adds new records to a table.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
//UPDATE: Modifies existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
//DELETE: Removes existing records from a table.
DELETE FROM table_name
WHERE condition;
//SELECT: Retrieves data from one or more tables.
SELECT column1, column2, ...
FROM table_name
WHERE condition;

3. Solutions for the provided relations and queries:

-- i. Create the given relations and insert at least 5 records into each relation
CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    ProjectID NUMBER,
    Salary NUMBER,
    CONSTRAINT fk_projects FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);

CREATE TABLE Projects (
    ProjectID NUMBER PRIMARY KEY,
    ProjectName VARCHAR2(50),
    StartDate DATE,
    EndDate DATE,
    ManagerID NUMBER,
    CONSTRAINT fk_employees FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

-- ii. Find projects managed by employees in a specific department. (using nested queries)
SELECT * 
FROM Projects 
WHERE ManagerID IN (
    SELECT EmployeeID 
    FROM Employees 
    WHERE DepartmentID = 'specific_department_id'
);

-- iii. Find projects where the manager's salary is higher than the overall average salary.
SELECT * 
FROM Projects 
WHERE ManagerID IN (
    SELECT EmployeeID 
    FROM Employees 
    WHERE Salary > (SELECT AVG(Salary) FROM Employees)
);

-- iv. List all employees their names concatenating both FirstName & LastName
SELECT EmployeeID, FirstName || ' ' || LastName AS FullName
FROM Employees;

-- v. List employee First names contains at least 5 characters.
SELECT * 
FROM Employees 
WHERE LENGTH(FirstName) >= 5;

-- vi. Reallocate to another project whose salary is greater than 20K.
UPDATE Employees 
SET ProjectID = 'new_project_id' 
WHERE Salary > 20000;