MySQL Programming Interview Questions & Answers

Download PDF

If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script:

mysql> DROP VIEW faqComment;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM faqComment;
ERROR 1146 (42S02): Table 'ggl.faqcomment' doesn't exist

You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:

mysql> CREATE TABLE comment (faqID INTEGER,
message VARCHAR(256));
Query OK, 0 rows affected (0.45 sec)

mysql> INSERT INTO comment VALUES (1, 'I like it');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE VIEW faqComment AS SELECT f.id, f.title,
f.description, c.message FROM faq f, comment c
WHERE f.id = c.faqID;
Query OK, 0 rows affected (0.06 sec)
<pre>mysql> SELECT * FROM faqComment;
+----+-------------+-------------------------+-----------+
| id | title | description | message |
+----+-------------+-------------------------+-----------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | I like it |
+----+-------------+-------------------------+-----------+</pre>
1 row in set (0.07 sec)

If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script:

mysql> DROP INDEX tip_subject ON tip;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
<pre>mysql> SHOW INDEX FROM TIP;
+------------+-------------+--------------+-------------+...
| Non_unique | Key_name | Seq_in_index | Column_name |...
+------------+-------------+--------------+-------------+...
| 0 | PRIMARY | 1 | id |...
+------------+-------------+--------------+-------------+...</pre>
1 row in set (0.00 sec)

If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:
<pre>mysql> SHOW INDEX FROM TIP;
+------------+-------------+--------------+-------------+...
| Non_unique | Key_name | Seq_in_index | Column_name |...
+------------+-------------+--------------+-------------+...
| 0 | PRIMARY | 1 | id |...
| 1 | tip_subject | 1 | subject |...
+------------+-------------+--------------+-------------+...</pre>
2 rows in set (0.03 sec)

It's interesting to see that there is a default index for the primary key column.

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the "CREATE INDEX" statement as shown in the following script:

mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE INDEX tip_subject ON tip(subject);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:
<pre>mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+</pre>
1 row in set (0.40 sec)

mysql> DROP TABLE tipBackup;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tipBackup;
ERROR 1146 (42S02): Table 'ggl.tipbackup' doesn't exist

Be careful, when you use the "DROP TABLE" statement. All data rows are gone too.

If you want to rename an existing table, you can use the "ALTER TABLE ... RENAME TO" statement. The tutorial script below shows you a good example:

mysql> ALTER TABLE tip RENAME TO faq;
Query OK, 0 rows affected (0.01 sec)
<pre>mysql> SELECT * FROM faq;
+----+-------------+-------------------------+--------+
| id | title | description | author |
+----+-------------+-------------------------+--------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | NULL |
+----+-------------+-------------------------+--------+</pre>
1 row in set (0.00 sec)

If you have an existing column in a table and you want to change the column name, you can use the "ALTER TABLE ... CHANGE" statement. This statement allows you to change the name of a column, and its definition. The tutorial script below gives you a good example:

mysql> ALTER TABLE tip CHANGE COLUMN subject
title VARCHAR(60);
Query OK, 1 row affected (0.51 sec)
Records: 1 Duplicates: 0 Warnings: 0
<pre>mysql> SHOW COLUMNS FROM tip;
+-------------+--------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-------------+--------------+------+-----+---------+-------
| id | int(11) | NO | PRI | |
| title | varchar(60) | YES | | NULL |
| description | varchar(256) | NO | | |
| author | varchar(40) | YES | | NULL |
+-------------+--------------+------+-----+---------+-------</pre>
4 rows in set (0.02 sec)

If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing column:

mysql> ALTER TABLE tip DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
Records: 1 Duplicates: 0 Warnings: 0
<pre>mysql> SELECT * FROM tip;
+----+-------------+-------------------------+--------+
| id | subject | description | author |
+----+-------------+-------------------------+--------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | NULL |
+----+-------------+-------------------------+--------+</pre>
1 row in set (0.00 sec)

As you can see the column "create_date" is gone.

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD COLUMN" statement. The tutorial script below shows you a good example:

mysql> ALTER TABLE tip ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
<pre>mysql> SHOW COLUMNS FROM tip;
+-------------+--------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-------------+--------------+------+-----+---------+-------
| id | int(11) | NO | PRI | |
| subject | varchar(80) | NO | | |
| description | varchar(256) | NO | | |
| create_date | date | YES | | NULL |
| author | varchar(40) | YES | | NULL |
+-------------+--------------+------+-----+---------+-------</pre>
5 rows in set (0.01 sec)

This SQL script added a new column called "author" to the "tip" table. NULL values were added to this column on all existing data rows.

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:

mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',
'Visit www.GlobalGuideLine.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)

mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
<pre>mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+</pre>
1 row in set (0.00 sec)

As you can see, this SQL script created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

If you want to know how an existing table was created, you can use the "SHOW CREATE TABLE" command to get a copy of the "CREATE TABLE" statement back on an existing table. The following tutorial script shows you a good example:

