Interview Questions and answers - SQL Server Day 3


Question:  What is the difference between Function and Stored Procedure in SQL Server?

Answer: Some the basic difference between stored procedure and function is described below.

The main difference between stored procedure and function is Function must return a value while stored procedure can or not this is optional. The function can have input parameters only while a stored procedure can have both input/output parameters. The function can be called from stored procedures, but stored procedures can’t call from function. 

Function 
Stored Procedure
A function must return a value
In stored procedure it is optional 
A function can have input parameter only
A stored procedure can have both inputs and output parameter s
A function can be called from procedure
The procedure can‘t be called from function
The function allows only select statement
Procedure allows DML statement(SELECT, INSERT, UPDATE, DELETE)
A function can use an SQL statement like WHERE/HAVING/SELECT
The procedure can’t be used in SQL a statement like WHERE/HAVING/SELECT
A function can return the table and can be treated as another row set. This can be used in joins with other tables.
The transaction can’t be implemented
The transaction can be implemented in stored procedure

Question: What is subquery in SQL server?

Answer: A SELECT statement query nested in another t-SQL statement is called subquery. SELECT subquery always runs independently and returns the result set to the t-SQL statement. The subquery doesn't depend on a statement in which it is nested.

Question: What are the different types of joins in SQL Server?

Answer: Types of joins in SQL Server is given bellow.
1- Cross join
2- Inner join
3- Outer join
    a)Left Outer join
    b)Right Outer join
    c)Full Outer join
4- Self-join

Question: What is Cross Join in SQL Server?

Answer: Joins between tables without conditions produces the Cartesian product called cross join. The size of the Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

example:


SELECT p.bID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BID;

Question: What is inner join in SQL Server?

Answer: Inner join displayed only the rows of tables which matched in both joined table on a particular column. This is the default type of join in the query.

Question: What is Outer join and it type in SQL Server?

Answer: Outer join includes related and rows and also not related rows. there is three types of outer joins in SQL Server.
a) Left Outer Join: the Left Outer join display all the rows of the first table and all matched rows of the second table but not appears unmatched rows of the second table.
b) Right Outer join: Right Outer joined display all the row of the second table and matched rows of the first table, but not appears unmatched rows if the first table.
c) Full Outer Join: Full Outer join display all the rows of joined tables, whether they matched or not.

Question: What is Self join in SQL Server?


Answer: Join between a table, and its alias is called self join. In this join table, join itself, but the same name may create confusion, so we used the alias of the table as the second table.


PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...