Database Technet Quiz - 6

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Guna24x7
G
Guna24x7
Community Contributor
Quizzes Created: 4 | Total Attempts: 3,224
| Attempts: 59
Quiz Flashcard
SettingsSettings
Please wait...
  • 1/10 Questions

    What will be the out of the following script? IF object_id('table_a') IS NOT NULL   DROP TABLE table_a   CREATE TABLE table_a   (      a INT IDENTITY(50,1),      b INT   )   SET identity_insert table_a ON   --stmt 1 INSERT INTO table_a             (a,              b) VALUES     (100,             1)   SET identity_insert table_a OFF   --stmt 2 INSERT INTO table_a VALUES      (2)   --stmt 3 SET identity_insert table_a ON   INSERT INTO table_a             (a,              b) VALUES     (4,             3) SET identity_insert table_a OFF   --stmt 4 INSERT INTO table_a VALUES      (4)     SELECT * FROM   table_a   where b=4

    • 52,4
    • 5,4
    • 102,4
    • 51,4
    • 101,4
Please wait...
About This Quiz

The 'Database TechNet Quiz - 6' assesses knowledge on SQL Server database maintenance, including script outputs, stored procedures, and dependency views. It tests practical skills crucial for database administration and maintenance.

Database Technet Quiz - 6 - Quiz

