SQL Section: -
#create database
CREATE DATABASE CompanyDB;
USE CompanyDB; # use database
#create table Employee and insert values
CREATE TABLE Employee (
empId INT PRIMARY KEY,
EmpName VARCHAR(50),
depId CHAR(1),
joiningDate DATE
);
INSERT INTO Employee (empId, EmpName, depId, joiningDate) VALUES
(1, 'Rahul', 'a', '2024-01-04'),
(2, 'Saransh', 'b', '2024-03-10'),
(3, 'Ritik', 'c', '2024-04-03'),
(4, 'Sneha', 'c', '2024-05-01'),
(5, 'Rahul', 'a', '2024-08-15');
#create table Department and insert values
CREATE TABLE Department (
depId CHAR(1) PRIMARY KEY,
depName VARCHAR(50)
);
INSERT INTO Department (depId, depName) VALUES
('a', 'Engineering'),
('b', 'Product'),
('c', 'Sales');
#create table Salary and insert values
CREATE TABLE Salary (
empId INT,
salary INT,
FOREIGN KEY (empId) REFERENCES Employee(empId)
);
INSERT INTO Salary (empId, salary) VALUES
(1, 100000),
(2, 150000),
(3, 75000),
(4, 110000),
(5, 100000)
[Link] a SQL query to find the department name that has the highest total salary
among all departments.
SELECT [Link],
SUM([Link]) AS Total_Salary
FROM Employee e
JOIN Department d ON [Link] = [Link]
JOIN Salary s ON [Link] = [Link]
GROUP BY [Link]
ORDER BY Total_Salary DESC
LIMIT 1;
[Link] a SQL query to calculate the total salary earned by each employee in the
year 2024. Display the results in alphabetical order of employee names.
SELECT [Link],
([Link] * 12) AS Total_Salary_2024
FROM Employee e
JOIN Salary s ON [Link] = [Link]
WHERE YEAR([Link]) <= 2024
ORDER BY [Link] ASC;
[Link] the monthly salary data, write a query to display each employee’s salary
along with ROW_NUMBER(), RANK(), and DENSE_RANK() based on salary in
descending order.
SELECT
[Link],
[Link],
ROW_NUMBER() OVER (ORDER BY [Link] DESC) AS RowNumber,
RANK() OVER (ORDER BY [Link] DESC) AS RankNumber,
DENSE_RANK() OVER (ORDER BY [Link] DESC) AS DenseRank
FROM Employee e
JOIN Salary s ON [Link] = [Link];
PYTHON Section: -
assignment [Link]
[Link]
usp=sharing