SQL interview questions

SQL interview questions:

Q1. If month in between jan and jul then 2022 else 2023. What function used to find and how?
select *, case when month between jan and july then 2022 else 2023 end as year from tablename;


Q2. how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary)
columns and table 2 (dept2) have (emp_id,emp_name,salary) columns,i want which employee have the
maximum salary among two tables?
SELECT MAX(Salary) FROM
(SELECT MAX(Salary) as Salary FROM dep1
UNION
SELECT MAX(Salary) as Salary FROM dep2) E


Q3. Is a NULL value same as zero or a blank space? If not then what is the difference?
 zero or blank space can be compared with another zero or blank space. whereas one null may not be
equal to another null.
 A null value is not the same as a blank space or a zero value. A zero value is an integer and a blank
space is a character while a null value is the one that has been left blank.
Q4. DROP command used for?
If you drop a table, all the rows in the table is deleted and the table structure is removed from the database.
Once a table is dropped we cannot get it back, so be careful while using DROP command.
Q5. Find the 3rd highest salary using window functions?
SELECT * FROM (
SELECT emp_name, emp_salary, DENSE_RANK() OVER (ORDER BY emp_salary DESC) AS r
FROM emp
) AS subquery
WHERE r = 3;
OR
SELECT emp_name, salary, ROW_NUMBER()
OVER(ORDER BY salary DESC) AS 3rd_highest_salary
FROM employee where 3rd_highest_salary =3;


Q6. How to get only duplicate records?
Select col1, COUNT(col1) as count from tablename group by col1 having count(col1)>1 ;


Q7. Write a query to fetch only the place name(string before brackets ie Toronto) from the city column of
EmployeeDetails table.

, EmpId, FullName ManagerId DateOfJoining City
121 John Snow 321 01/31/2014 Toronto(TR)
321 Walter White 986 01/30/2015 New York(NY)
421 Kuldeep Rana 876 27-11-2016 New Delhi(DL)


SELECT left(city, CHARINDEX('(','City') -1) as city from Employee;


Q8. Write the query for the following input.
Source:
Sno name sal
1 a 5000
2 b 3000
3 c 2000
Target:
Sno Name Sal Sal_diff
1 a 5000 0
2 b 3000 2000
3 c 2000 3000
select Sno, Name, Sal, (select max(Sal) as max from source) - Sal as Sal_diff from source;

Q9. Write the query for the following input.
Email







separate name and domain

name domain

xyz gmail.com

abc yahoo.in

qwe outlook.com

select left(Email, CHARINDEX('@',Email) -1) as name, substring(Email, CHARINDEX('@',Email) +1), LENGTH(Email)) as
domain from emp;