SQL Interview Questions

If you're looking for SQL Interview Questions and Answers for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many reputed companies in the world. SQL database market continues even stronger by 2024 will be 70%.

According to research SQL by Microsoft grew at 10.32%, while Oracle grew by 3.5%. In the upcoming era, the competition will be more heated than it has been for years. So, You still have the opportunity to move ahead in your career in the SQL certification guide. Mindmajix offers Advanced SQL Interview Questions and Answers 2024 that helps you in cracking your interview & acquire a dream career as SQL Developer.

We have categorized SQL Interview Questions - 2024(Updated) into 2 levels they are:

• For Beginners
• Advanced

Below mentioned are the Top Frequently asked SQL Interview Questions and Answers that will help you to prepare for the SQL interview. Let's have a look at them.

Top 10 Frequently Asked SQL Interview Questions

1. What are DBMS and RDBMS?
2. What are the different types of tables used in SQL?
3. What are local and global variables?
4. What are the Set Operators?
5. What is the difference between OLAP and OLTP?
6. What is the use of the MERGE statement?
7. What are the differences between SQL and PL/SQL?
8. What do you mean by COMMIT in SQL?
9. What do you mean by NESTED triggers?
10. What are the different types of SQL sandboxes?

Want to enrich your career and become a professional in SQL Server DBA, then enroll in "SQL Server DBA Training" - This course will help you to achieve excellence in this domain.

Top SQL Interview Questions - Beginners

1. What do you understand by database, and what does it have?

A database can be defined as the structured form of data storage from which data can be retrieved and managed based on requirements. Basically, a database consists of tables where data is stored in an organized manner. Each table consists of rows and columns to store data. Data can be stored, modified, updated, and accessed easily in a database. For instance, a bank management database or school management database are a few examples of databases.

2. What are DBMS and RDBMS?

DBMS – Database Management System.

DBMS is the software that allows storing, modifying, and retrieving data from a database. And it is a group of programs that act as the interface between data and applications. DBMS supports receiving queries from applications and retrieving data from the database.

RDBMS – Relational Database Management System

Like DBMS, RDBMS is also the software that allows storing, modifying, and retrieving data from a database but a RELATIONAL database. In a relational database, the data in the tables have a relationship. Besides, RDBMS is useful when data in tables are being managed securely and consistently.

3. What are Query and Query language?

A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.

Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language. SQL returns data as columns and rows in a table, whereas other languages return data in other forms, like graphs, charts, etc.

4. What do you mean by subquery?

It is a query that exists inside the statements such as SELECT, INSERT, UPDATE, and DELETE. It may exist inside a subquery too. A subquery is also known as an inner query or inner select. The statement with a subquery is an outer query or outer select.

Let’s see the example shown below in which the maximum unit price is the result that will be returned by the subquery using the SELECT statement. Also, orders is the value that will be returned by the outer query using the SELECT statement.

5. What is SQL, and mention its uses?

SQL – Structured Query Language

SQL is known as the query programming language. It uses SQL queries to store, modify and retrieve data into and from databases. Briefly, SQL inserts, updates, and deletes data in databases; creates new databases and new tables; creates views and stored procedures; and sets permissions on the database objects.

6. What is Dynamic SQL, and when can you use it?

Dynamic SQL is the programming method that allows building SQL statements during runtime. You can use dynamic SQL when you do not know the full text of the SQL statements used in the program until runtime. Moreover, dynamic SQL can execute SQL statements that are not supported by static SQL programs. So, Dynamic SQL helps to build more flexible applications.

7. What do you understand by tables and fields in a database?

Tables are the database objects where data is stored logically. Like a spreadsheet, data is stored in the form of rows and columns in a database table. A row in a table represents a record, and columns represent the different fields. Fields have the data types such as text, dates, numbers, and links.

For example, consider the below customer database in which rows consist of the company names and columns consist of the various details of customers like first name, last name, age, location, etc. Here, number 1 indicates a record, number 2 indicates a field, and number 3 indicates the field value. 

8. What are the different types of tables used in SQL?

The following are the table types used in SQL:

• Partitioned tables
• Temporary tables
• System tables
• Wide tables

9. What are temporary tables?

Temporary tables only store data during the current session, and they will be dropped once the session is over. With temporary tables, you can create, read, update and delete records like permanent tables. Know that there are two types of temporary tables: local and global temporary tables.

Local temporary tables are only visible to the user who created them, and they are deleted the moment the user disconnects from the instance of the SQL server.

On the contrary, global temporary tables are visible to all users, and they are deleted only when all the users who reference the tables get disconnected.

10. What do you mean by Primary Key and Foreign Key in SQL?

Primary Key: A primary is a field or combination of many fields that help identify records in a table. Note that there can be only one primary key for a table. The table that has the primary key is known as the parent table.

