MySQL: 10 Basic Interview Questions

I have been a programmer and a manager for a few years, in charge of a ton of things. I have been in charge of hiring and interviewing too.

MySQL or SQL has been one of the key needs when developing in the open source web development world. Here are a few basic question that you should get right in any interview:

1. What is SQL?

SQL or Structured Query Language is a programming language designed to work with Relational Database Management Systems.

2. How do you login to MySQL from the terminal?

You can login using mysql -u username -p. Press Return and type in your password.

user@yourvps ~$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 381
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3. How do you show all databases available?

Type in show databases;. This will return a list of all your databases.

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| CakePHPAPI                    |
| classifieds                   |
| emss                          |
| fourpics_development          |
| modx2_2_5                     |
| mysql                         |
| pollable                      |
| wordpress_development         |
+-------------------------------+
9 rows in set (0.04 sec)

4 How do you set a database to use?

To start querying a database table, you need to set the database to use. Type in use databasename;

mysql> use wordpress_development;
No connection. Trying to reconnect...
Connection id:    394
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

5. How do you list all tables in a database?

Now, that you’ve set which database to use, if you wan to see all tables in a database, type in show tables;

mysql> show tables;
+---------------------------------+
| Tables_in_wordpress_development |
+---------------------------------+
| wp_commentmeta                  |
| wp_comments                     |
| wp_links                        |
| wp_options                      |
| wp_postmeta                     |
| wp_posts                        |
| wp_term_relationships           |
| wp_term_taxonomy                |
| wp_terms                        |
| wp_usermeta                     |
| wp_users                        |
+---------------------------------+
11 rows in set (0.00 sec)

6 How do you count all records in a table?

It is a very normal task to count records in a database table. To count records in MySQL, you can use the COUNT method

mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

7 What is a Primary Key?

A primary key is a column in a database table that identifies each row. There can be no duplicates in a primary key column. A primary key is often used as foreign keys when modeling tables.

8 What is a Unique key?

A Unique Key works almost the same way as a Primary Key. One key difference, is that there can only be one Primary key in each table, and Unique keys can be a combination of many columns.

9 How do you show all records from a database table?

You only need to type a simple select query.

mysql> SELECT * FROM wp_terms;
+---------+---------------+---------------+------------+
| term_id | name          | slug          | term_group |
+---------+---------------+---------------+------------+
|       1 | Uncategorized | uncategorized |          0 |
|       2 | Blogroll      | blogroll      |          0 |
+---------+---------------+---------------+------------+
2 rows in set (0.00 sec)

10 How do you get all the fields of a database table?

mysql> DESCRIBE wp_terms;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| term_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(200)        | NO   | MUL |         |                |
| slug       | varchar(200)        | NO   | UNI |         |                |
| term_group | bigint(10)          | NO   |     | 0       |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.08 sec)

What’s Next?

These interview questions are not the end all questions when interviewing applicants but these can be guides on basic interview questions on the subject, MySQL.

 BLOB/TEXT column '' can't have a default value: CREATE TABLE from mysql

There are a few quirks with MySQL especially when you’re doing simple things. However, there comes a time when things go awry.

Here’s an error I encountered recently:

BLOB/TEXT column '' can't have a default value: CREATE TABLE from mysql

Now, who would have known that you can have a default blank value in a blob or text type column?

Anyway, here’s a simple fix for it:

  • Find your MySQL config file
  • Comment out the following in it:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  • Restart MySQL

After restarting, you won’t encounter this problem anymore!

 Import PostgresSQL database dump

Importing back a database dump from PosgreSQL is quite easy and with most import scripts it only takes one line.

psql -d database_name -U username -f database.dump
  • database_name — Your Database name to import the dump to
  • username — Your username that connects to the database
  • database.dump — Your database dump

Your database might ask additional information like your user’s password:

Password for user postgres: 

Type in your password and voila! Your database will be imported.

 Update Random rows in MySQL

Here’s a simple quick trick to update random rows in MySQL.

name head stomach
Thorpe Obazee healthy full
Random Guy light-headed empty
Another Random Guy sober full

MySQL Query

update people set stomach = 'empty' order by rand() limit 2;
1 row affected, taking 2ms

Results

Take note that random rows are affected. Sometimes rows chosen by random could have the value already. This won’t affect them.

In this sample, Random Guy and Another Random Guy were chosen. Only Another Random Guy was updated.

name head stomach
Thorpe Obazee healthy full
Random Guy light-headed empty
Another Random Guy sober empty