Frequently asked SQL queries in interview..

Join us on Facebook 

SQL Query to find second highest salary of Employee
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );


OR

SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);

Query to find Max Salary from each department.
SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

Inner and Outer Joins!..

SQL Query to display current date.
SQL has built in function called GetDate() which returns current timestamp.
SELECT GetDate();

SQL Query to check whether date passed to Query is date of given format or not.
SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly.
SELECT  ISDATE('1/08/13') AS "MM/DD/YY";

SQL Query to find  year from date.
SELECT YEAR(GETDATE()) as 'Year';

SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.
SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN ‘01/01/1960 ' AND ‘31/12/1975’  GROUP BY sex;

SQL Query to find employee whose Salary is equal or greater than 10000.
SELECT EmpName FROM  Employees WHERE  Salary>=10000;

SQL Query to find name of employee whose name Start with ‘H’
SELECT * FROM Employees WHERE EmpName like 'H%';

All about Normalization!

SQL Query to find name of employee whose name ends with ‘ani’
SELECT * FROM Employees WHERE EmpName like '%ani';

Find all Employee records containing the word "lal", regardless of whether it was stored as LAL, Lal, lAl or lal.
SELECT  * from Employees  WHERE  upper(EmpName) like upper('%lal%');

SQL query to find the duplicate rows.
SELECT * FROM EMPLOYEE e1 where rowid = (SELECT max(rowid) from EMPLOYEE e2 where e1.empno=e2.empno)

SQL query to delete the duplicate rows.
DELETE FROM EMPLOYEE e1 where rowid = (SELECT max(rowid) from EMPLOYEE e2 where e1.empno=e2.empno)

SQL query to find all the students whose marks are greater than average marks i.e. list of above average students.
SELECT StudentName, Marks FROM STUDENT_DETAILS HAVING Marks>AVG(Marks);

-K Himaanshu Shukla...



Copyright © 2014 - ScrutinyByKHimaanshu