MySQL Interview Questions

LIMIT clause is used with the SQL statement to retrieve a particular number of records from a table. From which record and how many records will be retrieved are defined by the LIMIT clause.

Example:

Get all records.

mysql> select * from shopping.customer;

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

4 rows in set (0.00 sec)

Get top 2 records.

mysql> select * from shopping.customer LIMIT 2;

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

2 rows in set (0.00 sec)

Get 2nd and 3rd record.

mysql> select * from shopping.customer LIMIT 1, 2;

+------------+--------------+------------+---------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address | CityID | PostalCode | CountryID |
+------------+--------------+------------+---------+--------+------------+-----------+
|          2 | Ram          | 9650423377 | A-487   |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485   |     11 |     110085 |        91 |
+------------+--------------+------------+---------+--------+------------+-----------+

2 rows in set (0.00 sec)

MySQL IFNULL() takes two expressions and if the first expression is not NULL, it returns the first expression. Otherwise, it returns the second expression.

Depending on the context in which it is used, it returns either numeric or string value.

Example

mysql> SELECT IFNULL(1,2);

+-------------+
| IFNULL(1,2) |
+-------------+
|           1 |
+-------------+

1 row in set (0.00 sec)

mysql> SELECT IFNULL(NULL,2);

+----------------+
| IFNULL(NULL,2) |
+----------------+
|              2 |
+----------------+

1 row in set (0.00 sec)

Unique data is stored in the primary key and unique key fields. The primary key field never accepts NULL value but a unique key field accepts a NULL value.

Example: Create below table

CREATE TABLE shopping.myusers(
        uid int(10) PRIMARY KEY,
        uname VARCHAR(50) UNIQUE KEY,
        password VARCHAR(50));

Now insert NULL in unique key, you won't get any error

INSERT INTO shopping.myusers(uid, uname, password) VALUES(1, NULL, '1234');

Query OK, 1 row affected (0.01 sec)

Now insert NULL in primary key, you will get error:

mysql> INSERT INTO shopping.myusers(uid, uname, password) VALUES(NULL, NULL, '1234');

ERROR 1048 (23000): Column 'uid' cannot be null

Database tables can be imported into a database from a SQL file by using the following MySQL statement.

mysql -u username -p database_name < sql_filename

Both UNIX TIMESTAMP and MySQL TIMESTAMP are used to represent the date and time value. The main difference between these values is that UNIX TIMESTAMP represents the value by using 32-bits integers and MySQL TIMESTAMP represents the value in the human-readable format.

Example: FROM_UNIXTIME

mysql> SELECT FROM_UNIXTIME (1596222320) AS MySQLTIMESTAMP;

+---------------------+
| MySQLTIMESTAMP      |
+---------------------+
| 2020-08-01 00:35:20 |
+---------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP

mysql> SELECT UNIX_TIMESTAMP ('2018-12-25 09:45:40') AS UNIXTIMESTAMP;
+---------------+
| UNIXTIMESTAMP |
+---------------+
|    1545711340 |
+---------------+
1 row in set (0.00 sec)

SET PASSWORD statement is used to change the password of a MySQL user.

Syntax

SET PASSWORD FOR ‘username'@'hostname' = PASSWORD(‘password');

mysqldump is a useful utility tool of MySQL that is used to dump one or more or all databases from the server for backup or transfer to another database server.

For a single database:

mysqldump [OPTIONS] db_name [TABLES]

For multiple databases:

mysqldump [OPTIONS] –databases DB1 [DB2 DB3...]

For all databases:

mysqldump [OPTIONS] –all-databases

Example: The following command will create a dump of the "newdb" database and export the content of the database in the file, newdb.sql.

mysqldump –databases newdb > newdb.sql

Here, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Example: Create Table

CREATE TABLE staff(
name VARCHAR(50) PRIMARY KEY,
email VARCHAR(50) NOT NULL,
salary DECIMAL(5, 2) NOT NULL);

Now Insert Data

INSERT INTO Shopping.staff (name, email, salary) VALUES ('Ram', 'ram@pb.com', 189.56);

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

An index is a data structure of a MySQL table that is used to speed up the queries.

It is used by the database search engine to find out the records faster. One or more fields of a table can be used as an index key. Index key can be assigned at the time of table declaration or can be assigned after creating the table.

To create index use below command:

CREATE TABLE users(
username VARCHAR(50) PRIMARY KEY,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
INDEX (username, email));

To show index use below command:

mysql> SHOW INDEXES FROM Shopping.users;

The syntax for adding any column in an existing table is shown below:

ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column]

The syntax for removing any column from an existing table is shown below:

ALTER TABLE table_name DROP COLUMN column_name;