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
);
