Knowledge of SQL queries is required for almost everyone in the IT field. Whether you are software developers, QA Engineers, data scientists, or a product guy, you are required to work with data in some way or the other.
So, in the interviews for these profiles instead of directly asking questions based on theoretical concepts of databases, the interviewers ask SQL queries that test both the theoretical as well as practical understanding and usage of the different SQL concepts.
In this article, we will be discussing some frequently asked SQL queries that you should prepare for the interviews. So, let’s begin.
Ques.1. Write an SQL query to return all the records that are present in one table but not present in another table.
Ans. For this, we can simply use the MINUS operator.
SELECT * FROM TableA MINUS SELECT * FROM TableB;
Ques.2. Write an SQL query to print the count of occurrence of a character in a particular column.
Ans. Here, we can simply use the Length() function with the subtraction operator and a Replace function.
SELECT Name, LENGTH(Name) – LENGTH(REPLACE(Name, ‘k’, ”)) FROM Employee;
Ques.3. Write a query to fetch all those employees who are not assigned any project.
Ans. Here we can use the IS NULL function.
SELECT EmpName FROM Employee WHERE Project IS NULL;
Ques.4. Write an SQL query to update the values by removing the leading and trailing spaces.
Ans. Here, we will be using the UPDATE command with LTRIM() and RTRIM() functions.
UPDATE Table1 SET Name = LTRIM(RTRIM(Name));
Ques.5. Write an SQL query to join 3 tables.
Ans. For joining 3 tables, we just need to use the JOIN clause twice like below.
SELECT col1, col2 FROM TableA JOIN TableB ON TableA.Col3 = TableB.Col3 JOIN TableC ON TableA.Col4 = TableC.Col4;
Ques.6. Write a query to return all the records lying in a particular year e.g. 2022.
Ans. For this, we can use the BETWEEN operator in SQL.
SELECT * FROM TableA WHERE DateJoin BETWEEN ‘2022/01/01’ AND ‘2022/12/31’;
Ques.7. Write a query to return only the odd records from a Table.
Ans. For returning odd records, we can use the MOD function with a ‘not equal to operator’ – <>.
SELECT * FROM Employee WHERE MOD (EmpId, 2) <> 0;
Source – SQL query interview questions by ArtOfTesting
Ques.8. Write a query to create a blank table with the structure the same as an existing table?
Ans. Here, we just need to use a false condition like ‘where 1=0’ with create table command. This condition will prevent any rows from getting copied. Hence, only the structure will be copied.
CREATE TABLE TableNew SELECT * FROM TableOld where 1=0;
Ques.9. Write a query to display the nth highest record.
Ans. For this, we can use the Top command.
SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP N Salary FROM TableA ORDER BY Salary DESC) ORDER BY Salary ASC;
Ques.10. Write a query to create a new table with data and structure the same as an existing table?
Ans. For creating a new table from an existing table, we can use the CREATE TABLE command that inherently supports this when used along with the SELECT command.
CREATE TABLE TableNew SELECT * FROM TableOld;
This completes our comprehensive list of SQL queries that you should prepare for your interviews. I hope these questions will be of your help. Thanks.
Image Source: Artoftesting