Chapter 7 - Review

Output
Show all Hide all
1Toggle answer visibilityWhat are the two primary types of joins?

There are INNER Joins and OUTER Joins. An INNER join returns only those records from all tables whose foreign keys are not Null. An OUTER join returns all the records from one table with any matching values from other tables or NULL.

2Toggle answer visibilityWhy are aliases often used with joins?
An Alias is a shorthand method of assigning a name to a column for ease of reference when you may have to type the column name multiple times in a query.
3Toggle answer visibilityWhy is it considered often necessary and at least a best practice to use the table.columnsyntax in joins?
In joins it is common to have columns with the same name therefore it is best to prepend the table name to the column name to allow for distinguishing between them then using an alias to help shorten the typing redundancy.
4Toggle answer visibilityWhat impact does the order of tables used have on an outer join?
In a LEFT OUTER JOIN, each record in the table on the left side of the command will be returned, in a RIGHT OUTER JOIN all records in the right side table will be returned.
5Toggle answer visibilityHow do you create a self-join?
A SELF JOIN is created by assigning an alias for each column in the table then joining them together using their unique alias name.
6Toggle answer visibilityWhat are the aggregate functions?
The aggregate functions perform a calculation on a set of values and return a single value. MySQL provides many aggregate functions including AVG, COUNT, SUM, MIN , MAX, etc. An aggregate function ignores NULL values when it performs calculation except for the COUNT function
7Toggle answer visibilityWhat impact does the DISTINCT keyword have on an aggregate function? What impact does GROUP BY have on an aggregate function?
The DISTINCT Keyword can be used with the AVG, SUM and COUNT aggregate functions. It removes duplicate values from the results. The GROUP BY clause returns a single aggregate value for each row in the result set. GROUP BY works best when you have a large amount of data to work with.
8Toggle answer visibilityWhat kind of index is required in order to perform FULLTEXT searches? What type of storage engine?
To perform a FULLTEXT search - the table needs to be using the MyISAM storage engine and the column (or columns) needs to have a FULLTEXT index assigned to it. FULLTEXT indexes can only be applied to columns within the same table, so if you need more robust search features, consider a search engine. FULLTEXT searches use a relevance algorithm that ignores words less than four letters in length, ignores popular/common words and ignores words that are contained in more than 50% of the table's records. FULLTEXT indexes slow down insertion of data into your database because of their complexity.
9Toggle answer visibilityWhat impact does it have when you conclude a SELECT query with \G instead of a semicolon in the mysql client?
In the mysql client, ending a SELECT query with \G will format results as a vertical list rather than the standard table format. This is sometimes easier to read for large result sets.
10Toggle answer visibilityHow 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 search results are NOT sorted by relevance, all results are returned even if they are in more than 50% of records and if a keyword is not preceded by an operator, the word is optional but a match will be ranked higher if it is present.
11Toggle answer visibilityWhat commands can you use to improve a table's performance?
You can improve a table's performance by using the builtin functions with MYSQL : OPTIMIZE and ANALYZE. The MySQL OPTIMIZE TABLE command will effectively de-fragment a mysql table and reindex the records. OPTIMIZE is very useful for tables which are frequently updated and/or deleted. The ANALYZE command is a shorter version of optimize and will update the index cardinality. Running either of these commands will effectively lock the table.
12Toggle answer visibilityHow do you examine the efficiency of a query?
Use the EXPLAIN clause to describe the actual process used to perform a query. The results include how many rows need to be scanned, the indexes used and how many sub queries were involved in addition to other pertinent information.
13Toggle answer visibilityWhy doesn't the forum database support transactions?
Transactions are only supported in the INNODB storage engine. Therefore the forum database is not fully compatible with this technique.
14Toggle answer visibilityHow 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?
START TRANSACTION begins a transaction. COMMIT will apply the current transaction to the database. ROLLBACK will return the data to it's pre START TRANSACTION state. TRANSACTIONS cannot be used with the following queries : create, alter, truncate (empty), or delete tables, create or delete databases
15Toggle answer visibilityWhat kind of column type is required to store the output from AES_ENCRYPT()?
The AES_ENCRYPT() function returns binary data. In order to store this in the database, the column must be defined as one of the binary types (e.g., VARBINARY or BLOB).
16Toggle answer visibilityWhat are the important criteria for the salt used in the encryption process?
The salt must be the same for both the encryption and decryption process - so it must be stored in the database as well. A longer salt provides greater encryption. Each salted data item must use a unique salt.
Source
<?php
$review = array(
	// 1=>array(
	// 	'q'=>'',
	// 	'a'=>''
	// ),	
	1=>array(
		'q'=>'What are the two primary types of joins?',
		'a'=>'<p>There are INNER Joins and OUTER Joins. An INNER join returns only those records from all tables whose foreign keys are not Null. 
		An OUTER join returns all the records from one table with any matching values from other tables or NULL.</p>'
	),
	2=>array(
		'q'=>'Why are aliases often used with joins?',
		'a'=>'An Alias is a shorthand method of assigning a name to a column for ease of reference when you may have to type the column name 
		multiple times in a query.'
	),	
	3=>array(
		'q'=>'Why is it considered often necessary and at least a best practice to use the table.columnsyntax in joins?',
		'a'=>'In joins it is common to have columns with the same name therefore it is best to prepend the table name to the column name
		 to allow for distinguishing between them then using an alias to help shorten the typing redundancy.'
	),
	4=>array(
		'q'=>'What impact does the order of tables used have on an outer join?',
		'a'=>'In a LEFT OUTER JOIN, each record in the table on the left side of the command will be returned, in a RIGHT OUTER JOIN all
		records in the right side table will be returned.'
	),
	5=>array(
		'q'=>'How do you create a self-join?',
		'a'=>'A SELF JOIN is created by assigning an alias for each column in the table then joining them together using their unique alias name.'
	),
	6=>array(
		'q'=>'What are the aggregate functions?',
		'a'=>'The aggregate functions perform a calculation on a set of values and return a single value. MySQL provides many aggregate 
		functions including AVG, COUNT, SUM, MIN , MAX, etc. An aggregate function ignores NULL values when it performs calculation except for the COUNT function'
	),
	7=>array(
		'q'=>'What impact does the DISTINCT keyword have on an aggregate function? What impact does GROUP BY have on an aggregate function?',
		'a'=>'The DISTINCT Keyword can be used with the AVG, SUM and COUNT aggregate functions. It removes duplicate values	from the results.
		 The GROUP BY clause returns a single aggregate value for each row in the result set. GROUP BY works best when 
		you have a large amount of data to work with.'
	),
	8=>array(
		'q'=>'What kind of index is required in order to perform FULLTEXT searches? What type of storage engine?',
		'a'=>'To perform a FULLTEXT search - the table needs to be using the MyISAM storage engine and the column (or columns) needs to have a FULLTEXT index
		assigned to it. FULLTEXT indexes can only be applied to columns within the same table, so if you need more robust search features, consider a 
		search engine. FULLTEXT searches use a relevance algorithm that ignores words less than four letters in length, ignores popular/common words and ignores
		 words that are contained in more than 50% of the table\'s records. FULLTEXT indexes slow down insertion of data into your database because of their complexity.'
	),
	9=>array(
		'q'=>'What impact does it have when you conclude a SELECT query with \G instead	of a semicolon in the mysql client?',
		'a'=>'In the mysql client, ending a SELECT query with \G will format results as a vertical list rather than the standard table format. 
		This is sometimes easier to read for large result sets.'
	),
	10=>array(
		'q'=>'How do IN BOOLEAN MODE FULLTEXT searches differ from standard FULLTEXT searches?',
		'a'=>'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 search results are NOT sorted by relevance, all results are returned even if they are in more than 50% of records and
		if a keyword is not preceded by an operator, the word is optional but a match will be ranked higher if it is present.'
	),
	11=>array(
		'q'=>'What commands can you use to improve a table\'s performance?',
		'a'=>'You can improve a table\'s performance by using the builtin functions with MYSQL : OPTIMIZE and ANALYZE. The MySQL OPTIMIZE TABLE command will 
		effectively de-fragment a mysql table and reindex the records. OPTIMIZE is very useful for tables which are frequently updated and/or deleted. The ANALYZE command is a 
		shorter version of optimize and will update the index cardinality. Running either of these commands will effectively lock the table.'
	),
	12=>array(
		'q'=>'How do you examine the efficiency of a query?',
		'a'=>'Use the EXPLAIN clause to describe the actual process used to perform a query. The results include how many rows need to be scanned, 
		the indexes used and how many sub queries were involved in addition to other pertinent information.'
	),
	13=>array(
		'q'=>'Why doesn\'t the forum database support transactions?',
		'a'=>'Transactions are only supported in the INNODB storage engine. Therefore the forum database is not fully compatible with this technique.'
	),
	14=>array(
		'q'=>'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?',
		'a'=>'START TRANSACTION begins a transaction. COMMIT will apply the current transaction to the database. ROLLBACK will return the data to it\'s 
		pre START TRANSACTION state. TRANSACTIONS cannot be used with the following queries : create, alter, truncate (empty), or delete tables, create or delete 
databases'
	),
	15=>array(
		'q'=>'What kind of column type is required to store the output from AES_ENCRYPT()?',
		'a'=>'The AES_ENCRYPT() function returns binary data. In order to store this in the database, the column must be defined as one 
		of the binary types (e.g., VARBINARY or BLOB).'
	),
	16=>array(
		'q'=>'What are the important criteria for the salt used in the encryption process?',
		'a'=>'The salt must be the same for both the encryption and decryption process - so it must be stored in the database as well. A longer salt 
		provides greater encryption. Each salted data item must use a unique salt.'
	)
);
include('templates/review.php');
?>