Program17:
MYSQL QUERIES
Managing employee and salary information is essential. This set of SQL create
and manipulate databases to store and manage this data efficiently. You will be
creating tables, inserting and updating data, and running queries to extract
useful information.
Employee table → EmpID, EmpName, Designation, DoJ, salary.
Salary table→ SalaryID, EmpID, Net-salary, TA, DA, PF, and adding one field Gross-
salary.
Then make the following Queries:
❖ Create a new database, use this database.
❖ Creating Employee and Salary tables.
❖ Inserting Data into the tables.
❖ Add a Gross-salary Column to the Salary Table.
❖ Write an SQL query to display all records Employee and Salary tables.
❖ To display all data from the Employee table in descending order by
netsalary.
❖ Write an SQL query to find all employees whose names start with the
letter 'A' and display their EmpName, Designation, and Salary.
❖ Write an SQL query to find all employees whose Salary is between 50,000 and
70,000. Display their EmpID, EmpName, and Salary.
❖ Write an SQL query to find all employees who have a salary greater than
60,000 and a designation of "Manager." Display their EmpID, EmpName, and
Designation.
❖ Find the Maximum Gross Salary for Each Designation.
❖ Calculate the Total Gross Salary for Each Designation.
❖ List All Employees with a Gross Salary Greater Than 50,000
Aim:
To create and manage employee and salary data efficiently using SQL queries,
including table creation, data insertion, and data retrieval with various
operators for filtering and analysis. This helps in understanding key SQL
concepts like JOIN, BETWEEN, logical, and relational operators.
➢ Create a New Database and Use It.
CREATE DATABASE SchoolDB;
USE SchoolDB;
➢ Create Employee and Salary Tables.
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR (50),
Designation VARCHAR (50),
DOJ DATE,
Salary FLOAT);
CREATE TABLE Salary (
SalaryID INT PRIMARY KEY,
EmpID INT,
NetSalary FLOAT,
TA FLOAT,
DA FLOAT,
PF FLOAT,
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);
➢ Insert Data into the Tables.
INSERT INTO Employee (EmpID, EmpName,
Designation, DoJ, Salary) VALUES (101, 'John Doe',
'Manager', '2022-01-01', 75000.00);
(Insert at least five records)
INSERT INTO Salary (SalaryID,EmpID, NetSalary, TA,
DA, PF) VALUES (001,101, 60000.00, 5000.00, 3000.00,
2000.00);
(Insert at least five records)
➢ Add a Gross-salary Column to the Salary Table.
ALTER TABLE Salary ADD GrossSalary float;
➢ Update GrossSalary in Salary Table.
UPDATE Salary SET
GrossSalary=NetSalary+TA+DA+PF ;
➢ To display all records from tables.
❖ SELECT * FROM Employee;
❖ SELECT * FROM Salary;
❖ To display all data from the Employee table in descending order by
EmpName.
SELECT * FROM Employee ORDER BY EmpName DESC;
❖ To display all data from the Salary table in Ascending order by netsalary.
SELECT * FROM Salary ORDER BY NetSalary ASC;
❖ Find all employees whose names start with the letter 'A' and display their
EmpName, Designation, and Salary.
SELECT EmpName, Designation, Salary
FROM Employee
where EmpName like 'A%';
❖ Write an SQL query to find all employees whose Salary is between 50,000 and
70,000. Display their EmpID, EmpName, and Salary.
SELECT EmpID, EmpName, Salary
FROM Employee
WHERE Salary BETWEEN 50000 AND 70000;
❖ Write an SQL query to find all employees who have a salary greater than
60,000 and a designation of "Manager." Display their EmpID, EmpName, and
Designation.
SELECT EmpID, EmpName, Designation
FROM Employee
WHERE Salary > 60000 AND Designation = 'Manager';
❖ Find the Maximum Gross Salary for Each Designation.
SELECT E.Designation, MAX(S.GrossSalary) AS MaxGrossSalary
FROM Employee E JOIN Salary S ON E.EmpID = S.EmpID
GROUP BY E.Designation;
❖ Calculate the Total Gross Salary for Each Designation.
SELECT E.Designation, SUM(S.GrossSalary) AS TotalGrossSalary
FROM Employee E JOIN Salary S ON E.EmpID = S.EmpID
GROUP BY E.Designation;
❖ List All Employees with a Gross Salary Greater Than 50,000.
SELECT E.EmpName, S.GrossSalary
FROM Employee E JOIN Salary S ON E.EmpID = S.EmpID
WHERE S.GrossSalary > 50000;
Result:
Thus, the queries to create the tables and execute the sql
commands was done successfully.
