Murach's MySQL

Question 1

What is the default date format used by MySQL?

Answer 1

YYYY-MM-DD

Question 2

What are the two ways to express a ‘not equals’ comparison operator in a where clause?

Answer 2

<>

!=

Question 3

Are character comparisons performed on a MySQL database case sensitive?

Answer 3

No

Question 4

If you compare a null value to one of the comparison operators (=, <, > etc), the result will always be a null value. To test for null values, use the ______ clause instead.

Answer 4

IS NULL

Question 5

Which two functions can be used to explicitly convert a data type?

Answer 5

CAST

CONVERT

Question 6

What is the order of precedence for the three logical operators?

Answer 6

1. NOT
2. AND
3. OR

Question 7

Are the two expressions used in the BETWEEN phrase for the range of values inclusive?

Answer 7

Yes

Question 8

What are the two operators you can use to match a specific pattern or mask?

Answer 8

LIKE

REGEXP

Question 9

The LIKE operator is an older operator that lets you search for simple string patterns. The mask can contain one or both of which wildcard symbols?

Answer 9

% (matches any string of zero or more characters)

_ (matches any single character)

Question 10

Can searches using the LIKE or REGEXP operator use a table’s indexes?

Answer 10

No

Question 11

Are masks used in the LIKE or REGEXP operators case sensitive?

Answer 11

No

Question 12

What is the clause used to test whether a column contains a null value?

Answer 12

IS NULL

Question 13

What is the clause used for specifying the sort order of a result set? And what is the default sequence (ASC or DESC)?

Answer 13

ORDER BY

ASC is the default, so it can be omitted if that’s the sort order required

Question 14

What is a nested sort?

Answer 14

To sort by more than one column, you may simply list them in the ORDER BY clause separated by commas.

Question 15

True or false: null values appear first in the sort sequence, even if using DESC.

Answer 15

True

Question 16

Can you sort by a column in the base table even if it’s not included in the SELECT clause?

Answer 16

Yes

Question 17

Can you sort by a column alias?

Answer 17

Yes

Question 18

Can you use an arithmetic or string expression (e.g. CONCAT) to sort on?

Answer 18

Yes

Question 19

You cannot use the column number that corresponds to the result set to specify a sort order. True or false?

Answer 19

False

Question 20

Is it recommended to use column numbers to specify the sort order? Why or why not?

Answer 20

No it is not recommended because it is more difficult to read since you have to look at the SELECT clause to see what columns the numbers refer to, and also, if you add or remove columns, you may also have to change the ORDER BY clause to reflect the new column positions.

Question 21

What are the two possible arguments for the LIMIT clause? Which one is optional?

Answer 21

LIMIT [offset,] row_count

Question 22

How can you retrieve all of the rows from a certain offset to the end of the result set?

Answer 22

Code -1 for the row count.

Question 23

You can also join tables based on relationships not defined in the database. These are called…

Answer 23

Ad hoc relationships

Question 24

What is a qualified column name?

Answer 24

If two columns in a join have the same name, they must be qualified with the table name so MySQL can distinguish between them.

Question 25

Is the INNER keyword commonly used?

Answer 25

It is optional and seldom used.

Question 26

After you assign a table alias, do you have to use the alias in place of the original table name throughout the query?

Answer 26

Yes

Question 27

What’s the difference between a database and a schema in MySQL?

Answer 27

They are the same.

Question 28

Can you join tables from multiple databases? If so, how do you do it?

Answer 28

Yes, by qualifying each table that is from another database with the database name.

Question 29

A join condition can include two or more conditions connected by which operators?

Answer 29

AND

OR

Question 30

What is a self-join?

Answer 30

A join that joins a table to itself. They are rare but sometimes useful for retrieving data that can’t be retrieved any other way.

Question 31

Are aliases required in a self-join?

Answer 31

You must use aliases for the tables, and qualify each column name with the alias.