SQL interview questions
SQL means Structured Query Language, it was initially developed during 1970s, and it is used to communicate with Database Management Systems (DBMS). We use SQL to manage and extract the data stored into Databases, so if we need a programmer that knows how it works then it is important to ask the correct SQL interview questions. In this section you will find a compilation of the most popular and useful questions about SQL and their respective answers or explanations, so if you are looking for a job or a employee then this list is perfect for you.
SQL interview questions and answers
What is SQL?
The most basic and popular question, besides all the written above, we should know a little about its history, it was first developed by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s at IBM, and was first commercially available in 1981.
What is a query?
Queries are the most important elements of SQL, they allow us to extract information out the database. Basically are instructions that retrieve the required information from databases.
Normalization is a process used to optimize our databases, it allows us to organize efficiently the data inside our databases, it provides:
- Elimination of Data Redundancy.
- Performance improvement.
- Data logically stored.
Explain the steps of normalization
Normalization is divided into normal forms, from the First Normal Form up to the Fourth (some authors name up to 5 or more), so the steps are first normal form, second, third and finally fourth normal form.
- The First one eliminates duplicate columns on the same table and creates different tables for each group of related data.
- The Second Form basically besides doing kind of the same as the first, (removes remaining subsets and separate them into new tables) creates relationships between the new tables.
- The Third meets all the requirements of the second, and removes columns that do not depend of the primary key.
- The Fourth Form meets the requirements of the Third, and has no multi-valued dependencies.
Can you define candidate key, alternate key and composite key?
Candidate key is the one that identifies a singular row on a table, if the table has more than one candidate key, one of them becomes primary key the others alternate key, if you need to use more than one column to create a primary key, then it is called composite key.
What is a constraint?
Is a limitation we use for a column on the table or more, that way we can control the inserted data, these are: Check, default, primary key, unique and foreign key.
What is a trigger?
A trigger is a procedure that runs each time a data is modified, or in other words, every time and event occurs. (In case insert, update or delete is executed)
What is a stored procedure?
A stored procedure is basically a created and stored SQL statements, they accept input parameters, so they can be used by a complete network.
Explain the command TRUNCATE
Unlike DELETE; the command TRUNCATE eliminates all the rows of a table without registering the process and without the instruction "where", also uses less resources and faster than "delete".
What is the difference between "where" and "having"?
"Where" is very used on SQL statements, but "having" has a special application, it can be used with "aggregate functions", and also is combined with "group by".
Besides the theoretical SQL interview questions, it is also important to have exercises to measure the creativity and abstraction level of the interviewee, usually you can have a table with about 10 rows of information, so you can ask the interviewee to extract the information you need, you can also have a similar table with similar data, but with normalization problems, so the interviewee must use normalization and then extract the information you need, you can also have an image of the database relationship, that way the interviewee has to create all the tables, create the relationships, if it's necessary use normalization and finally extract the information you ask him, this way is even better, because he/she must use different tables and primary keys to move inside the database and extract what is asked for.
Another thing to take into account is that the language used is SQL, and it is independent of the DBMS (Database Management System), you can use SQL server, MySQL or any other, so your SQL requirements may be a little different, it is also important in the way that some DBMS are more secure than others, some are free, so look carefully the questions you will ask, focus your SQL interview questions on the DBMS you are using, if you are an applicant, research as much as you can about the company, that way you can focus your preparation.