MySQL Interview Questions

• Data definition language (DDL) commands are the commands which are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.

• Data manipulation language (DML) commands are commands which are used for manipulation or modification of data. INSERT, UPDATE and DELETE are some common DML commands.

BLOB or Binary Large Object can be used to store binary data in MySQL. Sometimes binary data like images need to be stored in SQL databases. For example, you might want to store user photos along with other user details in the database table. Binary data of the user photo can be saved as a BLOB. By using BLOB, we will not require separate storage for images. BLOB helps in removing complexity and providing portability in such cases.

DEFAULT constraint provides a default value to a column. In case a row is inserted into the table and no value is provided to the column, the default value is taken.

Consider a table Customer created using the statement below.

CREATE TABLE `Customer`(
	`customerid` CHAR(5) NOT NULL,  
	`name` VARCHAR(25) NOT NULL, 
	`country` VARCHAR(25) NOT NULL, 
	PRIMARY KEY(`customerid`)); 

If we run the INSERT command as below, we will get an error as the country name is not provided.

INSERT INTO `Customer` (`customerid`, `name`) VALUES ('12345','William Jones'); 

To fix this problem you can add a DEFAULT constraint using the command below. Default value USA will be taken on running the INSERT command above.

ALTER TABLE Customer ALTER country SET DEFAULT 'USA';

Views are virtual tables that are stored in SQL queries with a certain given name. VIEW replaces a SELECT query with a given named view. The SELECT query could be a complex SELECT query across different database tables or a simple SELECT query. Once invoked, a view gives results using the query stored in the view.

A view can be created using the below syntax.

CREATE VIEW `name_of_view` 
AS SELECT select_statement;

A query can be made on a view similar to the database table below.

SELECT * FROM  name_of_view;

Aggregate functions are functions that are used to get a single summary value like the minimum, maximum, or average of a group of values.

COUNT, SUM, AVG, MIN, MAX are examples of MySQL aggregate functions.

COUNT function is used to count the number of entries returned by the SELECT query. Similarly, SUM, AVG, MIN, and MAX can be used to calculate the sum, average, minimum, and maximum of a set of values returned by the database query.

• DELETE is a Data Manipulation Language(DML) command. It can be used for deleting some specified rows from a table. DELETE command can be used with the WHERE clause.

• TRUNCATE is a Data Definition Language(DDL) command. It deletes all the records of a particular table. The TRUNCATE command is faster in comparison to DELETE. While the DELETE command can be rolled back, TRUNCATE can not be rolled back in MySQL.

Both CHAR and VARCHAR data types store characters up to a specified length.

1. CHAR stores characters of fixed length while VARCHAR can store characters of variable length.

2. Storage and retrieval of data are different in CHAR and VARCHAR.

3. CHAR internally takes fixed space, and if the stored character length is small, it is padded by trailing space characters. VARCHAR has 1 or 2-byte prefixes along with stored characters.

4. CHAR has slightly better performance.

5. CHAR has memory allocation equivalent to the maximum size specified while VARCHAR has variable length memory allocation.

MySQL has different INT data types with different storage requirements including:

• TINYINT (1 byte), *SMALLINT (2 bytes),

• MEDIUMINT (3 bytes),

• INT (4 bytes) and

• BIGINT (8 bytes).

All the INT types can be used as signed or unsigned. You can write UNSIGNED after data type to tell if it is unsigned.

For example below command will create a Student table which can mark as an unsigned SMALLINT value.

CREATE TABLE `Student`(
`name` VARCHAR(25) NOT NULL, 
`marks` SMALLINT UNSIGNED);

A Foreign key constraint ensures cross-referencing of values between two tables. A column or group of columns in a child table references a column or group of columns in another table. The foreign key constraint is defined in the child table telling about the table and column(s) which are being referred to.

Foreign key constraint ensures referential integrity. Foreign key columns need to be indexed in MySQL, so an index is automatically created on a foreign key column if the index is not created on the column while creating a table with a foreign key constraint.

A foreign key that is stored in a table itself is called to be a self-referencing foreign key.

For example, consider an Employee database table. It has employee_id as the primary key as well as a manager_id which is the employee_id of his manager. If we create a foreign key constraint, as a manager is also an employee, manager_id will refer to empolyee_id in the same table.

The Employee table with self-referencing foreign key manager_id can be created using the below statement.

CREATE TABLE `Employee`( 
`name` VARCHAR(25) NOT NULL, 
`employee_id` CHAR(9) NOT NULL, 
`manager_id` CHAR(9) NOT NULL, 
`salary` decimal(10,2) NULL,  
PRIMARY KEY(`employee_id`),
FOREIGN KEY (manager_id) REFERENCES employee(employee_id) ON DELETE CASCADE
);