Day-8 14 Frequently Asked SQL Query Interview Questions

Hey, fellow code adventurers! Get ready to hop on the SQL, I am very excited to move to the next step,

14 Frequently Asked SQL Query Interview Questions

Question 1: SQL Query to find the second highest salary of Employee

Answer:
To find the second highest salary in SQL, you can use the following query with a Subquery:

SELECT MAX(Salary) 
FROM Employee 
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);

For more approaches, check out "How to find the second highest salary in SQL."

Question 2: SQL Query to find Max Salary from each department

Answer:
To find the maximum salary for each department, you can use the GROUP BY clause. If you need department names, consider joining the Employee table with the Department table:

SELECT DeptName, MAX(Salary) 
FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID 
GROUP BY DeptName;

Remember to understand joins; a SQL Bootcamp course can be beneficial.

Question 3: Write SQL Query to display the current date?

Answer:
You can use the GetDate() function to display the current timestamp in SQL:

SELECT GetDate();

Question 4: Write an SQL Query to check whether the date passed to Query is the date of the given format or not?

Answer:
Use the ISDATE() function to check if the provided value is a date in the specified format:

SELECT ISDATE('1/08/13') AS "MM/DD/YY";

Question 5: Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

Answer:
Use the BETWEEN clause to get distinct employee names with DOB in the specified range:

SELECT DISTINCT EmpName 
FROM Employees 
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975';

Question 6: Write an SQL Query to find the number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

Answer:
Count the number of employees based on gender within the specified DOB range:

SELECT COUNT(*), sex 
FROM Employees  
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' 
GROUP BY sex;

Question 7: Write an SQL Query to find an employee whose salary is equal to or greater than 10000.

Answer:
Select employees with a salary equal to or greater than 10000:

SELECT EmpName FROM Employees WHERE Salary >= 10000;

Question 8: Write an SQL Query to find the name of an employee whose name starts with ‘M’.

Answer:
Retrieve employees whose names start with 'M':

SELECT * FROM Employees WHERE EmpName LIKE 'M%';

Question 9: Find all Employee records containing the word "Joe," regardless of the case.

Answer:
Search for employee records with the word "Joe" regardless of case:

SELECT * FROM Employees WHERE UPPER(EmpName) LIKE '%JOE%';

Question 10: Write an SQL Query to find the year from date.

Answer:
Get the year from the current date in SQL Server:

SELECT YEAR(GETDATE()) AS "Year";

Question 11: Write SQL Query to find duplicate rows in a database? And then write SQL query to delete them?

Answer:
To find duplicates:

SELECT * FROM emp a 
WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno = b.empno);

To delete duplicates:

DELETE FROM emp a 
WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno = b.empno);

Question 12: Find all students whose marks are greater than average marks.

Answer:
Use a subquery to find students with marks above the average:

SELECT student, marks 
FROM table 
WHERE marks > (SELECT AVG(marks) FROM table);

Question 13: How do you find all employees who are also managers?

Answer:
Utilize a self-join to identify employees and their respective managers:

SELECT e.name, m.name 
FROM Employee e, Employee m 
WHERE e.mgr_id = m.emp_id;

Question 14: Will an index be used if only two out of three columns are provided in the WHERE clause of a select query?

Answer:
If the provided columns are part of a composite index, the index may still be used for the operation.

Drop by our Telegram Channel and let the adventure begin! See you there, Data Explorer! 🌐🚀