MySQL Test 2: Traditional Test Questions

MySQL data types can be divided into __ categories.

7

Which data type is intended for storing a string of one or more characters?

Character

The terms character, string, and __ are used interchangeably to describe the character data type.

text

At a basic level, you can divide numbers into two categories: integer and __ numbers

real

Which data type is intended for storing numbers used for mathematical calculations?

Numeric

Integers are numbers that __ a decimal point.

don't have

Real numbers are numbers that __ a decimal point.

have

Date and time data types are typically referred to as date/time or __ data types

temporal

Which of these three data type categories are most widely used?

Numeric, Character, Date and time

The binary data type is useful for storing which of the following?

encryption keys, smaller image, audio, and video files

The LOB data type is useful for storing which of the following?

video, large amounts of character data, images, and sound

Which data type is used for storing geographical values such as GPS data?

Spatial

The spatial data type is also referred to as __ types because they define points that represent any location in the world.

geometry

The two most common character data types supported by MySQL are CHAR and __

VARCHAR

Use the __ type to store fixed

length strings.

If two characters (such as NE) are stored in a CHAR(10) column, what will be stored in this column?

2 characters and 8 spaces

Use the __ data type to store variable

length strings.

If two characters (such as NE) are stored in a VARCHAR(10) column, what will be stored in this column?

2 characters and 1 byte to store string length

By default, MySQL 8.0 and later uses the utf8mb4 character set for the CHAR and VARCHAR types.

True

The utf8mb4 character set can use up to __ bytes to store each character.

4

The utf8mb4 character set uses __ byte(s) to store characters used in the English language.

1

The CHAR type in the utf8mb4 character set in MySQL must reserve __ bytes for each character.

4

You can typically save storage space by using the __ type.

VARCHAR

Although you typically store numeric values using a numeric type, sometimes the character type is a better choice for some numeric values.

True

Which of the following would typically be stored in a character column rather than a numeric column?

zip code, telephone number, SCC classroom numbers like N1, N10 and social security number

Although, you can use either single quotes or double quotes to specify a string, single quotes are more typically used.

True

Double quotes are used to specify a string when the string contains a(n)

single quote '

Integer types __ include a decimal point.

do not

Integer types can store __ numbers.

negative, positive

To specify the integer type, you can use either the keyword INTEGER or INT. Of these two keywords, it is more common practice to use which one?

INT

The number of digits a value has to the right of the decimal point is called its __ and the total number of digits is called its

scale, precision

Fixed

point numbers are numbers that have a fixed number of digits to the __ of the decimal point.

When you use the DECIMAL type, MySQL uses a __ number of bytes to store the value.

varying

What would the original value of 1.2 be stored as when DECIMAL(9,1) data type is specified?

1.2

A primary key is used to uniquely identify each __ in a table.

row

The vendor_name column is a good candidate for a primary key.

False

Which of the following operations would violate the referential integrity of the tables?

deleting a row from the primary key table

An index can speed performance when __.

joining data from tables, searching for rows based on a search condition

MySQL automatically creates indexes for primary and foreign keys in each table.

True

When should you create an index?

When the column is updated infrequently, When a column is frequently used in search conditions

To apply the __ normal form, you continue to divide the tables of the data structure into __ tables until all __ has been removed

fifth, smaller, redundancy

For a table to be in first normal form, its columns must contain repeating values.

False

A table in first normal form often has repeating values in its rows. This can be resolved by applying the second normal form.

True

The second normal form only applies to tables that have composite primary keys.

True

Second normal form helps __.

save storage space, reduce the chance of storing inconsistent data, make maintenance easier

When you create or open a model, the diagram for the model is displayed by default.

True

When you are done creating your model, you can create a MySQL database creation script from the diagram.

True

When dropping a database, if the specified database doesn't exist, the DROP DATABASE statement will generate an __. To prevent this, you can add the __ EXISTS keywords to the statement.

error, IF

In its simplest form, the CREATE TABLE statement consists of the name of the new table followed by the names and data types of its columns. Each column has one or more __ coded for it.

attributes

To indicate that each row in a column must contain a unique value, you can code the __ attribute.

UNIQUE

Identifies a column whose value is automatically incremented by MySQL when a new row is added.

AUTO_INCREMENT

Defining a foreign key constraint at the table

level allows you to do what?

By default, the columns in a view are given __

the same names as the columns in the base tables

If a view contains a calculated column, you should name that column just like you would do in other SELECT statements.

True

On the view's SELECT statement, you will need to __

rename columns from different tables that have the same name

The fourth and fifth examples in Figure 12

3 shows techniques for assigning names to columns. The book states that you will typically want to use the technique shown in the __

The example in part 2 of Figure 12

3 shows a view that uses __

Use nested views sparingly because they can make dependencies confusing and code difficult to maintain.

True

If you name the columns of a view in the __

CREATE VIEW clause, you have to name all of the columns

If you name the columns of a view in the __

SELECT clause, you can name just the columns you need to rename

It is usually easier to alter a view by using the __

CREATE OR REPLACE VIEW statement

The DROP VIEW statement __

permanently deletes the view

Can be called from an application that has access to the database.

Stored procedure

Is executed in response to an INSERT, UPDATE, or DELETE statement on specified table.

Trigger

Procedural code that controls the flow of execution.

Stored program

Is executed at a scheduled time.

Event

Can be called from a SQL statement.

Stored function

Examine Figure 13

1. This script creates a stored procedure named __

The __

DELIMITER statement changes the delimiter from the default delimiter of the semicolon to two __

The code within the CREATE PROCEDURE statement is defined by a __ of code.

block

The code within the CREATE PROCEDURE statement begins with the __ keyword and ends with the __ keyword.

BEGIN, END

After the stored procedure has been __

created, this script uses the DELIMITER statement to change the delimiter back to the default delimiter of a __