Abhay 51048 Dbms Ass 04
Uploaded by
Abhay PawarAbhay 51048 Dbms Ass 04
Uploaded by
Abhay PawarProgressive Education Society's
Modern College of Engineering
MCA Department
Subject: 410917: Database Management System Laboratory
____________________________________________________________________________________________
Class: FY MCA Div: A Roll Number:51048
Name:Abhay Shankar Pawar Assignment No:04 Date of Implementation: 29/02/2024
_____________________________________________________________________________________________
Implementation of different types of function with suitable examples
Number function
Aggregate Function
Character Function
Conversion Function
Date Function
-- Create Employee table
CREATE TABLE Employee (
EmployeeID NUMBER PRIMARY KEY,
EmployeeName VARCHAR2(100),
Salary NUMBER,
Commission NUMBER,
HireDate DATE,
ManagerID NUMBER,
DepartmentID NUMBER
);
DESCRIBE Employee;
--Insert data in employee data
select * from Employee;
-- Create Department table
CREATE TABLE Department (
DepartmentID NUMBER PRIMARY KEY,
DepartmentName VARCHAR2(100)
);
DESCRIBE Department;
-- Insert sample data into Department table
INSERT INTO Department
VALUES
(10, 'HR');
INSERT INTO Department
VALUES
(20, 'Finance');
INSERT INTO Department
VALUES
(30, 'IT');
select * from Department;
1. Find out details of employees where commission is greater than 7% of salary.
SELECT *
FROM Employee
WHERE (Commission / Salary) > 0.07;
2. Select names of all employees whose name start with ‘S’.
SELECT EmployeeName
FROM Employee
WHERE EmployeeName LIKE 'S%';
3. Display the department name which has more than 3 employees in it.
SELECT d.DepartmentName
FROM Department d
JOIN Employee e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName
HAVING COUNT(*) > 2;
4. Write a query which concatenates the employee number, his name and manager
number. Display the output with spaces in between the columns.
SELECT EmployeeID || ' ' || EmployeeName || ' ' || ManagerID AS Concatenated_Info
FROM Employee;
5. List names of people who have salary less than the average salary for department 20.
SELECT EmployeeName
FROM Employee
WHERE Salary < (SELECT AVG(Salary) FROM Employee WHERE DepartmentID = 20);
6. Display details of all employees who were hired earliest and latest
SELECT *
FROM Employee
WHERE HireDate IN (SELECT MIN(HireDate) FROM Employee UNION SELECT MAX(HireDate) FROM
Employee);
7. Find out the department in which the maximum number of employees works
SELECT DepartmentID
FROM (
SELECT DepartmentID, RANK() OVER (ORDER BY COUNT(*) DESC) AS RankEmp
FROM Employee
GROUP BY DepartmentID
)
WHERE RankEmp = 1;
8.Display information about employees who have the maximum number of employees
reporting to him
SELECT *
FROM Employee
WHERE EmployeeID IN (
SELECT ManagerID
FROM (
SELECT ManagerID, RANK() OVER (ORDER BY COUNT(*) DESC) AS RankEmp
FROM Employee
GROUP BY ManagerID
)
WHERE RankEmp = 1
);
9. Find the day of the week, time of the day, month and centuryon which SMITH joined.
SELECT TO_CHAR(HireDate, 'Day'), TO_CHAR(HireDate, 'HH:MI:SS AM'), TO_CHAR(HireDate,
'Month'), TO_CHAR(HireDate, 'CC')
FROM Employee
WHERE EmployeeName = 'Smith';
10. Find number of months the PRESIDENT has worked for the company. Round the
months to the nearest whole number.
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, HireDate))
FROM Employee
WHERE EmployeeName = 'President';
