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. AHere 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.