PHPGurukul

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:

Anuj Kumar

This is Anuj Kumar. I’m a professional web developer with 4+ year experience. I write blogs in my free time. I love to learn new technologies and share with others.
I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials.
.

Recommended Tutorials for you


Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.