— Table: Department
CREATE TABLE Department (
Dno NUMBER(4) PRIMARY KEY,
Dname VARCHAR2(10) NOT NULL
);
— Table: Employee
CREATE TABLE Employee (
Eno VARCHAR2(5) CONSTRAINT pk_Employee_Eno PRIMARY KEY CHECK (Eno NOT LIKE ‘e%’),
Ename VARCHAR2(10) NOT NULL,
Salary NUMBER(7,2) CHECK (Salary >= 5000 AND Salary <= 30000),
Join_date DATE,
Birth_date DATE,
Dno NUMBER(4),
Address VARCHAR2(20),
Manager_id VARCHAR2(5) CHECK (Manager_id NOT LIKE ‘e%’),
CONSTRAINT fk_Employee_Dno FOREIGN KEY (Dno) REFERENCES Department(Dno)
);
INSERT INTO Department VALUES (101, ‘HR’);
INSERT INTO Department VALUES (102, ‘IT’);
select * from Department;
INSERT INTO Employee
VALUES (‘10001’, ‘John Doe’, 20000, TO_DATE(‘2023-01-15’, ‘YYYY-MM-DD’), TO_DATE(‘1990-05-20’, ‘YYYY-MM-DD’), 101, ‘123 Main St’, null);
INSERT INTO Employee
VALUES (‘10002’, ‘Jane Smith’, 25000, TO_DATE(‘2022-11-10’, ‘YYYY-MM-DD’), TO_DATE(‘1992-08-07’, ‘YYYY-MM-DD’), 102, ‘456 Elm St’, ‘10001’);
INSERT INTO Employee
VALUES (‘10003’, ‘Jill Smith’, 15000, TO_DATE(‘2020-08-16’, ‘YYYY-MM-DD’), TO_DATE(‘1991-08-30’, ‘YYYY-MM-DD’), 102, ‘456 Mlm St’, ‘10001’);
select * from Employee;
–a) Find the number of employees in each department with department name:
SELECT Department.Dname, COUNT(Employee.Eno) AS EmployeeCount
FROM Department
LEFT JOIN Employee ON Department.Dno = Employee.Dno
GROUP BY Department.Dname;
–b) Find the employees who earn the highest salary in each department:
SELECT D.Dname, E.Ename, E.Salary
FROM Employee E
JOIN (
SELECT Dno, MAX(Salary) AS MaxSalary
FROM Employee
GROUP BY Dno
) MaxSalaries ON E.Dno = MaxSalaries.Dno AND E.Salary = MaxSalaries.MaxSalary
JOIN Department D ON E.Dno = D.Dno;
–c) Find the name of the employees who have the second highest salary:
SELECT Ename, Salary
FROM Employee
WHERE Salary = (
SELECT MAX(Salary)
FROM Employee
WHERE Salary < (
SELECT MAX(Salary)
FROM Employee
)
);
–d) Find the name and salary of the employee with the name and salary of their manager:
SELECT E.Ename AS EmployeeName, E.Salary AS EmployeeSalary,
M.Ename AS ManagerName, M.Salary AS ManagerSalary
FROM Employee E
JOIN Employee M ON E.Manager_id = M.Eno;
–e) Find the name of employees whose joining month and birth month are the same:
SELECT Ename
FROM Employee
WHERE TO_CHAR(Join_date, ‘MM’) = TO_CHAR(Birth_date, ‘MM’);