mysql> SHOW CREATE TABLE tip;
<pre>+-------+-------------------------------
| Table | Create Table
+-------+-------------------------------
| tip | CREATE TABLE `tip` (
`id` int(11) NOT NULL,
`subject` varchar(80) NOT NULL,
`description` varchar(256) NOT NULL,
`create_date` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------</pre>
1 row in set (0.38 sec)

Comparing with the original "CREATE TABLE" statement used in the previous tutorial, the output tells you that:

► INTEGER data type was replaced by "int(11)".
► Default database engine "MyISAM" was used for the table.
► Default character set "latin1" was used for the table.

If you have an existing table, but you don't remember what are the columns used in the table, you can use the "SHOW COLUMNS FROM tableName" command to get a list of all columns of the specified table. You can also use the "DESCRIBE tableName" command, which gives you the same output as "SHOW COLUMNS" command. The following tutorial script shows you a good example:

mysql> SHOW COLUMNS FROM tip;
<pre>+-------------+--------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-------------+--------------+------+-----+---------+-------
| id | int(11) | NO | PRI | |
| subject | varchar(80) | NO | | |
| description | varchar(256) | NO | | |
| create_date | date | YES | | NULL |
+-------------+--------------+------+-----+---------+-------</pre>
4 rows in set (0.04 sec)

If you want to see the table you have just created, you can use the "SHOW TABLES" command to get a list of all tables in database. The tutorial script gives you a good example:

mysql> SHOW TABLES;
<pre>+---------------+
| Tables_in_ggl |
+---------------+
| links |
| tip |
+---------------+</pre>
3 rows in set (0.00 sec)

In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:

>cd mysqlin
>mysql -u guest -ppub

mysql> use ggl;
Database changed

mysql> CREATE TABLE test (id integer);
ERROR 1142 (42000): CREATE command denied to user
'guest'@'localhost' for table 'test'

If you get this error, you need to see the DBA to obtain the CREATE privilege.

If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":

mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);

Query OK, 0 rows affected (0.08 sec)

This scripts creates a testing table called "tip" with 4 columns in the current database.

DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:

► CREATE - Creating a new database object.
► ALTER - Altering the definition of an existing data object.
► DROP - Dropping an existing data object.

There are 8 ways to get the current time:

SELECT NOW() FROM DUAL;
2006-07-01 10:02:41

SELECT CURRENT_TIME() FROM DUAL;
10:02:58

SELECT SYSDATE() FROM DUAL;
2006-07-01 10:03:21

mysql> SELECT CURRENT_TIMESTAMP() FROM DUAL;
2006-07-01 10:04:03

SELECT LOCALTIME() FROM DUAL;
2006-07-01 10:07:37

mysql> SELECT LOCALTIMESTAMP() FROM DUAL;
2006-07-01 10:08:08

mysql> SELECT UTC_TIME() FROM DUAL;
14:09:22

mysql> SELECT UTC_TIMESTAMP() FROM DUAL;
2006-07-01 14:09:49

A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column:

► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an INSERT statement.
► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an UPDATE statement.
► If a TIMESTAMP column is assigned with NULL, the current date and time will be persisted to this column.
► You can turn off the default current date and time on INSERT by defining the column with "DEFAULT 0".
► The tutorial exercise below provides you 2 equivalent ways to create a TIMESTAMP column:

CREATE TABLE links (lastDate TIMESTAMP);

CREATE TABLE links (lastDate TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);

MySQL offers a number of functions for date and time values:

► ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD().
► ADDTIME(time1, time2) - Adding two time values together.
► CURDATE() - Returning the current date. Same as CURRENT_DATE().
► CURTIME() - Returning the current time. Same as CURRENT_TIME().
► DATE(expression) - Returning the date from the expression.
► DATEDIFF(date1, date2) - Returning dates difference in days.
► DATE_ADD(date, INTERVAL expr unit) - Adding days to a date.
► DATE_SUB(date, INTERVAL expr unit) - Subtracting days from a date.
► DATE_FORMAT(date, format) - Returning a character string representing a date.
► DAY(date) - Returning an integer representing the day of the month. Same as DAYOFMONTH()
► DAYNAME(date) - Returning the name of week day.
► DAYOFMONTH(date) - Returning an integer representing the day of the month.
► DAYOFWEEK(date) - Returning an integer representing the day of the week.
► DAYOFYEAR(date) - Returning an integer representing the day of the year.

If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:

ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;
28

ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;
23

ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;
36

If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:

SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58

SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.

If you have two time values, and you want to know the time difference between them, you can use the TIMEDIFF(time1, time2) function as shown below:

SELECT TIMEDIFF(TIME('19:26:50'), TIME('09:26:50'))
FROM DUAL;
10:00:00

SELECT TIMEDIFF('1997-03-01 19:26:50.000123',
'1997-02-28 09:26:50.000000') FROM DUAL;
34:00:00.000123

If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below:

SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))
FROM DUAL;
-1

If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:

SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-28

SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
1997-02-28