MySQL 202 Interview Questions

I will be making a list of intermediate to advanced interview questions, then listing their answers here.

1. Explain the difference between Mongo vs MySQL.
2. What’s the difference between noSql vs SQL?
3. What is a stored procedure?
4. In the context of SQL, what’s Function?
5. Function vs Stored Procedure?
6. How do you configure a database using JDBC?
7. What are transactions in SQL?
8. What is the difference between “PreparedStatement” and Statement in JDBC?
9. How do you create a prepared statement in MySQL?
10. What is normalization?

Here are my answers!

1. MySQL is a relational database language that stores data within a defined table structure. Mongo is a NoSQL type language that is non-relational and stores data in JSON-esque documents that can vary in structure. It’s simple to add new attributes to an entity in Mongo, but can be costly and difficult to redefine in MySQL.
2. NoSQL is a non-relational database while SQL is a relational database. NoSQL are document based, key-value pairs graph databases, or wide-column stores. SQL are databases that are table based.
3. A stored procedure like a function that can save and run multiple SQL statements for later use. It can also take in multiple parameters.
4. A function can run multiple SQL statements like a procedure and can take in parameters, but it returns a value directly unlike a procedure.
5. A stored procedure can run multiple statements in a block. It is a type of function that can take in parameters, but unlike functions, does not return a value. The only way to get a return value, is to use the out or inout parameters.
6. Add the SQL connection JAR to your program’s class path. Load and register the JDBC driver and connect to the database using the DriverManager class. Pass in the URL, USERNAME, and PASSWORD to access your database. Run through the process of executing a query in a java program.
   1. Establish a connection to the database and get the Connection.
   2. Create a Statement using the Connection.
   3. Execute the Statement with an SQL string.
   4. Get the ResultSet back from the executed Statement and use them to display the results.
7. A transaction is a sequence of operations performed using at least one SQL statement. Transactions can be committed to a database or rolled back (undone) from the database.
8. A Statement already has the entirety of the query written out and can be executed right away, while a PreparedStatement only has some of the query written out. A PreparedStatement is parameterized and you can pass parameters at runtime.
9. First initialize the PreparedStatement like below where conn is the Connection to your database.
   1. PreparedStatement pstmt = conn.prepareStatement(“select * from ACTOR where actor_id = ?”);
   2. After, you can set the parameters for the statement, since actor_id is of type int and it is the first, so its place is at 1.
   3. pstmt.setInt(1, 12);
   4. Above we set the first parameter (actor_id) as 12. If The parameter was some sort of string, there is a setString() function and others for more types. After, all you need to do is to execute the query.
   5. pstmt.executeQuery();
10. Normalization is a way of structuring your data/tables so you avoid repetitive data, insert anomalies, delete anomalies, and update anomalies.