Q6-Q10

Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?
Q7. Different types of Indexes. 
Q8. Maximum number of Indexes we can have in table?
Q9. Difference between clustered index and non-clustered indexes?
Q10. What are magic tables in SQL?
------------------------------------------------------------------------------------------------------------------------
Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?

Answer:
That will depend on the content available in column Stu_name. 

example:
 Stu_name Varchar(8) -  Represents that table has a column named 'Stu_name'. which can hold maximum varchar of up to 9 charters. 

LEN(Stu_name) - Represents the length of string present in Stu_name column. it must be always less than the size of column can hold. 

------------------------------------------------------------------------------------------------------------------------
Q7. Different types of Indexes. 

Answer:

There are mainly two types of indexes in MS SQL server. 

1. Clustered Indexes
2. Non Clustered Indexes

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15
------------------------------------------------------------------------------------------------------------------------
Q8. Maximum number of Indexes we can have in table?

Answer:
Clustered - 1

Non clustered - 999 was allowed SQL Server 2008 R2 so now with 2016. We do not need to worry around number of non clustered indexs. 
------------------------------------------------------------------------------------------------------------------------
Q9. Difference between clustered index and non-clustered indexes?

Answer:

 clustered index determines how the data is stored in the rows of a table physically. On the other hand, the non-clustered index stores the data at a single place and the indexes are stored at another place. Additionally, each table can have only one clustered index. As against, In case of a non-clustered index, a table can have multiple non-clustered indices

1)  One table can only have only one clustered index.One table can only have many non-clustered index.

2) A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.A non-clustered index requires separate storage than the table storage to store the index information.

3) The default index created as part of the primary key column is a clustered index.

------------------------------------------------------------------------------------------------------------------------

Q10. What are magic tables in SQL?

Answer:

Magic tables are tables created for insertion, deletion and update internally by server,. In case of new row , a new entry is made in INSERTED magic table. 

In case of deletion of a row, entry is made in DELETED magic table'

In case of update of a row, one new entry is made in DELETED magic table for old data and one new entry is made in INSERTED magic table for new entry. 

Before SQL 2008R2. they were used only in case of triggers but now they are available for all tables. 

https://www.c-sharpcorner.com/blogs/what-is-magic-table-in-sql-server1#:~:text=What%20is%20magic%20table%20in%20SQL%20Server%3F%201,deleted%20and%20updated%20rows.%20...%20More%20items...%20

Q1-Q5

Q1. Can we do data insertion using sql functions?
Q2. What are the Differences between stored procedure and functions?
Q3. Explain cross join in SQL.
Q4. Can we return table from a user defined function? 
Q5. How to get top 3rd or Nth element in table.

------------------------------------------------------------------------------------------------------------------------
Q1. Can we do data insertion using sql functions?

Answer:
User-defined functions cannot be used to perform actions that modify the database state. So Insert, Update, Delete are not allowed in SQL functions.

------------------------------------------------------------------------------------------------------------------------

Q2. What are the Differences between stored procedure and functions?

Answer:

1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

2. Functions can have only input parameters (could be multiple input parameters) for it whereas Procedures can have input/output parameters.

3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

4. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

5. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

http://www.dotnettricks.com/learn/sqlserver/difference-between-stored-procedure-and-function-in-sql-server

------------------------------------------------------------------------------------------------------------------------

Q3. Explain cross join in SQL.

Answer:

• Also known as Cartesian join of two tables. 
• The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.  
• if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.

SELECT *  FROM table1

CROSS JOIN table2

where table1.id = table2.fk_id;

is similar ton-->

select * from table1 

inner join table2 

on table1.id = table2.fk_id

https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

https://www.w3resource.com/sql/joins/cross-join.php

------------------------------------------------------------------------------------------------------------------------
Q4. Can we return table from a user defined function? 

Answer:
Yes, There are three types of function. 

1. Scalar Function - returns a single value as a result of actions performed by the function. We return any datatype value from a function.
2. Inline Table Valued Function - The user-defined inline table-valued function returns a table variable as a result of actions performed by the function. The value of the table variable should be derived from a single SELECT statement.
3. Multi Statement Table Valued Function - A user-defined multi-statement table-valued function returns a table variable as a result of actions performed by the function. In this, a table variable must be explicitly declared and defined whose value can be derived from multiple SQL statements.

https://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-functions

------------------------------------------------------------------------------------------------------------------------
Q5. How to get top 3rd or Nth element in table.

Answer:
Using "row_number" 

Syntax:

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ProjectID ASC) AS RowNo
      , ProjectName 
    FROM Projects1 ) AS FOO

Where FOO.RowNo = 3
------------------------------------------------------------------------------------------------------------------------