Foreign Key: A foreign key is the field or combination of fields of a table that links the primary key of another table. A foreign key is used to create a connection between two tables. Unlike a primary key, a table can have one or many foreign keys. The table that has a foreign key is known as the child table.

For example, customer ID (1) is the primary key of the Customers table, and customer ID (2) in the orders table is identified as the foreign key to the customer's table. 

11. What are Superkey and candidate key?

A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.

A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.

Note that all the candidate keys can be Super keys, but all the super keys cannot be candidate keys.

12. What are composite keys?

A composite key is the combination of two or more columns in a table used to identify a row in a table. Know that a combination of columns is essential in creating composite keys because a single column in a composite key cannot identify a row in a table. We can say that the composite key is the primary key with a few more attributes or columns. Also, a composite key can be a combination of candidate keys.

13. What is JOIN operation in SQL, and mention their types?

JOIN is the logical operation used to retrieve data from two or more tables. It can be applied only when there is a logical relationship between two tables. Moreover, the JOIN operator uses the data of one table to retrieve data from another table.

Following are the different types of logical operations:

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

14. What do you mean by Self Join?

In self-join operation, a table is joined with itself to retrieve the desired data. Every join operation needs two tables as a basic rule. Therefore, in self-join, a table is joined with an instance of the same table. By doing this, values of the two table columns are compared with each other, and the desired data is retrieved as the result set.

[Related Article: Tech Mahindra Interview Questions]

15. What do you mean by Cross Join?

Cross Join is basically the Cartesian product type in which each row in a table is paired with all the rows of another table. So, the result set will be the paired combinations of the rows of two tables. Generally, cross join is not preferred by developers as it increases complexity in programs when there are many rows in tables. But, it can be used in queries if you identify normal join operation won’t be effective for your query.

16. What are the SQL constraints?

 SQL constraints specify conditions for a column or table to manage the data stored in tables effectively.

 The following are the commonly used SQL constraints.

• NOT NULL - This condition ensures columns won’t accept a NULL value.
• UNIQUE - It ensures that all the values in a column must be unique.
• CHECK - It ensures that all the column fields obey a specific condition.
• DEFAULT - It provides a default value for the fields of a column unless no value is specified for the fields
• CREATE INDEX - It ensures creating an index for tables so that retrieving data from the tables becomes easier
• PRIMARY KEY - It must identify every row of a table
• FOREIGN KEY -  It must link tables based on common attributes

17. What are local and global variables?

Local variables are declared inside a function so that only that function can call them. They only exist until the execution of that specific function. Generally, local variables are stored in stack memory and cleaned up automatically.

Global variables are declared outside of a function. They are available until the execution of the entire program. Unlike local variables, global variables are stored in fixed memory and not cleaned up automatically.

18. What is an index in SQL, and mention its types?

An index is used to retrieve data from a database quickly. Generally, indexes have keys taken from the columns of tables and views. We can say, SQL indexes are similar to the indexes in books that help to identify pages in the books quickly.

There are two types of indexes:

• Clustered indexes
• Non-clustered indexes

19. Mention the different types of SQL commands or SQL subsets?

There are five types of SQL commands offered in SQL. They are given as follows;

• DDL - Data Definition Languages
• DML - Data Manipulation Languages
• DCL - Data Control Language
• TCL - Transaction Control Language
• DQL - Data Query Language

20. What are the Various Commands used in SQL Subsets?

DDL	CREATE, DROP, ALTER, TRUNCATE, ADD COLUMN, and DROP COLUMN
DML	INSERT, DELETE, and UPDATE
DCL	GRANT and REVOKE
TCL	COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION
DQL	SELECT

 

21. Can you brief me on a few DDL Commands?

ALTER	This command allows changing the structure of a table
CREATE	It allows the creation of database objects such as tables, views, and indexes.
DROP	This command allows removing database objects from a database
TRUNCATE	This command helps to delete all the rows of a table permanently.

22. Can you brief the DML Commands?

INSERT	This command allows inserting a data into a table of a database
DELETE	This command allows deleting specific rows from a table
UPDATE	This command allows modifying a data in a table

23. Can you brief me on a few DCL Commands?

GRANT	This command can be used to share a database with other users. All the database objects can be granted access with certain rights to users.
REVOKE	This command can be applied if you want to restrict the access of database objects by other users.

24. Can you brief me about TCL commands?

COMMIT	This command allows for saving the transactions made in a database.
ROLLBACK	This command helps undo the transactions made in a database with the condition that the transactions shouldn't be saved yet.
SAVEPOINT	This command helps to roll the transactions up to a certain point but not the entire transaction.

25. What are Stored Procedures?

It is a function that consists of a group of statements that can be stored and executed whenever it is required. Know that stored procedures are compiled only once. They are stored as ‘Named Object’ in the SQL server database. Stored procedures can be called at any time during program execution. Moreover, a stored procedure can be called another stored procedure.

