Top 23 SQL Interview Questions Answers

 

Q1. What is SQL? 

ANS : “Structured Query Language, more commonly known as SQL, is seen as the standard for managing data kept in relational database management systems. For example, SQL statements are used to carry out tasks including updating the data, and also retrieving specific data from a database.” 

Q2. Why do you want to work for our company in this SQL position? 

ANS : “Within any SQL job, it’s really important to choose your employer carefully. What I mean by this is, in order to carry out your job properly, you need to work as part of a team that has clearly defined goals, sets high standards and also works collaboratively to achieve all company objectives. From what I found during my research into your organization, you are very well thought of by other people who work within the industry, you have exciting and ambitious plans for the future, and it is clear you give your staff the support they need to grow and develop within their roles.”

Q3. What is MySQL? 

ANS : “MySQL is an open-source relational database management system that is based on SQL, most commonly used for web databases. It was co-founded by Michael Widenius and the ‘My’ aspect of MySQL is the name of his daughter. The remainder is obviously an abbreviation for Structured Query Language. MySQL is utilized by a variety of database-driven web applications. These include WordPress and Joomla amongst others. It is also used by many of the world’s most popular websites, including YouTube, Facebook and Twitter. Written using C and C++ languages, MySQL supports the major platforms you’d expect such as macOS, Linux, and Windows.”

Q4. What's the main difference between SQL and MySQL? 

ANS : “So, SQL is often used to access, manipulate and update the data within a database, whereas MySQL is a relational database management system that keeps the data within the database organized. In other words, SQL is a language created to manage relational databases, and MySQL is an open-source relational database management system, based on SQL.” 

Q5.  In SQL, what are joins? 

ANS : “There are four main types of joins in SQL. These are FULL JOINS, RIGHT JOINS, LEFT JOINS and INNER JOINS. Joins basically combine the rows from two or more tables.” 

Q6. What is an index and why is it useful to have? 

ANS : “An index is useful because it allows for the faster retrieval of records from a table. The index creates an entry for each value, which in turn, makes it much faster to retrieve the data required, or in other words, an index is a pointer to data contained within a table.” 

Q7. If a constraint is added in SQL, what does this mean? 

ANS : “There are many different types of SQL constraints which can be used. Their purpose is to specify a rule for the data within a table, limiting the type of data that can go within the table. The constraint can be added either during the creation of a table, or once it has been created. Constraints can be applied to either the table as a whole or column level.” 

Q8. What are the more common types of SQL constraint and what do they mean? 

ANS : “There are several common SQL constraints. These are PRIMARY KEY, which identifies each record in a table; NOT NULL is used to prevent a NULL value being inserted into a column; FOREIGN KEY, which provides a unique identification of a row or record within another table; and also DEFAULT, which will automatically assign a default value if there has not been a value already specified for a field. There is also CHECK, which is a constraint that is used to verify that all values meet a specific criteria or condition in a field; and UNIQUE, which ensures unique values are always inserted into a column. Finally, INDEX is used to create data and to obtain it from a database with speed.”

 

Q9. So far, you have referred to tables and fields in your answers. What are they? 

ANS : “In its simplest form, a TABLE is a collection of data that is organized into rows and columns. The columns are vertical, and the rows are horizontal. The columns are called FIELDS and the rows are often referred to as records.” 

 

Q10. Tell me what the different subsets of SQL are? 

ANS : “So, there are three main subsets of SQL. Firstly, there is Data Control Language, or DCL, which permits you to control access to the database. For example, you can either grant access to the database or revoke it. Then there is DDL, which is Data Definition Language, which allows you to specify data structures in the database, including deleting tables, creating them or altering them. Finally, there is Data Manipulation Language (DML) which, as the name suggests, allows you to manipulate the data, including updating, inserting, deleting or retrieving data in the table as required.” 

Q11. How would you format SQL server dates? So, how would you format SQL server dates? 

ANS : “For this, the FORMAT function can be used. This function allows the formatting of the date and time. Firstly, I would decide what format I wanted, for example DD/MM/YYYY or MM-DD-YY. If I wanted to get DD/MM/YYYY I would use SELECT FORMAT (getdate(), 'dd/MM/yyyy’).” 

Q12. What is primary and foreign key? 

ANS : “For a database table to qualify as ‘relational’, it needs to have a primary key. A primary key has one or more columns. The data within the columns is then used to identify each row in a table. Basically, these are very similar to street addresses, for example. So, if you think of the rows in the table as the dustbins or trashcans belonging to houses on a street, the primary key is the list of the addresses. Primary key attributes cannot have NULL values. A foreign key is a column in one table. In that table, the values are members of a primary key column in another table. It is possible for a foreign key attribute to accept NULL values and for there to be more than one foreign key in a table.” 

Q13. What is database denormalization? 

ANS : “Denormalization is a method used to improve the performance of a database, allowing the retrieval of data in the quickest time possible. Essentially, the process of denormalization adds redundant database data into a table which in turn enables the performance of the database to improve significantly, as the same data can be put in several places.”

 

Q14. What is database normalization? 

ANS : “Normalization reduces redundancy of data within a database. Normalization is a design technique that places larger database tables into smaller tables and then uses specific relationships to link them. In other words, normalization is the process of putting each piece of data in the appropriate place, using multiple tables. The benefit is keeping data integrity and it makes updating faster, but retrieval of data, can then in turn be slower.”

 

Q15. In SQL, what is a subquery? 

ANS : “A subquery is often referred to as an inner query or inner select. As the name suggests, it is an SQL query nested inside a larger query, such as a SELECT, UPDATE, or INSERT statement, or indeed inside another subquery. A subquery can be used to retrieve data that will be used in the main query. This in turn provides a condition to restrict the retrieval of data even further.” 

Q16. What happens to the data rows in the table when the table contains a clustered index? What happens to the data rows in the table when the table contains a clustered index? 

ANS : “The data rows will be stored in order. Cluster indexes sort data rows based on their key values. When a table doesn’t use clustered index, this is called a heap – in other words, the data rows will be unordered.”