TSQL Database - Quiz -5 - Quiz & Trivia

1. What is returned by select statement?

CREATE TABLE Test

( Ident INT NOT NULL IDENTITY (1,1)

, varfield varchar(100)

);

INSERT INTO Test VALUES ('abc')

DBCC CHECKIDENT ('Test',RESEED,100)

truncate table Test

INSERT INTO Test VALUES ('def')

SELECT Ident FROM Test

 GO
drop table Test

100

101

1

2

The correct answer is 1 because the SELECT statement is retrieving the Ident column from the Test table, which has been populated with the values 'abc' and 'def'. Since the table was truncated after the first INSERT statement, the IDENTITY value was reset to 1. Therefore, the SELECT statement will return the value 1 as the only record in the Test table.

Explanation

The correct answer is 1 because the SELECT statement is retrieving the Ident column from the Test table, which has been populated with the values 'abc' and 'def'. Since the table was truncated after the first INSERT statement, the IDENTITY value was reset to 1. Therefore, the SELECT statement will return the value 1 as the only record in the Test table.

2. What will be the output of the select statement?

CREATE TABLE [dbo].[IndexTable](

[ID] [int] NOT NULL,

[Value] [varchar](50) NULL,

CONSTRAINT [PK_IndexTable] PRIMARY KEY CLUSTERED

([ID] ASC)

GO

INSERT INTO [dbo].[IndexTable]

 VALUES(1, 'value1'), (2, 'value2')

GO

ALTER INDEX [PK_IndexTable] ON [dbo].[IndexTable] DISABLE

Go

INSERT INTO [dbo].[IndexTable]

 VALUES(3, 'Value3')

GO

ALTER INDEX [PK_IndexTable] ON [dbo].[IndexTable] REORGANIZE

GO

INSERT INTO [dbo].[IndexTable]

 VALUES(4, 'Value4')

GO

ALTER INDEX [PK_IndexTable] ON [dbo].[IndexTable] REBUILD

GO

INSERT INTO [dbo].[IndexTable]

 VALUES(5, 'Value5')

GO

SELECT * FROM indextable

Value1,value2,value3,value4,value5

Value1,value2,value3,value5

Value1,value2,value4,value6

Value1,value2,value5

The correct answer is "value1,value2,value5". This is because the select statement retrieves all the rows from the "indextable" table, which includes the values inserted into the table using the INSERT INTO statements. The table has a primary key clustered index on the "ID" column, which ensures that the rows are stored in a specific order. The REORGANIZE and REBUILD index statements do not change the order of the rows in the table. Therefore, the select statement will return the rows in the order they were inserted, which is "value1,value2,value5".

Explanation

The correct answer is "value1,value2,value5". This is because the select statement retrieves all the rows from the "indextable" table, which includes the values inserted into the table using the INSERT INTO statements. The table has a primary key clustered index on the "ID" column, which ensures that the rows are stored in a specific order. The REORGANIZE and REBUILD index statements do not change the order of the rows in the table. Therefore, the select statement will return the rows in the order they were inserted, which is "value1,value2,value5".

3. What is EBS Database recovery Model?

SIMPLE

FULL

BULK_LOGGED

The EBS Database recovery model determines how the database can be restored or recovered in case of a failure. The FULL recovery model allows for the complete restoration of the database to a specific point in time, using transaction log backups. This model provides the highest level of recoverability but requires more storage space and frequent log backups.

Explanation

The EBS Database recovery model determines how the database can be restored or recovered in case of a failure. The FULL recovery model allows for the complete restoration of the database to a specific point in time, using transaction log backups. This model provides the highest level of recoverability but requires more storage space and frequent log backups.

4. What is returned from #tblTrans when you run the below code. CREATE TABLE #tblTrans(RowId TINYINT) GO BEGIN TRAN DECLARE @getId TINYINT SET @getId=100 INSERT INTO #tblTrans (RowId) VALUES(@getId) IF @getId >10 RAISERROR('RowId should not be greater than 10',11,16) PRINT @@ERROR IF @@ERROR = 0 BEGIN COMMIT TRAN PRINT 'I am here at commit!' END ELSE BEGIN ROLLBACK TRAN PRINT 'I am here at rollback!' END GO SELECT * FROM #tblTrans

No rows were returned

100

NULL

The code first creates a temporary table called #tblTrans with a single column called RowId. It then begins a transaction.

Next, it declares a variable called @getId and sets its value to 100. It inserts the value of @getId into the #tblTrans table.

After that, it checks if @getId is greater than 10. If it is, it raises an error with the message "RowId should not be greater than 10".

If there are no errors, it commits the transaction and prints "I am here at commit!". Otherwise, it rolls back the transaction and prints "I am here at rollback!".

Finally, it selects all rows from the #tblTrans table.

Since the value of @getId is 100 and it is not greater than 10, there are no errors and the transaction is committed. However, since the value of @getId is not specified to be inserted into the #tblTrans table, no rows are returned when selecting from it. The output of the code is "No rows were returned", followed by "100" and "NULL".

Explanation

The code first creates a temporary table called #tblTrans with a single column called RowId. It then begins a transaction.

Next, it declares a variable called @getId and sets its value to 100. It inserts the value of @getId into the #tblTrans table.

After that, it checks if @getId is greater than 10. If it is, it raises an error with the message "RowId should not be greater than 10".

If there are no errors, it commits the transaction and prints "I am here at commit!". Otherwise, it rolls back the transaction and prints "I am here at rollback!".

Finally, it selects all rows from the #tblTrans table.

Since the value of @getId is 100 and it is not greater than 10, there are no errors and the transaction is committed. However, since the value of @getId is not specified to be inserted into the #tblTrans table, no rows are returned when selecting from it. The output of the code is "No rows were returned", followed by "100" and "NULL".

5.

Create table test(a int)

insert into test values (null) insert into test values (2) insert into test values (3) insert into test values (1) insert into test values (null) select *from test order by a What will be the order of null values?

In the begining of the result set

At the end of the result set

No particular order

One null in the begining and one at the last of result set

When ordering a column that contains null values, the null values are usually placed at the beginning of the result set. In this case, the "order by a" statement will sort the values in the "a" column, and since null values are considered to be the smallest possible value, they will be placed at the beginning of the result set.

Explanation

When ordering a column that contains null values, the null values are usually placed at the beginning of the result set. In this case, the "order by a" statement will sort the values in the "a" column, and since null values are considered to be the smallest possible value, they will be placed at the beginning of the result set.

6. I create the following two (2) tables , and insert the data as shown.

CREATE TABLE t1(x INT,A INT IDENTITY(1,1));

 INSERT INTO t1 VALUES (10),(20),(4),(20),(10);

CREATE TABLE t2(y INT,B INT IDENTITY(15,15) PRIMARY KEY);

 INSERT INTO t2 VALUES (1),(20),(3);

I then execute the following T-SQL statenent

SELECT x AS 'Answer' FROM t1 EXCEPT SELECT y FROM t2

The question is: How many rows are returned by the SELECT statment ?

1 row is returned by the SELECT statement

2 row is returned by the SELECT statement

3 row is returned by the SELECT statement

4 row is returned by the SELECT statement

5 row is returned by the SELECT statement

The SELECT statement returns 2 rows because it uses the EXCEPT operator to compare the values in the "x" column of table t1 with the values in the "y" column of table t2. The EXCEPT operator returns all distinct rows from the left table (t1) that are not in the right table (t2). In this case, the values 10 and 4 from table t1 are not present in table t2, so they are returned as the result of the SELECT statement.

Explanation

The SELECT statement returns 2 rows because it uses the EXCEPT operator to compare the values in the "x" column of table t1 with the values in the "y" column of table t2. The EXCEPT operator returns all distinct rows from the left table (t1) that are not in the right table (t2). In this case, the values 10 and 4 from table t1 are not present in table t2, so they are returned as the result of the SELECT statement.

7. You have a table named Product with three columns: ProductID, Name and Color. Which of the following queries runs successfully based on this information? (select 3)

SELECT ProductID,Name,Color FROM Product

SELECT ProductID Name Color FROM Product

SELECT ProductID, Name FROM Product

SELECT ProductID Name FROM Product

The correct answers are:
1) SELECT ProductID,Name,Color FROM Product
2) SELECT ProductID, Name FROM Product
3) SELECT ProductID, Name FROM Product

