SQL (Structured Query Language) is a vital skill for a career in analytics, Big Data, databases and Data Science. As a go-to tool for pulling and manipulating data in a database, we have listed and explained top 50 interview questions in SQL from basic to advanced questions.
These interview questions will equip you for any interview in Data analytics positions.
Q1. What is the difference between DELETE and TRUNCATE statements?
Q2. What are the different subsets of SQL?
- DDL (Data Definition Language) – DDL helps you perform various operations on the database such as CREATE, ALTER and DELETE objects.
- DML (Data Manipulation Language) – DML mainly used to manipulate data in a database with the help of – INSERT, UPDATE and DELETE to retrieve data from a database.
- DCL (Data Control Language) – DCL allows you to control access to the database. (Grant, Revoke access permissions).
Q3. What is the meaning of DBMS? What are the different types of DBMS?
A database is a collected, stored and organized data.
A Database Management System (DBMS) is a set of application programs used to access, update, delete and manage data.
There are two types of DBMS:
- Relational Database Management System: RDBMS data are stored in relationship tables. Usually used to manage business data. Example – SQL Server.
- Non-Relational Database Management System: In Non- RDBMS data stored do not have any relationship. Example – Mongo
Q4. What is a Table and Field in SQL?
A Table is a collection of data in an organized manner in form of rows and columns. A Field refers to the number of columns in a table. For example:
Table: Hospital B
Field: Patient ID, Patient Name, Patient Treatment
Q5. What are joins in SQL
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Join clause can be used to merge two or more tables to retrieve data. There are 4 joins in SQL namely:
- Inner Join
- Right Join
- Left Join
- Full Join
Q6. What is the difference between CHAR and VARCHAR datatype in SQL?
CHAR is used for Fixed length of character and
VARCHAR is used for character strings of variable length. (Can store any length of datatype)
Q7. What is a Primary key?
APrimary keyis a column in a table used to identify records. Example: Hospital records – Name, Address, SSN, Sex, Race etc.
Q8. What are Constraints?
Constraints are used to specify the limit on the data type of the table. Used for specification while creating or altering the table statement. The sample of constraints are:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Q9. What is the difference between SQL and MySQL?
SQL is a standard language which stands for Structured Query Language whereas MySQL is a database management system. SQL is the core of relational database which is used for accessing and managing database, MySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix etc.
Q10. What is a Unique key?
A Unique key identifies a single row in the table, allows Null values and multiple values allowed per table.
Q11. What is a foreign key?
A foreign key is a table that contains a primary key in another table thereby creating relationship between both tables.
Q12. Write a SQL query to fetch only even rows from table.
Ans. Using the same Row_Number() and checking that the remainder when divided by 2 is 0-
Q13. What is the difference between clustered and non-clustered index in SQL?
The differences between the clustered and non-clustered index in SQL are:
- Clustered index is used for easy retrieval of data from the database and it’s faster whereas reading from non-clustered index is relatively slower.
- Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non-clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
- One table can only have one clustered index whereas it can have many non-clustered index.
Q14. Write a SQL query to display the current date?
Q15. List the different type of joins?
There are various types of joins which are used to retrieve data between the tables. There are four types of joins, namely:
Inner join: Returns rows when there is a match in both tables.
Left Join: Left Join in SQL Server is used to return all the rows from the right table but only the matching rows from the left table.
Right Join: Right Join in SQL Server returns rows from the left table and matched rows from the right table.
Full Join: Full join returns all the records when there is one match in one of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
Q16. What is Denormalization?
Denormalization refers to a technique used to increase database performance by addition of redundant data into a table by incorporating database queries that combine data from various tables into a single table.
Q17. What are Entities and relationships?
Entities: An Entity could be person, or place in the real world scenario about which data can be stored in a database. Tables store data that represents one type of entity. For example – A hospital database has a patient table to store patient visit and treatment information. These information are stored as a set of attributes (columns within the table) for each patient.
Relationships: Relationship shows the connection between each other. For example – The patient name is related to the patient contact information, which might be in the same table.
Q18. What is an Index?
An index is a performance tuning method to enhance faster retrieval of records in a table. Indexes create an entry for each value and retrieving data becomes faster.
Q19. Explain different types of Index
There are three types of index namely:
Unique index does not allow the field to have duplicate values if the column is unique indexed. If a primary key is defined, a unique index can be applied automatically.
Clustered index reorders the physical order of the table and searches are based on key values.
Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data.
Q20. What is Normalization and what are the advantages of it?
Normalization is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:
- Better Database organization
- Data access becomes more efficient
- Quickly find the information
- Security implementation becomes easier
- Reduction of redundant and duplicate data
- Ensure data Consistency after modification
Q21. What do you mean by “Trigger” in SQL?
Trigger in SQL is a special type of stored procedure that executes automatically when modifications are done to a database. Execution occur when an insert, update or any other query is executed in a table within its database.
Q22. You are given a string “DataCatchup”. The string contains two unnecessary spaces – one at the start and another at the end. You find out the length of this string by applying the below queries.
If op1, op2, op3, op4 are the output of the Query 1, 2, 3 and 4 respectively, what will be the correct relation between these four queries?
- op1 = op2 and op3 = op4
- op1< op3 and op2 > op4
- op1 > op3 and op2< op4
- op1 > op3 and op2 > op4
A) 1 or 2
D) 1 and 4
Q23. Are NULL values same as that of zero or a blank space?
A NULL value is not the same as that of zero or a blank space. NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character.
Q24. What is subquery in SQL?
A subquery is a query inside another query to retrieve data. In a subquery, the outer query is called the main query whereas the inner query is called subquery.
Q25. Write a SQL query to fetch common records between two tables.
Ans. Using INTERSECT-
Q26. SQL query to count number of records in a table
Q27. Write a SQL query to get the third highest salary of an employee from Employee table?
Q28. What is the difference with “BETWEEN” and “IN” condition operators?
BETWEEN operator is used to display records within a range of values, while the IN condition operator is used to check for values contained in a specific set of values. Example of BETWEEN:
Example of IN:
Q29. Why are SQL functions used?
SQL functions are used for the following:
- COUNT function performs some calculations on the data
- To format dates and numbers
- To modify individual data items
- Data type conversion
Q30. What is the need of MERGE statement?
The MERGE statement allows conditional update or insertion of data into a table by performing an UPDATE if a row exists, or an INSERT if the row does not exist.
Q31. What is the difference between ‘HAVING’ CLAUSE and ‘WHERE’ CLAUSE?
HAVING clause is used to filter groups is used after GROUP BY function in a query whereas WHERE Clause is simply used to filter rows in a table and used before GROUP BY clause.
Q32. List the ways that Dynamic SQL can be executed?
Following are the ways in which dynamic SQL can be executed:
- Write a query with parameters.
- Using EXEC.
- Using sp_executesql.
Q33. What are the types of set operators in SQL?
Set operators in SQL are:
Intersect or Minus operators
Q34. What is an ALIAS command?
ALIAS name can be any abbreviation given to a table or a column in a SQL query. The ALIAS name can be added in the WHERE clause to identify a particular table or a column.
The above example, pp refers to ALIAS name for Person.Person table.
Q35. How can you fetch alternate records from a table?
You can fetch alternate records i.e both odd and even row numbers.
For example- To display even numbers, use the following command:
Now, to display odd numbers:
Q36. How can you fetch first 5 characters from a string.
Q37. What is a View?
A view is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. View can have data of one or more tables combined and it depends on the relationship.
Q38. What are Views used for?
A view can be referred to a logical snapshot based on a table or another view.
Views are used for the following reasons:
- Restricting access to data.
- Ensuring data independence.
- Providing different views of same data.
- Making complex queries simple.
Q38. What is a Stored Procedure
A Stored Procedure is a function which consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required which saves time and avoid writing code again and again.
Q40. What is Auto Increament in SQL
An Auto increment keyword allows each user create a unique number that gets generated whenever
A new record is inserted into the table.
This keyword is usually required whenever PRIMARY KEY is used.
AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
Q41. What is a Data warehouse
Data warehouse refers to a central repository of data where the data is pulled from multiple sources of information. These data are consolidated, compared, analyzed, transformed and made available for business decision. Warehouse data also have a subset of data called Data Marts.
Q42. What are STUFF and REPLACE function
STUFF Function: STUFF function is used to overwrite existing character or insert a string into another string.
string_expression: it is the string that will have characters substituted
Start: This refers to the starting position
length: It refers to the number of characters in the string which are substituted.
replacement_string: They are the new characters which are injected in the string.
REPLACE function: This function is used to replace the existing characters of all the occurrences.
Q43. Assume you are given the two tables Hospital 1 and Hospital 2 which represent two different departments of hospitals
Hospital 1 TABLE
Hospital 2 TABLE
Now, you want the names of all people who work in both the Hospitals. Which of the following SQL query would you write?
A) SELECT NAME FROM Hospital 1 INTERSECT SELECT NAME FROM Hospital 2;
B) SELECT NAME FROM Hospital1 UNION SELECT NAME FROM Hospital2;
C) SELECT NAME FROM Hospital1 DIFFERENCE SELECT NAME FROM Hospital 2;
D) None of these
INTERSECT would be used for such output.
Q44. Write a query to fetch only the FirstName (string before space) from the FullName column of employeeDetail’s table.
Ans. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use LOCATE method in mySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use SUBSTRING OR MID method.
MySQL- Using MID
SQL Server-Using SUBSTRING
Also, we can use LEFT which returns the left part of a string till specified number of characters.
Q45. List some advantages and disadvantages of Stored Procedure
A Stored Procedure can be used as a modular programming, meaning create once, store and call for several times whenever it is required. This supports faster execution. It also reduces network traffic and provides better security to the data.
The only disadvantage of Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server.
Q46. Suppose you have 1000 records in a table called “Customers”. You want to select top 100 records from it. Which of the below commands can you use?
C) 1 and 2
D) None of them
Both query can be used to get the desired output.
Q46. Which of the following is the outcome of the following query?
A) Fred and Anita are close friends
B) Anita and Anita are close friends
C) Fred and Fred are close friends
D) Anita and Fred are close friends
“Fred” will be replaced by “Anita”.
Q47. Explain how to send an email from SQL database.
SQL Server has a feature for sending mails. Stored
procedures can also be used for sending mail on demand. With SQL Server 2005,
MAPI client is not needed for sending mails.
The following is the process for sending emails from database.
- Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.
- Write a script to send an e-mail. The following is the script.
Q48. What is the SQL CASE statement used for? Explain with an example?
SQL Case statement allows you to embed an IF-ELSE clause in the SELECT clause.
Q49. Write a SQL query to fetch employee names having salary greater than or equal to 2000 and less than or equal to 5000.
Ans. Here, we will use BETWEEN in the ‘where’ clause to return the empId of the employees with salary satisfying the required criteria and then use it as subquery to find the FullName of the employee from EmployeeDetails table.
Q50. Write a SQL query to fetch only odd rows from table.
Ans. This can be achieved by using Row_number in SQL server.
While studying SQL Interview Questions, if you want to get trained from quintessential professionals in SQL Database technology, you can opt for a structured training from Data Catchup.
Click the Contact Us and subscribe button.