SQL Interview Questions and Answers - Part 07
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 07th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 07
Q50. What is the difference between DELETE and TRUNCATE command?
Both Delete and Truncate commands remove the records from a table. The TRUNCATE statement quickly deletes all records in a table by deallocating the data pages used by the table.
This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation.
The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.
DELETE command delete one row one at a time, logging each row in the transaction log, as well as maintaining Log Sequence Number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary.
The DELETE command stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables.
DELETE vs TRUNCATE Command:
- DELETE is a DML command while TRUNCATE is a DDL command.
- You require to have DELETE permission on a table to use the Delete command, But you require Alter table permissions to TRUNCATE a table.
- DELETE command deletes records one by one and makes an entry for each and every deletion in the transaction log, whereas TRUNCATE de-allocates all the pages and makes an entry for the de-allocation of pages in the transaction log.
- TRUNCATE is faster compared to delete as it makes less use of the transaction log.
- TRUNCATE reseeds identity values, whereas delete does not.
- TRUNCATE command does not fire the trigger which could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. But a DELETE command always fires the trigger.
- You cannot specify a WHERE clause in a TRUNCATE TABLE statement, it is all or nothing.
- You can use the Delete statement with the indexed views but you cannot use the TRUNCATE command with the indexed views.
- Delete command retains the object statistics and allocated space. But since Truncate deallocates all data pages of a table, it removes all statistics and allocated space as well.
- TRUNCATE is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.
Please watch the DELETE vs TRUNCATE video here for the live demo.
Q51. What is the difference between the WHERE clause and the HAVING clause in SQL Server?
A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. Though both are used to exclude or filter the rows from the result set, there is some difference between them.
The HAVING clause is often used with the GROUP BY clause in the SELECT statement. If you use a HAVING clause without a GROUP BY clause, the HAVING clause behaves like the WHERE clause in some of the databases.
A query can contain both a WHERE clause and a HAVING clause in a single SELECT statement. If a SELECT statement contains both WHERE and HAVING clauses:
- The WHERE clause is applied first to the individual rows in the tables and only the rows that meet the conditions in the WHERE clause are grouped. This Means WHERE evaluates per row.
- The HAVING clause is then applied to the group set. Only the groups that meet the HAVING conditions appear in the query output. This Means HAVING evaluates per group.
You can use any column in the WHERE clause but you can apply a HAVING clause only to those columns that also appear in the GROUP BY clause or in an aggregate function.
The where clause cannot be used with aggregates or aggregate functions, but the having clause can be used.
In MS SQL the WHERE Clause can be used with SELECT, UPDATE, or DELETE statements, but the HAVING Clause can only be used with SELECT statements.
The WHERE Clause is used before the GROUP BY Clause, but the HAVING Clause is used after the GROUP BY Clause
Please read the complete article here. Please watch the WHERE vs HAVING clause video here for the live demo. You can also watch below ON clause vs WHERE clause video for your reference
Q52. What is the difference between COUNT() and COUNT_BIG() SQL functions?What is the difference between COUNT(*), COUNT(col), and COUNT(1) in SQL?
There is two most common method to count the no of records in any table, i.e. COUNT() and COUNT_BIG(). The only difference between these two functions is the return type.
The COUNT_BIG returns BIGINT data type whereas the COUNT returns INT data type value. The second difference between them is we cannot create a clustered index on view when the view has COUNT (*) in it. But Index could be created if it has COUNT_BIG (*).
Both COUNT(*) and COUNT(1) return the total records count in a table including both NULL and NON-NULL records.
By default, the Count Function uses the ALL keyword so it counts all records in a table. But if you DISTINCT keyword the COUNT function only count the unique values.
On the other hand COUNT(column_name) returns only the NON-NULL records count.
The biggest disadvantage of the COUNT() function is that the SQL Server has to run a blocking scan against the entire table in order to derive this count which is expensive.
The benefit of using COUNT is that it is an accurate indicator of exactly how many rows exist in the table at the time query processing begins. However, as the table is scanned, locks are being held. This means that other queries that need to access this table have to wait in line.
Q53. What is the difference between UNION and UNION ALL in SQL?
Both UNION and UNION ALL are used to combine the result-set of two SELECT queries into a single result-set. But you should have to follow the following rule while using UNION operations:
- The Number of Columns must be the same in both the SELECT statements.
- The Order of Columns must be the same in both the SELECT statements.
- The Type of Columns must be the same in both the SELECT statements.
Apart from this if you want to sort the result set, you must have to use the ORDER BY clause after the last SELECT Query statement, otherwise, you will get a syntax error.
In the UNION operation, the order of SELECT Statements does not matter. So if you change the SELECT statement order, you will still get the same result.
UNION vs UNION ALL:
The UNION ALL combine the result of both the SELECT statements without any sorting and removing the duplicate. So UNION ALL does not remove the duplicate records from the final resultset. All the duplicate rows will be there in the result if any. Since the UNION ALL Operator does not remove the duplicate rows, it is faster compared to the UNION Operator.
On the other hand UNION operator also combines the result of both the SELECT statements but it removes the duplicate records if any. Since the UNION Operator removes the duplicate rows, it is slower compared to the UNION ALL Operator. If you see the Query Plan for UNION, it is doing Distinct Sorting for duplicate removal after the concatenation, and in SQL sorting is the most expensive operation.
Q54. How can you get the last inserted record in SQL? OR
What is the difference between @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() in SQL?
There are multiple ways to get the last inserted record, but each one has its own pros and cons.
@@IDENTITY:
@@IDENTITY gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
It means @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SCOPE_IDENTITY():
The SCOPE_IDENTITY() function like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well.
This means it returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
So it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user-defined function. Always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
IDENT_CURRENT(table name):
IDENT_CURRENT is not limited by scope and session, it is limited to a specified table. The IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
So it returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. It is not recommended because, under concurrency, you will often get the wrong answer.
All the above way returns only a single value. Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated.
Q55. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() in SQL?
All three functions are used to get the ascending integer sequential numbers in SQL. However, all of them have different ways to generate the numbers.
All the three functions ROW_NUMBER, RANK, and DENSE_RANK have the following similarities:
- All are used to get the increasing integer values.
- The return type of all three functions is BIGINT.
- All three SQL functions are Nondeterministic.
- All three functions use the OVER Clause.
- All three functions use the ORDER BY Clause.
- All three functions may use the PARTITION BY Clause.
All the ROW NUMBER, RANK, and DENSE RANK functions generate the same number if there are no duplicate records, on which we have used the Order By Clause within the OVER clause.
When you do not apply the PARTITION BY Clause, all these functions treats all rows of the query result set as a single group.
SQL ROW NUMBER Function:
The Row Number Function in SQL is a nondeterministic function that numbers the output of a result set starting with one. ROW_NUMBER is a temporary value calculated when the query is run.
The Row Number function numbers all the rows sequentially without any gap or duplicate unlike Rank and Dense Rank. The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. The ORDER BY clause is mandatory here.
We can also use the PARTITION BY clause to divide the result set produced by the FROM clause into partitions. In that case, the RowNumber returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SQL RANK Function:
The RANK Function in SQL is a nondeterministic function. It returns the rank of each row within the partition of a result set. Rank is a temporary value calculated when the query is run.
The Rank of a row is one plus the number of ranks that come before the row in question. If two or more rows tie for a rank, each tied row receives the same rank. Therefore, the RANK function does not always return consecutive integers. You can also use the PARTITION BY clause to divide the result set produced by the FROM clause into partitions, and then we can get the Rank for each partition.
The Row Number function numbers all the rows sequentially without any gap or duplicate but the RANK function generates the same Rank for two or more rows that are tied (duplicate). It also skips the number for the position of each duplicate row.
SQL DENSE RANK Function:
The DENSE RANK Function in SQL is also a nondeterministic function. It also returns the rank of each row within the partition of a result set. Dense Rank is also a temporary value calculated when the query is run.
The only difference between RANK vs DENSE RANK is that DENSE RANK returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.
If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive rank values.
Q56. What is the main difference between IN() and NOT IN() operators in SQL?
Both IN and NOT IN operators are used to check the table column records against a list of values. The NOT operator just negates the IN operator result.
The IN operator is typically used to filter a column for a certain list of values. You can also use the IN operator to search the values in the result set of a sub-query.
On the other hand, the NOT IN operator is used to filter against a list of values. It is nothing but a series of NOT EQUAL TO operators (!=) that are separated by the OR condition.
The NO IN command compares specific column values from the first table with another column values in the second table or a sub-query and returns all values from the first table that are not found in the second table, without performing any filter for the distinct values.
If a NULL value is present in the NOT IN() list, the result set is empty. This means that NOT IN can return unexpected results if suddenly a NULL value pops up in the result set of the sub-query.
Please watch the IN() vs NOT IN() operator video here.
Q57. What is the difference between SQL INTERSECT and EXCEPT operator?
INTERSECT is used to find the common records or rows from two tables or SELECT statements. So INTERSECT Operator returns only those rows which are common in both the SELECT Statement.
In the case of the INTERSECT operator as well, the order of SELECT statements does not matter, so you will get the same result in both cases.
On the other hand, the Except Operator returns all the rows of the first SELECT statement excluding the common rows of both SELECT statements. So the EXCEPT operation returns only those records which are not in the second SELECT statement or table.
Q58. What is the difference between IN() and EXIST() operators?
The IN operator is typically used to filter a column for a certain list of values. You can also use the IN operator to search the values in the result set of a subquery.
SELECT UserId FROM User WHERE UserID IN (SELECT UserId FROM TempUser)
The EXISTS operator doesn't check for values but instead checks for the existence of rows. Typically, a subquery is used in conjunction with EXISTS. It actually doesn't matter what the subquery returns, as long as rows are returned.
SELECT UserId FROM User PU WHERE EXISTS (SELECT TU.UserId FROM TempUser TU WHERE TU.UserId = PU.UserId)
Please watch the IN vs EXISTS operator video here.
Q59. What is the difference between NOT IN(), NOT EXISTS(), and EXCEPT operator?
By prefixing the IN and EXIST operators with the NOT, we negate the Boolean output of those operators. Logically, NOT IN and NOT EXISTS are the same i.e they return the same result sets as long as NULLS aren't involved.
If a NULL value is present in the NOT IN() list, the result set is empty. This means that NOT IN can return unexpected results if suddenly a NULL value pops up in the result set of the subquery.
The SQL NOT EXISTS command is used to check for the existence of specific values in the provided subquery. The subquery will not return any data; it returns TRUE or FALSE values depending on the subquery values existence check.
The NOT IN can return unexpected results if suddenly a NULL value pops up in the result set of the subquery. NOT EXISTS doesn't have this issue, since it doesn't matter what is returned. If an empty result set is returned, NOT EXISTS will negate this, meaning the current record isn't filtered out.
The EXCEPT command is used to return all distinct records from the first SELECT statement that are not returned from the second SELECT statement, with each SELECT statement will be considered as a separate dataset. i.e it returns all distinct records from the first dataset and removes from that result the records that are returned from the second dataset.
EXCEPT is nothing but a combination of the SQL NOT EXISTS command and the DISTINCT clause.
To Be Continued Part-08...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.