I have already given article on complex sql queries and complex sql queries for interview. In this new article I would like to give you the tricky sql queries which are also useful for interview purpose.This article will give you multiple Tricky SQL Queries which are useful not only in day to day real time sql scenarios but also for the interviews.
What you will find in this article?
1.Tricky SQL Queries and Interview questions
2.Video for tricky interview queries
Question 1 : What is query to fetch the employee records where salary of employee is greater than 57 k and less than 75 k?
Answer :
There are two different solutions to achieve this ,
Solution 1 : Using Between..and operator
Select * from Employee where salary between 57000 and 75000;
Solution 2 : Using operators (Greater than and less than)
Select * from Employee where salary >= 57000 and salary <= 75000;
Question 2 : Most Asked Tricky SQL Queries for Interview
What is query to find out second highest salary of employee?
Answer :
I will give you two solutions for calculating second highest salary,
Solution 1 :
Select distinct Salary from Employees e1 where 2=Select count(distinct Salary) from Employees e2 where e1.salary<=e2.salary;
Solution 2 :
select min(salary)from(select distinct salary from Employees order by salary desc)where rownum<=2;
Question 3 : What is query to display first 50 records of table?
Answer :
Select * from Employee where Rownum <= 50;
Question 4 : What is query to fetch last record in students table?
Answer :
Select * from Student where Rowid= select max(Rowid) from Student;
Question 5 : What is query to find out the Students whose names starts with S,K,U?
Answer :
Tip : You require to use the % operator (Like Wildcard)
Select * from Students where name like ‘[SKU]%’;
Query 6 :What is query to find out the Students whose name not starts with S,K,U?
Answer :
There are two ways to achieve this using wildcard character,
Solution 1 : Using Not operator symbol
Select * from Students where name like ‘[!SKU]%’;
Solution 2 : Using Not Operator
Select * from Employees where name not like ‘[SKU]%’;
Query 7 : How to fetch common records of student and student master table which does not have joining condition but which has same column names with same datatype?
Answer:
To achieve this we need to use intersect operator,
Select * from Student
Intersect
Select * from Student_Master;
Check here difference between Intersect and Minus operator ,
| Intersect | Minus |
| 1.Intersect Set operator is used to fetch the common records from 2 different tables . | 1.Minus Operator is used to fetch the records from first table which eliminates common records. |
| 2.Syntax:Select col1,col2…from table1;IntersectSelect col1,col2…from table2; | 2.Syntax:Select col1,col2…from table1;MinusSelect col1,col2…from table2; |
| 3.For Performance tuning Intersect operator is not preferable as it takes time to fetch duplicate records | 3.Minus operator is preferable operator in Performance tuning. |
Query 8 : What is query to fetch all records of students whose admission year is ‘2020’?
Answer :
I would like to give you two solutions
Oracle:
select * from Students where To_char(Admission_date,’YYYY’)=’2020′;
MS SQL:
select * from Students where substr(convert(varchar,Admission_date,103),7,4)=’2020′;
Question 9 : What is query to create skeleton of Student table without data?
Answer :
Create table Student_Master_dup as select * from Student where 1=2;
You can check multiple ways to create a duplicate table
Question 10 : How to fetch distinct records without using distinct keyword?
Answer :
This can be used in postgresql as distinct is paid keyword in postgress,
select * from Student a where rowid = (select max(rowid) from Student b where a.roll_no=b.roll_no);
Question 11 : What is query to find out the Students whose name ends with ‘Rao’.
Answer :
Select * from Students where Student_Surname like ‘%Rao%’;
Query 12 : What is query to find out the even and odd rows from Student_Master table?
Answer :
Finding even rows from Student_Master Table :
Select * from(Select rownum as rno,E.* from Student_master) where Mod(rno,2)=0;
Finding odd rows fron Student_Master Table,
Select * from(Select rownum as rno,E.* from Student_Master E) where Mod(rno,2)=1;
Query 13 : What is query to find out all users from database using system tables?
Answer :
SELECT Username FROM All_Users ORDER BY Username;
Query 14 : What is query to gather stats of Employee Schema?
Answer :
exec dbms_stats.gather_schema_stats(ownname=>’Employee’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
Query 15 : How to create duplicate table using metadata method?
Answer :
You can use following query to create duplicate table using metadata method,
SELECT dbms_metadata.get_ddl( ‘TABLE’, ‘Student_master’, ‘OTHER_SCHEMA_NAME’ ) FROM DUAL;
Query 16 : How to gather stats for 2 schema’s together?
Answer :
exec dbms_stats.gather_table_stats(‘Employee_master’, ‘Employee’);
Query 17 :How to get information of students who is not assingned to any class.
Answer:
We can achieve this using not in operator,
Select * from Students where roll_no Not in(Select roll_no from Student_Class);
Query 18 : What is query to find out maximum and minimum marks for students together?
Answer :
Select max (Marks) from Student
Union
Select min (marks) from Student;
Query 19: How to remove duplicate rows from student table?
Answer :
Select Student_No FROM Students WHERE ROWID <>
(Select max (rowid) from Students b where Student_No=b.Student_No);
Query 20 : How to validate Email address using Oracle?
Answer :
SELECT
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);
I hope you like this article on tricky sql queries. These are most important as well as useful tricky sql queries which are useful everywhere.