Murach's MySQL Flashcards
What is the default date format used by MySQL?
YYYY-MM-DD
What are the two ways to express a ‘not equals’ comparison operator in a where clause?
<>
!=
Are character comparisons performed on a MySQL database case sensitive?
No
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.
IS NULL
Which two functions can be used to explicitly convert a data type?
CAST
CONVERT
What is the order of precedence for the three logical operators?
- NOT
- AND
- OR
Are the two expressions used in the BETWEEN phrase for the range of values inclusive?
Yes
What are the two operators you can use to match a specific pattern or mask?
LIKE
REGEXP
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?
% (matches any string of zero or more characters)
_ (matches any single character)
Can searches using the LIKE or REGEXP operator use a table’s indexes?
No
Are masks used in the LIKE or REGEXP operators case sensitive?
No
What is the clause used to test whether a column contains a null value?
IS NULL
What is the clause used for specifying the sort order of a result set? And what is the default sequence (ASC or DESC)?
ORDER BY
ASC is the default, so it can be omitted if that’s the sort order required
What is a nested sort?
To sort by more than one column, you may simply list them in the ORDER BY clause separated by commas.
True or false: null values appear first in the sort sequence, even if using DESC.
True
Can you sort by a column in the base table even if it’s not included in the SELECT clause?
Yes
Can you sort by a column alias?
Yes
Can you use an arithmetic or string expression (e.g. CONCAT) to sort on?
Yes
You cannot use the column number that corresponds to the result set to specify a sort order. True or false?
False
Is it recommended to use column numbers to specify the sort order? Why or why not?
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.
What are the two possible arguments for the LIMIT clause? Which one is optional?
LIMIT [offset,] row_count
How can you retrieve all of the rows from a certain offset to the end of the result set?
Code -1 for the row count.
You can also join tables based on relationships not defined in the database. These are called…
Ad hoc relationships
What is a qualified column name?
If two columns in a join have the same name, they must be qualified with the table name so MySQL can distinguish between them.
Is the INNER keyword commonly used?
It is optional and seldom used.
After you assign a table alias, do you have to use the alias in place of the original table name throughout the query?
Yes
What’s the difference between a database and a schema in MySQL?
They are the same.
Can you join tables from multiple databases? If so, how do you do it?
Yes, by qualifying each table that is from another database with the database name.
A join condition can include two or more conditions connected by which operators?
AND
OR
What is a self-join?
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.
Are aliases required in a self-join?
You must use aliases for the tables, and qualify each column name with the alias.
