Common Sql Interview Questions and Answers

Create a SQL table tblemployes then try these queries
Structure of tblemployes table and insert some data into this table.

CREATE TABLE `tblemployes` (
  `id` int(11) NOT NULL,
  `empName` varchar(225) DEFAULT NULL,
  `empId` char(15) DEFAULT NULL,
  `empDept` varchar(255) DEFAULT NULL,
  `empSalary` char(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `tblemployes` (`id`, `empName`, `empId`, `empDept`, `empSalary`) VALUES
(1, 'Anuj Kumar', '10806121', 'IT', '54000'),
(2, 'Sanjeev Kumar', '1098761', 'Development', '60000'),
(3, 'Rahul Singh', '121331', 'HR', '42000'),
(4, 'John Doe', '313131', 'QA', '62000'),
(5, 'Sarita Pandey', '436436', 'Operations', '30000'),
(6, 'Garima', '858568', 'IT', '52000');
ALTER TABLE `tblemployes` ADD PRIMARY KEY (`id`);
ALTER TABLE `tblemployes`  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

tb lemployee
1.How to get a employee record with max salary ?

SELECT * FROM `tblemployes` where empSalary=(SELECT max(empSalary) from tblemployes);

2.How to get max salary  in tblemployes ?

SELECT max(empSalary) from tblemployes;

3.How to get 2nd highest salary  in tblemployes ?

SELECT max(empSalary)
from tblemployes
where empSalary not in (SELECT max(empSalary) from tblemployes)

4.How to get emp name,  highest salary and department ?

SELECT empName,empDept,empSalary
from tblemployes
where empSalary in (SELECT max(empSalary) from tblemployes)

5.How to get   highest salary of each department ?

SELECT empDept,empSalary
from tblemployes
where empSalary in (SELECT max(empSalary) from tblemployes group by empDept)

6.How to get   number of employee in each department ?

SELECT empDept,COUNT(empName)
from tblemployes group by empDept

Here are a few hand-picked articles for you to read next:

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Privacy & Cookies Policy