3i InfoTech SQL Interview Question

Exercise 1

CREATE TABLE #TABLE1

(

       RowNo INT IDENTITY,

       ProductName VARCHAR(50),

       Price MONEY

)

INSERT INTO #TABLE1(ProductName,Price)VALUES('A',10)

INSERT INTO #TABLE1(ProductName,Price)VALUES('B',20)

INSERT INTO #TABLE1(ProductName,Price)VALUES('C',30)

INSERT INTO #TABLE1(ProductName,Price)VALUES('D',NULL)

INSERT INTO #TABLE1(ProductName,Price)VALUES('E',NULL)

INSERT INTO #TABLE1(ProductName,Price)VALUES('F',10)

SELECT * FROM #TABLE1

RowNo   ProductName   Price

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

1       A             10.00

2       B             20.00

3       C             30.00

4       D             NULL

5       E             NULL

6       F             10.00

(6 row(s) affected)

Question

1. SUM () of Price

2. AVG () of Price

Answer

1. SUM () of Price

SELECT SUM (Price) FROM #TABLE1

Results

70.00

2. AVG () of Price

SELECT AVG (Price) FROM #TABLE1

Results

17.50

Question

How it’s calculated?

Answer

Formula for calculating the average.

Average = Total of price / Number of Rows count

So, Price total is 70 and Rows count is 4 respectively excluding “NULL” values.

Average = 70 / 4

Average = 17.50

Note:-

An aggregate function, if column contains “NULL” value is eliminated by an aggregate or other SET operation.

Messages you will received

Warning: Null value is eliminated by an aggregate or other SET operation.

Exercise 2

CREATE TABLE #TABLE2

(

       ProductName VARCHAR(50),

       Quantity INT,

       Price MONEY,

       RefDate DATETIME

)

INSERT INTO #TABLE2(ProductName,Quantity,Price,RefDate)VALUES('A',10,100,'2016/01/01')

INSERT INTO #TABLE2(ProductName,Quantity,Price,RefDate)VALUES('A',20,200,'2016/01/02')

INSERT INTO #TABLE2(ProductName,Quantity,Price,RefDate)VALUES('A',30,300,'2016/01/03')

INSERT INTO #TABLE2(ProductName,Quantity,Price,RefDate)VALUES('B',40,200,'2016/01/04')

INSERT INTO #TABLE2(ProductName,Quantity,Price,RefDate)VALUES('B',50,500,'2016/01/05')

INSERT INTO #TABLE2(ProductName,Quantity,Price,RefDate)VALUES('C',60,300,'2016/01/06')

SELECT * FROM #TABLE2

ProductName   Quantity   Price    RefDate

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

A             10         100.00   2016-01-01 00:00:00.000

A             20         200.00   2016-01-02 00:00:00.000

A             30         300.00   2016-01-03 00:00:00.000

B             40         200.00   2016-01-04 00:00:00.000

B             50         500.00   2016-01-05 00:00:00.000

C             60         300.00   2016-01-06 00:00:00.000

(6 row(s) affected)

Question

Write query to get the each product last details inserted.

Hint

ProductName   Quantity   Price    RefDate

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

A             30         300.00   2016-01-03 00:00:00.000

B             50         500.00   2016-01-05 00:00:00.000

C             60         300.00   2016-01-06 00:00:00.000

Answer

SELECT ProductName,Quantity,Price,RefDate

FROM(

       SELECT ROW_NUMBER() OVER(PARTITION BY ProductName ORDER BY RefDate DESC)'RowNo',*

       FROM #TABLE2

) Result

WHERE RowNo = 1

Results

ProductName   Quantity   Price    RefDate

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

A             30         300.00   2016-01-03 00:00:00.000

B             50         500.00   2016-01-05 00:00:00.000

C             60         300.00   2016-01-06 00:00:00.000

Exercise 3

CREATE TABLE #TABLE3

(

       ProductId INT IDENTITY,

       ProductName VARCHAR(50),

       Quantity INT,

       Price MONEY,

       RefDate DATETIME

)

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('A',10,100,'2016/01/01')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('A',20,200,'2016/01/02')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('A',30,300,'2016/01/03')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('B',40,200,'2016/01/04')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('B',50,500,'2016/01/05')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('C',60,300,'2016/01/06')

SELECT * FROM #TABLE3

ProductId   ProductName   Quantity   Price    RefDate

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

1           A             10         100.00   2016-01-01 00:00:00.000

2           A             20         200.00   2016-01-02 00:00:00.000

3           A             30         300.00   2016-01-03 00:00:00.000

4           B             40         200.00   2016-01-04 00:00:00.000

5           B             50         500.00   2016-01-05 00:00:00.000

6           C             60         300.00   2016-01-06 00:00:00.000

(6 row(s) affected)

Question

Describe below ranking function?

1. ROW_NUMBER ()

2. RANK ()

3. DENSE_RANK ()

Answer

1. ROW_NUMBER ()

The ROW_NUMBER () function returns the sequential numbers, starting at 1, for the rows in a result set based on column. The ORDER BY keyword in the OVER clause specifies that the result set will appear in the descending order of the ProductId column.

SELECT ProductId,Price,ROW_NUMBER() OVER(ORDER BY Price DESC)'Rank'

FROM #TABLE3

ProductId   Price    Rank

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

5           500.00   1

6           300.00   2

3           300.00   3

4           200.00   4

2           200.00   5

1           100.00   6

(6 row(s) affected)

2. RANK ()

The RANK () function returns the rank of each row in a result set based on specified criteria.

For example, you want to rank the products based on the purchase made during a year. For this, you can use the RANK function. This function will consider the ORDER BY clause and the record with maximum value will get the highest rank if the ORDER BY clause is ASC. 

