Table of Contents
- 1 Introduction
- 1.1 Question 1: What is SQL?
- 1.2 Question 2: What are the differences between the ‘delete’ and ‘truncate’ commands?
- 1.3 Question 3: What is a SQL Server?
- 1.4 Question 4: What is MySQL?
- 1.5 Question 5: What is a ‘table’ and a ‘field’?
- 1.6 Question 6: What is a ‘join’?
- 1.7 Question 7: What is ‘view’?
- 1.8 Question 8: What is a ‘constraint’?
- 1.9 Question 9: How would you display the current time and date with SQL queries?
- 1.10 Question 10: What is ‘denormalization’?
- 1.11 Question 11: What’s a ‘query’?
- 2 SQL Interview Questions: Advanced
- 2.1 Question 1: Explain ‘index’.
- 2.2 Question 2: What to do when you forget your root password?
- 2.3 Question 3: Can you say that NULL values are equal to a zero?
- 2.4 Question 4: What should you do if the data disk is overloaded?
- 2.5 Question 5: What’s ‘auto increment’?
- 2.6 Question 6: Name the components that make up the most basic MySQL architecture.
- 2.7 Question 7: Create an empty table from an existing one.
- 2.8 Question 8: How would you get the current SQL version?
- 2.9 Question 9: How would you fetch alternative odd records from the table?
- 2.10 Question 10: Please select a unique record from the table.
- 2.11 Question 11: Explain the variables of SQL.
- 2.12 Question 12: What’s a ‘datawarehouse’?
- 2.13 Question 13: What is the main usage for a ‘recursive stored procedure’?
- 2.14 Question 14: How would you retrieve the first 3 characters from a character strong?
- 2.15 Question 15: Please retrieve common records from two tables.
- 3 General Tips
- 4 Conclusions
IntroductionAs I’ve said, let’s take it from the top and start at one of the most commonly asked SQL interview questions.
Question 1: What is SQL?At the very beginning, I’ve mentioned that SQL isn’t really a programming language at all. So what is it? SQL stands for Structured Query Language. It is primarily a database management language. On its own, it isn’t really a programming language per se, but its standard may extend it to being a fully legit coding language – that why most people simply refer to it as a “programming language”.
Question 2: What are the differences between the ‘delete’ and ‘truncate’ commands?The main difference is that the “delete” command deletes one row from the table, while “truncate” deletes all of them.
Question 3: What is a SQL Server?You are bound to get some SQL Server interview questions during your job application, so I’ve added some of the main ones in this tutorial. SQL Server is a Database Management System (DBMS). It gathers and analyzes data, updates and administers the database.
Question 4: What is MySQL?As with the SQL Server, MySQL interview questions are also commonplace during job interviews involving SQL. MySQL is an open source database management system. It provides a wide range of available functions and interfaces, has a cross-platform support and possesses nifty tools to help you get to work in no time.
Question 5: What is a ‘table’ and a ‘field’?A “table“ is simply organized data made into separate rows and columns. A “field” is the number of columns in a table.
Question 6: What is a ‘join’?The “join” command combines rows from different tables. The way that these rows are combined depends on the related column that joins them. With this command, you can merge two tables into one, or simply transfer data from one table to another.
Question 7: What is ‘view’?A “view” is simply a virtual representation of a “table“. More so, views can have more than one table’s data represented and combined – it’s very situation and relationship-dependant.
Question 8: What is a ‘constraint’?“Constraints” are commands that are used to underline and specify the amount of a specific type of data used in a single table.
Question 9: How would you display the current time and date with SQL queries?“How to” SQL interview questions are also common and normal, so don’t be surprised if you get one or two during your own interview. SQL has an inbuilt command GetDate() that retrieves and showcases the current time and day.
Question 10: What is ‘denormalization’?Denormalization is a process when you access the database from the higher forms towards the lower forms. This process is performed with the goal of increasing database performance.
Question 11: What’s a ‘query’?It might sound like one of the more self-explanatory SQL interview questions, and if that’s your initial thought after reading it – you’re absolutely right. As the term itself implies, a query is simply a question. This question is sent to the database via a specific code in order to retrieve some sort of information from it.
SQL Interview Questions: AdvancedNow that we’ve covered some of the more basic SQL interview questions, you should have a better understanding of what the beginning of your job interview will look like. After your potential employers see that you’ve cracked the SQL interview questions and answers, however, they might one-up the odds and give you some advanced SQL interview questions. This is why it’s better to come prepared – now we’ll move on to the more challenging questions.
Question 1: Explain ‘index’.Indexes are methods of enhancing performance, namely – retrieving information from the database with higher speed and efficiency. In total, there can be three types of indexes – clustered, non-clustered and unique. Clustered indexes reorder the table and search for information with the use of key values, while non-clustered ones maintain the order of the table. Unique indexes forbid fields to have duplicating values. A table can only have a single clustered index, but multiple non-clustered ones. Although it might not seem like one of the more advanced SQL questions, the difficulty here lies in the “explain” part. This is honestly what makes a lot of these questions advanced – employers may require you to explain or expand on your given answer with a follow-up.
Question 2: What to do when you forget your root password?If you forgot or lost your root password, start the database with the command of “skip-grants-table”. After you set the new password, restart the database in normal mode and enter the new password.
Question 3: Can you say that NULL values are equal to a zero?
Question 4: What should you do if the data disk is overloaded?You might encounter situations where you fill up the data disk. You can’t really continue to do anything if it’s overloaded. What you would do in this situation is to apply what is known as a soft link. These links create a location where you are able to store your .frm and .idb files – and that is exactly what you should do. This will resolve the overload problem.
Question 5: What’s ‘auto increment’?“Auto increment” is a command that allows you to generate and create a unique number for a new record within the table. This keyword can be used on multiple platforms, but it might vary a little. Since you are most likely to get some SQL Server interview questions, let me just tell you – the replacement for the “auto increment” command in SQL Server is “identity”.
Question 6: Name the components that make up the most basic MySQL architecture.Let’s try to mix in some MySQL interview questions, shall we? The most basic MySQL is actually made up of three main components – query optimizer, connection manager and pluggable engines.
Question 7: Create an empty table from an existing one.
Select * into employeecopy from employee where 1=2
Question 8: How would you get the current SQL version?Sounds like one of the more basic SQL interview questions, but a lot of people might stumble on this due to a lack of prior research – it becomes like a trick question. The way that you would get the most current SQL version is by issuing this command:
Question 9: How would you fetch alternative odd records from the table?You would do it using this command:
Select employeeId from (Select rowno, employeetId from employee) where mod(rowno,2)=1
Question 10: Please select a unique record from the table.The way that you would select unique records from a single table is by using the “distinct” command. Here’s an example:
Select DISTINCT employeeID from Employee
Question 11: Explain the variables of SQL.In SQL, there are two different variables – local and global. Local variables are those which can only exist in one, single function. Opposite to that, global variables may be located through ought the entire program.
Question 12: What’s a ‘datawarehouse’?A “datawarehouse” performs a similar function that a real warehouse does, only with data. In a “datawarehouse”, data is stored from many different areas and sources. There, it is sorted out and prepared for usage.
Question 13: What is the main usage for a ‘recursive stored procedure’?A recursive stored procedure is when a procedure acts upon itself up to a point when it reaches a set boundary. The main benefit of this type of a procedure is that it allows programmers to use the same code over and over again.
Question 14: How would you retrieve the first 3 characters from a character strong?There are quite a few ways to do this, but this is one of the more popular and easier ones:
Select SUBSTRING(EmployeeSurname,1,5) as employeesurname from employee
Question 15: Please retrieve common records from two tables.You may do so by performing the below-located task:
Select employeeID from employee. INTERSECT Select EmployeeID from WorkShiftSo, now you’re a bit more familiar with both the basic and more advanced SQL interview questions. Now that you know what to expect during your job interview, let’s move on to some more general tips for you to consider.