SQL Queries for Interview

Q1 SQL select total and split into success and failed

Table 1                Table 2                   
|leadid|Location|      |leadid|leadstatus|       
|---------------|      |-----------------|
|1     |Japan   |      |1     | Hired    |
|2     |China   |      |2     | Failed   |
|3     |Korea   |      |3     | Hired    |
|4     |Japan   |      |4     | Hired    |
|5     |Japan   |      |5     | Hired    |

|Location|Interview|Hired|Failed|
|-------------------------------|
|Japan   | 3       |3    |0     |
|Korea   | 1       |1    |0     |
|China   | 1       |0    |1     |

SELECT Location, COUNT(*) as Interview,
SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
FROM table1 
LEFT JOIN table2 ON table1.leadid = table2.leadid
            GROUP BY Location 
            ORDER BY Interview DESC

Select location,count(*) as Interview,
SUM(CASE WHEN (status='Hired')Then 1 Else 0 END) as Hired,
SUM(CASE WHEN(status='Failed') Then 1 Else 0 END) as Failed 
from loc inner join status on loc.leadid= status.leadid 
group by location;

Q 2 Second maximum salary 

Second maximum salary using sub query and IN clause 

mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN 

(SELECT max(salary) FROM Employee);


mysql> SELECT max(salary) FROM Employee WHERE salary < 

(SELECT max(salary) FROM Employee);


Second highest salary using TOP keyword of Sybase or SQL Server database


SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC)

 AS emp ORDER BY salary ASC

Second maximum salary using LIMIT keyword of MYSQL database

SELECT salary  FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2)

AS emp ORDER BY salary LIMIT 1;



 Q 3 Write an SQL query to clone a new table from another table.

 SELECT * INTO WorkerClone FROM Worker; (With data )
 SELECT * INTO WorkerClone FROM Worker WHERE 1 = 0; (Without data )
 CREATE TABLE WorkerClone LIKE Worker; (Without data )