Relational Database Management System (RDBMS) is one of the best and commonly used databases, therefore SQL skills are essential in most of the job roles. In this SQL Interview Questions and answers blog, you will learn the most frequently asked questions on SQL (Structured Query Language).
There is a collection of 70+ SQL interview questions that an interviewer plans to ask during an interview process included in this blog. Remember, this blog is not written for only freshers. It is also recommended for experienced candidates for a quick revision of major SQL interview questions before appearing for an interview.
Let’s start.
1. What is DBMS?
DBMS (Database Management System) is a software that handles the creation, maintenance, and use of a database. It may include MySQL, SQL Server, Microsoft Access, Oracle, RDBMS, dBASE, and others.
2. What is RDBMS?
RDBMS (Relational Database Management System) is a software that stores the data into the collection of tables in a relationship based on common fields between the columns of the table.
3. What is SQL?
Structured Query Language is an ANSI (American National Standards Institute) standard language used to interact with the database for the execution of different queries for creation, deletion, insertion, and record updates. Don’t forget to understand the difference between static vs dynamic SQL.
4. What is MySQL?
MySQL is a Relational Database Management System (RDMS) such as SQL Server, Informix, etc., and uses SQL as the standard database language. It is open-source software backed by Oracle and used to deploy cloud-native applications using an opensource database.
5. What is a Database?
It is the structured form of data stored in a well-organized manner that can be accessed and manipulated in different ways. The database is based on the collection of schemas, tables, queries, and views. In order to interact with the database, different database management systems are used. MySQL is used in WordPress and gives you the option to create a MySQL database and its User with the help of a control panel (cPanel).
6. Can you explain tables and fields?
The table is the set of organized data stored in columns and rows. Each database table has a specified number of columns known as fields and several rows which are called records. For example:
Table: Student
Field: Std ID, Std Name, Date of Birth
Data: 23012, William, 10/11/1989
7. What is the primary key?
The primary key is the combination of fields based on implicit NOT NULL constraint that is used to specify a row uniquely. A table can have only one primary key that can never be the NULL.
8. What is a unique key?
The unique key is the group of one or more fields or columns that uniquely identifies the database record. The unique key is the same as a primary key but it accepts the null value.
9. What is a foreign key?
The foreign key is used to link two tables together and it is a field that refers to the primary key of another table.
10. What is a join?
As the name indicates, join is the name of combining columns from one or several tables by using common values to each. Whenever the joins are used, the keys play a vital role.
11. What are the types of join and explain each?
Depending on the relationship between tables, join has the following types:
- Inner Join
It returns rows when there is at least one match of rows between the tables.
- Right Join
It returns the common rows between the tables and all rows of the Right-hand side table. In other words, it returns all the rows from the right-hand side table even there is no matches in the left-hand side table.
- Left Join
It returns the common rows between the tables and all rows of the left-hand side table. In other words, it returns all the rows from the left-hand side table even there are no matches in the right-hand side table.
- Full Join
As the name indicates, full join returns rows when there are matching rows in any one of the tables. It combines the results of both left and right table records and it can return very large result-sets.
12. What is normalization?
Normalization is the process of organizing fields into a related table for increasing integrity and removing redundancy in order to improve the performance of the query. The main aim of the normalization is to add, delete, or modify the fields that can be made in a single table.
13. What is Denormalization.
It is a technique in which data from higher to lower normal forms accessed and it also used to add redundant data to one or more tables.
14. What are all the different normalizations?
Following are the different normalization forms:
- First Normal Form (1NF):
It is the process of removing all the duplicate columns from the table and ensuring that each cell contains only one piece of information. In the first normal form, there are only atomic values without any repeating groups.
- Second Normal Form (2NF):
A relation is in 2NF if it satisfies the first normal form and does not contain any partial dependency. Moreover, make sure the non-key attributes are fully functional dependent on the primary key.
- Third Normal Form (3NF):
Remove the columns that are not dependent on primary key constraints and make sure it meets all the requirements of the second normal form.
- Fourth Normal Form (4NF):
It should not have multi-valued dependencies and meet all the requirements of the third normal form.
It depends on the interviewers, they can only ask a single form of normalization or all in their SQL interview questions.
15. What is a View?
The view is a virtual table that consists of the subset of data contained in a table and takes less space to store. It can have data from multiple tables depending on the relationship.
16. What is an Index?
The index is the method of creating an entry for each value in order to retrieve the records from the table faster. It is a valuable performance tuning method used for faster retrievals.
17. What are all the different types of indexes?
There are three types of indexes:
- Unique Index
The unique index ensures the index key column has unique values and it applies automatically if the primary key is defined. In case, the unique index has multiple columns then the combination of values in these columns should be unique.
- Clustered Index
Clustered index reorders the physical order of a table and searches based on its key values. Keep in mind, each table can have only one clustered index.
- Non-Clustered Index
Non-Clustered Index does not alter the physical order but maintains a logical order of table data. Each table in the non-clustered index can have 999 indexes.
18. What is a Cursor?
Cursor is known as the control of the database that enables traversal over the rows in the table. It is very useful tool for different operations such as retrieval, addition, and removal of database records. You can view the cursor as a pointer to one row in a set of rows.
19. What is a database relationship and what are they?
The database relationship is the connection between the tables of any particular database. Following are the main types of relationships:
- One to One Relationship
- One to Many Relationship
- Many to One Relationship
- Self-Referencing Relationship
20. What is a query?
The database query is the name of getting information back from the database with the help of coding. We can get expected results from the database without wasting our valuable time because the query gives us output within a short-time period.
21. What is subquery?
As the name indicates, it is also a query but used in another query. The outer query is known as the main query and the inner query is called a subquery. The subquery will be executed first and pass the results to the main query then the output of both queries will be displayed.
22. What are the types of subquery?
Correlated and non-correlated are the types of subquery where the non-correlated query is considered an independent query and correlated is treated as a dependent query.
23. Enlist the name of different subsets of SQL?
There are four subsets of SQL:
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
24. Explain DDL with its examples?
As the name indicates, Data Definition Language (DDL) is used to define the structure of data, table, schema, and modify it. For example:
- CREATE: is used to create tables, databases, schema, etc.
- DROP: is used to drop/remove tables and other database objects.
- ALTER: is used to alter or change the definition of database objects.
- TRUNCATE: is used to remove tables, procedures, and other database objects.
- ADD COLUMN: is used to add any column to table schema.
- DROP COLUMN: is used to drop a column from any database table structure.
25. Explain DML with its examples?
DML (Data Manipulation Language) is a computer programming language is used for the addition (insertion), deletion and modification of data in the database. For example:
- SELECT INTO: is used to select data from one table and insert into another table.
- INSERT: is used to insert data or records into a table.
- DELETE: is used to delete records from any database table.
- UPDATE: is used to update the values of different records in the database.
26. Explain DCL with its examples?
DCL (Data Control Language) deals with the access rights and permission control of the database.
- GRANT command is used to grant access rights to database objects.
- REVOKE is used to withdraw permission from database objects.
27. Explain TCL with its examples?
TCL (Transaction Control Language) is a set of commands used to perform a specific task on objects in a single unit of execution. In simple words, TCL commands deal with transactions in a database. For example:
- COMMIT: is used to commit transactions. Once a commit is made it cannot be rolled back, so the previous image of the database cannot be retrieved before running this transaction.
- ROLLBACK: is used to revert the steps in transactions if an error arises.
- SAVEPOINT: is used to set the savepoint in the transaction to which steps can be rolled back.
- SET TRANSACTION: is used to set characteristics of the transaction.
28. Write a SQL query to display the current date?
Following SQL query is used to return the database system date and time:
SELECT GETDATE();
This type of short queries are asked in the SQL interview questions so that they can understand about the candidate’s knowledge and hands-on experience.
29. What is a stored procedure?
It is a subroutine available to applications that need to access a relational database management system (RDBMS). These procedures are stored in the data dictionary and only executed in the database which is considered a big disadvantage because it occupies more memory in the database server. Moreover, it provides security and functionality to those users who are unable to access data directly, so they can be granted access via stored procedures.
30. What is a trigger?
A database trigger is a code or program that helps to maintain the integrity of the database. It automatically executes the response to particular events on a table in the database.
For example, when a new employee is added to the employee database, new records should be created in the related tables like Salary, Attendance, and Bonus tables.
31. What is the difference between DELETE and TRUNCATE commands?
TRUNCATE command removes all the table rows permanently that can never be rolled back. DELETE command is also used to remove the rows from the table but commit and rollback can be performed after the deletion. Moreover, the WHERE clause is also used as conditional parameters.
32. What is the main difference between local and global variables?
As the name indicates, the local variables can be used inside the function but global ones are used throughout the program. Local variables are only limited to the function and cannot be referred or used with other functions.
33. What is a constraint and what are the common SQL constraints?
SQL constraint is used to specify the rules for data in a table and can be specified while creating or altering the table statement. The following are the most widely used constraints:
- NOT NULL
- CHECK
- DEFAULT
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
34. What is Data Integrity?
Data integrity is the overall accuracy, completeness, and consistency of data stored in a database. For example, in order to maintain data integrity, the numeric columns/sells should not accept alphabetic data.
35. What is Auto Increment?
Auto increment allows the users to create a unique number to be generated whenever a new record is inserted into the table. It helps to keep the primary key unique for each row or record. If you are using Oracle then AUTO INCREMENT keyword should be used otherwise use the IDENTITY keyword in the case of the SQL Server.
36. What is the difference between the Cluster and Non-Cluster Index?
The clustered index defines the order in which data is physically stored in a database table and used for easy retrieval by altering the way that the records are stored.
The non-clustered index improves the speed of data retrieval from database tables but stores data separately from the data rows. It makes a copy of selected columns of data with the links to the associated table.
37. What is Datawarehouse?
It is the name of a central repository of data from multiple information sources for analytics and business intelligence activities. Datawarehouse has an enterprise-wide depth and based on its subsets called data marts which are oriented to a specific business line or team.
38. What is Self-Join?
A self-join SQL query is used to compare to itself and values in a column are compared with other values in the same column in the same database table.
39. What is Cross-Join?
Cross-join is used to generate a paired combination of each row of table A with each row of table B. If the WHERE clause is used in cross join then the SQL query will work like an INNER JOIN.
40. What is user-defined functions?
Like functions in programming languages, SQL user-defined functions are routines or functions that accept parameters, perform an action, such as a complex calculation, and return the output of that particular action as a value. There is no need to write the same logic several times because the function can be called whenever needed.
41. What are all types of user-defined functions?
Following are the user-defined functions:
- Scalar Functions that return the unit or single-valued results
- Inline Table-valued functions that return table as a return
- Multi statement valued functions that return table as a return
42. What is collation?
Collation provides a set of rules that determine how character data can be sorted and compared in a database. It also provides the case and accent sensitivity properties. Collation is used to compare A and other language characters with the help of ASCII value.
43. What are all different types of collation sensitivity?
Following are the different types of collation sensitivity:
- Case Sensitivity (A & a, B & b or any other uppercase and lowercase letters)
- Accent Sensitivity
- Kana Sensitivity (Japanese Kana characters)
- Width Sensitivity (Single byte character (half-width) & same character represented as a double-byte character)
44. What are the pros and cons of Stored Procedure?
The stored procedure is based on modular programming that means create once, store and call several times whenever you need it. It supports faster execution and reduces the network traffic for optimum performance.
The main disadvantage of stored procedure is that it executed only in the database and utilized more memory.
45. What is Online Transaction Processing (OLTP)?
As the name indicates, Online Transaction Processing (OLTP) is used to manage transaction-based applications that can be used for data entry, retrieval, and processing. It makes data management simple and efficient that is the reason why bank transactions are using OLTP. You can view an insight into the working of an OLTP system.
46. Define COMMIT
COMMIT command is used to save all the transactions to the database since the last COMMIT or ROLLBACK command where a transaction is a unit of work performed against any database.
Here is the syntax of COMMIT command:
COMMIT;
Consider an example of the deletion of those records which have age = 25 and then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
47. What is CLAUSE?
SQL clause is used to filter some rows from the whole set of records with the help of different conditional statements. For example, WHERE and HAVING conditions.
48. What is the difference between null, zero and blank space?
Null is neither same as zero nor blank space because it represents a value that is unavailable, unknown, or not applicable at the moment. Whereas zero is a number and blank space belongs to characters.
49. What is a recursive stored procedure?
It is the same as stored procedures but it calls by itself until it reaches any boundary condition. So, it is the main reason why programmers use recursive stored procedures to repeat their code any number of times.
50. What is Union, minus and Intersect commands?
Union operator is used to combining the results of two tables and removes the duplicate rows. Minus is used to return matching records of the first and second queries and other rows from the first query but not by the second one. The Intersect operator is used to return the common rows returned by the two select statements.
51. What is an ALIAS command?
ALIAS name can be given to a column or table and referred in WHERE clause to identify the column or table. Alias column syntax:
SELECT column_name AS alias_name
FROM table_name;
52. What are aggregate and scalar functions?
The aggregate function performs mathematical calculations against a collection of values and returns a single summarizing value. The scalar function returns a single value based on the input value.
53. How can you create an empty table from an existing table?
With the help of the following command, we can create an empty table from an existing table with the same structure with no rows copied:
Select * into copytable from student where 1=2
54. How to fetch common records from two tables?
Common records can be achieved by the following command:
Select studID from student INTERSECT Select StudID from Exam
55. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers with the following commands:
For even numbers:
Select studentId from (Select rownum, studentId from student) where mod(rowno,2)=0
For odd numbers:
Select studentId from (Select rownum, studentId from student) where mod(rowno,2)=1
56. How to select unique records from a table?
Apply the following SQL query to select unique records from any table:
Select DISTINCT StudentID, StudentName from Student
57. What is the command used to fetch first 4 characters of the string?
Following are the ways to fetch the first 4 characters of the string:
- Select SUBSTRING(StudentName,1,4) as studentname from student
- Select LEFT(Studentname,4) as studentname from student
58. What is a composite primary key?
The primary key that is created on more than one column is known as composite primary key.
59. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching.
% (percent sign) Matches zero or more characters and _ (Underscore) is used for matching exactly one character. For example:
- Select * from Student where studentname like ‘m%’
- Select * from Student where studentname like ‘meh_’
60. What do you mean by ROWID?
ROWID is an 18-character long pseudo column attached with each row of a database table.
61. What are Entities and Relationships?
The entity is the name of real-world objects either tangible or intangible and it is the key element of relational databases. For a database entity, workflow and tables are optional but properties are necessary. For example: In the database of any institute, students, professors, workers, departments and projects can be known as entities. Each entity has associated properties that offer it an identity.
The relationship is the name of links or relations between entities that have something to do with each other. For example, the employee table should be associated with the salary table in the company’s database.
62. What are STUFF and REPLACE functions?
STUFF Function is used to insert a string into another string or overwrite existing characters.
Syntax:
- STUFF(string_expression,start, length, replacement_characters)
REPLACE function is used to replace any existing characters of all the occurrences.
Syntax:
- REPLACE (string_expression, search_string, replacement_string)
63. What are GROUP functions and give some examples?
Group functions are mathematical functions used to work on the set of rows and return one result per group. AVG, COUNT, SUM, VARIANCE, MAX, MIN are most commonly used Group functions.
64. What is the MERGE statement?
MERGE statement is used to combine insert, delete and update operations into one statement. It is also used to synchronize two tables and make the changes in one table based on values matched from another.
65. What are the different case manipulation functions in SQL?
Following are the case manipulation functions in SQL:
- LOWER: This function takes a string as an argument and returns it by converting it into the lowercase string.
Syntax:
LOWER(‘string’)
- UPPER: This function takes a string as an argument and returns it into uppercase string. Syntax:
UPPER(‘string’)
- INITCAP: This function returns the string with the first letter in uppercase and others in lowercase.
Syntax:
INITCAP(‘string’)
66. What are the character manipulation functions?
Character-Manipulative Functions
1. CONCAT: This function is used to append or concatenate string2 to the end of string1.
Syntax:
CONCAT(‘String1’, ‘String2’)
2. LENGTH : This function returns the total length of the input string used.
Syntax:
LENGTH(Column|Expression)
3. SUBSTR : This function will return a portion of a string from any given start point to an endpoint.
Syntax:
SUBSTR(‘String’,start-index,length_of_extracted_string)
4. INSTR : This function used to return numeric position of a character (or a string) in any given string.
Syntax:
INSTR(Column|Expression, ‘String’, [,m], [n])
5. LPAD and RPAD: LPAD returns the strings padded to the left and RPAD to the right (as per the use.
Syntax:
LPAD(Column|Expression, n, ‘String’)
Syntax:
RPAD(Column|Expression, n, ‘String’)
6.TRIM : This function is used to trim the string input from the start or end (or both).
Syntax:
TRIM(Leading|Trailing|Both, trim_character FROM trim_source)
7. REPLACE : As the name indicates the REPLACE function searches for a character string and, if found, replaces it with a given replacement string at all the occurrences.
Syntax:
REPLACE(Text, search_string, replacement_string)
67. What is a shared lock?
Shared lock doesn’t cause much problems and occurs when the object needs to be read. It comes when two transactions are granted and read access. There is no conflict because noting is being updated due to read-only mode of both transactions.
68. What is a deadlock?
A deadlock situation is created when two processes are competing for the access to a resource but unable to obtain because the other process is preventing it. So, the process cannot proceed until one of the processes to be terminated.
For example: Process A locks the Table A and process B locks the Table B. Now the process A requests the Table B and waiting for it and Process B is also waiting for Table A.
69. What is lock escalation?
Lock escalation is an optimization technique used to convert many fine-grained locks such as row or page locks into table locks for handling the large updates. SQL Server is using row-level locking by default, so it is easier to convert a large number of row locks into a single table lock for optimum results.
70. Does View contain Data?
No, because views are virtual structures.
71. What is CTE?
Common Table Expression (CTE) is an expression that contains temporary results defined in a SQL statement.
Final Words
These SQL interview questions and answers are not enough to pass out your interviews conducted by the top business brands. So, it is highly recommended to keep practice of your theoretical knowledge in order to enhance your performance. Keep in mind, “practice makes a man perfect”.