Consider the following example, you need to create the report of all the product with their price. The highest ranked product should be given the rank as 1. In addition, if two products have the same price, they should be given the same rank. However, the next rank number will be addition of the rank of the products that have the same price and the number of products holding the same price.  

SELECT ProductId,Price,RANK() OVER(ORDER BY Price DESC)'Rank'

FROM #TABLE3

ProductId   Price    Rank

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

5           500.00   1

6           300.00   2

3           300.00   2

4           200.00   4

2           200.00   4

1           100.00   6

(6 row(s) affected)

3. DENSE_RANK ()

The function is used where sequential ranking values need to be given based on a specified criteria. It performs the same ranking task as the RANK function, but provides sequential ranking values to an output. For example, you want to rank the products based on the purchase made during a year. If two products have the same price, they should be given the same rank. However, the next rank number would be assigned the next rank value.  

Consider the following example, you need to give the same rank to the products with the same price and sequential rank to the next one.

SELECT ProductId,Price,DENSE_RANK() OVER(ORDER BY Price DESC)'Rank'

FROM #TABLE3

ProductId   Price    Rank

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

5           500.00   1

6           300.00   2

3           300.00   2

4           200.00   3

2           200.00   3

1           100.00   4

(6 row(s) affected)

Exercise 4

Question

If you have a product table and on that insert trigger is created. So, which identity function you will be used to return, which one first identity value generated on the table.

Answer

CREATE TABLE Product

(

       RowNo INT IDENTITY,

       ProductName VARCHAR(50),

       Price MONEY

)

CREATE TRIGGER TRG_Product

ON Product

FOR INSERT

AS

INSERT INTO Product(ProductName,Price) SELECT ProductName,Price FROM INSERTED

INSERT INTO Product(ProductName,Price)VALUES('A',10)

SELECT * FROM Product

RowNo   ProductName   Price

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

1       A             10.00

2       A             10.00

(2 row(s) affected)

You will see here, two record inserted on the table, first from your query and next from trigger fire on that.

Now run the each identity function.

SELECT @@IDENTITY

Results

2

Here, you will received the last identity value generated in the current scope.

SELECT SCOPE_IDENTITY()

Results

1

Here, you will received the first identity value generated by a trigger in current scope.

SELECT IDENT_CURRENT('Product')

Results

2

Here, you will received the last identity value generated in specified table in any scope.

So, correct answer is SCOPE_IDENTITY()

Note:-

@@IDENTITY

Return the last identity value generated in current scope

SCOPE_IDENTITY()

Return the first identity value generated by a trigger or a user defined function in current scope, otherwise it's also return last identity.

IDENT_CURRENT()

Return the last identity value generated in specified table in any scope.

Exercise 5

CROSS JOIN

A cross join, also known as Cartesian product, between two tables join each row from one table with each row of the other table. The number of rows in the result set is the number of rows in the first table multiplied by the number of rows in the second table.

For example, if Table 1 has 5 rows and Table 2 has 3 rows, then all 5 rows of Table 1 are joined with all 3 rows of Table 2. There for, the result set will contain 15 rows.

CREATE TABLE #TABLE1

(

       RowNo INT

)

INSERT INTO #TABLE1(RowNo)VALUES(10)

INSERT INTO #TABLE1(RowNo)VALUES(20)

INSERT INTO #TABLE1(RowNo)VALUES(30)

INSERT INTO #TABLE1(RowNo)VALUES(40)

INSERT INTO #TABLE1(RowNo)VALUES(50)

CREATE TABLE #TABLE2

(

       RowNo INT

)

INSERT INTO #TABLE2(RowNo)VALUES(10)

INSERT INTO #TABLE2(RowNo)VALUES(20)

INSERT INTO #TABLE2(RowNo)VALUES(30)

SELECT *

FROM #TABLE1

CROSS JOIN #TABLE2

RowNo       RowNo

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

10          10

20          10

30          10

40          10

50          10

10          20

20          20

30          20

40          20

50          20

10          30

20          30

30          30

40          30

50          30

(15 row(s) affected)

Exercise 6

Do yourself.

Insert each insert query one by one and respectively check identity value each time.

CREATE TABLE Product

(

       RowNo INT IDENTITY,

       ProductName VARCHAR(50),

       Price MONEY

)

CREATE TABLE Product1

(

       RowNo INT IDENTITY,

       ProductName VARCHAR(50),

       Price MONEY

)

CREATE TRIGGER TRG_Product

ON Product

FOR INSERT

AS

INSERT INTO Product(ProductName,Price) SELECT ProductName,Price FROM INSERTED

INSERT INTO Product1(ProductName,Price) SELECT ProductName,Price FROM INSERTED

INSERT INTO Product(ProductName,Price)VALUES('A',10)

INSERT INTO Product(ProductName,Price)VALUES('B',20)

INSERT INTO Product(ProductName,Price)VALUES('C',30)

INSERT INTO Product(ProductName,Price)VALUES('D',40)

INSERT INTO Product(ProductName,Price)VALUES('E',50)

INSERT INTO Product(ProductName,Price)VALUES('F',10)

SELECT * FROM Product

SELECT * FROM Product1

--Return the last identity value generated in current scope

SELECT @@IDENTITY

--Return the first identity value generated by a trigger or a user defined function in current scope, otherwise it's also return last identity.

SELECT SCOPE_IDENTITY()

--Return the last identity value generated in specified table in any scope.

SELECT IDENT_CURRENT('Product')

Exercise 7

Question

Can we use the try block without catch block?

Answer

No, We need at least catch or finally block at the end of try block.

try

{

}

You will get the following Error

Expected catch or finally