Quiz Preview

  • 2. 

    When we install and configure EBS Database Maintenance utility will create ‘BonusingInfrastructure’ Database to store purge history?

    • True

    • False

    Correct Answer
    A. True
    Explanation
    When we install and configure EBS Database Maintenance utility, it will create a 'BonusingInfrastructure' Database to store purge history. This means that when the utility is set up, it automatically creates this specific database for the purpose of storing purge history. Therefore, the statement "True" is the correct answer.

    Rate this question:

  • 3. 

    Which System function used to get SQL Server Edition(i.e Standard/Enterprise)?

    • SERVERPROPERTY

    • OBJECTPROPERTY

    • DATABASEPROPERTY

    • DATABASEPROPERTYEX

    Correct Answer
    A. SERVERPROPERTY
    Explanation
    The SERVERPROPERTY function is used to retrieve various properties of the SQL Server instance, including the edition (Standard/Enterprise). It can be used to programmatically determine the edition of the SQL Server being used, which can be useful for performing different actions or implementing specific features based on the edition.

    Rate this question:

  • 4. 

    What are the results for cp.pkey when you run following statement: CREATE TABLE dbo.Sales(name nvarchar(50), pkey int);   insert dbo.Sales values('AAA',0);  insert dbo.Sales values('BBB',1);    SELECT up.name, cp.name,up.pkey,cp.pkey  FROM dbo.Sales AS up, Sales AS cp   WHERE up.pkey = 1 or cp.pkey = 0  ORDER BY 4;   drop table dbo.Sales;

    • 0,0,1,1

    • 0,1,1,1

    • 0,1,1

    • 0,0,1

    • 0,1

    Correct Answer
    A. 0,0,1
    Explanation
    The query is selecting the "name" and "pkey" columns from two instances of the "dbo.Sales" table, aliased as "up" and "cp". The WHERE clause specifies that the "up.pkey" should be equal to 1 or the "cp.pkey" should be equal to 0. The ORDER BY clause is ordering the results based on the fourth column, which is "cp.pkey". Since the only row where "cp.pkey" is 0 is the second row, it will be the first row in the result set. Therefore, the correct answer is 0,0,1.

    Rate this question:

  • 5. 

    What will be the result set?  Declare @customer Table ( ID int                     , Name varchar(20)                     );   Declare @Order Table ( OrderID int identity(1,1)                     , customerID int                     );   Insert into @customer  Values (1,'JOHNNY BRAVO'), (2,'MICKI'), (3,'POWERPUFF GIRLS')   INSERT INTO @Order   VALUES (1), (3)   SELECT     ID, NAME  FROM @customer C  WHERE NOT EXISTS ( SELECT 1                     FROM @Order WHERE ID = c.ID)

    • SQL Server will show an error.

    • One row will be in the result set.

    • No rows will be in the result set.

    Correct Answer
    A. No rows will be in the result set.
    Explanation
    The query is selecting the ID and NAME columns from the @customer table where there is no record in the @Order table with the same ID. Since there is a record in the @Order table with ID = 1, the WHERE NOT EXISTS condition will evaluate to false for the first row in the @customer table. Therefore, no rows will be in the result set.

    Rate this question:

  • 6. 

    Which option would we use to encrypt the definition of the view and prevent users of the database from seeing it?

    • SCHEMABINDING

    • Transparent Data Encryption (TDE)

    • ENCRYPTION

    • None of the above

    Correct Answer
    A. ENCRYPTION
    Explanation
    ENCRYPTION is the correct option to use in order to encrypt the definition of the view and prevent users of the database from seeing it. Encryption is a security measure that transforms data into a coded form that can only be accessed or deciphered with the use of a decryption key. By encrypting the definition of the view, it ensures that unauthorized users cannot view or understand the underlying structure or content of the view. This helps to protect sensitive information and maintain data privacy and security.

    Rate this question:

  • 7. 

    How many rows a returned from this batch? CREATE PROC #TEMP AS DECLARE @A INT = 0 DECLARE @TABLE TABLE (ID INT IDENTITY(1,1), VAL INT)   WHILE 1 = 1 BEGIN          INSERT INTO @TABLE     SELECT 1       IF @a = 10     BEGIN                  INSERT INTO @TABLE         SELECT @a                  RETURN 1;                          INSERT INTO @TABLE         SELECT 2     END          SET @a += 1; END   SELECT * FROM @TABLE WHERE VAL > 1;   GO   EXEC #TEMP   DROP PROC #TEMP

    • Two rows in the result set.

    • Only one row in the result set.

    • No row in the result set.

    Correct Answer
    A. No row in the result set.
    Explanation
    The code creates a temporary table called @TABLE and inserts rows into it using a while loop. The loop continues indefinitely until a condition is met. Inside the loop, the code checks if the value of @a is equal to 10. If it is, it inserts a row with the value of @a into the @TABLE and then returns 1, which ends the execution of the stored procedure. However, since @a is initially set to 0 and is incremented by 1 inside the loop, it will never reach the value of 10. Therefore, the loop will continue indefinitely and no rows will be inserted into the @TABLE. As a result, when the SELECT statement is executed at the end of the code, there will be no rows with a value greater than 1 in the @TABLE, resulting in no rows in the result set.

    Rate this question:

  • 8. 

    Number of execution threads in SQL Server varies based on number of CPU and Server Editions(32-bit/64-bit)?

    • True

    • False

    Correct Answer
    A. True
    Explanation
    The number of execution threads in SQL Server can vary based on the number of CPUs and the server edition (32-bit or 64-bit). SQL Server can utilize multiple execution threads to process queries and perform tasks concurrently, which can improve performance and scalability. The number of threads is typically determined by the number of available CPU cores and the server edition's limitations. Therefore, the statement is true.

    Rate this question:

  • 9. 

    Which batches execute successfully? (select 4) --First batch create table dbo.existing (c int); go --Second batch create procedure dbo.test_existing as  if (1<>1)   begin    select c from dbo.non_existing;   end  else   begin    select c from dbo.existing;   end go --Third batch execute dbo.test_existing; go --4th batch alter procedure dbo.test_existing as  if (1=1)   begin    select c from dbo.non_existing;   end  else   begin    select c from dbo.existing;   end go --5th batch execute dbo.test_existing; go

    • 1st

    • 2nd

    • 3rd

    • 4th

    • 5th

    Correct Answer(s)
    A. 1st
    A. 2nd
    A. 3rd
    A. 4th
    Explanation
    The first batch executes successfully because it creates a table called "existing". The second batch does not execute successfully because it tries to select from a table called "non_existing" which does not exist. The third batch executes successfully because it calls the "test_existing" procedure, which selects from the "existing" table. The fourth batch executes successfully because it alters the "test_existing" procedure to select from the "existing" table. The fifth batch executes successfully because it calls the altered "test_existing" procedure.

    Rate this question:

  • 10. 

    From SQL Server 2008 and above, Which Database Manitenance View(DMV) is used to find dependent Stored Procedures for given table?

    • Sys.tables

    • Sys.sql_modules

    • Sys.sql_expression_dependencies

    • Sys.dm_sql_referencing_entities

    Correct Answer
    A. Sys.sql_expression_dependencies
    Explanation
    The sys.sql_expression_dependencies DMV is used to find dependent Stored Procedures for a given table in SQL Server 2008 and above. This DMV provides information about the objects that depend on the specified table, including stored procedures, views, functions, and triggers. It helps in understanding the impact of any changes to the table structure on the dependent objects.

    Rate this question:

Quiz Review Timeline (Updated): Jun 6, 2023 +

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

  • Current Version
  • Jun 06, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Aug 22, 2013
    Quiz Created by
    Guna24x7
Back to Top Back to top
Advertisement