Be it developers, testers, data scientists, or even someone from a business analyst profile, knowledge of SQL is required in all the fields.
During interviews for these profiles instead of directly asking questions based on theoretical concepts, 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 of the most commonly asked SQL queries for the interviews that you should prepare. So, let’s begin.
Ques.1. Write an SQL query to return the count of occurrence of a character in a particular field.
Ans. For this query, 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.2. Write an SQL query to display all the records that are present in one table - TableA but not present in another table - TableB.
Ans. In order to display the records from one table not present in another table, we can use the MINUS operator.
SELECT * FROM TableA MINUS SELECT * FROM TableB;
Ques.3. Write an SQL query to update a field’s values by removing any leading and trailing spaces.
Ans. In this query, we will be using the UPDATE command with LTRIM() and RTRIM() functions.
UPDATE Table1 SET Name = LTRIM(RTRIM(Name));
Ques.4. Write an SQL query to fetch those employees who are not assigned any project.
Ans. With this query, the interviewer wants to see if you know the usage of the IS NULL function.
SELECT EmpName FROM Employee WHERE Project IS NULL;
Ques.5. Write an SQL query to return all the records lying in a particular year e.g. 2021.
Ans. Here, we can use the BETWEEN operator in SQL.
SELECT * FROM TableA WHERE DateJoin BETWEEN '2021/01/01' AND '2021/12/31';
Ques.6. Write an SQL query for joining 3 tables.
Ans. For joining three 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.7. Write a query to display only the odd records from a Table.
Ans. For returning odd records, we can use the MOD function with the ‘not equal to operator’ - <>.
SELECT * FROM Employee WHERE MOD (EmpId, 2) <> 0;
Ques.8. How can we create a new table with data and structure the same as an existing table?
Ans. Creating a new table from an existing table just requires us to use the CREATE TABLE command that inherently supports this when used along with the SELECT command.
CREATE TABLE TableNew SELECT * FROM TableOld;
Ques.9. How can we create a blank table with the structure the same as an existing table?
Ans. With the above SQL query asked in the previous question, we have seen the creation of a new table from an existing table. Using the same query, we just need to use a false condition like ‘where 1=0’. 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.10. Write an SQL query to return the nth highest record (e.g. salary, marks, rank, etc) from a table.
Ans. In SQL Server, 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;
In MySQL, we can use the limit clause-
SELECT Salary FROM TableA ORDER BY Salary DESC LIMIT N-1,1;
For example, if we are required to find the 3rd highest record, the query would be-
SELECT Salary FROM TableA ORDER BY Salary DESC LIMIT 2, 1;
This completes our list of SQL queries that you should prepare in your interviews. I hope you liked these questions. Thanks for reading.
Top comments (0)