SQL Server 2008 Beginner to Intermediate leval Online Test

DECLARE @sid int

DECLARE @table Table (KeyID int primary key,KeyData varchar(5))

INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')

INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')

SET @sid = SCOPE_IDENTITY()

set nocount on

declare @result varchar (5)

create table #n (n int)

Go

insert #n values (1)

insert #n values (2)

insert #n values (3)

Go

set @result = cast (@@rowcount as varchar)

/*SET NOCOUNT OFF*/

select @result = @result + cast (@@rowcount as varchar) from #n

select @result + cast (@@rowcount as varchar)

Choose the statement that best describes a composite index.
It physically sorts the rows in a table.
It contains multiple keys.
It logically sorts the rows in a table in both ascending and descending order.
It contains multiple columns in its key.
Omitting the WHERE clause from a TRUNCATE statement has which of the following effects?
The truncate statement will fail because there are no records to delete.
The truncate statement will prompt the user to enter criteria for the deletion
The truncate statement will fail because of syntax error.
The truncate statement will remove all records from the table.
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed?
before the WHERE clause
before the FROM clause
after the ORDER BY clause
after the WHERE clause
What statement could you use in the WHERE clause to select all the rows in a table where no price is defined?
WHERE price <>NULL
WHERE price != 0
WHERE price IS NULL
WHERE price IS NOT NULL
Referential integrity refers to what?
The enforced uniqueness of a row in a table
The enforced uniqueness of a column in a table
Ensuring that a Foreign Key attribute cannot be NULL
The enforced synchronization of Primary Key and Foreign Key values
Where the first name is Bobby or Bobbi. Choose all that apply.
WHERE name = ‘Bobby’ or name = ‘Bobbi’
WHERE name LIKE ‘Bobb_’
WHERE name LIKE ‘Bobb[iy]’
Above all

What does NULL mean?

CREATE PROC FixedLength

@InputStr VARCHAR(16)

AS

WHILE LEN(@InputStr) &lt; 16

BEGIN

SELECT @InputStr = @InputStr + &apos; &apos;

END

RETURN @InputStr

GO

EXEC FixedLength &apos;154620&apos;;GO

Given the following table :

DECLARE @T TABLE (A INTEGER NULL);

INSERT @T VALUES (NULL);

Which one or more of the following statements will produce a row (select all that apply)? Notice that ANSI_NULLS is OFF.

SET ANSI_NULLS OFF;

SELECT &apos;A&apos; FROM @T WHERE A = NULL;

SELECT &apos;B&apos; FROM @T WHERE A = (SELECT NULL);

SELECT &apos;C&apos; FROM @T WHERE A = LIKE (SELECT NULL);

SELECT &apos;D&apos; FROM @T WHERE A = A;

What is the output from this code in SQL Server 2005?

create table #temp (num int, numdesc varchar(50))

insert #temp select 1, &apos;one&apos;

union select 2, &apos;two&apos;

union select 3, null

union select 4, &apos;four&apos;

select isnull(left(numdesc, 5), &apos;IsUnknown&apos;) from #temp where num = 3

What will be the result of following query?

DECLARE @bit BIT

SET @bit = &apos; &apos;

IF @bit = 1

PRINT &apos;yes&apos;

ELSE

PRINT &apos;no&apos;