NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
Q1. Create the following table Employees:-
QUERY:-
CREATE TABLE Employees
(
emp_id number PRIMARY KEY,
emp_name varchar2(20),
dept_id varchar2(10), salary
decimal(10,2), joining_date
date
);
Column Name Datatype Size
emp_id (PRIMARY KEY) NUMBER 10
emp_name VARCHAR2 20
dept_id VARCHAR2 10
salary DECIMAL 10,2
joining_date DATE
Q2. Insert the following data in the Employees table:-
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
QUERY:-
INSERT INTO Employees (emp_id ,emp_name,dept_id, salary, joining_date)
VALUES(1, 'Alice', 10, 5000,'10-Jan-2020');
INSERT INTO Employees (emp_id ,emp_name,dept_id, salary, joining_date)
VALUES (2, 'Bob' , 20, 6000, '15-Mаг-2019');
INSERT INTO Employees (emp_id, emp_name, dept_id, salary, joining_date)
VALUES (3,'Charlie',30, 7000, '01-July-2021') ;
INSERT INTO Employees (emp_id, emp_name, dept_id, salary, joining_date)
VALUES (4, 'David',nul1,4000,'20-May-2022');
INSERT INTO Employees (emp_id, emp_name, dept_id, salary, joining_date)
VALUES ( 5, 'Emma', 10, 8000, '22-Aug-2018') ;
INSERT INTO Employees (emp_id, emp_name, dept_id, salary, joining_date)
VALUES(6, 'Frank', 20,5500, '30-Sep-2020');
INSERT INTO Employees (emp_id, emp_name, dept_id, salary, joining_date)
VALUES(7 ,'Grace',null,3000, '01-Jan-2023');
Q3. Create the following table Departments :-
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
QUERY:-
CREATE TABLE Departments
(
dept_id number (10) PRIMARY KEY, dept_name
varchar2(20)
);
Column Name Datatype Size
dept_id (PRIMARY KEY) NUMBER 10
dept_name VARCHAR2 20
Q4. Insert the following data in the Departments table :-
QUERY:-
INSERT INTO Departments (dept_id,dept_name)
VALUES (10,'HR');
INSERT INTO Departments (dept_id, dept_name)
VALUES (20, 'IT');
INSERT INTO Departments (dept_id ,dept_name)
VALUES (30, 'Finance');
INSERT INTO Departments (dept_id ,dept_name)
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
VALUES (40, 'Marketing');
Q5. Based on above two tables answer the following
Questionaries: -
i)Display all employee names in uppercase along with their salaries.
QUERY:-
SELECT UPPER (emp_name )
AS UPPERCASE_name, salary
FROM Employees;
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
ii)
Retrieve the total number of employees.
QUERY:-
SELECT COUNT (emp_id)
AS total_employees
FROM Employees;
Calculate the average salary of all employees.
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
iii
)
QUERY:-
SELECT AVG(salary)
FROM Employees;
Count the number of employees in each department.
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
iv
)
QUERY:-
SELECT COUNT(emp_id)
AS TOTAL emp, dept_name
FROM Employees join Departments
ON Employees.dept_id=Departments.dept_id
GROUP BY Departments.dept_id;
Calculate the total salary paid in each department.
QUERY:-
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
v)
SELECT dept_id,
SUM(salary) AS total_salary
FROM Employees
GROUP BY dept_id;
Display the length of each employee's name.
QUERY:-
SELECT emp_name,
LENGTH (emp_name)
AS lengthOFemp_name
FROM Employees;
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
vi
)
Display the first three characters of each employee's name.
QUERY:-
SELECT SUBSTRING(emp_name,1,3)
AS Short_name
FROM Employees;
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
vi
i)
List all employees who joined after January 1, 2020.
QUERY:-
SELECT *
FROM Employees
WHERE joining_date > '2020-01-01';
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
vi
ii)
Find the department name and total salary paid in that department
using LEFT JOIN.
QUERY:-
SELECT d. dept_name, sum
(e.salary) as total_salary
FROM Departments as d
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
ix
)
LEFT JOIN Employees as e
on d.dept_id=e.dept_id
GROUP BY d.dept_name;
Calculate the total salary paid to employees, rounded to two decimal
places.
QUERY:-
SELECT ROUND
(SUM(salary), 2)
AS total_salary
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
x)
FROM Employees;
COURSE-BCA (hons) WITH AI AND DS
NAME-Divyanshu Sharma ROLL NO - 35 SECTION-D
COURSE-BCA (hons) WITH AI AND DS
