mysql linkedin assessment answers
1. Management has requested that you build an employee database. You need to include each employee's current position and salary, as well as all prior positions and salaries with the company. You decide to use a one-to-many structure: an employee table with the main information such as name and address, and an employment table with position and salary history. You can use the employee ID field to connect them. What is employment. employeeID an example of?
- secondary key
- primary key
- foreign key
- alternate key
2. You are working with an UPDATE trigger on the employee table in this diagram. How can you access the new value for the address inside the trigger?
- Use DELETED.address.
- Use NEW.address.
- Use OLD.address.
- Use INSERTED.address.
3. In the diagram below, the field price is declared as type DECIMAL. What would be a more efficient declaration for this field?
- DOUBLE
- DECIMAL (10,2)
- NUMERIC
- FLOAT
4. Why would you use stored functions?
- for formulas and business rules that you want to apply to columns in an SQL query
- for formulas and business rules that should be applied on a specific trigger event like on inserts
- for reusing recurring queries
- to automatically modify the data of a table based on a query
5. How are permissions implemented in MySQL?
- access controllists
- encrypted algorithms
- user settings
- administrator schema
6. You are working with the tables shown below. You need to generate the list of price totals for each make and model of car, with subtotals for each make, and the grand total of all prices. Which SQL clause do vou use?
- UNION
- UNION ALL
- SHOW TOTALS
- WITH ROLLUP
7. You need to run a complex query with recursive subqueries, but without creating a stored procedure or a function. Which command or clause do you use?
- COLLATE
- WITH
- FULL JOIN
- UNION
8. You are managing a database with a table called customers. You created a temporary table also called customers with which you are working for the duration of your session. You need to re-create the temporary table with different specifications. Which command do you need to run first?
- DROP TEMP TABLE customers;
- CREATE TEMPORARY TABLE customers;
- DROP TEMPORARY TABLE customers;
- DROP TABLE customers;
9. You are working with the tables shown below. You need to make sure that any record added to the purchases table consists of a customerID, which already exists in the customers table, and a carD, which alreadv exists in the cars table. You decide to use a trigger to do the validation. Which one do you use?
- IF EXISIS
- AFTER INSERT
- BEFORE INSERT
- CROSS JOIN
10. What is the product of the database designing phase?
- a list of entities, their relationship, and constraints
- all tables, columns, data types, indexes and their relationships
- all tables and their names, which are needed to implement the logical model
- a list of entities, their relationship, constraints, data types, and cardinalities
11. A stored routine is a set of SQL statements stored on the server and takes form as either a procedure or a function. Which statement cannot be used inside stored routines?
- DECLARE
- SET
- USE
- SELECT
13. You need to make your MySQL system secure against attackers. What should you not do?
- Grant PROCESS or SUPER privilege to other users.
- Run MySQL server as the Unix root user.
- Run MySQL server as a normal user.
- Use the compressed protocol.
14. Which choice is not a processing algorithm for database views?
- TEMPTABLE
- MERGE
- UPDATABLE
- UNDEFINED
15. What is one reason to introduce data redundancy into a normalized database design?
- to reduce storage space
- to make the system faster
- to reduce corruption in data
- to prevent data anomalies
