Database Technet Quiz - 6 | Attempts: 59

1.

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

A.

True
B.

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:

2.

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

A.

52,4
B.

5,4
C.

102,4
D.

51,4
E.

101,4

Correct Answer
C. 102,4

Explanation
The script first checks if a table named "table_a" exists, and if it does, it drops the table. Then, it creates a new table called "table_a" with two columns: "a" with an identity starting at 50 and incrementing by 1, and "b".

Next, it sets identity_insert on for "table_a" and inserts a row with values 100 for "a" and 1 for "b". Then, it sets identity_insert off and inserts a row with a value of 2 for "b".

After that, it sets identity_insert on again and inserts a row with values 4 for "a" and 3 for "b". Finally, it sets identity_insert off and inserts a row with a value of 4 for "b".

The script then selects all rows from "table_a" where "b" is equal to 4.

The correct answer, 102,4, represents the second row inserted into the table, with "a" being 102 and "b" being 4.

Rate this question:

3.

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

A.

Sys.tables
B.

Sys.sql_modules
C.

Sys.sql_expression_dependencies
D.

Sys.dm_sql_referencing_entities

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

4.

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

A.

Two rows in the result set.
B.

Only one row in the result set.
C.

No row in the result set.

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

5.

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

A.

True
B.

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:

6.

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

A.

1st
B.

2nd
C.

3rd
D.

4th
E.

5th

Correct Answer(s)
A. 1st
B. 2nd
C. 3rd
D. 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:

7.

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;

A.

0,0,1,1
B.

0,1,1,1
C.

0,1,1
D.

0,0,1
E.

0,1

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

8.

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

A.

SERVERPROPERTY
B.

OBJECTPROPERTY
C.

DATABASEPROPERTY
D.

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:

9.

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)

A.

SQL Server will show an error.
B.

One row will be in the result set.
C.

No rows will be in the result set.

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

10.

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

A.

SCHEMABINDING
B.

Transparent Data Encryption (TDE)
C.

ENCRYPTION
D.

None of the above

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