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.
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
Q4. Can we return table from a user defined function?
Answer:
Yes, There are three types of function.
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
------------------------------------------------------------------------------------------------------------------------
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.
------------------------------------------------------------------------------------------------------------------------
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
Q4. Can we return table from a user defined function?
Answer:
Yes, There are three types of function.
- Scalar Function - returns a single value as a result of actions performed by the function. We return any datatype value from a function.
- 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.
- 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.
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
------------------------------------------------------------------------------------------------------------------------