These queries run successfully because they all select columns from the "Product" table. The first query selects all three columns (ProductID, Name, and Color), the second query selects only the ProductID and Name columns, and the third query also selects only the ProductID and Name columns. The fourth query is incorrect because it is missing a comma between the columns ProductID and Name.

Explanation

The correct answers are:
1) SELECT ProductID,Name,Color FROM Product
2) SELECT ProductID, Name FROM Product
3) SELECT ProductID, Name FROM Product

These queries run successfully because they all select columns from the "Product" table. The first query selects all three columns (ProductID, Name, and Color), the second query selects only the ProductID and Name columns, and the third query also selects only the ProductID and Name columns. The fourth query is incorrect because it is missing a comma between the columns ProductID and Name.

Submit

8. If "order by" clause is not used in a query then in which order is the result set returned?

Insertion Order

Primary key order

Clustered Index order

No guarantee of order

If the "order by" clause is not used in a query, the result set is returned with no guarantee of order. This means that the order in which the rows are retrieved from the database may vary each time the query is executed. The database engine is free to return the rows in any order it deems efficient, which could be based on factors such as data storage and retrieval algorithms. Therefore, if a specific order is required, it is necessary to use the "order by" clause in the query to explicitly specify the desired ordering criteria.

Explanation

If the "order by" clause is not used in a query, the result set is returned with no guarantee of order. This means that the order in which the rows are retrieved from the database may vary each time the query is executed. The database engine is free to return the rows in any order it deems efficient, which could be based on factors such as data storage and retrieval algorithms. Therefore, if a specific order is required, it is necessary to use the "order by" clause in the query to explicitly specify the desired ordering criteria.

