Here we write a SQL Query for retrieve Max and Min salary with employees name in MySql.
First we create a sample table with data.
--
-- Table structure for table `employees`
--
CREATE TABLE `employees` (
`employee_id` int(11) NOT NULL,
`employee_name` varchar(100) NOT NULL,
`salary` int(11) NOT NULL,
`department` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `employees`
--
INSERT INTO `employees` (`employee_id`, `employee_name`, `salary`, `department`) VALUES
(1, 'Mohan', 10000, 'Sales'),
(2, 'Manoj', 9000, 'Technology'),
(3, 'Manveer', 8000, 'Marketing'),
(4, 'Manpreet', 7000, 'Human Resources'),
(5, 'Manmohan', 6000, 'Administration'),
(6, 'Ravi', 10000, 'Sales'),
(7, 'Rohan', 9000, 'Technology'),
(8, 'Ravindra', 8000, 'Marketing'),
(9, 'Ranveer', 7000, 'Human Resources'),
(10, 'Rahul', 6000, 'Administration'),
(11, 'pappu', 5000, 'Cleaning');
--
-- Indexes for dumped tables
-- Indexes for table `employees`
--
ALTER TABLE `employees`
ADD PRIMARY KEY (`employee_id`);
--
-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;
Here we use two way to achieve the result.
First, We use Union all with IN clause.
SELECT `employee_name`,`salary` FROM employees WHERE `salary`
IN ( SELECT MAX(`salary`)
FROM employees UNION ALL SELECT MIN(`salary`) FROM employees);
After run the query, Below screen show the result which we want to achieve.
Second way to achieve desire result.
SELECT `employee_name`,`salary` FROM employees WHERE `salary` IN
((SELECT MAX(salary) FROM employees) , (SELECT MIN(salary) FROM employees));
After run the query, Below screen show the result which we want to achieve.
Above we demonstrate that we can use these two query
1). SELECT `employee_name`, `salary` FROM employees WHERE `salary` IN ( SELECT MAX(`salary`) FROM employees UNION ALL SELECT MIN(`salary`) FROM employees);
2). SELECT `employee_name`,`salary` FROM employees WHERE `salary` IN
((SELECT MAX(salary) FROM employees) , (SELECT MIN(salary) FROM employees));
to find Max And Min Salary Of Employees With Employee Name In MySql.
I hope this article helps you.
Thanks for visiting Inflay.com .