Friday, July 29, 2011

PL/SQL quiz

The employee table contains these columns:
LAST_NAME VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
SALARY NUMBER(8, 2)
You need to display the names of employees on more than an average salary of all employees.
Evaluate the SQL statement.
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE SALARY < AVG(SALARY);
Which change should you make to achieve the desired results?

A. Change the function in the WHERE clause.
B. Move the function to the SELECT clause and add a GROUP clause.
C. Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause.
D. Use a sub query in the where clause to compare the average salary value.

Answer: [D]

DBCC OPENTRAN

You manage a database named LTDB. Today, you received a warning that the drive on which the LTDB log file is located is near capacity. Although the transaction log is backed up every five minutes, you observed that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in LTDB. What should you do?

A. Connect to the LTDB database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
B. Connect to the master database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
C. In SQL Server Management Studio, open the Activity Monitor. Select the Process Info page and apply the following filter settings.
Database = LTDB, Open Transactions = Yes, View the Process ID and Last Batch columns.
D. Open a query window. Connect to the master database. Execute the following statement
SELECT TOP 1 spid, last_batch
FROM sys.sysprocesses WHERE dbid = db_id('LTDB') AND open_tran > 0
ORDER BY last_batch

Answer:[A]
Highlight to find out the answer