Study guide questions Flashcards
MySQL indexes are for what purpose (pick 2):
- There are some internally created schemas.
- Optimize queries and data manipulation operations
- Store data in a sorted order
- Optimize queries and data manipulation operations
- Store data in a sorted order
Why MySQL will use index (pick 3):
- If the column specified in the WHERE clause is indexed, MySQL can use the index to quickly locate the rows that match the condition.
- If the index does not significantly reduce the number of rows scanned, MySQL will use it an index.
- Indexes can be used to efficiently sort or group results, as specified in ORDER BY and GROUP BY clauses.
- Indexes on the columns used in join conditions can speed up the process of matching rows from different tables.
- If the column specified in the WHERE clause is indexed, MySQL can use the index to quickly locate the rows that match the condition.
- Indexes can be used to efficiently sort or group results, as specified in ORDER BY and GROUP BY clauses.
- Indexes on the columns used in join conditions can speed up the process of matching rows from different tables.
Why MySQL will NOT use index (pick 2):
- For small tables, the overhead of using an index might outweigh the benefits
- If used in join statements
- If the index column is not listed first in the select statement
- If the index does not significantly reduce the number of rows scanned (i.e., low selectivity), MySQL might opt not to use it.
- For small tables, the overhead of using an index might outweigh the benefits
- If the index does not significantly reduce the number of rows scanned (i.e., low selectivity), MySQL might opt not to use it.
Why MySQL will NOT use index (pick 2):
- Using functions or calculations on the indexed column (e.g., WHERE UPPER(column) = ‘value’) can prevent MySQL from using the index.
- Hint Overrides: Index usage can be overridden by query hints. For example, using IGNORE INDEX in the query can prevent MySQL from using a specific index.
- If the index does significantly reduce the number of rows scanned (i.e., low selectivity), MySQL might opt not to use it.
- If the select statement is perfect
- Using functions or calculations on the indexed column (e.g., WHERE UPPER(column) = ‘value’) can prevent MySQL from using the index.
- Hint Overrides: Index usage can be overridden by query hints. For example, using IGNORE INDEX in the query can prevent MySQL from using a specific index.
Join that returns all records from left table and matched records from right table
- Left Join (Outer Join)
- Right Join (Outer Join)
- Join (Inner Join)
- Full Join
Left Join (Outer Join)
Which rows will this statement return:
SELECT * FROM emp LEFT JOIN orders ON emp.id=orders.emp_id;
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
All employees and their registered orders:
Which rows will this statement return:
SELECT * FROM emp LEFT JOIN orders ON emp.id=orders.emp_id WHERE orders.emp_id IS NULL
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
Employees who have not registered any orders:
Which rows will this statement return:
SELECT * FROM emp RIGHT JOIN orders ON emp.id=orders.emp_id;
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
All orders and employees who have registered them:
Which rows will this statement return:
SELECT * FROM emp RIGHT JOIN orders ON emp.id=orders.emp_id WHERE emp.id IS NULL
- All orders that do not have an employee:
- All employees and their registered orders:
- All orders and employees who have registered them:
- Employees who have not registered any orders:
All orders that do not have an employee:
What is true about the EXPLAIN statement (pick 2):
- MySQL statement keyword used to see information about joined tables order
- Returns all records from the left table and the matched records from the right table
- Returns all records from the right table and the matched records from the left table
- Includes information about how tables are joined and in which order.
- MySQL statement keyword used to see information about joined tables order
- Includes information about how tables are joined and in which order.
What is true about the EXPLAIN statement (pick 2):
- Works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
- Returns all records from the left table and the matched records from the right table
- Returns all records from the right table and the matched records from the left table
- EXPLAIN shows how MySQL will process a statement
Works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
EXPLAIN shows how MySQL will process a statement
Statement used to assign privileges to a MySQL user
GRANT ALL PRIVILEGES ON . TO ‘test’@’%’ WITH GRANT OPTION;
Statement used to start MySQL server without using privilege system
mysqld –skip-grant-tables
Privilege category for SYSTEM_USER and FIREWALL_ADMIN
- Dynamic Privileges
- User Privileges
- System Privileges
- Plugin Privileges
Dynamic Privileges
MySQL user account elements (pick 2)
- privileges
- username@host ” identifier
- password
- hostname
- username@host ” identifier
- password
Statement to assign password to MySQL user account (pick 2):
CREATE USER ‘ jeffrey ‘@’localhost’ IDENTIFIED BY ‘password_value1’;
SET PASSWORD = ‘ mypass’;
Alter ‘password’ from ‘ jeffrey ‘@’localhost’ = ‘new_password’:
CREATE USER ‘ jeffrey ‘@’localhost’ IDENTIFIED BY ‘password_value1’;
SET PASSWORD = ‘ mypass’;
Statement to assign password to MySQL user account (pick 2):
Alter ‘password’ from ‘ jeffrey ‘@’localhost’ = ‘new_password’:
SET PASSWORD FOR jeffrey’@’localhost’ = ‘password_value2’;
CREATE fred@localhost IDENTIFIED WITH mysql_native_password BY ‘HisPa55w0rd!’;
SET PASSWORD FOR jeffrey’@’localhost’ = ‘password_value2’;
CREATE fred@localhost IDENTIFIED WITH mysql_native_password BY ‘HisPa55w0rd!’;
Statement to create one or more roles in MySQL
CREATE ROLE ‘role1’, ‘role2’, ‘role3’
MySQL Enterprise Authentication characteristics (pick 3)
- Integrates with Centralized Authentication Infrastructure
- Pluggable Authentication Modules (PAM)
- FIDO interface (passwordless authentication)
- Open source Authentication
- Integrates with Centralized Authentication Infrastructure
- Pluggable Authentication Modules (PAM)
- FIDO interface (passwordless authentication)
MySQL Enterprise Authentication characteristics (pick 3)
- Linux PAM Standard interface (Unix, LDAP, Kerberos, others)
- Plugin is available to access native LDAP service for authentication.
- Plugin is available to access native Windows service.
- Open source Authentication
- Linux PAM Standard interface (Unix, LDAP, Kerberos, others)
- Plugin is available to access native LDAP service for authentication.
- Plugin is available to access native Windows service.
MySQL Enterprise Masking functions that masks SSN?
mask_ssn(‘302-99-8097’);
mask(‘302-99-8097’);
dataMask_ssn(‘302-99-8097’);
mask_ssn(‘302-99-8097’);
Data Masking ‒ String ‒ Dictionary based ‒ Specific – Social Security Number – Payment card (strict/relaxed)
MySQL Enterprise Masking functions that masks payment card?
DataMask_pam(‘3002-9911-8097’);
mask(‘3002-9911-8097’);
mask_pam(‘3002-9911-8097’);
mask_pam(‘3002-9911-8097’);
Data Masking ‒ String ‒ Dictionary based ‒ Specific – Social Security Number – Payment card (strict/relaxed)
Why using the –initialize-insecure option to initialize the data directory insecure ?
- The root password is used on command line
- Has no root password generated with this option.
- Any 8 character password will work
Has no root password generated with this option.
This is an insecure option, it is assumed that you intend to assign a password to the account in a timely fashion before putting the server into production use.
List the mandatory MySQL variables (pick 2)
- basedir
- default_storage_engine
- datadir
- log_err
- basedir
- datadir
