Whether you are a developer creating web applications or a tester testing these websites. SQL plays a very important skill to be learned for both in database programming and database validation. So what does SQL stands for? SQL is an abbreviation for Structured Query Language. SQL helps in data management i.e. store, update or delete data in a relational database.
In general, a query is a request for something. But in the world of programming query means retrieving data from the database with preset commands. i.e. SELECT, INSERT, SUM, WHERE, etc. SQL Queries are very important to learn as it tests both your practical as well as theoretical knowledge and is asked directly in interviews.
Here, are the top 10 SQL queries that are asked most and you can go through the refered links for more questions and boost your preparation.
Consider the below two tables for reference while trying to solve the SQL queries for practice.
Table – EmployeeDetails
Table – EmployeeSalary
Que-1 Write an SQL query to find the MAX, MIN and AVERAGE salary of the employees?
Ans- An SQL aggregate function is used to calculate the MAX(), MIN() and AVG() salary of the employees.
SELECT Max(Salary),
MIN(Salary),
AVG(Salary)
FROM Employee Salary;
Que- 2 Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId).
Ans- We can use GROUP BY and apply it on fields for finding duplicate rows from tables. After that we can use HAVING clause so that it return only those fields whose count is greater than 1.
SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*)
FROM EmployeeDetails
GROUP BY FullName, ManagerId, DateOfJoining, City
HAVING COUNT(*) > 1;
Que- 3 Write an SQL query to create a new table with data and structure copied from another table?
Ans- The SQL query to create a new table for copying data and structure from another table is:
CREATE TABLE NewTable
SELECT * FROM EmployeeSalary
Que- 4 Write an SQL query to find the nth highest salary from table.
Ans- The TOP clause can be used for finding the nth highest salary as TOP clause fetches the TOP N numbers of a row.
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
)
ORDER BY Salary ASC;
Using the LIMIT Clause
SELECT Salary
FROM Employee
ORDER BY Salary DESC LIMIT N-1,1;
Que- 5 Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table.
Ans- A Self-Join(a table is joined with itself) would be used here as we need to analyze EmployeeDetails table as two tables. E & M are used for Engineers and Managers.
SELECT DISTINCT E.FullName
FROM EmployeeDetails E
INNER JOIN EmployeeDetails M
ON E.EmpID = M.ManagerID;
Que-6 Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.
Ans- The EXISTS operator is used to test whether a subquery exists or not.
SELECT * FROM EmployeeDetails E
WHERE EXISTS
(SELECT * FROM EmployeeSalary S
WHERE E.EmpId = S.EmpId);
Que-7 Write an SQL Query to create an empty table with the same structure as some other table?
Ans-
CREATE TABLE NewTable
SELECT * FROM EmployeeSalary where 1=0;
Que-8 Write an SQL Query to fetch the position of a given character(s) in a field?
Ans- The function INSTR() is a case sensitive search. The INSTR() function returns the first occurrence of string in another string.
SELECT INSTR(FullName,'Snow')
FROM EmployeeDetails;
Que-9 Fetch all the employees who are not working on any project.
Ans- This is a very common question asked during interviews. We will be using NULL operator a NULL value is different from ZERO value as NULL value represents that a space is left blank during the creation of record.
SELECT EmpId
FROM EmployeeSalary
WHERE Project IS NULL;
Que-10 Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field.
Ans- We can use the ‘LENGTH’ function and can subtract the total length of the FullName field with a length of the FullName after replacing the character – ‘n’.
SELECT FullName,
LENGTH(FullName) - LENGTH(REPLACE(FullName, 'n', ''))
FROM EmployeeDetails;
This is the end of our top 10 questions that are mostly asked in interviews. Thanks for reading. :)
REFERENCES:-