Tsql Database - Quiz -5
-
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
-
About This Quiz
TSQL Database - Quiz -5 assesses knowledge on SQL operations, trigger creation, and table manipulations. It challenges users to understand execution orders, result sets, and the behavior of SQL commands in a TSQL environment, enhancing practical SQL skills.
(41).webp "Tsql Database - Quiz -5 - Quiz")
Quiz Preview
- 2.
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
Correct Answer
A. No guarantee of orderExplanation
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.Rate this question:
-
- 3.
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
Correct Answer
A. 1Explanation
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.Rate this question:
-
- 4.
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."
Correct Answer(s)
A. Batch ONE returns 1
A. Batch TWO returns "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."Rate this question:
-
- 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
Correct Answer
A. In the begining of the result setExplanation
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.Rate this question:
-
- 6.
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
Correct Answer(s)
A. SELECT ProductID,Name,Color FROM Product
A. SELECT ProductID, Name FROM Product
A. SELECT ProductID Name FROM ProductExplanation
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.Rate this question:
-
- 7.
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
Correct Answer
A. Value1,value2,value5Explanation
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".Rate this question:
-
- 8.
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
Correct Answer
A. 2 row is returned by the SELECT statementExplanation
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.Rate this question:
-
- 9.
What is EBS Database recovery Model?
-
SIMPLE
-
FULL
-
BULK_LOGGED
Correct Answer
A. FULLExplanation
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.Rate this question:
-
- 10.
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
Correct Answer
A. 100Explanation
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".Rate this question:
-
Quiz Review Timeline (Updated): Oct 6, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Oct 06, 2023Quiz Edited by
ProProfs Editorial Team -
Aug 02, 2013Quiz Created by
Guna24x7
Back to top