Join quiz 1

Look at the above picture and choose the correct answer:
A. SELECT * FROM TableA t1 INNER JOIN TableB t2 ON t1.ID = t2.ID
B. SELECT * FROM TableA t1 LEFT JOIN TableB t2 ON t1.ID = t2.ID
C. SELECT * FROM TableA t1 RIGHT JOIN TableB t2 ON t1.ID = t2.ID
D. SELECT * FROM TableA t1, TableB t2 WHERE t1.ID = t2.ID
E. Both A and D
F. None of the above

Answers: [E]

CLR Quiz

You're developing an application which has a feature to read data from a spreadsheet, performs some calculations, and returns the data to a SQL Server computer. You decide to use CLR function and need to register the assembly with SQL Server by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use?

A. Default
B. SAFE
C. EXTERNAL_ACCESS
D. UNSAFE
E. Both B and C
F. None of the above

Answer: [C]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Login failed

You're developing a website with 5 members. A member needs access to a SQL Server database that is located on a server named SQL1. You create a login named member1 by using the following Transact-SQL statement. CREATE LOGIN member1 WITH PASSWORD = 'member$1'. The member reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow him to access to SQL1. What should you do?

A. Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
B. Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
C. Give the login access to a specific database by using the CREATE USER Transact-SQL statement. 
D. Ensure that the login name is created with square brackets ([]).
E. Both A and C
F. None of the above

Answer: [A]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

SQL Quiz

You discovered that the schema changes that were recently made to your SQL Server database have caused your applications (website, mobile, or desktop apps) to stop functioning. It is unclear who made the changes.  You need to implement a mechanism that will track schema changes in your database. What should you do?

A. Implement a stored procedure that writes data about schema changes to a log table.
B. Implement DDL AFTER triggers that write user and schema information to a log table.
C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D. Implement a DML AFTER trigger that writes data about schema changes to a log table.
E. Both A and C
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Add new column as NOT NULL to existing table

You are modifying a table named tblEmployee in a SQL Server database. You want to add a new column named JobTitle to the tblEmployee table. The table currently contains data. The HR Department has not yet created a job title for each employee. JobTitle is a required value for each employee. You want to add this new column by using the least amount of effort. What should you do?

A. Define the new column as NULL. Update the JobTitle column to the same value as 'Undefined'. Modify the JobTitle column to be NOT NULL.
B. Define the new column as NOT NULL with a default value of 'Undefined.'
C. Define the new column as NULL. Use application logic to enforce the data constraint.
D. Define the new column as NULL with a default value of 'Undefined.'
E. Both A and B
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Create View Quiz

You are creating a view to join the tblEmployee and tblDepartment tables in a SQL Server database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do?

A. Create CHECK constraints on the tables.
B. Create a DDL trigger to roll back any changes to the tables if the changes affect the
columns in the view.
C. Create the view, specifying the WITH SCHEMABINDING option.
D. Create the view, specifying the WITH CHECK option.
E. Both C and D
F. None of the above

Answer: [C]
Highlight to find out the answer.
Come here to know more about CREATE VIEW
Visit here to practice your SQL Server skills.

Trigger quiz

Your company uses a SQL Server database to manage data on e-Commerse site. This database contains a trigger named tgInsertOrder, which fires when order data is inserted into the tblOrder table. The trigger is responsible for ensuring that a customer exists in the tblCustomer table before data is inserted into the tblOrder table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort.
Which T-SQL statements can you use to achieve this goal?

A. ALTER TABLE tblOrder DISABLE TRIGGER tgInsertOrder
B. DROP TRIGGER tgInsertOrder
C. DISABLE TRIGGER tgInsertOrder ON tblOrder
D. ALTER TRIGGER tgInsertOrder ON tblOrder NOT FOR REPLICATION
E. Both A & C
F. None of the above

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Query transactions by date

You work for a supermarket that uses a SQL Server database to store line items from point of sales (POS) transactions. The POS processes 150,000 transactions every day. The application requires a clustered index on the TransactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What will you do to achieve this goal?

A. Place a nonclustered index on the date column.
B. Add a unique clustered index on the date column.
C. Map each partition to a filegroup, with each filegroup accessing a different physical drive.
D. Create a partitioning scheme that partitions the data by date.
E. Both A and D
F. None

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Data type precedence

Do you remember data type precedence?
Which of the following data types has the highest data type precedence in SQL Server 2008?

A. DATETIME2
B. XML
C. BIGINT
D. UNIQUEIDENTIFIER
E. DATETIME
F. NTEXT

Answer:[B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

SQL Server quiz

You're managing a SQL Server computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which action should you perform?

A. In Event Viewer, view the system log or the application log
B. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
C. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
E. Both A and C
F. Both B and C

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

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

XOR operator

What will the output of the following scripts?
DECLARE @A INT, @B INT
SELECT @A = 12, @B = 15
SET @A = @A^@B
SET @B = @B^@A
SET @A = @A^@B
SELECT @A, @B

A. 12,15
B. Error occurs: no operator ^
C. Error occurs: incorrect syntax
D. 15,12
E. 13,14

Answer:[D]
Highlight to find out the answer.

SET NUMERIC ROUNDABORT

What will be the output of the following scripts?

SET NUMERIC_ROUNDABORT ON
SET ARITHABORT ON
GO
DECLARE @c NUMERIC(5, 2),
   @a NUMERIC(5, 4), 
   @b NUMERIC(5, 4)
SET @a = 1.1234
SET @b = 1.1234 
SELECT @c = @a + @b
SELECT @c

A. 2.24
B. 2.25
C. Error message: Arithmetic overflow error converting numeric to data type numeric.
D. 2.2468

Answer:[C]
Highlight to find out the answer.

Defragment indexes

You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragment the indexes in an online manner. What command should you use?

A. DBCC DBREINDEX
B. DBCC INDEXDEFRAG
C. ALTER INDEX with the REORGANIZE option
D. ALTER INDEX with the LOB_COMPACTION option set to OFF

Answer:[C]
Highlight to find out the answer.

Logical join

Do you know the difference between logical join and physical join?
What are not logical join?

A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. MERGE JOIN
E. SELF JOIN
F. CROSS JOIN

Answer:[D]
Highlight to find out the answer.

Page size again

Do you remember pages in SQL Server?
Your SQL Server database log file is 5MB. How many pages are allocated to it?

A. 0
B. 640
C. 320
D. 80
E. It depends

Answer:[A]
Highlight to find out the answer.

Page size

Do you remember pages in SQL Server?
In SQL Server, the page size is _____

A. 1KB
B. 8KB
C. 16KB
D. 64KB
E. 125KB
F. 256KB

Answer:[B]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?
DECLARE @Test TABLE(TestValue CHAR(1))
INSERT INTO @Test
SELECT 'A'UNION SELECT 'B'
UNION SELECT 'C'UNION SELECT 'D'
UNION SELECT 'E'
SELECT DISTINCT CAST(5 * RAND(CHECKSUM(NEWID())) + 1 as INT) AS RandomValue
FROM @Test

A. 1
B. 2
C. 3
D. 4
E. 5
F. It depends

Answer:[F]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?
DECLARE @Test TABLE(TestValue CHAR(1))
INSERT INTO @Test
SELECT 'A'UNION SELECT 'B'
UNION SELECT 'C'UNION SELECT 'D'
UNION SELECT 'E'
SELECT DISTINCT CAST(5 * RAND() + 1 as INT) AS RandomValue
FROM @Test

A. 1
B. 2
C. 3
D. 4
E. 5
F. It depends

Answer:[A]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?

DECLARE @DBMS TABLE(DBMS VARCHAR(15))
INSERT INTO @DBMS
SELECT 'SQL Server'
UNION SELECT 'MySQL'
UNION SELECT 'DB2'
UNION SELECT 'Oracle'
UNION SELECT 'PostgreSQL'
UNION SELECT 'SQLite'
UNION SELECT 'Access'
UNION SELECT 'EnterpriseDB'
DECLARE @DBMSValue VARCHAR
SET @DBMSValue = 'SQL'
SELECT * FROM @DBMS WHERE DBMS LIKE '%' + @DBMSValue + '%'

A. 0
B. 2
C. 4
D. 6
E. 8
F. Error will be generated

Answer:[D]
Highlight to find out the answer.