Explore Oracle PL SQL Interview Questions

26. What are the SQL database functions?

SQL offers the flexibility to developers to use built-in functions as well as user-defined functions.

The functions are categorized as follows:

• Aggregate functions: They process a group of values and return a single value. They can combine with GROUP BY, OVER, HAVING clauses and return values. They are deterministic functions.
• Analytic functions: They are similar to aggregate functions but return multiple rows as result set after processing a group of values. They help calculate moving averages, running totals, Top-N results, percentages, etc.
• Ranking functions: They return ranking values for rows in a table based on the given conditions. Here, the results are non-deterministic.
• Rowset functions: They return an object used as the table reference.
• Scalar functions: They operate on a single value and return a single value.

27. Mention the different types of operators used in SQL?

There are six types of operators used in SQL. They are given as follows:

Arithmetic Operators	Addition, Subtraction, Multiplication, Division, and Remainder/Modulus
Bitwise Operators	Bitwise AND, Bitwise OR, Bitwise XOR, etc.
Comparison Operators	Equal to, Not equal to, Greater than, Not greater than, Less than, Not less than, Not equal to, etc.
Compound Operators	Add equals, Multiply equals, Subtract equals, Divide equals, and Modulo equals
Logical Operators	ALL, ANY/SOME, AND, BETWEEN, NOT, EXISTS, OR, IN, LIKE, and ISNULL
String Operators	String concatenation, wildcard, character matches, etc.

28. What are the Set Operators?

There are four types of set operators available in SQL. They are given as follows:

Union	This operator allows combining result sets of two or more SELECT statements.
Union All	This operator allows combining result sets of two or more SELECT statements along with duplicates.
Intersect	This operator returns the common records of the result sets of two or more SELECT statements.
Minus	This operator returns the exclusive records of the first table when two tables undergo this operation.

29. What do you mean by buffer pool and mention its benefits?

A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server. The number of pages that can be stored in a buffer pool depends on its size.

The following are the benefits of a buffer pool:

•  Increase in I/O performance
•  Reduction in I/O latency
• Increase in transaction throughput
•  Increase in reading performance

30. What are Tuple and tuple functions?

A tuple is a single row in a table that represents a single record of a relation. A tuple contains all the data that belongs to a record. At the same time, tuple functions allow retrieving tuples from a database table. They are extensively used in analysis services that have multidimensional structures.

For example, the highlighted row in the below table shows all the data belonging to a customer, which is nothing but a tuple.

Customer Name	Phone Number	Email Address	Postal Address
Naren	123 -456 -789	xyz@gmail.com	PO No:123, New Delhi
Raman	234 -567 -891	abc@gmail.com	PO No:143, Mumbai
Krishna	345 -678 -912	pqr@gmail.com	PO No:443, Hyderabad

31. What do you mean by dependency and mention the different dependencies?

Dependency is the relation between the attributes of a table. The following are the different types of dependencies in SQL.

• Functional dependency
• Fully-functional dependency
• Multivalued dependency
• Transitive dependency
• Partial dependency

32. What do you mean by Data Integrity?

Data integrity ensures the accuracy and consistency of data stored in a database. Data integrity, in a way, represents the data quality. So, the data characteristics defined for a column should be satisfied while storing data in the columns. For instance, if a column in a table is supposed to store numeric values, then it should not accept Alphabetic values; otherwise, you can mean that data integrity is lost in the table.

33. What is Database Cardinality?

Database Cardinality denotes the uniqueness of values in the tables. It supports optimizing query plans and hence improves query performance. There are three types of database cardinalities in SQL, as given below:

• Higher Cardinality
• Normal Cardinality
•  Lower Cardinality

34. What are database Normalisation and various forms of Normalisation?

It is the process that reduces data redundancy and improves data integrity by restructuring the relational database.

The following are the different forms of normalization:

• First normal form – 1NF
• Second normal form – 2 NF
• Third normal form – 3 NF
• Boyce Codd Normal Form/Fourth Normal form – BCNF/4NF

35. What is Cursor, and how to use it?

In general, the result set of a SQL statement is a set of rows. If we need to manipulate the result set, we can act on a single row of the result set at a time. Cursors are the extensions to the result set and help point a row in the result set. Here, the pointed row is known as the current row.

Cursors can be used in the following ways:

• Positions a row in a result set
• Supports retrieving the current row from the result set
• Supports data modifications in the current row
• Allowing  SQL statements in stored procedures, scripts, and triggers to access the result set

36. Mention the different types of Cursors?

