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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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; |
1. How to get an employee record with max salary?
|
1 |
SELECT * FROM `tblemployes` where empSalary=(SELECT max(empSalary) from tblemployes); |
2. How to get max salary in tblemployes ?
|
1 |
SELECT max(empSalary) from tblemployes; |
3. How to get the 2nd highest salary in tblemployes ?
|
1 2 3 |
SELECT max(empSalary) from tblemployes where empSalary not in (SELECT max(empSalary) from tblemployes) |
4. How to get emp name, highest salary, and department?
|
1 2 3 |
SELECT empName,empDept,empSalary from tblemployes where empSalary in (SELECT max(empSalary) from tblemployes) |
5. How to get the highest salary in each department?
|
1 2 3 |
SELECT empDept,empSalary from tblemployes where empSalary in (SELECT max(empSalary) from tblemployes group by empDept) |
6. How to get the number of employees in each department?
|
1 2 |
SELECT empDept,COUNT(empName) from tblemployes group by empDept |
Here are a few hand-picked articles for you to read next:
