Tech Point Fundamentals

Friday, July 15, 2022

Home / SQL-Interview-Questions / SQL Interview Questions and Answers - Part 08

SQL Interview Questions and Answers - Part 08

by on in

SQL Interview Questions and Answers - Part 08

sql-interview-questions-answers

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 08th 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 08


Q60. What is SubQuery? What is the difference between Standalone SubQuery, Co-Related SubQuery, and Nested SubQuery in SQL?

A subquery is a query nested inside another query. So a subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. 

A subquery can be used anywhere an expression is allowed. You can use a subquery in many different places of the main query: SELECT, FROM, WHERE.

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

A subquery can return different data types i.e. a scalar subquery(single value), a row, or a table. The data produced by the subquery determines how the main query may interact with the data. When the main query is complete, the data from any subqueries gets dropped. So the subquery acts like a temporary table.








Fundamental Points of SubQuery:

  1. The SELECT query of a subquery is always enclosed in parentheses. 
  2. The Order BY clause can not be used unless the TOP clause is used.
  3. The select list of an inner subquery introduced with a comparison operator can include only one expression or column name.
  4. If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).
  5. The NText, Text, and Iimage data types cannot be used in the select list of subqueries.
  6. The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
  7. Subqueries can be joined to each other or to other tables if they have a column in common.
  8. If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
  9. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query.

Some common places where the subquery is used like EXISTS Subquery, IN Subquery, ALL Subquery, ANY Subquery, and SOME Subquery. 



Types of SubQuery: 

There are two types of SubQuery:

Stand Alone SubQuery or Simple SubQuery:

A simple subquery can run independently. It has its own SELECT clause and FROM clause. You can run the inner query without the outer or main query.

They are enclosed in parentheses and it is best practice to indent a subquery for easy reading. Subqueries can also be given an alias, in the same way as a table or column.



CoRelated SubQuery or Repeating Subquery:

A co-related subquery is also known as Synchronized Subquery or Repeating Subquery. A correlated subquery is a subquery that uses values from the outer query. Because of this dependency, a correlated subquery cannot be executed independently as a simple subquery.

When the subquery depends on the current row that the outer SELECT statement is evaluating; in this case, the subquery is called a correlated subquery. So correlated subquery depends on the outer query for its values. Since in co-related subquery the subquery may be evaluated once for each row processed by the outer query, it can be slow. 

Moreover, a correlated subquery is executed repeatedly, once for each row evaluated by the outer query. Hence the correlated subquery is also known as a repeating subquery.

Correlated subqueries may appear elsewhere besides the WHERE clause. Since the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.



SELECT  TOP 1 Salary FROM Payroll  OuterSubQuery
WHERE 2 = ( SELECT COUNT(DISTINCT InnerSubQuery.Salary) 
FROM Payroll InnerSubQuery 
WHERE InnerSubQuery.Salary >= OuterSubQuery.Salary)

It is generally meaningless to have a correlated subquery in the FROM clause because the table in the FROM clause is needed to evaluate the outer query, but the correlated subquery in the FROM clause can't be evaluated before the outer query is evaluated, causing a chicken-and-egg problem.

However, in some database systems, it is allowed to use correlated subqueries while joining in the FROM clause, referencing the tables listed before the join using a specified keyword, producing a number of rows in the correlated subquery, and joining it to the table on the left.  In Microsoft SQL Server, using the keyword CROSS APPLY or OUTER APPLY instead of JOIN achieves the effect.

Please watch the SubQuery vs CoRelated SubQuery video here.



Q61. What is a Temporary Table or Temp Table in SQL? What is the difference between Local Temp Table and Global Temp Table?

A temporary table is a database table that exists temporarily on the database server. Temporary tables are stored inside “TempDB” which is a system database. All the temp tables start with a hash or pound (#) sign. Temporary Tables help us to store and process intermediate results. 

Temporary tables can be created with the CREATE TABLE statement, or by the SELECT INTO command. You can also use the “INSERT INTO #table EXEC stored_procedure” statement with a temporary table. 

Please read more about SELECT INTO vs INSERT INTO EXEC here and watch the video for the same here for more detail.








Temp Table Fundamentals Points:

  1. Temp Tables are Created in TempDB and are automatically deleted as soon as the last connection is terminated.
  2. Temp Tables are accessible within child batches, nested triggers, procedures, and exec calls.
  3. The temp table can do all the DDL operations. Temporary tables can have named constraints, indexes, etc. It also allows creating the indexes, dropping, altering, etc.
  4. Temp table can be used for the current session or global. So that a multiple-user session can utilize the results in the table.
  5. When we do the DML operations with the temp table then it can be rollback or commit the transactions.
  6. Since the tempdb database is recreated each time SQL Server starts, you could use a startup stored procedure to add the UDDT to tempdb. 
  7. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.








Restrictions on Temp Table:

  1. Foreign Key constraints cannot be applied to a temporary table.
  2. Temporary tables cannot be partitioned.
  3. Temp Tables cannot be used in views and you cannot associate triggers with them.
  4. Temp Tables cannot be used inside a function. But table variables can be used inside scalar or multi-statement table UDFs.
  5. Temp Tables' columns cannot be defined with user-defined data types (UDDT) that are not created in tempdb, you must use the native data types. Because UDDTs are specific to the database, and temporary tables belong to tempdb.
  6. Columns of the XML type cannot be defined with an XML collection unless the collection has been added to tempdb.
  7. A Temp Table can not be passed to a stored procedure, but they are still in scope to nested procedures.

Types of Temp Table: 

There are two types of temp tables.



Local Temp Table:

  1. A Local Temp Table is created with a single hash or pound sign(#).
  2. A Local Temp Table is available only for the session that has created it. It is not accessible to other connections.
  3. It is automatically dropped when the connection that has created it, is closed.
  4. There will be random numbers that are appended to the name of the local temp table. So you can create multiple local tables with the same name.
  5. If it is a local temporary table and it was created inside a stored procedure, it will be dropped when the stored procedure is finished. All other local temporary tables will be dropped at the end of the current session.
  6. Its name is limited to 116 characters. This is because the database engine must be able to identify different temporary tables created by different sessions at the same time. To do so, it internally appends a numeric suffix to the end of the name.
  7. Local temporary tables are visible to the current session once created, including nested procedures; however, they will not be visible to parent procedures. 



Global Temp Table:

  1. A Global Temp Table is created with a double hash or pound sign(##). They are accessible to all the open connections.
  2. Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. 
  3. Global Table Name must have a Unique Table Name. So There will be no random Numbers suffixed at the end of the Table Name.
  4. Global temporary tables are visible to all sessions until dropped and all other sessions have stopped using them.

Please watch the Temp Table video here for live demo.



Q62. What is Derived Table or DT in SQL? How DT is different from Common Table Expression (CTE) in SQL?

In SQL a Derived Table is nothing but a Subquery used in the From Clause. When a stand-alone subquery is used in the FROM clause of a SELECT statement, it is called a derived table. Unlike a subquery, a derived table must have an alias so that you can reference its name later in the query. 

So a derived table is a virtual table returned from a SELECT statement. A derived table in SQL is a result table that is derived from one or more base tables, by evaluating expressions against those input base tables.

SELECT TOP 1 Salary FROM
(
SELECT Salary,  
  DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DenseRank 
FROM [dbo].[Payroll]
) DT WHERE DenseRank = 3;



The derived table behaves like an inline view. The rows of the result set of the derived table are read and joined to the rest of the query. When you need to obtain complex result sets from a database table for example using multiple aggregation functions, a derived table is mostly used.

The derived tables' contents can be used within the query itself because they become unavailable once the query execution is completed. Columns of the derived table can be referenced in SELECT, ON, WHERE, GROUP BY, and HAVING clauses of the containing query, qualified by the correlation name, if necessary.

T-SQL supports a number of table operators that yield a derived table but are not supported as stand-alone expressions. Those are JOIN, PIVOT, UNPIVOT, and APPLY. You do need a clause for them to operate within (typically FROM, or USING clause in the MERGE statement) and a host query. 








Derived Table Fundamental Points:

  1. All columns of the DT must have names and All column names must be unique. 
  2. You can not use order by clause unless the TOP clause is applied in the DT expression.
  3. The DT must have an alias name in order to access the data from the DT expression.
  4. Because you can treat derived tables like regular SQL Server tables, you can join two or more derived tables.
  5. The derived table cannot be used in the TRUNCATE statement.



Use Of Derived Table:

  1. You can use an inline naming technique where you assign the target column name after the computation and an optional AS clause.
  2. A derived table is used to check and delete duplicate records.
  3. A derived table is used to update the table records.
  4. A Derived table is used when Applying Multiple Aggregate Functions
  5. A derived table is used to check and get the 3rd highest salary.
  6. A derived table is also used in SQL's Table Valued Constructor. A table value constructor allows you to construct a table value based on self-contained scalar expressions. 

SELECT UserId, UserName, UserEmail FROM 
( VALUES
( 1, 'Thomas', 'thomas@gmail.com' ),
( 2, 'Joyson', 'joyson@gmail.com' ),
( 3, 'Alex', 'alex@yahoomail.com' ) 
)
AS TempUser(UserId INT, UserName VARCHAR(50), UserEmail VARCHAR(50));

Please watch the Derived Table (DT) video here for a complete demo.



Q63. What is Common Table Expression or CTE in SQL?

CTEs work similarly to derived tables. We can define CTE by adding a WITH clause directly before SELECT, INSERT, UPDATE, DELETE, or MERGE statement. To create a CTE, you have to start with the ‘WITH’ keyword followed by the name of the CTE and the ‘AS’ keyword. But once you create a CTE, you have to immediately use it.

CTE was first introduced in SQL Server in 2005, then PostgreSQL made them available starting with Version 8.4 in 2009. MySQL waited a little bit longer and made them available in 2018, starting with Version 8.0.  

As soon as a query completes they are removed from the database memory.  They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. CTE can be referenced in a query as many times as you want and they can also be self-referencing. 








CTE Fundamental Points:

  1. All columns of CTE must have valid unique names. 
  2. You can not use order by clause unless the TOP clause is applied.
  3. A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. 
  4. You cannot nest CTEs, but you can join a CTE to another previously defined CTE. 
  5. You cannot USE the CTE in the WHERE clause.
  6. When using CTEs in T-SQL batches with multiple statements, the preceding batch MUST be terminated with a semi-colon because otherwise, we will get an error message.



Use of CTE:

  1. CTE is used whenever you want to use a ranking function such as ROW_NUMBER(), RANK(), NTILE(), etc.
  2. CTE is used for multi-level aggregations functions.
  3. CTE is used to check and delete duplicate records. But the CTE must be based on a single table in order to delete the records.
  4. CTE is used to update and delete the table records. However, updating data does work with multiple base tables as long as only one base table is being changed. 
  5. CTE is used to generate and print Fibonacci Numbers.
  6. CTE is used to generate and print Factorial numbers.
  7. Sample Test Data can be created using Recursive CTE.
  8. CTE can also be used to create a table value constructor.
  9. CTE was used for data pagination before the introduction of OFFSET-FETCH in SQL

WITH CTE AS
(
SELECT Salary,  
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DenseRank 
FROM [dbo].[Payroll]
)
SELECT TOP 1 Salary FROM CTE WHERE DenseRank = 3;

A CTE can be both recursive and non-recursive.



Recursive CTE:

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data as the CTE continues to execute until the query returns the entire hierarchy. 

The recursive common table expressions must contain at least two CTE query Definitions, a recursive member, and an anchor member. A CTE can reference itself and previously defined CTEs in the same WITH clause.

In a recursive CTE, we should provide a where condition to terminate the recursion. If a CTE is created incorrectly it can enter an infinite loop. 



You can use any of the Set operators: UNION, UNION ALL, EXCEPT, or INTERSECT to combine the anchor members of SQL CTE. UNION ALL operator is the only operator used for combining multiple recursive members.

The number of columns and the data types should be the same in the anchor and recursive members.

You cannot use GROUP BY, SELECT DISTINCT, PIVOT, HAVING, TOP, LEFT JOIN, RIGHT JOIN, OUTER JOIN, Subqueries, and Scalar Aggregations inside the recursive members CTE Definition.

WITH FibonacciSeries(PreviousNumber,  Number)  AS 
(
SELECT 0, 1
UNION ALL
SELECT Number, PreviousNumber + Number FROM FibonacciSeries WHERE Number < 1000 -- Max Number Limit
)
SELECT PreviousNumber AS Fibo, Number FROM FibonacciSeries


Please watch the Common Table Expression (CTE) video here for the live demo.



Q64. What is Table Variable? What is the difference between Table Variable and Temp Table in SQL?

The table variable is a special type of the local variable that helps to store data temporarily, similar to the temp table in SQL Server.  Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. 

In many cases, a table variable can outperform a solution using a temporary table. Microsoft defines it as a variable of type table i.e they are created with the DECLARE @local_variable statement. The table variable provides all the properties of the local variable Its definition includes column definitions, names, data types, and constraints. 








Table Variable Fundamental Points:

  1. Tempdb database is used to store table variables the same as a temp table. But the name that is returned for the table variable is a system-assigned name.
  2. The name of the local table variable must start with at(@) sign and they must be declared with the DECLARE statement. 
  3. The TABLE keyword specifies that this variable is a table variable. After the TABLE keyword, we have to define column names and datatypes of the table variable in SQL Server.
  4. Unlike a regular or temporary table, you cannot alter the structure of the table variables after they are declared.
  5. Assignment operation between table variables isn`t supported, unlike normal primitive variables.
  6. The life cycle of the table variables starts at the declaration point and ends at the end of the batch. They are scoped to the batch or routine in which they are created, and are removed automatically once it completes execution.


  7. If a table variable is declared in a stored procedure, it is local to that stored procedure and cannot be referenced in a nested procedure.
  8. They behave like other variables in their scoping rules, Once out of scope, they are disposed of.  A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement.
  9. The only PRIMARY KEY, UNIQUE, NULL, and CHECK constraint types are allowed are but FOREIGN KEY constraints are not allowed.
  10. You can’t call user-defined functions from CHECK constraints, DEFAULT values, and computed columns in the table variable.
  11. If you are using a table variable with a join, you need to alias the table in order to execute the query.
  12. You cannot use user-defined data types in table variables as well same as temporary tables unless the datatypes exist in TempDB.
  13. You cannot use a table variable as an input or an output parameter for stored procedures or functions. 
  14. Table variables require fewer locking resources as they are ‘private’ to the process that created them.

DECLARE  @TableVariable TABLE
(
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](50) NOT NULL,
[Email] [varchar](50) NOT NULL,
[MobileNumber] [nchar](15) NULL
);



Temp Table vs Table Variable:

  1. Table variable has local or batch scope while temp table may have a global scope or session scope.
  2. Length of Table name can be Maximum of 128 characters for table variable while Maximum of 116 characters for temp table.
  3. A table variable can not be used in the SELECT INTO command but the temp table is mostly created and used in the SELECT INTO command.
  4. You can not use the table variable directly for INSERT INTO Clause like Temp Table before the declaration.
  5. A table variable cannot be the target of any DDL commands like ALTER, TRUNCATE, DROP but a temp table can be.
  6. A table variable can not be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT and also does not support table hints such as WITH (FORCES CAN), but a temp table can be used.


  7. A table variable cannot be used in the Dynamic SQL Query, but a temp table can be used in Dynamic SQL Query.
  8. You can not use Table Variable after Go statement but the temp table can be used.
  9. You cannot define explicit indexes on table variables but you can define explicit indexes on the temporary tables.
  10. Temp tables cannot be used inside a function but table variables can be used inside scalar or multi-statement table UDFs.
  11. You can return a table variable from a user-defined function but you can not return a temp table from any UDF.
  12. Transaction rollbacks do not affect table variables because table variables have a limited scope and are not part of the persistent database. On the other hand, temp table transactions can be rolled back.
  13. Though both temp table and table variables are stored in the temp table, the biggest difference between the temp table and table variable is that the table variable uses the current database collation by default while the temp table always uses the TempDB collation.
  14. Additionally, User-defined data types and XML collections must be in tempdb to use for temp tables but table variables can use them from the current database.
  15. The biggest problem with table variables is that statistics aren’t maintained on the columns i.e Table variables don`t have distribution statistics. On the other hand temp table that statistics aren’t maintained on the columns.



CREATE FUNCTION TableVariableAndFunction()
RETURNS @TableVariable TABLE
(
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](50) NOT NULL,
[Email] [varchar](50) NOT NULL,
[MobileNumber] [nchar](15) NULL
)
AS
    BEGIN
        INSERT INTO @TableVariable
SELECT
  [Name]
  ,[Email]
  ,[MobileNumber]
  FROM [dbo].[User]
WHERE Email IS NOT NULL AND  MobileNumber IS NOT NULL;
        RETURN;
    END;
GO

SELECT * FROM [dbo].[TableVariableAndFunction]();

Please watch the SQL Table Variable video here for live demo.



Q65. What is Table-Valued Type? What is the difference between Table Variable and Table-Valued Type in SQL?

Table-Valued Parameters or Table-Valued Type is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER, it is not possible to pass a table variable in a stored procedure or function as a parameter, but now in SQL SERVER 2008, we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

User-Defined Table Types or Table Valued Parameter are used to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL. Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. 








CREATE TYPE UserTableTypeParameter AS TABLE 
(
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](50) NOT NULL,
[Email] [varchar](50) NOT NULL,
[MobileNumber] [nchar](15) NULL    
)

Once you have this table type, you can use it to declare table variables. However, you cannot use the type with CREATE TABLE.

DECLARE @TVP AS  [dbo].[UserTableTypeParameter];

A user-defined table type created with CREATE TYPE is only meta-data. An instance of the type is created at run time when the type is used as a local variable or table-valued parameter. You can query sys.types in the current database to determine any table types that have been created.

SELECT  * FROM sys.types WHERE is_table_type = 1;



TVP Fundamental Points:

  1. Table-valued parameters are strongly typed and their structure is automatically validated. 
  2. Table-valued parameters are declared by using user-defined table types. They are commonly used to pass a table as a parameter into stored procedures or functions. 
  3. When table variables are passed as parameters, the table is materialized in the TempDB system database as a table variable and passed by reference,  a pointer to the table in the TempDB.
  4. Table-valued parameters must be passed as READONLY parameters to SQL routines. 
  5. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  6. Like a Table variable, you cannot use a table-valued parameter as the target of a SELECT INTO or INSERT EXEC statement. However, a table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
  7. You cannot return data in a table-valued parameter. This Means Table-valued parameters are input-only; the OUTPUT keyword is not supported.
  8. The Table Valued Parameter in any procedure cannot be nullable, so you can not pass NULL for the TVP. Since NULL is a scalar value, not a table value. 
  9. Since the table-valued parameter always has the implicit default value of an empty table. If you do not pass any value for the TVP in SP, the default will be used and the SP will execute without any issue. 


  10. You cannot create stored procedures or user-defined functions in the CLR that take a table-valued parameter. But the other way works i.e you can call a T-SQL procedure with a TVP from a CLR procedure.
  11. SQL Server does not maintain statistics on columns of table-valued parameters. 
  12. Like Table Variables TVP also does not acquire locks when the data is being populated from a client.  
  13. User-defined table types also support PRIMARY KEY, UNIQUE and CHECK constraints, Default constraints, and IDENTITY. But Foreign Key is not allowed.
  14. If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.
  15. You can also define computed columns and index definitions as well in the TVP. 
  16. Same as Table Variable you cannot use ALTER TABLE statements to modify the design of table-valued parameters.
  17. One thing with table types that is not apparent is that you need to EXECUTE permission to use a user-defined table type. However, this does not apply to normal scalar types.
  18. You cannot use table-valued parameters across linked servers. You cannot even use table-valued parameters across databases. They are local to the database.



Table Variable vs Table-Valued Parameter:

  1. The TVP also ceases to exist once it is out of scope same as the table variable but the TVP type definition remains until it is explicitly dropped. 
  2. A TVP can be used in the Dynamic SQL but a table variable cannot be used in Dynamic SQL.
  3. TVP can be passed to stored procedures and user-defined functions but Table Variables can not be passed as parameters.
  4. For TVP you need to have Execute permission while it is not required for the table variable.
  5. You can not use the Update, Delete, Insert command on TVP in the calling routine.

Please watch the SQL Table Variable video here for the live demo.



Q66. What is the difference between PIVOT and UNPIVOT Functions in SQL?

In SQL PIVOT operators are used for converting rows to columns and UNPIVOT operators are used to convert columns to rows. Pivot tables are a piece of summarized information that is generated from a large underlying dataset. The SQL PIVOT clause allows you to write a cross-tabulation. This means that you can aggregate your results and rotate rows into columns.

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements.  A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to give a summary of the data.



SQL Server PIVOT operator rotates a table-valued expression. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output.

UNPIVOT carries out almost the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. 

However. PIVOT carries out aggregation and merges possible multiple rows into a single row in the output. UNPIVOT doesn't reproduce the original table-valued expression result because rows have been merged. Also, null values in the input of UNPIVOT disappear in the output.  When the values disappear, it shows that there may have been original null values in the input before the PIVOT operation.



To-Do for PIVOTing:

  1. First, select a base dataset for pivoting i.e we want to specify what fields to include in our cross-tabulation results.
  2. Second, create a temporary result by using a derived table or common table expression (CTE) i.e we need to specify a SELECT statement that will return the source data for the pivot table. And you must specify an alias for the source query.
  3. Third, apply the PIVOT operator. Here you need to specify what aggregate function to use when creating our cross-tabulation query. You also need to specify what pivot values to include in our results within the IN clause. These will be used as the column headings in our cross-tabulation query.

The important point to note here is that while writing the query for the pivot table in SQL Server, we need to provide a distinct list of column values that we would like to visualize in the pivot table. Also when aggregate functions are used with PIVOT, the presence of any null values in the value columns are not considered when computing an aggregation.



SELECT 
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (DT or CTE query that produces the data)   
    AS alias-for-the-source-query
PIVOT  
(  
    aggregation-function>(column-being-aggregated)  
FOR   
[ <column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  ... [last pivoted column])  
) AS alias-for-the-pivot-table  
 ORDER BY optional-clause; 

In the PIVOT query, there are some specific keywords like aggregate functions (SUM, AVG, MAX, MIN, etc), FOR, and IN, which are meant for use by the PIVOT operator only. 

The FOR keyword is a special keyword used for the pivot table in SQL Server scripts. This operator tells the pivot operator on which column do we need to apply the pivot function. Basically, the column which is to be converted from rows into columns.

The IN keyword, as already explained above, lists all the distinct values from the pivot column that we want to add to the pivot table column list. 



The aggregate functions like SUM, and AVG operator, will essentially aggregate the values from the column so that they can be used in the pivot table. It is mandatory for the pivot operator to use an aggregated column that it can display for the values sections.

Pivot-Table

The only limitation in this process is that we need to provide hardcoded values for the columns that we need to select from the pivot table. To overcome this limitation you need to use a dynamic stored procedure that will return a PIVOT table in SQL.



Q67. What is the CASE statement in SQL? Can you use the CASE statement in the WHERE clause?

The CASE in SQL is the same as the SWITCH CASE statement of other programming languages. The CASE statement is SQL's way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements.

The CASE statement goes through conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. 

If no conditions are true, it returns the value in the ELSE clause. The ELSE statement is optional and provides a way to capture values not specified in the WHEN/THEN statements. 

If there is no ELSE part and no conditions are true, it returns NULL. Every CASE statement must end with the END statement. 

We can use a Case statement in SELECTqueries, with Where, Order By, and Group By clause and within AGGREGATE functions. The case is also used in INSERT and UPDATE commands.



Simple SELECT CASE statement: This is used to evaluate one expression against multiple values.

SELECT CASE Gender
When Male Then M
When Female Then F
ELSE U
END

CASE with Comparison Operator:  In this type of CASE we can evaluate a condition using comparison operators only, not by any field.

CASE
    WHEN Marks > 60 THEN FirstDivision
    WHEN Marks > 50 AND Marks < 60  THEN SecondDivision
WHEN Marks > 35 AND Marks < 50  THEN ThirdDivision
    ELSE Failed
 END



Case with ORDER BY clause:  In SQL, we use Case with Order By clause to sort results in ascending or descending order. 

SELECT Name, Gender, Marks FROM Student
ORDER BY CASE Gender
WHEN 'F' THEN Marks End DESC,
CASE WHEN Gender = 'M' THEN Marks  
END

Case with GROUP BY clause: We can use a Case statement with Group By clause as well. 

SELECT CASE 
WHEN Gender = 'Male' THEN 'M'
WHEN Gender = 'Female' THEN 'F'
        ELSE 'Unknown' 
END AS GenderType,
    COUNT(1) AS Totalcount
FROM User
GROUP BY CASE 
WHEN Gender = 'Male' THEN 'M'
WHEN Gender = 'Female' THEN 'F'
        ELSE 'Unknown' 
END



CASE with AGGREGATE Functions: A CASE statement is also used in the Aggregate functions. Since COUNT ignores NULL, you could use a CASE statement to evaluate the condition and produce null or non-null values depending on the outcome. 

SELECT COUNT(CASE WHEN Gender = 'Male' THEN 1 ELSE NULL END) AS MaleCount,
       COUNT(CASE WHEN Gender = 'Female' THEN 1 ELSE NULL END) AS FemaleCount,
       COUNT(CASE WHEN Gender IS NULL THEN 1 ELSE NULL END) AS UnknownCount      
FROM User;

CASE with UPDATE Statement: We can use a Case statement in SQL with the UPDATE command as well to update the data conditionally. 

UPDATE User 
SET StatusCode  = CASE StateCode
WHEN 0 THEN 'Active' 
WHEN 1 THEN 'Deactive' 
ELSE  'Deleted' 
END



CASE with INSERT Statement: We can insert values into tables as well with the help of the Case statement in SQL.

INSERT INTO Employee VALUES 
(@EmployeeName,
CASE @Gender
        WHEN 0 THEN 'M'
        WHEN 1 THEN 'F'
END,
@Statecode,
@salary)


To Be Continued Part-09...


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.

    

You May Also Like:
By at
Labels:

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.

Subscribe to: Post Comments (Atom)