• Forward Only: It is known as the firehose cursor that can make only a forward movement. The modification made by the current user and other users is visible while using this cursor. As it is the forward-moving cursor, it fetches rows of the result set from the start to end serially.
• Static: This cursor can move forward and backward on the result set. Here, only the same result set is visible throughout the lifetime of the cursor. In other words, once the cursor is open, it doesn’t show any changes made in the database that is the source for the result set.
• Keyset: This cursor is managed by a set of identifiers known as keys or keysets. Here, the keysets are built by the columns that derive the rows of a result set. When we use this cursor, we can’t view the records created by other users. Similarly, if any user deletes a record, we can’t access that record too.
• Dynamic: Unlike static cursors, once the cursor is open, all the modifications performed in the database are reflected in the result set. The UPDATE, INSERT and DELETE operations made by other users can be viewed while scrolling the cursor.

37. What are Entity and Relationship?

Entities are real-world objects that are individualistic and independent. Rows of a table represent the members of the entity, and columns represent the attributes of the entity. For instance, a ‘list of employees of a company is an entity where employee name, ID, address, etc., are the attributes of the entity.

 A relationship indicates how entities in a database are related to each other. Simply put, how a row in a table is related to row(s) of another table in a database. The relationship is made using the primary key and the foreign key primarily.

There are three types of relationships in DBMS, as mentioned below:

• One-to-one relationship
•  One-to-many relationship
• Many-to-many relationship

38. What is a Trigger, and mention its various types?

Triggers are nothing but they are special stored procedures. When there is an event in the SQL server, triggers will be fired automatically.

There are three types of triggers – LOGON, DDL, and DML.

             LOGON triggers: They get fired when a user starts a Logon event

           DDL triggers: They get fired when there is a DDL event

          DML Triggers: They get fired when there is a modification in data due to DML

39. What is Schema in SQL, and mention its advantages?

The schema represents the logical structures of data. Using schemas, the database objects can be grouped logically in a database. Schema is useful for segregating database objects based on different applications, controlling access permissions, and managing a database's security aspects. Simply out, Schemas ensure database security and consistency.

Advantages:

• Schemas can be easily transferred
• You can transfer database objects between schemas
• It protects database objects and achieves effective access control

40. What are the types of UDFs?

There are three types of UDFs. They are defined as follows:

• User-defined scalar functions
• Table-valued functions
• System functions

41. What is the difference between char and varchar data types?

Char data type is a fixed-length data type in which the length of the character cannot be changed during execution. It supports storing normal and alphanumeric characters.

 On the other hand, varchar is the variable-length data type in which the length of the character can be changed during execution. That's why, it is known as a dynamic data type.

42. Mention the Aggregate Functions used in SQL?

The following aggregate functions are used in SQL.

• COUNT
•  SUM
• AVG
• MAX
• MIN

43. What are Case Manipulation Functions used in SQL?

The following are the case manipulation functions used in SQL.

• LOWER
• UPPER
• INITCAP

44. What are Character Manipulation Functions used in SQL?

The following are the character manipulation functions used in SQL.

• CONCAT
• SUBSTR
• LENGTH
• INSTR
• LPAD
• RPAD
• TRIM
• REPLACE

45. How would you differentiate single-row functions from multiple-row functions?

Single row functions can act on a single row of a table at a time. They return only one result after executing a row. Length and case conversions are known to be single-row functions.

Multiple row functions can act on multiple rows of a table at a time. They are also called group functions and return a single output after executing multiple rows.

Experienced:

46. What is the difference between SQL and NoSQL?

SQL	No SQL
Works on relational databases	Works on non-relational databases
Stores data in tables based on schemas so that data are organized and structured	No specific method is followed for data storage, so it offers flexibility in storing data.
Easy to execute complex queries	Difficult to execute complex queries
Scaling is performed vertically increasing the processing power of servers	Scaling is performed horizontally adding more servers and nodes
SQL satisfies ACID Properties such as atomicity, consistency, isolation, and durability.	Follows CAP theory – according to this, any two of the following need to be satisfied – Consistency, Availability, and Partition tolerance.

 47. What is the difference between SQL and MySQL?

SQL	MySQL
It is the programming language	It is the RDMS – Relational Database Management System
It is used for querying relational database systems	It is used to store, modify and delete data in a database in an organized way.
It is a licensed product of Microsoft.	It is an open-source platform managed by Oracle corporation
It provides adequate protection to SQL servers against intruders	As it is an open-source platform, security cannot be reliable
It doesn’t support any connectors	Support connectors such as the Workbench tool to build databases

48. What is the difference between Index and View?

Generally, an index is created in a separate table. They are the pointers that indicate the address of data in a database table. An index helps speed up querying and the data retrieval process in a database.

On the other hand, a view is a virtual table created from the rows and columns of one or more tables. The main thing about a view is that the rows and columns are grouped logically. With the support of views, you can restrict access to the entire data in a database.

49. What is the use of Views, and mention its types in SQL?

Views are the virtual database tables created by selecting rows and columns from one or more tables in a database. They support developers in multiple ways, such as simplifying complex queries, restricting access to queries, and summarising data from many tables.

There are two types of views, as mentioned below:

