Database and SQL is a very important skill, not just for DBA or Database admins but also for any application developer like Java, .NET, or Web developers. This is why you would often see questions from SQL and Database in Programming interviews. For DBAs, SQL is more important than a programmer, because it being their primary skill, they are also expected to know more than a common Java or .NET developer. Since no Java interview is just about Java questions, many times I receive a request from my reader about SQL questions like how to solve a particular query or some tricky questions based upon database indexes.
Earlier, I have shared SQL query questions from interviews and In this article, I am going to share some 50 SQL and Database interview question, which is often asked in the telephonic round of DBA and Programmer interviews. Phone interviews are a little different than face-to-face interviews and tend to be more specific.
Since this is usually a sort of screening round to weed out unsuitable candidates, the interviewer often likes to cover as many concepts as possible. That's why it becomes a test of general knowledge of the developer about a particular skill than the depth of his knowledge.
But, if you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.
Earlier, I have shared SQL query questions from interviews and In this article, I am going to share some 50 SQL and Database interview question, which is often asked in the telephonic round of DBA and Programmer interviews. Phone interviews are a little different than face-to-face interviews and tend to be more specific.
Since this is usually a sort of screening round to weed out unsuitable candidates, the interviewer often likes to cover as many concepts as possible. That's why it becomes a test of general knowledge of the developer about a particular skill than the depth of his knowledge.
But, if you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.
50 Essential Topics for SQL and Database Interview Questions
Here is a list of essential topics programmers should prepare for SQL and Database interview questions. The list includes most essential SQL and database concepts like normalization, index, aggregation, joins stored procedure, and constraints.Interviewer normally asks 20 to 25 questions to get a feel of overall skill of candidate, sometimes they purposefully include a tough question, couple of tricky one but mostly questions based upon fundamentals. You will usually see questions from the following topics in any SQL phone interview:
As I mentioned, on phone interviews, Interviewers are usually in hurry, they want to hear the correct and concise answer, and not blah blah blah answers, because of that I have kept answers short and sweet. One more reason for keeping your answer short and specific is to avoid getting shot by providing additional information which is not accurate.
At the same time, It's also hard for any SQL developer to go through five-page articles to revise some 30 most frequently asked SQL questions, keep answers to the point helps them a lot.
So, let's start our journey with these beautiful databases and SQL interview questions, I am sure you will also learn new things as well.
Thanks for reading this article so far. If you like these popular Database and SQL Interview Questions then please share them with your friends and colleagues. If you have any feedback, please drop a note.
- Database fundamental like Normalization, Referential Integrity
- Database Relationship like one to one, one to many, and many to many
- Different types of keys like Primary key, Unique key, Candidate key, etc
- Database indexes like how they work, benefits, disadvantages, etc
- The SQL query to extract records from tables
- SQL Join
- Database indexes
- Question about a particular database like Oracle, SQL Server, MySQL, or PostgreSQL
- Triggers
- Temporary table
- Stored procedures
- Views
- Query Optimization
- Group By clause and aggregate function
- CRUD operation like INSERT, UPDATE, DELETE
- SQL clause based questions like TRUNCATE, DROP, etc
- CONSTRAINTS like CHECK constraint, PRIMARY key constraints
I have tried to include as many good questions as possible from the above topics, to make them useful resources for all levels of SQL developers and DBA like beginners to experienced programmers having 2 to 5 years of experience.
As I mentioned, on phone interviews, Interviewers are usually in hurry, they want to hear the correct and concise answer, and not blah blah blah answers, because of that I have kept answers short and sweet. One more reason for keeping your answer short and specific is to avoid getting shot by providing additional information which is not accurate.
At the same time, It's also hard for any SQL developer to go through five-page articles to revise some 30 most frequently asked SQL questions, keep answers to the point helps them a lot.
So, let's start our journey with these beautiful databases and SQL interview questions, I am sure you will also learn new things as well.
1. Difference between UNION and UNION ALL in SQL? (answer)
hint - UNION doesn't include duplicate records, UNION ALL does. Both can be used to combine results from multiple queries.
2. Difference between WHERE and HAVING clause in SQL? (answer)
hint - in case of WHERE filtering applies before aggregation while in case of HAVING, filtering applies after aggregation
3. Describe the difference between clustered and non-clustered indexes in a database? (answer)
hint - clustered index defined the order in which data is physically stored in the table. Since data can be sorted in only one way physically, there is only one clustered index per table and that's usually the primary key. You can have multiple non-clustered indexes to speed up your queries.
4. Write an SQL query to find the second highest salary of an employee without using TOP or LIMIT? (answer)
You can use correlated queries to solve this problem.
5. How to find duplicate rows in the database? (answer)
6. Difference between correlated and non-correlated subquery in SQL? (answer)
7. How many clustered indexes you can have in a table? (answer)
This is a tricky question, you can only have one clustered index per table.
8. Difference between the PRIMARY key and the UNIQUE key constraint in SQL? (answer)
9. Difference between view and materialized view in SQL? (answer)
10. Difference between TRUNCATE, DELETE and DROP in SQL? (answer)
delete removes record but also put an entry into log segment so you can roll back, truncate removes all rows of a table without logging each record on log segment, DROP removes table itself.
11. What is Referential Integrity in a relational database? (answer)
It's a rule which ensures that when a record is deleted from the primary table, all associated records are deleted from the related table. It ensures data integrity.
12. What is Normalization? (answer)
A way to avoid duplication of data in tables.
13. When a table is said to be in 1NF, 2nd NF, and 3rd NF? (answer)
14. Describe the difference between ISNULL() and COALESCE() in SQL Server? (answer)
15. How do you ensure that only values between 1 to 5 are allowed in an integer column? (answer)
16. Difference between CHAR and VARCHAR data types in SQL? (answer)
CHAR is fixed length, VARCHAR is variable length
17. Difference between VARCHAR and NVARCHAR in SQL Server? (answer)
NVARCHAR supports Unicode
18. How do you get Day, Month, and Year from a date in SQL Server? (answer)
By using the DATEPART() function
19. How to check if a date is valid in SQL? (answer)
20. Difference between LEFT OUTER JOIN and INNER JOIN in SQL? (answer)
21. What is SELF JOIN in SQL? (answer)
When we join two instances of tables it's called self-join. For example, if the table contains employee name and role then you can use self join to find all employees who are managers.
22. In a classical Employee and Department relationship, write SQL query to print all departments and number of employees on each department. (answer)
23. Difference between COUNT(*), COUNT(1), and COUNT(column_name) in SQL?
COUNT(*) includes null values while counting but COUNT(1), and COUNT(column_name) doesn't take null values into consideration during counting.
24. What is Database statistics? How it affects the performance of your Query?
25. Suppose you have to create a compound index in a table, involving two columns like book_id and active. Now you can create them as either (book_id, active) or (active, book_id), do you think the order of columns in an index matter? How it will affect your SQL queries?
26. What do _ and % are used in the SQL query?
They are used in the LIKE operators while writing SQL queries. The underscore is used to match one character, while % is used for any number of characters.
27. How do you ensure that a particular SQL query will use a particular Index? (answer)
You can use SQL hints for that purpose.
28. In SQL Server, which one is fastest and slowest between an index seek, an index scan, and table scan? (answer)
In a general, the table scan is slower than the index scan and index seek. Your goal should be to write queries that can take advantage of the index by using index scan and index seek for faster retrieval. You can use the SQL EXPLAIN command to retrieve the query plans and find out whether indexes are used for your query or not.
29. What does NULL = NULL will return in SQL? (answer)
This is another tricky question to check your knowledge about NULLs in SQL. I think it will return unknown in SQL Server. But different Databases may return different values, it will not be true though. You can try running this query on SQL Server, MySQL, PostgreSQL, and Oracle to see if you get the same response or different response.
30. Write SQL query to find all rows where EMP_NAME, a VARCHAR column is NULL?
This is a simple question often asked beginners to check whether they know how to check for NULL in SQL or not. The trick here is to use "IS NULL" instead of the "=" operator to find all rows where EMP_NAME is NULL.
Here is the SQL query for this question:
$ SELECT EMP_NAME FROM Employee where EMP_NAME IS NULL
31. What is the temp table? (answer)
A temp table or a temporary table is a base table that is not stored in the database and only exists while the current database session is active. Once the database connection is closed, all temp tables are lost. They may look similar to view but they are not. A view exists only for a single query but you can use a temporary table as a regular table until your session is active.
32. What is the fastest way to empty or clear a table? (answer)
You can use the truncate command to empty or clear the table. It's faster than delete because it doesn't log each deleted entry on a log, that's why you cannot roll back it. So be careful while using truncate to clear or empty a table.
33. What is an identity column in SQL Server? How do you return an identity value from a table? (answer)
34. How do you return an identity value from a table with a trigger?
In SQL Server, you can use a function like @@IDENTITY to generate identity values.
Example - SELECT @@IDENTITY AS 'Identity';
35. How do you return a value from a stored procedure?
In SQL Server, you can either use the OUTPUT parameter or use the return statement to return a value from a stored procedure.
36. How do you return a VARCHAR value from a stored procedure?
hint - using OUTPUT parameter return clause
37. If you have a column that will only have values between 1 and 250 what data type will you use?
If you are using SQL Server database then you can use the TINYINT datatype which can accommodate numbers between 0 and 255 and it needs 1 byte for storage.
This question is asked to test your knowledge of SQL data type and whether you can choose the right data type for a given requirement or not. Your goal should be to use a data type that can accommodate a given range. You can also ask questions that whether it's fixed or it can change in the future.
38. Difference between LEFT and RIGHT OUTER JOIN in SQL? (answer)
Both are outer joins, in LEFT outer join, all rows from the left side table will be included, and only matching rows from the other side of the table are included. In case of RIGHT outer join, all rows of the right side of the table on join condition are included.
39. Can you write an SQL query to select all last names that start with 'T'? (answer)
40. How would you select all rows where the date is 20211002? (answer)
41. What is the difference between a local and global temporary table in SQL Server? (answer)
42. How do you create a copy of a table in SQL Server? (answer)
43. How do you change the data type of a column in a table in SQL? (answer)
44. What data type should you use to store monetary values in a table? (answer)
45. What does SELECT 3/2 will return? 1 or 1.5? (answer)
46. What is the maximum value that Decimal(6, 5) can hold in SQL Server? (answer)
47. If you have a table with one integer column ID, and it has three values 101, 201, and NULL? What will the following SQL query SELECT * FROM TestTable where ID !=101 will return? (answer)
48. What is your favorite SQL book? (answer)
This one is an easy question and the interviewer just wants to know whether you have read any book or not. You can name the SQL book you have read, if you haven't read any SQL book so far then I highly recommend you to read Head First SQL if you are learning SQL from scratch and Joe Celko's SQL Puzzles if you already know SQL and looking for some SQL puzzles to test your SQL query skills.
49. Tell me two SQL best practices you follow? (answer)
hint - creating indexes and using them on SQL queries, normalization, and updating statistics regularly.
50. What is the different ISOLATION level in the Microsoft SQL Server database? (answer)
51. If you create a local temp table and then call a proc is the temp table available inside the proc? (answer)
52. Which date format is the only safe one to use when passing dates as strings? (answer)
53. How do you suppress rows affected messages when executing an insert statement in SQL Server? (answer)
54. Difference between ANSI-89 and ANSI-92 syntax of writing SQL Join? (answer)
55. Differences between IN and EXISTS (and NOT IN, and NOT EXISTS) in SQL? (answer)
51. If you create a local temp table and then call a proc is the temp table available inside the proc? (answer)
52. Which date format is the only safe one to use when passing dates as strings? (answer)
53. How do you suppress rows affected messages when executing an insert statement in SQL Server? (answer)
54. Difference between ANSI-89 and ANSI-92 syntax of writing SQL Join? (answer)
55. Differences between IN and EXISTS (and NOT IN, and NOT EXISTS) in SQL? (answer)
That's all on this list of database and SQL interview questions. We have also covered a lot of SQL Server questions in this list and some questions from Oracle Database but most of the questions are applicable to all the databases including MySQL. You can use this list to quickly revise the essential SQL and Database concepts before you go for the interview.
Further Learning
Other SQL and Interview Question Articles You may like to explore
- Top 5 Courses to learn SQL and Database for Beginners
- 130+ Java Interview Questions with Answers
- 20+ Spring Boot Interview Questions with Answers
- 40+ Object-Oriented Programming Questions with Answers
- 10 Free Courses to learn SQL and Database
- 20 Software Design and Pattern Questions from Interviews
- 50+ Microsoft SQL Server Phone Interview questions
- 10 Oracle Interview Questions with Answers
- 20 JUnit Interview Questions with Answers
- 17 Spring AOP Interview Questions with Answers
- 25+ Spring Security Interview Questions with Answers
- 20 Algorithms Interview Questions for Software Developers
Thanks for reading this article so far. If you like these popular Database and SQL Interview Questions then please share them with your friends and colleagues. If you have any feedback, please drop a note.
P. S. - If you are new to the SQL and Database world and looking for a free online course to start learning SQL and Database from scratch then I highly recommend you to join Introduction to Databases and SQL Querying [FREE] course on Udemy. More than 369,000 IT professionals have joined this course so far.
No comments :
Post a Comment