SQL is a robust database management tool to manipulate and manage relational databases, and it continues to be broadly utilized in the industry today. Companies of all sizes across different sectors use SQL for data analysis and reporting since the data volume generated daily increases. So, learning SQL will be a stepping stone for careers like Software Engineering, Data Analysis, Data Engineers, etc. Due to the reasons mentioned above, we have compiled an extensive list of SQL interview questions that include SQL topics like Subquery, Primary Key, Foreign Key, Stored Procedures, Functions, etc. If you are prepared with these questions, you will be ready to get into various SQL job roles.
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 2023 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 2023 that helps you in cracking your interview & acquire a dream career as SQL Developer.
We have categorized SQL Interview Questions - 2023(Updated) into 2 levels they are:
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.
|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.|
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.
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.
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.
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.
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.
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.
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.
The following are the table types used in SQL:
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.
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.
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.
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.
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:
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]
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.
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.
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.
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:
There are five types of SQL commands offered in SQL. They are given as follows;
|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|
|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.|
|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|
|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.|
|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.|
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|
SQL offers the flexibility to developers to use built-in functions as well as user-defined functions.
The functions are categorized as follows:
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.|
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.|
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:
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 -email@example.com||PO No:123, New Delhi|
|Raman||234 -567 -firstname.lastname@example.org||PO No:143, Mumbai|
|Krishna||345 -678 -email@example.com||PO No:443, Hyderabad|
Dependency is the relation between the attributes of a table. The following are the different types of dependencies in SQL.
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.
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:
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:
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:
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:
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
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.
There are three types of UDFs. They are defined as follows:
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.
The following aggregate functions are used in SQL.
The following are the case manipulation functions used in SQL.
The following are the character manipulation functions used in SQL.
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.
|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?
|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|
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.
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:
|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|
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.
|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|
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.
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.
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.
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:
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.
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|
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.
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.
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.
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.
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
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:
FROM Sales. customers
FROM Sales. Orders
WHERE Month (Orderdate) = December;
The common clauses such as FOR, ORDER BY, GROUP BY, and HAVING are used with SELECT statements.
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:
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.
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:
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.
The following example will show the use of rename query.
|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|
|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.|
We can eliminate duplicate keys in SQL by using the following methods:
The following five factors affect the functionalities of databases.
The following are the factors that affect the performance of queries.
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
|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|
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,
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.
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’
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’)
For the REPLACE statement;
SELECT REPLACE (‘ramanathan’,’an’,’ar’)
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:
WHERE incentive = 1000;
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|
|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.|
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.
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.
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;|
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.
Equi-join creates a join operation to match the values of the relative tables. The syntax for this operation can be given as follows:
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.
WHERE table_name1.column[>|<|>=|<=] table_name2.column;
There are three types of SQL sandboxes. They are given as follows:
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.
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:
Column1 = new_value1,
Column2 = new_value2,
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.
CREATE PROCEDURE HR.GetEmployeesTest2
SET NOCOUNT ON
SELECT FirstName, LastName, Division
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
You can use the following statement to run the newly created stored procedure.
|EXECUTE HR.GetEmployeesTest2 N'Ackerman', N'Pilar';|
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.
The following are the different types of indexes in SQL.
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)|
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.
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.
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)
Syntax for ISNULL is given as:
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.
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!
|Team Foundation Server||BizTalk Server Administrator|
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
|SQL Server DBA Training||Dec 09 to Dec 24||View Details|
|SQL Server DBA Training||Dec 12 to Dec 27||View Details|
|SQL Server DBA Training||Dec 16 to Dec 31||View Details|
|SQL Server DBA Training||Dec 19 to Jan 03||View Details|
Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.
Copyright © 2013 - 2023 MindMajix Technologies