Datatypes
Datatypes2
CRUD
Chisholm Trail
Mixed Bag
100
Which of these datatypes will store a whole (not fractional) value ranging from 1 to 100,000 using the least amount of storage space?
  1. tinyint
  2. smallint
  3. int
  4. bigint
  5. float(24)
  6. numeric(19,19)
  7. varchar(7)
What is C. int
100
Which of these datatypes is best suited to store a date and time when we also need to know what timezone the event occurred in?
  1. date
  2. datetime
  3. datetime2(7)
  4. datetimeoffset(2)
  5. varchar(30)
  6. Two columns – datetime and an int to store the GMT Offset
What is D. datetimeoffset(2)
100
Which of these statements will return rows where MiddleName is not a NULL value?
  1. WHERE MiddleName NOT IN (Null)
  2. WHERE MiddleName != NULL
  3. WHERE MiddleName ^Null
  4. WHERE MiddleName IS NOT NULL
  5. WHERE MiddleName <> NULL
    What is D. WHERE MiddleName IS NOT NULL
    100
    You are creating a HikingTrail table. One of the columns is TrailName varchar(50). You are concerned that some trails have local nicknames that overlap official trail names and you do not want more than one trail to be referenced by the same TrailName. Which of the following is the best solution to ensure this does not happen?
    1. Change the TrailName datatype to uniqueidentifier
    2. Check constraint
    3. Foreign Key
    4. Unique constraint
    5. Computed Column
    6. Trigger
    What is D. Unique constraint
    100
    Which of these databases contain login information?
    1. Master
    2. Model
    3. MSDB
    4. Resource
    5. AdventureWorks
    What is A. Master
    200
    Which of these datatypes is best suited to store a large note field (perhaps the text from a book)?
    1. char(8000)
    2. char(max)
    3. note
    4. text
    5. binary(max)
    6. varbinary(max)
    7. varchar(8000)
    8. varchar(max)
    What is H. varchar(max)
    200
    Which of these datatypes is best suited to store a date and time to 4 decimal places?
    1. date
    2. time
    3. datetime
    4. datetime2(0)
    5. datetime2(2)
    6. datetime2(4)
    7. datetime2(7)
    8. varchar(24)
    What is F. datetime2(4)
    200
    Which of these statements would set the value of MiddleName to NULL?
    1. UPDATE Patient SET MiddleName NULL
    2. UPDATE Patient SET MiddleName = ISNULL()
    3. ALTER TABLE Patient ALTER COLUMN MiddleName NULL
    4. UPDATE Patient SET MiddleName = 'NULL'
    5. UPDATE Patient SET MiddleName IS NULL
    6. UPDATE Patient SET MiddleName = NULL
    What is F. UPDATE Patient SET MiddleName = NULL
    200
    You are creating a HikingTrail table. One of the columns is GoverningEntityID. The valid values for this column are stored in the GoverningEntity table. You are concerned that someone may delete a record from the GoverningEntity table which would make it impossible to know what entity is assigned a trail. Which of the following is the best choice to ensure that the GoverningEntity for a trail can always be found?
    1. Change the GoverningEntity column in HikingTrail to a varchar(100) and store the name instead of the ID
    2. Check constraint
    3. Foreign Key
    4. Unique constraint
    5. Computed Column
    6. Trigger
    What is C. Foreign Key
    200
    Which of these statements are you certain will remove all data from the Patient table?
    1. ALTER TABLE Patient WITH CLEAR DATA
    2. DELETE FROM PATIENT
    3. ALTER TABLE Patient WITH TRUNCATE
    4. TRUNCATE TABLE Patient
    5. UPDATE Patient SET FirstName = NULL, LastName = NULL
    What is B. DELETE FROM PATIENT
    300
    Which of these datatypes is best suited to store birthdates when we know that the system will only ever store birthdates for people who are currently alive and will be born in the next 50 years?
    1. date
    2. smalldatetime
    3. datetime
    4. datetime2(0)
    5. datetimeoffset(2)
    6. varchar(10)
    What is A. date
    300
    Which of these datatypes is best suited to store unicode data?
    1. char(8000)
    2. nchar(max)
    3. nvarchar(2000)
    4. unitex
    5. uvarchar(2000)
    6. note
    7. text
    8. ntext
    What is C. nvarchar(2000)
    300
    Which of these where statements would find a value in a field named “Chad” that contained the phrase “hillarious”?
    1. WHERE Chad = 'hillarious'
    2. WHERE Chad CONTAINS 'hillarious'
    3. WHERE Chad is hillarious
    4. WHERE Chad CONTAINS '%hillarious%'
    5. WHERE Chad LIKE '%hillarious%'
    6. WHERE HAS (Chad, 'hillarious')
    What is E. WHERE Chad LIKE '%hillarious%'
    300
    You are creating a HikingTrail table. One of the columns is GoverningEntityID. The valid values for this column are stored in the GoverningEntity table. You are concerned that someone may delete a record from the GoverningEntity table which would make it impossible to know what entity is assigned a trail. Which of the following is the best choice to ensure that if a GoverningEntity for a trail is deleted, the trails associated with it are deleted as well?
    1. Change the GoverningEntity column in HikingTrail to a varchar(100) and store the name instead of the ID
    2. Check constraint
    3. Foreign Key
    4. Unique constraint
    5. Computed Column
    6. Trigger
    What is C. Foreign Key
    300
    Widgetworks Inc. needs to store address information in the most efficient and flexible format. They have customers in both North and South America. They will frequently need to find customers within a specific distance of a regional shipping hub. The table structure should prevent bad data as much as possible. Which of the tables in figure 3 accomplishes that goal?
    What is "C"
    400
    Which of these datatypes is best suited to store PDF documents?
    1. char(8000)
    2. char(max)
    3. note
    4. text
    5. binary(max)
    6. varbinary(max)
    7. varchar(8000)
    8. varchar(max)
    What is F. varbinary(max)
    400
    Which of these datatypes is best suited to store the width of microscopic structures within an electronic circuit, some of which are as small as .0000000000000001?
    1. tinyint
    2. smallint
    3. int
    4. bigint
    5. float(24)
    6. numeric(19,0)
    7. varchar(20)
    What is E. float(24)
    400
    Given the table schema in figure 1, which of the following inserts will certainly succeed? (pick 2)
    1. INSERT INTO HikingTrail VALUES ('Timpanogos', NULL, NULL, NULL)
    2. INSERT INTO HikingTrail VALUES ('Timpanogos', 12.14, NULL, 'West')
    3. INSERT INTO HikingTrail VALUES ('Timpanogos', 12.14, 1, 'West')
    4. INSERT INTO HikingTrail VALUES (1, 'Timpanogos', NULL, NULL, NULL)
    5. INSERT INTO HikingTrail VALUES (1, 'Timpanogos', 12.14, NULL, 'West')
    6. INSERT INTO HikingTrail VALUES (1, 'Timpanogos', 12.14, 1, 'West')
    What is
    A. INSERT INTO HikingTrail VALUES ('Timpanogos', NULL, NULL, NULL)
    B. INSERT INTO HikingTrail VALUES ('Timpanogos', 12.14, NULL, 'West')
    400
    You are creating a HikingTrail table. One of the columns is TrailLength numeric(9,2) NOT NULL. Which of the following is the best choice to restrict the length of the trail to positive numbers less than 1000 miles?
    1. Change the datatype to a smallint
    2. Check constraint
    3. Foreign Key
    4. Unique constraint
    5. Computed Column
    6. Trigger
    What is B. Check constraint
    400
    Which of the following will block changes to tables that your view depends on?
    1. WITH ENCRYPTION
    2. WITH SCHEMABINDING
    3. WITH VIEW_METADATA
    4. WITH CHECK OPTION
    5. An Indexed View
    What is B. WITH SCHEMABINDING
    500
    Which of these datatypes would be a reasonable candidate for a column in a table?
    1. timestamp
    2. hierarchyid
    3. sql_variant
    4. text
    5. image
    6. ntext
    7. vardecimal(5)
    What is B. hierarchyid
    500
    You need to set up an archive server to aggregate sales information from remote client databases. The client list is fluid and hundreds of new clients are added and removed from the list every day. The client installation creates a job that sends new rows from the Sales table to the archive server. Which of the following is the best choice for the primary key in the Sales table on the remote systems?
    1. int IDENTITY
    2. bigint IDENTITY
    3. numeric(38,0) IDENTITY
    4. timestamp
    5. uniqueidentifier
    What is E. uniqueidentifier
    500
    Which of these where statements would find a notes value that contained an opening bracket - [? (pick two)
    1. WHERE notes = '['
    2. WHERE notes = '%[%'
    3. WHERE notes = '[[]'
    4. WHERE notes LIKE '%[%'
    5. WHERE notes LIKE '%[[]%'
    6. WHERE notes LIKE '%|[%' ESCAPE '|'
    7. WHERE notes LIKE '%[%' ESCAPE '['
    What is
    E. WHERE notes LIKE '%[[]%'
    F. WHERE notes LIKE '%|[%' ESCAPE '|'
    500
    You are creating a HikingTrail table. One of the columns is Difficulty varchar(10). The difficulty of the trail is dependent on the TrailLength, ElevationDelta, and TrailSurface columns in the HikingTrail table. Which of the following is the most efficient choice to ensure the Difficulty column contains correct data?
    1. Change the datatype to geography
    2. Check constraint
    3. Foreign Key
    4. Unique constraint
    5. Computed Column
    6. Trigger
    What is E. Computed Column
    500
    You are creating a Patient table. Several of the columns hold protected health information. Several employees have direct access to the database, but should not be able to see the PHI. Which of the following is the best solution?
    1. Create two tables, one with the PHI data and one without
    2. Use a check constraint on the PHI columns
    3. Assign an employee with PHI access to export and provide only non-PHI information to those who do not have PHI access
    4. Use a query that implements EXCEPT to exclude the PHI columns
    5. Create a view on the Patient table that does not expose the PHI columns
    What is E. Create a view on the Patient table that does not expose the PHI columns
    M
    e
    n
    u