MySQL Interview Questions

Highlight the differences between DELETE and TRUNCATE commands.

• DELETE is a DML (Data manipulated Language) command while TRUNCATE is a DDL (Data Definition Language).
• DELETE can remove all records or a selected subset (filter using WHERE clause) while TRUNCATE remove all the records.
• DELETE is slower than TRUNCATE since it remove records from the table on row-by row basis.
• Records removed via TRUNCATE cannot be recovered while those removed through DELETE can be recovered. This is because DELETE command creates an entry in the transaction log for each deleted record.

Difference between WHERE and HAVING clauses in an SQL statement

WHERE clause is used to filter records from a query results where as HAVING clause is used to filter results that have been grouped. In their order of precedence, WHERE clause is executed before HAVNG.

Write a query that duplicate a table row

INSERT INTO table_1 SELECT * FROM table_2 WHERE field_name = 1;

MySQL is a RDBMS. Explain what is the difference between DBMS and RDBMS?

RDBMS(relational database management system)	DBMS(Database management system)
Data is stored in tabular format	Data is stored in a flat file
Data stored in the tables is related making it possible to have normalization	Data cannot be normalized since it is not related
Multiple user support	It can only support a single user at a time

What is InnoDB?

InnoDB is an ACID-compliant (ACID: Atomicity, Consistency, Isolation and Durability) storage engine for MySQL tables, that replaced MyISAM as the default storage engine.

What are INDEXES and what is their function in a MySQL table

They are data structures that contain list of reference keys to the records in a table. They are very critical when optimizing your database for performance since they assist in finding records quickly.

What is the difference between DATETIME and TIMESTAMP?

DATETIME data is stored as it is while the TIMESTAMP is converted from the current time zone to UTC. DATETIME have a length of 8byte while TIMESTAMP is 4byte long.

What the difference between views and tables?

Views contain tabular structure just like tables, but they do not store any data by themselves. They are built on tables or other views.

Write a code in SQL that will export database named as “sample” when executed on CLI Answer

mysqldump -u root -p password sample > “e:\ sample.sql”

What is a summarized difference between SCHEMA and DATABASE?

A schema is a collection of tables while a database is a collection of schemas.

What is the default port number for MySQL server?

3306

What is the difference between primary key and foreign key?

A primary key is a non-null field that uniquely identifies a record. A foreign key is a non-null field that contain a primary key of another table.

What do you mean by and _ in the LIKE statement?

The underscore _ wildcard is used to match any single character. For example

SELECT * FROM table_1 WHERE firstname LIKE ‘D_n 

The above query will return results of all names that begins with D and ends with N like Dan, Don….

What does the query below return?

SELECT * FROM students WHERE lastname LIKE "W%" 

Returns all the students whose lastname starts with ‘W’

Write a query to demonstrate how to concatenate values from the following fields “firstname” and “lastname”

SELECT CONCAT (firstname, " ", lastname) AS FullNames FROM Students; 

Write a query that shows how you remove a column from a table

ALTER TABLE table_1 DROP COLUMN column_1

What is a join? List the type of joins that can be applied in MySQL

Join is a method of linking data from 2 or more tables. There are 3 types of joins in MySQL

• Inner join
• Left join
• Right join
• Cross join
• Self-join

What is ON DELETE CASCADE and what does it do

It is a command that is defined during enforcing of foreign key constraints. It forces the deletion of data from a secondary that is similar to the primary data (related records). If you delete data on the primary table all related record(s) in the secondary table is/are deleted.

What are temporary tables?

It is a table that allows user to store temporary data to be used multiple times in a single session.

What is a stored procedure?

It is a block of declarative SQL statements. Stored procedures can have parameters, control flow, loops and they can call other procedures

What is the difference between a stored procedure and a stored function?

Stored Functions	Stored Procedures
Must return a value	Optional return of values
Contain only input parameters	Can contains input and output parameters
Can be called inside normal query expression or even inside a stored procedure	Cannot be called from query expression or from a function

What is the simplest way of making your SELECT query execute faster

By making sure you are selecting the only required columns.

You can save media data (video, audio and images) inside a database table. Which data type are you going to assign to that field.

BLOB

What is the difference between IN and BETWEEN?

Between is used to select values that are between ranges, while IN is used to show results sets of values inside a set of values

What is constraints? List types of constraints?

Constraints a set of rules that are enforced to columns on the table. They tightly defines type of data that is going to be saved in the table.

Types of constraints Primary key, not null, foreign key, unique.

What is a subquery?

Subquery is basically a query within another query. The query that is nested is often called inner query while the query providing the nesting is referred to as outer query.

What does UNION operators do?

It allows different query result sets to be combined to one set.