• System-defined views: They can be used for specific purposes and perform specific actions only. It provides all the information and properties of databases and tables.
• User-defined views: They are created as per the requirements of users. They are routines that accept parameters, perform complex functions, and return a value.

50. Compare: LONG and LOB Data types

LONG Datatype	LOB Datatype
helps store large scale semi-structured and unstructured data	Known as Large Objects. It is used to store large size data
Stores up to 2GB of data	Can store up to 4GB  of data
Difficult to maintain	Supports manipulating and accessing data easily
A table can have only one LONG column	A table can have multiple LOB columns where LOB type data is stored
Subqueries cannot select LONG data types	Subqueries can select LOB datatypes
Access data only sequentially	Access data randomly

51. What is the difference between Zero and NULL values in SQL?

When a field in a column doesn’t have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.

52. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN	OUTER JOIN
It is the intersection of two tables	It is the union of two tables
Only retrieves rows that are common to two tables	Retrieves the rows common to two tables and all the values of one table

53. What are Database Testing and its benefits?

Database testing is also known as back-end testing. It consists of the SQL queries executed to validate database operations, data structures, and attributes of a database. It helps to ensure the data integrity by eliminating duplicate entries of data in a database, failing which will create many problems while managing the database. Besides, it deals with testable items hidden and not visible to users.

54. What is Database Black box testing?

Blackbox testing helps to examine the functionality of a database. It is performed by validating the integration level of a database. The incoming and outgoing data are verified by various test cases such as the cause-effect graphing technique, equivalence partitioning, and boundary value analysis. This kind of testing can be performed at the early stages of development to ensure better performance.

55. What is the use of Defaults in SQL?

In a database, default values are substituted when no value is assigned to a field in a table column. Basically, each column can be specified with a default value. In this way, SQL server management studio specifies default values, which can be created only for the current databases. Note that if the default value exceeds the size of the column field, it can be truncated.

56. What is SQL Injection, and how to avoid it?

SQL injection is a malicious attack sent targeting an SQL server instance. It is usually sent through strings of statements and passed into the SQL server for execution. To avoid SQL injection, all statements must be verified for malicious vulnerabilities before allowing for execution.

In addition to that, the following methods can be applied to avoid SQL injections. They are given as follows:

• Using type-safe SQL parameters
• Using parameterized input with stored procedures
• Filtering inputs
• Reviewing codes
• Wrapping parameters

57. What do you mean by Autonomous Transaction?

An autonomous transaction is an independent transaction initiated by a transaction that is the main transaction. Autonomous transaction holds the main transaction, performs SQL operations, and commits or rolls back. After that, it resumes the main transaction. Note that autonomous transaction doesn’t share locks and resources with the main transaction.

58. Write the SQL statements that can be used to return even number records and odd number records?

You can use the following statement to retrieve even number records from a table.

SELECT * from table where id % 2 = 0

You can use the following statement to retrieve odd number records from a table.

SELECT * from table where id % 2 ! = 0

59. What is Alias in SQL?

SQL aliases help to assign temporary names for a table or column. It is used to simplify table or column names. And aliases can exist only for that query period. It can be created using the ‘AS’ keyword. Know that creation of an alias is in no way affecting the column names in the database. It can be applied when more than one table is involved in a query.

60. What is the difference between OLAP and OLTP?

OLAP is known as Online Analytical Processing. It consists of tools used for data analysis that will be used for making better decisions. It can work on multiple database systems' historical data and provide valuable insights. For example, NETFLIX and SPOTIFY generate insights from past data.

On the other hand, OLTP is known as Online Transaction Processing, and it works on operational data. OLTP manages ACID properties during transactions. Specifically, it performs faster than OLAP so that it can be used in online ticket booking, messaging services, etc.

61. What do you mean by Data Inconsistency?

Data inconsistency occurs when the same data exists in many tables in different formats. In other words, the same information about an object or person may be spread across the database in various places creating duplication. It decreases the reliability of the data and decreases the query performance significantly. To overcome this drawback, we can use constraints on the database.

62. What do you mean by Collation in SQL?

Collation allows to sort and compare data with pre-defined rules. These rules help to store, access and compare data effectively. The collation rules are applied while executing insert, select, update and delete operations. SQL servers can store objects that have different collations in a single database. Note that collation offers case-sensitivity and accent sensitivity for datasets.

63. How to create a table from an existing table?

A copy of a table can be created from an existing table using the combination of CREATE and SELECT statements. Using these statements, you can select all the columns or specific columns from an existing table. As a result, the new table will be replaced with all the values of the existing table. Here, the WHERE clause can select the specific columns from the table.

The syntax for this type of table creation is given below:

CREATE TABLE NEW_TABLE_NAME1 AS                 SELECT [column1,column2,…..columnN]                 FROM EXISTING_TABLE_NAME1                 [WHERE]

64. How to fetch common records from two tables?

We can fetch common records using INTERSECT commands in SQL. The main thing about this statement is that it returns only the common records. It means that this statement helps to eliminate duplication of data.

The syntax for this statement is given as below:

SELECT CustomerID              FROM Sales. customers INTERSECT               SELECT CustomerID               FROM Sales. Orders               WHERE Month (Orderdate) = December;

65. What are the common clauses used with SELECT Statements?

The common clauses such as FOR, ORDER BY, GROUP BY, and HAVING are used with SELECT statements.

• FOR Clause - it specifies the different formats for viewing result sets such as browser mode cursor, XML, and JSON file.
• ORDER BY Clause - It sorts the data returned by a query in a specific order. It helps to determine the order for ranking functions.
• GROUP BY Clause - It groups the result set of the SELECT statement. It returns one row per group.
• HAVING Clause – It is used with the GROUP BY clause and specifies a search condition for a group.

66. What is COALESCE and describe any two properties of COALESCE functions?

COALESCE is an expression that evaluates arguments in a list and only returns the non-NULL value.

For example, consider the following statement:

SELECT COALESCE (NULL, 14, 15);

This statement will return 14 after the execution since the first value is the NULL in this argument list.

                        Properties of COALESCE function:

• The datatype must be the same
• It functions as a syntactic shortcut for the case expression

67. What is the use of the MERGE statement?

MERGE allows combining the INSERT, DELETE and UPDATE functions altogether. This statement can be applied when two statements have complex matching characteristics. Though the MERGE statement seems to be complex, it provides much more advantages to developers when they get familiar with this statement. It reduces I/O operations significantly and allows to read data only from the source.

68. What is the use of CLAUSE in SQL?

Clauses are nothing but they are the built-in functions of SQL. They help to retrieve data very quickly and efficiently. Clauses are much-needed for developers when there is a large volume of data in a database. The result set of clauses would be a pattern, group, or an ordered format.

The following are the various clauses used in SQL:

• WHERE Clause
• OR Clause
• And Clause
• Like Clause
• Limit Clause
• Order By
• Group By

69. How to change a table name in SQL?

If you need to rename a table name in SQL, you can use the RENAME OBJECT statement to achieve the same.

You have to execute the following steps to change a table name using SQL.

• First, connect to a database engine in Object Explorer
• Select a new query on the standard bar
• Then, write the query and execute it.

The following example will show the use of rename query.

Advanced SQL Interview Questions & Answers

70. What are the differences between SQL and PL/SQL?

SQL	PL/SQL
It is a Structured Query Language	It is a Procedural Language where SQL statements are processed effectively
Only a single operation can be performed at a time	A Group of operations as a single block can be performed at a time
SQL executes the queries such as creating tables, deleting tables, and inserting into tables.	It is used to write program blocks, functions, procedures, triggers, packages, and cursors.
Mainly, it is used to retrieve data from databases and modify tables.	Used for creating web applications and server pages
Processing speed is low	It has the excellent processing speed

71. What are the advantages of PL/SQL functions?

• It has tight interaction with SQL.
• It has high performance and productivity.
• It has high portability and scalability.
•  It is highly flexible and secure.
• Supports developing web applications and server pages

72. Differentiate: CHAR and VARCHAR data types in SQL?

CHAR	VARCHAR
It is a fixed-length character string data type	It is a variable-length character string data type.
The data type can be a single byte or multiple-byte	It can accept character strings up to 255 bytes
This data type can be used when the character length is known	This data type is used when the character length is not clear
It uses static memory location	It uses dynamic memory location
This is used when the character length of the data is the same.	This is used when the character length of the data is variable.

73. How can you avoid Duplicate Keys in SQL?

We can eliminate duplicate keys in SQL by using the following methods:

• Using INSERT INTO SELECT
•  Using WHERE NOT IN
• Using WHERE NOT EXISTS
• Using IF NOT EXISTS
• Using COUNT(*)=0

74. Brief the factors that affect the functionalities of databases?

The following five factors affect the functionalities of databases.

• Workload
• Throughput
• Resources
• Optimization
• Contention

75. List out the factors that affect the query performance?

The following are the factors that affect the performance of queries.

• Number of nodes, processors, or slices
• Node types
• Data distribution
• Data sort order
• Dataset size
• Concurrent operations
• Query structure
• Code compilation

76. Differentiate: UNION and INTERSECT statements?

UNION: It is the operator that returns a single result set for two separate queries. And this operator functions based on specific conditions.

Syntax: query 1 UNION query2

INTERSECT: It is the operator that returns only the distinct rows from two separate queries.

Syntax: query 1 INTERSECT query2

77. What is the difference between DROP and TRUNCATE statements?

DROP	TRUNCATE
It removes a whole database	It removes a table or data or index
All the constraints will be removed after the execution of the DROP function.	Constraints don’t get affected because of the execution of this statement
The structure of the data also will be removed	The structure of the data won’t get affected
It is a slow process	It is faster than the DROP statement

78. What is the use of the SELECT DISTINCT statement?

This statement is used to select distinct values from a table. The table might consist of many duplicate records, whereas this statement helps to return only the distinct values.

The syntax for the statement is given as follows;

SELECT DISTINCT column1, column2, FROM table_name1;

79. How can you differentiate the RANK and DENSE_RANK functions?

Both RANK and DENSE_RANK are used as the ranking functions, which perform ranking of data based on specific conditions. When the RANK statement is executed, it returns a ranking of values of a table based on specific conditions. At the same time, the result set up skip positions in the ranking if there are the same values. Simply put, there will be a discontinuity in the numbering of ranking. On the other hand, when the RANK_DENSE function is executed, it doesn’t skip any position in the ranking of values even though there are the same values present in the table. It returns continuous numbering of ranking.

The following example will explain the use of the RANK and DENSE_RANK functions.

80. What is the difference between IN and BETWEEN operators?

Both IN and BETWEEN operators are used to return records for multiple values from a table. The IN operator is used to return records from a table for the multiple values specified in the statement. On the other side, BETWEEN operator is used to return records within a range of values specified in the statement.

             Syntax for the IN statement is given as:

SELECT * FROM table_name1 WHERE column_name1 IN (value 1,value2)

The syntax for the BETWEEN statement is given as:

SELECT * FROM table_name1 WHERE column_name1 BETWEEN ‘value 1’ AND ‘value2’

81. Compare: STUFF and REPLACE statements?

Both STUFF and REPLACE statements are used to replace characters in a string. The STUFF statement inserts the specific characters in a string replacing existing characters. In comparison, the REPLACE statement replaces existing characters with specific characters throughout the string.

For example, consider the following examples:

For the STUFF statement;

SELECT STUFF (‘raman’,2,3,’aja’) Output: rajan

For the REPLACE statement;

SELECT REPLACE (‘ramanathan’,’an’,’ar’) Output: ramarathar

82. What do you mean by COMMIT in SQL?

COMMIT statement allows saving the changes made in a transaction permanently. Once a transaction is committed, the previous values cannot be retrieved.

The following syntax is used for this operation:

SELECT * FROM Staff WHERE incentive = 1000; sql>COMMIT;

83. What is the use of the GRANT Statement?

This statement grants permissions for users to perform operations such as SELECT, UPDATE, INSERT, DELETE, or any other operations on tables and views.

For example, if you would like to provide access to a user for updating tables, then the following statement must be used. In addition, the user too can grant permissions to other users.

GRANT UPDATE ON table_name TO user_name WITH GRANT OPTION

84. What is the difference between White Box Testing and Black Box Testing?

Black Box Testing	White Box Testing
The internal structure of the program is hidden from testers	Testers know the internal structure of the program
It is performed by software testers	It is performed by software developers
Testing is known as outer or external software testing	Testing is known as inner or internal software testing
Programming knowledge is not required for testers	Programming knowledge is a must for testers
Functional testing, non-functional testing, and regression testing are the types of black-box testing.	Path testing, loop testing, and condition testing are types of white box testing.

85. What do you mean by ETL in SQL?

ETL in SQL represents Extract, Transform and Load.

Extracting – It is about extracting data from the source, which can be a data warehouse, CRMs, databases, etc.

Transforming – It includes many processes such as cleansing, standardization, deduplication, verification, and sorting.

Loading – It is the process of loading the transformed data into the new destination. There are two types of loading data: full loading and incremental loading.

86. What do you mean by NESTED triggers?

If a trigger fires another trigger while being executed, it is known as a NESTED trigger. Nested triggers can be fired while executing DDL and DML operations such as INSERT, DROP and UPDATE. Nested triggers help to back up the rows affected by the previous trigger. There are two types of nested triggers: AFTER triggers and INSTEAD OF triggers.

87. How to insert multiple rows in a database table in SQL?

We can use the INSERT INTO statement to insert multiple rows in a database table in SQL.

The following syntax can be used for this case:

INSERT INTO table_name VALUES (value1, value), (value3, value4)…;

The inserted data can be selected using the following syntax:

SELECT * FROM table_name;

88. What do you mean by live-lock in SQL?

When two processes repeat the same type of interaction continually without making any progress in the query processing, it leads to a live-lock situation in the SQL server. There is no waiting state in live-lock, but the processes are happening concurrently, forming a closed loop.

 For example, let us assume process A holds a resource D1 and requests resource D2. At the same time, assume that process B holds a resource D2 and requests resource D1. This situation won’t progress any further until any of the processes should either drop holding a resource or drop requesting a resource.

89. What do you mean by Equi-JOIN and non-Equi-JOIN?

Equi-join creates a join operation to match the values of the relative tables. The syntax for this operation can be given as follows:

SELECT column_list FROM table1, table2,….. WHERE table1.column_name = table.2column_name;

On the other side, Non-Equi join performs join operations except equal. This operator works with <,>,>=, <= with conditions.

SELECT * FROM table_name1,table_name2 WHERE table_name1.column[>|<|>=|<=] table_name2.column;

90. What are the different types of SQL sandboxes?

There are three types of SQL sandboxes. They are given as follows:

• Safe access sandbox
• Unsafe access sandbox
• External access sandbox

91. What do you mean by lock escalation?

It is the process of converting row and page locks into table locks. Know that Reduction of lock escalation would increase the server performance. To improve performance, we need to keep transactions short and reduce lock footprints in queries as low as possible. Besides, we can disable lock escalation at the table and instance levels, but it is not recommended.

92. How can you update a table using SQL?

The UPDATE statement allows you to update a database table in SQL. After the execution, one or more columns in a table will be replaced by new values.

The syntax for the UPDATE statement is given as follows:

UPDATE table_name SET   Column1 = new_value1,   Column2 = new_value2,   ..….. WHERE    Condition;

This statement requires a table name, new values, and conditions to select the rows. Here, the WHERE statement is not mandatory. Suppose the WHERE clause is used, all the rows in a table will be updated by the new values.

93. How to create a Stored Procedure using T-SQL?

• Connect to the instance of a database engine in ‘object explorer’
• Click ‘new query’ from the ‘files menu’
• Copy and paste the following sample codes in the query window

USE AdventureWorks2012;  GO  CREATE PROCEDURE HR.GetEmployeesTest2       @LastName nvarchar(25),       @FirstName nvarchar(25)   AS       SET NOCOUNT ON      SELECT FirstName, LastName, Division      FROM HR.vEmployeeDivisionHistory       WHERE FirstName = @FirstName AND LastName = @LastName       AND EndDate IS NULL;  GO

• Now, execute the codes

You can use the following statement to run the newly created stored procedure.

EXECUTE HR.GetEmployeesTest2 N'Ackerman', N'Pilar';

94. What do you mean by DELETE CASCADE constraint?

When a foreign key is created under this option, and if a referenced row in the parent table is deleted, the referencing row(s) in a child table also gets deleted.

On similar tracks, when a referenced row is updated in a parent table, the referencing row(s) in a child table is also updated.

95. Explain the different types of indexes in SQL?

The following are the different types of indexes in SQL.

• Single-column indexes
• Unique indexes
• Composite indexes
• Implicit indexes

96. What do you mean by auto-increment?

It is a unique number that will be generated when a new record is inserted into a table. Mainly, it acts as the primary key for a table.

The following syntax is used for this purpose:

IDENTITY (starting_value, increment_value)

97. What do you mean by Pattern Matching?

We can use the LIKE command in SQL to identify patterns in a database using character strings. Generally, a pattern may be identified using wildcard characters or regular characters. So, pattern matching can be performed using both wildcard characters and string comparison characters as well. However, pattern matching through wildcard characters is more flexible than using string comparison characters.

98. What is the difference between blocking and deadlocking?

Blocking is a phenomenon that occurs when a process locks a resource ‘A’, and the same resource is requested by another process ‘B’. Now, process ‘B’ can access the resource ‘A’ only when process ‘A’ releases the lock. The process ‘B’ has to wait until the process ‘A’ releases the lock. The SQL server doesn't interfere and stops any process in this scenario.

On the contrary, deadlocking is the phenomenon that occurs when a resource 'A' is locked by a process 'A' and the same resource is requested by another process 'B'. Similarly, a resource 'B' is locked by process 'B' and requested by process A. This scenario causes a deadlock situation, and it is a never-ending process. So, the SQL server interferes and voluntarily stops any one of the processes to remove the deadlock.

99. What is the difference between COALESCE ( ) and ISNULL ( )?

COALESCE function returns the first value that is non-NULL in the expression, whereas ISNULL is used to replace the non-NULL values in the expression.

Syntax for COALESCE function is given as:

SELECT column(s),COALESCE (exp_1,…..,exp_n) FROM table_name;

Syntax for ISNULL is given as:

SELECT column(s),ISNULL(column_name,value_to_replace) FROM table_name;

100. What is the difference between NVL and the NVL (2) functions in SQL?

Both the functions are used to find whether the first argument in the expression is NULL. The NVL function in the SQL query returns the second argument if the first argument is NULL. Otherwise, it returns the first argument.

The NVL2 function in SQL query returns the third argument if the first argument is NULL. Otherwise, the second argument is returned.

Conclusion

All of us know that knowledge is power. After reading this blog, we hope you might have gathered good knowledge about SQL and understood it in depth. Keep reading the Q&A questions for few more times. It will help you get familiar with the terminologies and syntaxes used in this blog.

ALL THE BEST!

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSAS	SharePoint
SSRS	SQL Server
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator