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.
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.
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.
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.
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.
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).
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:
Field: Std ID, Std Name, Date of Birth
Data: 23012, William, 10/11/1989
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.
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.
The foreign key is used to link two tables together and it is a field that refers to the primary key of another table.
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.
Depending on the relationship between tables, join has the following types:
It returns rows when there is at least one match of rows between the tables.
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.
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.
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.
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.
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.
Following are the different normalization forms:
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.
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.
Remove the columns that are not dependent on primary key constraints and make sure it meets all the requirements of the second normal form.
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.
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.
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.
There are three types of indexes:
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 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 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.
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.
The database relationship is the connection between the tables of any particular database. Following are the main types of relationships:
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.
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.
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.
There are four subsets of SQL:
As the name indicates, Data Definition Language (DDL) is used to define the structure of data, table, schema, and modify it. For example:
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:
DCL (Data Control Language) deals with the access rights and permission control of the database.
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:
Following SQL query is used to return the database system date and time:
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
Following are the user-defined functions:
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.
Following are the different types of collation sensitivity:
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.
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.
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:
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 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.
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.
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.
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.
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
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.
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
Common records can be achieved by the following command:
Select studID from student INTERSECT Select StudID from Exam
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
Apply the following SQL query to select unique records from any table:
Select DISTINCT StudentID, StudentName from Student
Following are the ways to fetch the first 4 characters of the string:
The primary key that is created on more than one column is known as composite primary key.
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:
ROWID is an 18-character long pseudo column attached with each row of a database table.
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.
STUFF Function is used to insert a string into another string or overwrite existing characters.
REPLACE function is used to replace any existing characters of all the occurrences.
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.
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.
Following are the case manipulation functions in SQL:
1. CONCAT: This function is used to append or concatenate string2 to the end of string1.
2. LENGTH : This function returns the total length of the input string used.
3. SUBSTR : This function will return a portion of a string from any given start point to an endpoint.
4. INSTR : This function used to return numeric position of a character (or a string) in any given string.
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.
LPAD(Column|Expression, n, ‘String’)
RPAD(Column|Expression, n, ‘String’)
6.TRIM : This function is used to trim the string input from the start or end (or both).
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.
REPLACE(Text, search_string, replacement_string)
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.
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.
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.
No, because views are virtual structures.
Common Table Expression (CTE) is an expression that contains temporary results defined in a SQL statement.
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”.