Tsql Database - Quiz -5

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Guna24x7
G
Guna24x7
Community Contributor
Quizzes Created: 4 | Total Attempts: 3,155
Questions: 10 | Attempts: 79

SettingsSettingsSettings
Tsql Database  - Quiz -5 - Quiz


This is your description.


Questions and Answers
  • 1. 

    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  

    • A.

      1

    • B.

      2

    • C.

      3

    • D.

      4

    • E.

      5

    Correct Answer(s)
    A. 1
    B. 2
    C. 3
    E. 5
    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.

    Rate this question:

  • 2. 

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

    • A.

      Insertion Order

    • B.

      Primary key order

    • C.

      Clustered Index order

    • D.

      No guarantee of order

    Correct Answer
    D. No guarantee of order
    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.

    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

    • A.

      100

    • B.

      101

    • C.

      1

    • D.

      2

    Correct Answer
    C. 1
    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.

    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)

    • A.

      Batch ONE returns 1

    • B.

      Batch ONE returns 2

    • C.

      Batch ONE returns 3

    • D.

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

    • E.

      Batch TWO returns 4

    • F.

      Batch TWO returns 5

    • G.

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

    Correct Answer(s)
    A. Batch ONE returns 1
    G. 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?

    • A.

      In the begining of the result set

    • B.

      At the end of the result set

    • C.

      No particular order

    • D.

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

    Correct Answer
    A. In the begining 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.

    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)

    • A.

      SELECT ProductID,Name,Color FROM Product

    • B.

      SELECT ProductID Name Color FROM Product

    • C.

      SELECT ProductID, Name FROM Product

    • D.

      SELECT ProductID Name FROM Product

    Correct Answer(s)
    A. SELECT ProductID,Name,Color FROM Product
    C. SELECT ProductID, Name FROM Product
    D. SELECT ProductID Name FROM Product
    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.

    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

    • A.

      Value1,value2,value3,value4,value5

    • B.

      Value1,value2,value3,value5

    • C.

      Value1,value2,value4,value6

    • D.

      Value1,value2,value5

    Correct Answer
    D. 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".

    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 ?

    • A.

      1 row is returned by the SELECT statement

    • B.

      2 row is returned by the SELECT statement

    • C.

      3 row is returned by the SELECT statement

    • D.

      4 row is returned by the SELECT statement

    • E.

      5 row is returned by the SELECT statement

    Correct Answer
    B. 2 row is returned by 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.

    Rate this question:

  • 9. 

    What is EBS Database recovery Model?

    • A.

      SIMPLE

    • B.

      FULL

    • C.

      BULK_LOGGED

    Correct Answer
    B. FULL
    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.

    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

    • A.

      No rows were returned

    • B.

      100

    • C.

      NULL

    Correct Answer
    B. 100
    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".

    Rate this question:

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Oct 06, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Aug 02, 2013
    Quiz Created by
    Guna24x7

Related Topics

Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.