9. Out of 4 statements namely 1,2,3,4,5, which of the statement/statements will execute successfully? if object_id('test2') is not null drop table test2 if object_id('test1') is not null drop table test1 create table test1 (a int not null primary key); create table test2 (b int,a int ) GO --statement 1 insert into test1 output inserted.a select 1 GO --statement 2 create trigger trig_test1 on test1 after insert as select 2 GO --statement 3 insert into test1 select 3 GO --statement 4 insert into test1 output inserted.a select 4 GO --statement 5 declare @temp as table (b int) insert into test1 output inserted.a into @temp select 5 select * from @temp GO

1

2

3

4

5

Statements 1, 2, 3, and 5 will execute successfully.

Statement 1 checks if the table "test2" exists and drops it if it does. Since the table "test2" is created later in the script, this statement will not have any effect.

Statement 2 creates a trigger on the "test1" table after an insert operation. This trigger will be created successfully.

Statement 3 inserts a value into the "test1" table. This statement will execute successfully.

Statement 4 inserts a value into the "test1" table and outputs the inserted value. This statement will execute successfully.

Statement 5 declares a variable and inserts a value into the "test1" table, outputting the inserted value into the variable. This statement will execute successfully. The final select statement displays the contents of the variable.

Explanation

Statements 1, 2, 3, and 5 will execute successfully.

Statement 1 checks if the table "test2" exists and drops it if it does. Since the table "test2" is created later in the script, this statement will not have any effect.

Statement 2 creates a trigger on the "test1" table after an insert operation. This trigger will be created successfully.

Statement 3 inserts a value into the "test1" table. This statement will execute successfully.

Statement 4 inserts a value into the "test1" table and outputs the inserted value. This statement will execute successfully.

Statement 5 declares a variable and inserts a value into the "test1" table, outputting the inserted value into the variable. This statement will execute successfully. The final select statement displays the contents of the variable.

Submit

10. What is the result of this batch? (select two) --batch ONE select 1 return select 2 select 3 go --batch TWO select 4 return (select 5)

Batch ONE returns 1

Batch ONE returns 2

Batch ONE returns 3

Batch ONE returns "A RETURN statement with a return value cannot be used in this context."

Batch TWO returns 4

Batch TWO returns 5

Batch TWO returns "A RETURN statement with a return value cannot be used in this context."

The correct answer is Batch ONE returns 1 and Batch TWO returns "A RETURN statement with a return value cannot be used in this context." This is because in Batch ONE, the first select statement returns 1, but the second select statement is followed by a return statement, which is not valid in this context. In Batch TWO, the select statement returns 4, but the return statement is also followed by a select statement, which is not valid in this context. Therefore, the result of Batch TWO is the error message "A RETURN statement with a return value cannot be used in this context."

Explanation

The correct answer is Batch ONE returns 1 and Batch TWO returns "A RETURN statement with a return value cannot be used in this context." This is because in Batch ONE, the first select statement returns 1, but the second select statement is followed by a return statement, which is not valid in this context. In Batch TWO, the select statement returns 4, but the return statement is also followed by a select statement, which is not valid in this context. Therefore, the result of Batch TWO is the error message "A RETURN statement with a return value cannot be used in this context."

Submit

Tsql Database - Quiz -5