MySQL 101 Interview Questions

With my new need for SQL usage comes new questions. What are key differences to remember and complex concepts in MySQL? Here are ten of them for you to ask yourself and answer in the comments below.

1. When would one decide to use CHAR over VARCHAR for a faster performance with more limited data entry?
2. How would one make a clustered index in a table?
3. How do normalization and denormalization differ?
4. What are triggers most commonly used for?
5. Using sample database sakila, write a subquery to display information for an actor and all of the films that actor had been in.
6. Explain constraints.
7. List at least three different SQL constraints.
8. How can you select unique records from a table?
9. What is a stored procedure and how is it used?
10. Which function can be used to replace specific parts of a string?

Here are my answers!

1. Using fixed data in a column/attribute such as a Social Security number. After all, there are no SSN(s) holding more or less than 9 digits.
2. A clustered index defines data order and is normally made by making a primary key as there can only be one of both. This new clustered index of unique numbered rows makes searching for data by rows much easier.
3. Both different processes, normalization creates duplicates from existing overlapping data groups while denormalization removes those duplicates, making these data groups irregular in size, but not duplicated.
4. Triggers are most commonly used to run when “triggered” by particular events such as when a statement inserts, updates, or deletes rows in the associated table.
5. select * from film_actor where actor_id = (select actor_id from actor where last_name = ‘wahlberg’);
6. Constraints are used to specify the limit on the data type of columns or the table. UNIQUE for example is a constraint that forbids duplicate data in any column.
7. SQL constraints are: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT
8. You can select unique records from a table by using the DISTINCT clause.
9. A stored procedure is a written routine in a database to be called upon and perform a list of commands. Handy for running multiple commands at once.
10. The replace function. Syntax: REPLACE (string_expression, search_string, replacement_string)

These questions had been inspired from this article on Edureka.