Database

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.