About Me

My photo
I'm just normal guy who like to travel, addicted to TV and Computer, Playing Computer Games and Watching Movies and many more. Have lot of idea and quote in life
Showing posts with label Sql Server Test. Show all posts
Showing posts with label Sql Server Test. Show all posts

Sql Server Test - 2

1. Which of the following data types has the least data type precedence?

A. BIGINT

B. FLOAT

C. DECIMAL

D. MONEY

E. REAL

F. NUMERIC

2. You have a column that will only contain values from 0 to 256. What's the most economical data type to use for the column?

A. TINYINT

B. SMALLINT

C. INT

D. DECIMAL(3)

E. VARCHAR(3)

3. What will be the result of the following statement?

SELECT STR(6365, 3)

A. 6365

B. 6,365

C. 6400

D. 6,400

E. 6365.000

F. ***

4. Which of the following is NOT true about the SMALLDATETIME data type?

A. SQL Server stores SMALLDATETIME values as two 2-byte integers.

B. The first 2 bytes store the number of days after January 1, 1900

C. The second byte store the number of seconds since midnight.

D. Dates range from January 1, 1900 through June 6, 2079.

5. What's the maximum number of rows a table can have in SQL Server 2000?

A. 1,024

B. 65,536

C. 1,073,741,824

D. Limited by available storage

6. What's the minimum value can a TINYINT data type hold?

A. -255

B. -256

C. -512

D. 0

7. What will be the result of the following statement?

SELECT CAST(0 AS DATETIME)

A. 1900-01-01 00:00:00.000

B. 9999-12-31 00:00:00.000

C. 1753-01-01 00:00:00.000

D. 1800-01-01 00:00:00.000

E. 1853-01-01 00:00:00

F. The statement will generate an error. An integer value cannot be converted to a DATETIME data type.

8. What will be the result of the following statement?

SELECT CAST(-1 AS DATETIME)

A. 1900-01-01 00:00:00.000

B. 9999-12-31 00:00:00.000

C. 1753-01-01 00:00:00.000

D. 1800-01-01 00:00:00.000

E. 1853-01-01 00:00:00

F. The statement will generate an error. An integer value cannot be converted to a DATETIME data type.

9. To identify records that were updated in a table from a trigger, which of the following tables need to be investigated?

A. Inserted system table

B. Updated system table

C. Deleted system table

D. Inserted and Deleted system tables

10. Which of the following is a reserved keyword in SQL Server 2000 and therefore cannot be used as an object name or as a column name in a table unless delimited?

A. ADD

B. SUBTRACT

C. MINUS

D. MULTIPLY

E. DIVIDE

F. None of the above is a reserved keyword in SQL Server 2000.

11. Which of the following is NOT true about the PRIMARY KEY constraint?

A. Can be of more than 1 column.

B. Always created as CLUSTERED.

C. Enforces data uniqueness by creating a unique index for the primary key columns.

D. Column that participates in the PRIMARY KEY constraint cannot accept NULL values.

Answers :

1. A

Here is the order of data type precedence of the given options from highest to lowest: FLOAT, REAL, DECIMAL/NUMERIC, MONEY and BIGINT

2. B

A SMALLINT data type uses only 2 bytes of data and can store value from -32,768 to 32,767. A TINYINT data type cannot be used because the maximum value it can hold is 255. Although an INT data type can be used, SMALLINT will use only 2 bytes compared to INT's 4 bytes. VARCHAR(3) will use 3 bytes while DECIMAL(3) will use 5 bytes.

3. F

The STR function returns character data converted from numeric data. The second parameter of the STR function is for the total length of the output, including the decimal point, sign, digits and spaces. When the float expression, specified in the first parameter, exceeds the specified length, the STR function returns * for the specified length.

4. C

A SMALLDATETIME data type is stored in SQL Server as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes (not seconds) since midnight. Dates ranges from January 1, 1900 through June 6, 2079, with accuracy to the minute.

5. D

The maximum number of rows that any table in SQL Server 2000 can have is limited by the available storage in the server.

6. D

A TINYINT data type uses only 1 byte of data and only stores values from 0 to 255

7. A

A DATETIME data type is stored internally in SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date of January 1, 1900. Given this, a value of 0 corresponds to January 1, 1900.

8. F

Arithmetic overflow error converting expression to data type smalldatetime.

9. D

The inserted system table and deleted system table, also known as the magic tables, are used by triggers to place inserted, updated or deleted records in a table. For inserts, the inserted table will contain the newly inserted records. For deletes, the deleted system table will contain the deleted records. For updates, the old values are contained in the deleted system table while the new values are contained in the inserted system table. There is no updated system table.

10. A

Of these list of words, only ADD is a reserved keyword in SQL Server 2000. One of the uses of the ADD is in the ALTER TABLE when adding a new column to a table

11. B

Primary keys are not always created as CLUSTERED indexes. If a table already has an existing clustered index before a PRIMARY KEY constraint is created for the table, adding a PRIMARY KEY constraint on the table will be created as a non-clustered index.

Category : | Read More......

Sql Server - Test 1

1. What is the best data type to store the birthdays of the US Presidents, starting with George Washington's birthday of February 22, 1732?

A. DATETIME
B. SMALLDATETIME
C. INT
D. VARCHAR

2. Which of the following is NOT true about the TRUNCATE TABLE command?

A. Removes all records from the table.
B. Resets the identity column to the seed of the column.
C. Delete triggers are executed.
D. It cannot be used on tables referenced by foreign tables.

3. What will be the value of @TodaysDate in the following script?

DECLARE @TodaysDate DATETIME
SET @TodaysDate = '12/25/2005'
SET @TodaysDate = @TodaysDate + 5

A. 12/25/2010
B. 12/30/2005
C. 04/24/2006
D. An error is generated. You cannot add together a DATETIME value and an INT value.

4. What will be the value of @TodaysDate in the following script?

DECLARE @TodaysDate Varchar(10)
SET @TodaysDate = '12/25/2005'
SET @TodaysDate = @TodaysDate + 5

A. 12/25/2010
B. 12/30/2005
C. 04/24/2006
D. An error is generated. You cannot add together a DATETIME value and an INT value.

5. Which of the following statements is the correct syntax for adding a new column called Gender into a table called dbo.Employees that already contains data?

A. ALTER TABLE dbo.Employees ADD COLUMN Gender CHAR(1) NOT NULL
B. ALTER TABLE dbo.Employees ADD COLUMN GENDER CHAR(1) NULL
C. ALTER TABLE dbo.Employees ADD Gender CHAR(1) NOT NULL
D. ALTER TABLE dbo.Employees ADD Gender CHAR(1) NULL

6. What will be the output of the following statement?

SELECT CHARINDEX('is','Missississippi', -1)

A. 0
B. 2
C. 8
D. -1
E. NULL
F. Statement will generate an error. Only positive integer values are allowed.

7. What will be the output of the following statement?

SELECT CHARINDEX('Missississippi', 'is', -1)

A. 0
B. 2
C. 8
D. -1
E. NULL
F. Statement will generate an error. Only positive integer values are allowed.

8. What's the maximum length that can be specified in an NVARCHAR data type in SQL Server 2000?

A. 2000
B. 4000
C. 8000
D. No Limit

9. What will be the result of the following statement?

SELECT ROUND(123.89, -1)

A. 120.00
B. 123.00
C. 123.90
D. 124.00

10. What's the maximum number of parameters can a SQL Server 2000 stored procedure have?

A. 128
B. 256
C. 512
D. 1,024

Answers :

1. C

The earliest date that can be stored in a DATETIME data type is January 1, 1753 while the earliest date that can be stored in a SMALLDATETIME data type is January 1, 1900 so neither of these data types can be used. Both an INT data type and a VARCHAR data type can be used but between these two data types, the best option is an INT data type where the date value can be stored in the YYYYMMDD format. An INT data type will only use 4 bytes while a VARCHAR data type storing the same date value will need 8 bytes.

2. C

When the contents of a table are removed using the TRUNCATE TABLE command, delete triggers are not executed. Delete triggers are only triggered when the DELETE command is used.

3. B

Adding an INT value to a DATETIME value is like adding that number of days to the DATETIME value. This is similar to using the DATEADD function and passing DD as the first parameter (the datepart parameter).

4. D

Conversion failed when converting the varchar value '12/25/2005' to data type int.

5. D

To add a column to an existing table, the ALTER TABLE command is used with the ADD option (without the word COLUMN). Since there's data on the table already and since no default value has been specified, the column should be created with the NULL option. Otherwise an error will be encountered.

6. B

The CHARINDEX function returns the starting position of the specified expression in a character string. The third parameter of the CHARINDEX functions is the character position to start searching for the first parameter in the second parameter. If the starting location is a negative number, the search starts at the beginning of the second parameter

7. A

8. B

NVARCHAR data types store variable-length Unicode data with a maximum length of 4,000 characters. On the other hand, VARCHAR data types store variable-length non-Unicode data with a maximum of 8,000 characters.

9. A

The ROUND function returns a numeric expression rounded to the specified length or precision. The second parameter of the ROUND function is the precision to which the input numeric expression is to be rounded. When the second parameter is a negative number, the numeric expression is rounded on the left side of the decimal point, as specified in the length.

10. D

The maximum number of parameters a stored procedure in SQL Server can have is 1,024, which is also the maximum number of columns a table can have.

Category : | Read More......