Relational Database Management System (RDBMS) is one of the best and most 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.
Table of Contents
Top SQL Interview Questions And Answers
What is DBMS?
DBMS (Database Management System) is software that handles the creation, maintenance, and use of a database. It may include MySQL, SQL Server, Microsoft Access, Oracle, RDBMS, dBASE, and others.
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.
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.
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 open-source database.
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. 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).
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
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 NULL.
What is a Unique Key?
The unique key is the group of one or more fields or columns that uniquely identify the database record. The unique key is the same as a primary key but it accepts the null value.
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.
What is a Join?
As the name indicates, join is the name for combining columns from one or several tables by using common values for each. Whenever the joins are used, the keys play a vital role.
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 if there are 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 if 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.
What is Normalization?
Normalization is the process of organizing fields into a related table to increase integrity and remove redundancy 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.
What is Denormalization?
It is a technique in which data from higher to lower normal forms are accessed and it is also used to add redundant data to one or more tables.
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 and dependent on the primary key.
- Third Normal Form (3NF)
Remove the columns that are not dependent on primary key constraints and make sure they meet 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 of their SQL interview questions.
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.
What is an Index?
The index is the method of creating an entry for each value to retrieve the records from the table faster. It is a valuable performance-tuning method used for faster retrievals.
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
A clustered index reorders the physical order of a table and searches based on its key values. Keep in mind, that 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.
What is a Cursor?
The cursor is known as the control of the database that enables traversal over the rows in the table. It is a 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.
What is a Database Relationship And What Are They?
The database relationship is the connection between the tables of any particular database. The following are the main types of relationships:
- One-to-One Relationship
- One-to-Many Relationship
- Many-to-One Relationship
- Self-Referencing Relationship
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 period.
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.
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.
Enlist The Names 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)
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: This is used to remove tables, procedures, and other database objects.
- ADD COLUMN: This is used to add any column to the table schema.
- DROP COLUMN: This is used to drop a column from any database table structure.
Explain DML With its Examples
DML (Data Manipulation Language) is a computer programming language 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 it into another table.
- INSERT: is used to insert data or records into a table.
- DELETE: This is used to delete records from any database table.
- UPDATE: This is used to update the values of different records in the database.
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.
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: This is used to set the characteristics of the transaction.
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 query is asked in the SQL interview questions so that they can understand the candidate’s knowledge and hands-on experience.
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.
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.
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 a conditional parameter.
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 to or used with other functions.
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
What is Data Integrity?
Data integrity is the overall accuracy, completeness, and consistency of data stored in a database. For example, to maintain data integrity, the numeric columns/sales should not accept alphabetic data.
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 the AUTO INCREMENT keyword should be used otherwise use the IDENTITY keyword in the case of the SQL Server.
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.
What is a Data Warehouse?
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 is based on its subsets called data marts which are oriented to a specific business line or team.
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.
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.
What are User Defined Functions?
Like functions in programming languages, SQL user-defined functions are routines or functions that accept parameters, act, 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.
What Are All Types of User Defined Functions?
The following are the user-defined functions:
- Scalar Functions that return the unit or single-valued results
- Inline Table-valued functions that return the table as a return
- Multi-statement valued functions that return table as a return
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.
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)
What Are The Pros and Cons of Stored Procedure?
The stored procedure is based on modular programming which 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 the stored procedure is that it is executed only in the database and utilizes more memory.
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 which is the reason why bank transactions are using OLTP. You can view an insight into the workings of an OLTP system.
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 the 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;
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.
What is The Difference Between Null, Zero, and Blank Space?
Null is neither the 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.
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.
What are Union, Minus, and Intersect Commands?
Union operator is used to combine the results of two tables and remove 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.
What is an ALIAS command?
ALIAS name can be given to a column or table and referred to in the WHERE clause to identify the column or table. Alias column syntax:
SELECT column_name AS alias_name
FROM table_name;
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.
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
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
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
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
What is The Command Used to Fetch The 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
What is a Composite Primary Key?
The primary key that is created on more than one column is known as the composite primary key.
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_’
What Do You Mean by ROWID?
ROWID is an 18-character-long pseudo column attached to each row of a database table.
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.
What are STUFF and REPLACE Functions?
The 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)
What Are GROUP Functions and Give Some Examples?
Group functions are mathematical functions used to work on a set of rows and return one result per group. AVG, COUNT, SUM, VARIANCE, MAX, and MIN are the most commonly used Group functions.
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.
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 a lowercase string.
Syntax:
LOWER(‘string’)
- UPPER: This function takes a string as an argument and returns it into the uppercase string. Syntax:
UPPER(‘string’)
- INITCAP: This function returns the string with the first letter in uppercase and others in lowercase.
Syntax:
INITCAP(‘string’)
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 is used to return the 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)
What is a Shared Lock?
The shared lock doesn’t cause many 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 the read-only mode of both transactions.
What is a Deadlock?
A deadlock situation is created when two processes are competing for access to a resource but are unable to obtain it because the other process is preventing it. So, the process cannot proceed until one of the processes is terminated.
For example: Process A locks Table A and Process B locks Table B. Now Process A requests Table B and waiting for it and Process B is also waiting for Table A.
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 large updates. SQL Server uses 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.
Does View Contain Data?
No, because views are virtual structures.
What is CTE?
A 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 practicing your theoretical knowledge to enhance your performance. Keep in mind, “practice makes a man perfect”.