Set-14

1. PL/SQL Program to find if a given number is even or odd:
DECLARE
    num NUMBER := 10; -- Change the number here
BEGIN
    IF MOD(num, 2) = 0 THEN
        DBMS_OUTPUT.PUT_LINE(num || ' is even');
    ELSE
        DBMS_OUTPUT.PUT_LINE(num || ' is odd');
    END IF;
END;

2. Examples of creating a table with NULL, NOT NULL, CHECK constraints:

-- Table with NULL constraint
CREATE TABLE example_null (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50) NULL
);

-- Table with NOT NULL constraint
CREATE TABLE example_not_null (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL
);

-- Table with CHECK constraint
CREATE TABLE example_check (
    id NUMBER PRIMARY KEY,
    age NUMBER CHECK (age >= 18)
);


3. Solutions for the provided relations and queries:
-- Create Employees table
CREATE TABLE Employees (
    Employee_ID NUMBER PRIMARY KEY,
    Emp_Name VARCHAR2(50),
    Skill_ID NUMBER,
    Salary NUMBER,
    CONSTRAINT fk_skills FOREIGN KEY (Skill_ID) REFERENCES Skills(SkillID)
);
-- Create Skills table
CREATE TABLE Skills (
    SkillID NUMBER PRIMARY KEY,
    SkillName VARCHAR2(50),
    Description VARCHAR2(100)
);
-- Insert sample records into Employees table
INSERT INTO Employees (Employee_ID, Emp_Name, Skill_ID, Salary) VALUES (1, 'John', 1, 5000);
INSERT INTO Employees (Employee_ID, Emp_Name, Skill_ID, Salary) VALUES (2, 'Alice', 2, 6000);
INSERT INTO Employees (Employee_ID, Emp_Name, Skill_ID, Salary) VALUES (3, 'Bob', 1, 5500);
INSERT INTO Employees (Employee_ID, Emp_Name, Skill_ID, Salary) VALUES (4, 'Emma', 3, 7000);
INSERT INTO Employees (Employee_ID, Emp_Name, Skill_ID, Salary) VALUES (5, 'Mike', NULL, 4500);

-- Insert sample records into Skills table
INSERT INTO Skills (SkillID, SkillName, Description) VALUES (1, 'Java', 'Programming language');
INSERT INTO Skills (SkillID, SkillName, Description) VALUES (2, 'SQL', 'Database querying language');
INSERT INTO Skills (SkillID, SkillName, Description) VALUES (3, 'Python', 'High-level programming language');

-- ii. Find employees who have not acquired any skills
SELECT * FROM Employees WHERE Skill_ID IS NULL;

-- iii. Find employees with the highest salary
SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);

-- iv. Find the average salary of employees in each department
-- Assuming there's a department_id column in the Employees table
SELECT department_id, AVG(Salary) AS Avg_Salary FROM Employees GROUP BY department_id;

-- v. Update the salary of an employee
UPDATE Employees SET Salary = 6000 WHERE Employee_ID = 1;

-- vi. Retrieve skills that are possessed by at least two employees
SELECT SkillID, SkillName FROM Skills WHERE SkillID IN (
    SELECT Skill_ID FROM Employees GROUP BY Skill_ID HAVING COUNT(*) >= 2
);