MySQL Interview Questions and Answers: MySQL is an open-source RDBMS - Relational database management system. It is written in C, C++.
Q:- What's MySQL ?
MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL). It's Original author - MySQL AB and it is written in C, C++.
Q:- What is the Latest version of MySQL ?
Current veriosn of MySQL: 8.0 MySQL 8.0 New Features
MySQL Interview Questions - DDL, DML, DCL and TCL
Q:- DDL, DML, DCL and TCL Statements in SQL with Examples
This is the most important asked MySQL Interview Questions.
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DCL: Data Control Language
- TCL: Transaction Control Language
| DDL | DML | DCL | TCL |
|---|---|---|---|
| CREATE | SELECT | GRANT | BEGIN |
| ALTER | INSERT | REVOKE | COMMIT |
| DROP | UPDATE | ROLLBACK | |
| TRUNCATE | DELETE | SAVEPOINT | |
| COMMENT | UPSERT | ||
| RENAME | CALL | ||
| LOCK | |||
| EXPLAIN |
DDL:
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
- CREATE - to create objects in the database
- ALTER - alters the structure of the database
- DROP - delete objects from the database
- TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
- COMMENT - add comments to the data dictionary
- RENAME - rename an object
DML:
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
- SELECT - retrieve data from the a database
- INSERT - insert data into a table
- UPDATE - updates existing data within a table
- DELETE - deletes all records from a table, the space for the records remain
- MERGE - UPSERT operation (insert or update)
- CALL - call a PL/SQL or Java subprogram
- EXPLAIN PLAN - explain access path to data
- LOCK TABLE - control concurrency
DCL:
Data Control Language (DCL) statements. Some examples:
- GRANT - gives user's access privileges to database
- REVOKE - withdraw access privileges given with the GRANT command
TCL:
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
- COMMIT - save work done
- SAVEPOINT - identify a point in a transaction to which you can later roll back
- ROLLBACK - restore database to original since the last COMMIT
- SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
You may also like - MongoDB Interview Questions Answers
MySQL Interview Questions - MySQL Data Types
Q:- What are the data type supported by mysql?
| Data type | Description |
|---|---|
| CHAR(size) | Holds a fixed length string (can contain letters, numbers, and special characters). |
| VARCHAR(size) | Holds a variable length string (can contain letters, numbers, and special characters). |
| TINYTEXT | Holds a string with a maximum length of 255 characters |
| TEXT | Holds a string with a maximum length of 65,535 characters |
| BLOB | For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
| MEDIUMBLOB | For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters |
| LONGBLOB | For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data |
| ENUM(A,B,C,etc.) | Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. |
| SET | Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice. |
You may like this - Database Interview Questions
Q:-How to get current MySQL version?
SELECT VERSION();
Q:- What are the drivers in MySQL?
- PHP Driver
- JDBC Driver
- ODBC Driver
- C WRAPPER
- PYTHON Driver
- PERL Driver
- RUBY Driver
Q:- How do you login to MySql using Unix shell?
- Log into your server using SSH.
- Now, Login into MySQL
- Now you can list all database using - show databases; command
- Now choose your database using - use DATABSENAME; command
mysql -h hostname -u username -p password
OR
//Type following and press Enter
mysql -u USERNAME -p
//Now Enter Password
Q:-What are the techniques for query optimization?
Following are some techniques of query optimization
- use Indexes
- select only required columns instead of slecting all columns with *
Q:- What is SQL Injection? How can we prevent SQL Injection?
SQL injection is a code injection technique through which attacker can execute malicious SQL statements on your database server.
You can prevent SQL injection by using following:
- Sanitize input variables.
- Parameters binding.
- Using stored procedures
- Never use depericated functions etc
Q:- Get Highest salary from each department with employee name?
select e.name, e.salary, e.department
from employee as e
INNER join(select max(salary) as m_s, department from employee as me group by department)
on e.salary=me.m_s and e.department=me.department;
MySQL Interview Questions - MySQL Regular Expressions
Q:- What is MySQL Regular expressions (Regexp)?
Regular expressions in MySql are used in queries for searching a pattern in a string.
* Matches 0 more instances of the string preceding it.
+ matches 1 more instances of the string preceding it.
? Matches 0 or 1instances of the string preceding it.
. Matches a single character.
[abc] matches a or b or z
| separates strings
^ anchors the match from the start.
REGEXP can be used to match the input characters with the database.
MySQL Interview Questions - Database
Q:Difference between Unix timestamps and MySQL timestamps?
Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.
Q:- What is difference between mysql_connect and mysql_pconnect?
Mysql_connect:
- Opens a new connection to the database- The database connection can be closed
- Opens the page every time the page is loaded.
Mysql_pconnect:
- Opens a persistent connection to the database.- The database connection cannot be closed.
- The page need not be opened every time the page is loaded.
You may like this - Laravel Interview Questions Answers
Q:- What does myisamchk do?
myisamchk compress the MyISAM tables, which reduces their disk or memory usage.
Q:- What are federated tables?
federated tables which allow access to the tables located on other databases on other servers.
Q:- What is MySQL Stored Procedure Explain?
A Stored Procedure is a routine or set of SQL Statements invoked with a CALL statement.
Read more in details about MySQL Stored Procedure
MySQL Interview Questions - MySQL Stored Procedure and Functions
Q:- What is the difference between Stored Procedure and Functions?
| Stored Procedure | Functions |
| It Compiles only one time. | It Compiles every time. |
| Return values is not mandatory, It may return or not. It can use the IN, OUT, INOUT parameters | It must return value. IN, OUT and INOUT cannot be used in function |
| We can call function inside stored procedure | We can't call stored procedure inside function |
| Stored Procedure Supports DML Commands | Functions supports only SELECT Commands |
| It support TCL Commands | It does not support TCL Commands |
| Stored Procedure support Exception Handling | Function doesn’t support Exception Handling |
You may like this - MySQL Stored Procedure example with parameter
Q:- Explain mysql_fetch_array(), mysql_fetch_object(), mysql_fetch_row()?
mysql_fetch_array(): Fetch a result row as an associative array, a numeric array, or both
mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());
mysql_select_db("my_database");
$result = mysql_query("SELECT id,name FROM my_table");
while($row = mysql_fetch_array($result, MYSQL_NUM)) {
echo "\n Id is: ".$row[0]. " and Name is: ". $row[1];
}
mysql_free_result($result);
?>
Note: In place of "MYSQL_NUM" you can use "MYSQL_ASSOC" or "MYSQL_BOTH"
mysql_fetch_object(): it returns the result from the database as objects
mysql_fetch_row(): it returns result as an numeric
//Note: this extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0.
Q:-How many columns we can update in single mysql query?
64 columns
Q:-What is maximum length of column name, table name and database name?
column name: It can be upto 64 chars.
table name: It can be upto 64 chars.
database name: It can be upto 64 chars.
You may like this - Node.js Interview Questions Answers
Q:What is the difference between CHAR_LENGTH and LENGTH?
- CHAR_LENGTH() returns the length of the string measured in characters.
- LENGTH() returns the length of the string measured in bytes.
Q:-What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.
Q:-What does + mean in REGEXP?
At least one character.
Q:-How can we get the number of rows affected by query?
$connection = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$connection) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('my_database');
mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Deleted records: %d\n", mysql_affected_rows());
/* with a where clause that is never true, it should return 0 */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Deleted records: %d\n", mysql_affected_rows());
//Output:
Deleted records: 10
Deleted records: 0
Q:- How do I get the number of rows in MySQL without using count()?
Suppose Table Name is "employee"
- SELECT TABLE_ROWS from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'employee';
- SELECT SQL_CALC_FOUND_ROWS * FROM employee;
- SELECT FOUND_ROWS();
Q:- How can I get the number of tables in a database?
SELECT table_name FROM information_schema.tables WHERE table_schema = 'DATABASE_NAME';
another way to find this is below:
USE DATABASE_NAME;
SHOW TABLES;
SELECT FOUND_ROWS();
Q:- How do I copy/clone/duplicate the data, structure and indexes of a MySQL table to a New Table?
To copy with indexes and triggers do these 2 queries:
CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;
To copy just structure and data use this one:
CREATE TABLE tbl_new AS SELECT * FROM tbl_old;
Q:-How do you get the number of rows?
SELECT COUNT (*) FROM users;
Q:-If the value in the column is repeatable, how do you find out the unique values?
Use DISTINCT in the query
SELECT DISTINCT first_name FROM users;
Q:-How do you return hundred books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100;
The first number LIMIT is the offset, the second is the number of records.
Q:- How to create case insensitive query in mysql?
Use binary for case insensitive query.
#Example:
select * from users where name= binary 'FullStackTutorials';
Q:- How would you write a query to select all users whose id in 2, 4, 6 or 8?
SELECT name FROM users WHERE id IN (2, 4, 6, 8);
Q:-How would you select all the users, whose phone number is null?
SELECT name FROM users WHERE phonenumber is NULL;
OR
SELECT name FROM users WHERE ISNULL(phonenumber);
Q:-How do you find last insert id?
SELECT LAST_INSERT_ID();
will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.
Q:-When would you use ORDER BY in DELETE statement?
When you’re not deleting by row ID. Such as in DELETE FROM questions ORDER BY timestamp LIMIT 1.
This will delete the most recently posted question in the table questions.
Q:-How can you see all indexes defined for a table?
SHOW INDEX FROM TABLE_NAME;
OR
SHOW INDEXES FROM TABLE_NAME;
Q:-How would you delete a column?
ALTER TABLE tablename DROP columnname;
Q:-How would you change a table to InnoDB?
ALTER TABLE tablename ENGINE innodb;
Q:-How do I find out all databases starting with "users" to which I have access to?
SHOW DATABASES LIKE "users%";
Q:-How do you concatenate strings in MySQL?
CONCAT(string1, string2, string3);
Q:-How do you get a portion of a string?
SELECT SUBSTR(name, 1, 10) from users;
Q:-How to get the second highest salary from a employee table?
You can do this in two ways
1) select salary from employee order by salary desc limit 1,1;
2) select max(salary) from employee where salary < (select max(salary) from employee);
Q:-How do you get the month from a timestamp?
SELECT MONTH(timestamp) from users;
You may like this - Core PHP Interview Questions Answers
Q:-How to remove duplicate rows from table in MySQL?
Suppose Table Name is - users
Select * from users;
+----+--------+
| id | name |
+----+--------+
| 1 | Ravi |
| 2 | Suraj |
| 3 | Tom |
| 4 | Reddy |
| 5 | Suraj |
| 6 | Reddy |
| 7 | Reddy |
| 8 | XYZ |
| 9 | PQR |
| 10 | Robert |
+----+--------+
Delete row with highest id:
DELETE u1 FROM users u1, users u2 WHERE u1.id > u2.id AND u1.name = u2.name
Delete row with lowest id:
DELETE u1 FROM users u1, users u2 WHERE u1.id < u2.id AND u1.name = u2.name
Q:- What is the difference between WHERE and HAVING clause?
- WHERE act as Pre-filter whereas HAVING act as Post-filter.
- A WHERE is used to filter records before any groupings take place.
- HAVING is used to filter values after they have been groups. Only columns or expression in the group can be included in the HAVING clause’s conditions.
Note:
- WHERE always comes before GROUP BY and HAVING clause when both WHERE and HAVING clause are used in same query.
- HAVING clause can also be used without Group BY.
You may like this - SQL JOINs Types with Examples