Previous Questions (11-20)

SQL Interview Question with Answers

21. What is a RANKING function and what are the four RANKING functions?

Ranking functions are used to give some ranking numbers to each row in a dataset based on some ranking functionality. Every ranking function creates a derived column which has integer value. 

Different types of RANKING function:

ROW_NUMBER(): 

Assigns an unique number based on the ordering starting with 1. Ties will be given different ranking positions.

RANK(): 

Assigns an unique rank based on value. When the set of ties ends, the next ranking position will consider how many tied values exist and then assign the next value a new ranking with consideration the number of those previous ties. This will make the ranking position skip placement. position numbers based on how many of the same values occurred (ranking not sequential).

DENSE_RANK(): 

Same as rank, however it will maintain its consecutive order nature regardless of ties in values; meaning if five records have a tie in the values, the next ranking will begin with the next ranking position.

Syntax: 

<Ranking Function> () OVER(condition for ordering) -- always have to have an OVER clause 

Ex: SELECT SalesOrderID, SalesPersonID, TotalDue, ROW_NUMBER() OVER(ORDER BY TotalDue), RANK() OVER(ORDER BY TotalDue), DENSE_RANK() OVER(ORDER BY TotalDue) FROM Sales.SalesOrderHeader

NTILE(n): 

Distributes the rows in an ordered partition into a specified number of groups.

22. What is PARTITION BY? 

Creates partitions within the same result set and each partition gets its own ranking. That is, the rank starts from 1 for each partition.

Ex: SELECT *, DENSE_RANK() OVER(PARTITION BY Country ORDER BY Sales DESC) AS DenseRank FROM SalesInfo

23. What is Temporary Table and what are the two types of it? 

• They are tables just like regular tables but the main difference is its scope. 
• The scope of temp tables is temporary whereas regular tables permanently reside. 
• Temporary table are stored in tempDB. 
• We can do all kinds of SQL operations with temporary tables just like regular tables like JOINs, GROUPING, ADDING CONSTRAINTS, etc. 
• Two types of Temporary Table

Local 

#LocalTempTableName -- single pound sign Only visible in the session in which they are created. It is session-bound

Global 

##GlobalTempTableName -- double pound sign Global temporary tables are visible to all sessions after they are created, and are deleted when the session in which they were created in is disconnected. It is last logged-on user bound. In other words, a global temporary table will disappear when the last user on the session logs off.  

24. Explain Variables ?

• Variable is a memory space (place holder) that contains a scalar value EXCEPT table variables, which is 2D data. 
• Variable in SQL Server are created using DECLARE Statement. 
• Variables are BATCH-BOUND. 
• Variables that start with @ are user-defined variables.

25. Explain Dynamic SQL (DSQL)? 

Dynamic SQL refers to code/script which can be used to operate on different data-sets based on some dynamic values supplied by front-end applications. It can be used to run a template SQL query against different tables/columns/conditions. 

Declare variables: which makes SQL code dynamic. 
Main disadvantage of D-SQL is that we are opening SQL Tool for SQL Injection attacks. You should build the SQL script by concatenating strings and variable.

 26. What is SQL Injection Attack? 

• Moderator’s definition: when someone is able to write a code at the front end using DSQL, he/she could use malicious code to drop, delete, or manipulate the database. There is no perfect protection from it but we can check if there is certain commands such as 'DROP' or 'DELETE' are included in the command line. 
• SQL Injection is a technique used to attack websites by inserting SQL code in web entry fields.

27. What is SELF JOIN? 

• JOINing a table to itself 
• When it comes to SELF JOIN, the foreign key of a table points to its primary key. 
• Ex: Employee(Eid, Name, Title, Mid) ○ Know how to implement it!!!

28. What is Correlated Subquery? 

• It is a type of subquery in which the inner query depends on the outer query. This means that that the subquery is executed repeatedly, once for each row of the outer query. 
• In a regular subquery, inner query generates a result set that is independent of the outer query. 

Ex: 

SELECT * 

FROM HumanResources.Employee E 

WHERE 5000 IN (SELECT S.Bonus 

FROM Sales.SalesPerson 

S WHERE S.SalesPersonID = E.EmployeeID) 

• The performance of Correlated Subquery is very slow because its inner query depends on the outer query. So the inner subquery goes through every single row of the result of the outer subquery.  

30. What are the differences between DELETE and TRUNCATE .? 

Delete: 

DML statement that deletes rows from a table and can also specify rows using a WHERE clause. Logs every row deleted in the log file.

Slower since DELETE records every row that is deleted.

DELETE continues using the earlier max value of the identity column. Can have triggers on DELETE. 

Truncate: 

DDL statement that wipes out the entire table and you cannot delete specific rows. 
Does minimal logging, minimal as not logging everything. 

TRUNCATE will remove the pointers that point to their pages, which are deallocated. 

Faster since TRUNCATE does not record into the log file. 

TRUNCATE resets the identity column.

Cannot have triggers on TRUNCATE