What are the two primary types of joins?
INNER JOIN and OUTER JOIN.
Why are aliases often used with joins?
Aliases are used as a shorthand way of representing the same table multiple times within the same query
Why is it considered often necessary and at least a best practice to use the table.column syntax in joins?
It is best practice to use a fully qualified name (table.column) since columns from joined tables may have the same name and in those cases a syntax that does not reference the table name prior to the column is invalid.
What impact does the order of tables used have on an outer join?
For a left outer join the first table listed will have all applicable records returned with records from the second table if there is a match
For a right outer join the second table listed will have all records returned with records from the first table if there is a match
How do you create a self-join?
A self join is created by the SELECT query treating the two references to the same table as if they were single references to two different tables.
A self join assigns an alias to each of the table references
SELECT m1.subject, m2.subject AS Reply FROM messages AS m1 LEFT JOIN messages AS m2 ON m1.message_id = m2.parent_id WHERE m1.parent_id=0
What are the aggregate functions?
An aggregate function is a grouping function. What an aggregate function returns is based upon a value present in a single column over a set of rows.
Grouping functions include: AVG(), MAX(), MIN(), COUNT(), SUM()
SELECT MAX(balance), MIN(balance) FROM accounts
What impact does the DISTINCT keyword have on an aggregate function? What impact does GROUP BY have on an aggregate function?
The DISTINCT keyword causes the aggregation to only be used on distinct values
When an aggregate function is used with a GROUP BY clause, a single aggregate value will be returned for each row in the result set
SELECT AVG(balance), customer_id FROM accounts GROUP BY customer_id
What kind of index is required in order to perform FULLTEXT searches? What type of storage engine?
FULLTEXT searches require a FULLTEXT index. FULLTEXT indexs can only be created on MyISAM tables.
What impact does it have when you conclude a SELECT query with \G instead of a semicolon in the mysql client?
Using /G instead of ; to end a query tells MySQL to return the result in a vertical list instead of a table (horizontal)
SHOW TABLE STATUS/G
How do IN BOOLEAN MODE FULLTEXT searches differ from standard FULLTEXT searches?
IN BOOLEAN MODE FULLTEXT searches allow for manipulation of the relevance of search results based on assigning an operator to matched character combinations. IN BOOLEAN MODE if a keyword is not preceded by an operator, the word is optional but a match will be ranked higher if it is present; results will be returned even more than 50% of records match the search; and the results are NOT automatically sorted by relevance
SELECT * FROM tablename WHERE MATCH(columns) AGAINST('terms' IN BOOLEAN MODE)
What commands can you use to improve a table's performance?
The ANALYZE command updates the indexes on a table, thereby improving their usage in queries
How do you examine the efficiency of a query?
Use EXPLAIN or EXPLAIN EXTENDED on the query. Use on queries that may be resource intensive: join two or more tables, use groupings and aggregate functions, or have WHERE clauses.
Why doesn't the forum database support transactions?
To perform transactions with MySQL you must use the InnoDB table type (or storage engine).
How do you begin a transaction? How do you undo the effects of a transaction in progress? How do you make the effects of the current transaction permanent?
Begin a transaction using START TRANSACTION;
Use ROLLBACK; to undo the effects of a transaction in process.
Use COMMIT; to make the effects of the current transaction permanent
What kind of column type is required to store the output from the AES_ENCRYPT() function?
Since the encrypted data returned by the AES_ENCRYPT() function will be in binary format it must be stored in a column defined as a binary type (VARBINARY or BLOB)
What are the important criteria for the salt used in the encryption process?
There must be a unique salt for each piece of stored value. The salt must be exactly the same in the in the encryption and decryption which means it must be stored somewhere.