Top MySQL Interview Questions ana Answers
Top 50 MySQL Interview Questions & Answers
1. What is MySQL?
MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)
2. What are the technical features of MySQL?
MySQL database software is a client or server system which includes
· Multithreaded SQL server supporting various client programs and libraries
· Different backend
· Wide range of application programming interfaces and
· Administrative tools.
3. Why MySQL is used?
MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet.
4. What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary
• BLOB or TEXT fields are not allowed
• Only comparison operators can be used =, <,>, = >,=<
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL
5. What is the default port for MySQL Server?
The default port for MySQL server is 3306.
6. What are the advantages of MySQL when compared with Oracle?
· MySQL is open source software which is available at any time and has no cost involved.
· MySQL is portable
· GUI with command prompt.
· Administration is supported using MySQL Query Browser
7. Differentiate between FLOAT and DOUBLE?
Following are differences for FLOAT and DOUBLE:
• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
8. Differentiate CHAR_LENGTH and LENGTH?
CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.
9. How to represent ENUMs and SETs internally?
ENUMs and SETs are used to represent powers of two because of storage optimizations.
10. What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values and that can be used
during table creation.
1) Create table size(name ENUM('Small', 'Medium','Large');
11. Define REGEXP?
REGEXP is a pattern match in which matches pattern anywhere in the search value.
12. Difference between CHAR and VARCHAR?
Following are the differences between CHAR and VARCHAR:
· CHAR and VARCHAR types differ in storage and retrieval
· CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
· When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.
13. Give string types available for column?
The string types are:
14. How to get current MySQL version?
1) SELECT VERSION ();
is used to get the current version of MySQL.
15. What storage engines are used in MySQL?
Storage engines are called table types and data is stored in files using various techniques.
· Storage mechanism
· Locking levels
· Capabilities and functions.
16. What are the drivers in MySQL?
Following are the drivers available in MySQL:
· PHP Driver
· JDBC Driver
· ODBC Driver
· C WRAPPER
· PYTHON Driver
· PERL Driver
· RUBY Driver
· CAP11PHP Driver
17. What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?
TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table.
18. What is the difference between primary key and candidate key?
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.
19. How do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
20. What does myisamchk do?
It compress the MyISAM tables, which reduces their disk or memory usage.
21. How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.
22. What is the difference between MyISAM Static and MyISAM Dynamic?
In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.
23. What are federated tables?
Federated tables which allow access to the tables located on other databases on other servers.
24. What, if a table has one column defined as TIMESTAMP?
Timestamp field gets the current timestamp whenever the row gets altered.
25. What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
26. How can we find out which auto increment was assigned on Last insert?
LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.
27. How can you see all indexes defined for a table?
Indexes are defined for the table by:
SHOW INDEX FROM <tablename>;
28. What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
29. How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.
30. What are the column comparisons operators?
The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
31. How can we get the number of rows affected by query?
Number of rows can be obtained by
1) SELECT COUNT (user_id) FROM users;
32. Is Mysql query is case sensitive?
1) SELECT VERSION(), CURRENT_DATE;
2) SeLect version(), current_date;
3) seleCt vErSiOn(), current_DATE;
All these examples are same. It is not case sensitive.
33. What is the difference between the LIKE and REGEXP operators?
LIKE and REGEXP operators are used to express with ^ and %.
1) SELECT * FROM employee WHERE emp_name REGEXP "^b";
2) SELECT * FROM employee WHERE emp_name LIKE "%b";
34. What is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –
· MEDIUMBLOB and
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
· MEDIUMTEXT and
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.
35. What is the difference between mysql_fetch_array and mysql_fetch_object?
Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object – Returns a result row as object from database.
36. How can we run batch mode in mysql?
Following commands are used to run in batch mode:
1) mysql ;
2) mysql mysql.out
37. Where MyISAM table will be stored and also give their formats of storage?
Each MyISAM table is stored on disk in three formats:
· The ‘.frm’ file stores the table definition
· The data file has a ‘.MYD’ (MYData) extension
· The index file has a ‘.MYI’ (MYIndex) extension
38. What are the different tables present in MySQL?
Total 5 types of tables are present:
· INNO DB
MyISAM is the default storage engine as of MySQL .
39. What is ISAM?
ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.
40. What is InnoDB?
lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
41. How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with
ORDER BY clause.
1) SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
42. How to enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is a string.
43. How to display top 50 rows?
In MySql, top 50 rows are displayed by using this following query:
1)SELECT * FROM
2) LIMIT 0,50;
44. How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any standard table.
45. What is the different between NOW() and CURRENT_DATE()?
NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.
46. What are the objects can be created using CREATE statement?
Following objects are created using CREATE statement:
47. How many TRIGGERS are allowed in MySql table?
SIX triggers are allowed in MySql table. They are as follows:
· BEFORE INSERT
· AFTER INSERT
· BEFORE UPDATE
· AFTER UPDATE
· BEFORE DELETE and
· AFTER DELETE
48. What are the nonstandard string types?
Following are Non-Standard string types:
49. What are all the Common SQL Function?
CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.
FORMAT(X, D) – Formats the number X to D significant digits.
CURRDATE(), CURRTIME() – Returns the current date or time.
NOW() – Returns the current date and time as one value.
MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.
HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age
SUBTIMES(A, B) – Determines the difference between two times.
FROMDAYS(INT) – Converts an integer number of days into a date value.
50. Explain Access Control Lists.
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
MySQL Interview Questions
1) What is MySQL?
MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. This is the world's second most popular and widely used open source database.
2) In which language MySQL is written?
MySQL is written in C and C++ and its SQL parser is written in yacc.
3) What are the technical specification of MySQL?
MySQL has the following technical specifications -
- Flexible structure
- High performance
- Manageable and easy to use
- Replication and high availability
- Security and storage management
4) What is the difference between MySQL and SQL?
SQL is known as standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.
A PHP script is required to store and retrieve the values inside the database.
5) What is the difference between database and table?
There is a major difference between a database and a table. The differences are as follows:
- Tables are a way to represent the division of data in a database while, database is a collection of tables and data.
- Tables are used to group the data in relation with each other and create a dataset. This dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but the reverse is not true.
6) Why do we use MySQL database server?
The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.
7) What are the different tables present in MySQL?
There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:
- INNO DB
8) What is the difference between CHAR and VARCHAR?
A list of differences between CHAR and VARCHAR:
- CHAR and VARCHAR types are different in storage and retrieval.
- CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.
- When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.
9) What is the difference between TRUNCATE and DELETE in MySQL?
The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.
10) How many Triggers are possible in MySQL?
There are only six Triggers allowed to use in MySQL database.
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
11) What is heap table?
Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
12) What is BLOB and TEXT in MySQL?
BLOB is an acronym stands for binary large object. It is used to hold a variable amount of data.
There are four types of BLOB.
The differences among all these are the maximum length of values they can hold.
TEXT is case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.
There are four types of TEXT.
13) What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.
14) What is the difference between heap table and temporary table?
Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.
The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.
The heap tables are shared among clients while temporary tables are not shared.
Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).
15) What is the difference between FLOAT and DOUBLE?
FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.
16) What are the advantages of MySQL in comparison to Oracle?
- MySQL is a free, fast, reliable, open source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
- MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
- MySQL is great for database enabled websites while Oracle is made for enterprises.
- MySQL is portable.
17) What are the disadvantages of MySQL?
- MySQL is not so efficient for large scale databases.
- It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
- Transactions are not handled very efficiently.
18) What is the difference between CHAR and VARCHAR?
- CHAR and VARCHAR are differ in storage and retrieval.
- CHAR column length is fixed while VARCHAR length is variable.
- The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
- CHAR is 50% faster than VARCHAR.
- CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.
19) What is the difference between MySQL_connect and MySQL_pconnect?
- It opens a new connection to the database.
- Every time you need to open and close database connection, depending on the request.
- Opens page every time when it loaded.
- In Mysql_pconnect, "p" stands for persistent connection so it opens the persistent connection.
- the database connection can not be closed.
- it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.
20) What does " i_am_a_dummy flag" do in MySQL?
The " i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.
21) How to get the current date in MySQL?
To get current date, use the following syntax:
1. SELECT CURRENT_DATE();
22) What are the security alerts while using MySQL?
Install antivirus and configure the operating system's firewall.
Never use the MySQL Server as the UNIX root user.
Change root username and password
Restrict or disable remote access.
23) How to change a password for an existing user via Mysqladmin?
Mysqladmin -u root -p password "newpassword".
24) What is the difference between Unix timestamps and MySQL timestamps?
Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.
25) How to display Nth highest salary from a table in a MySQL query?
Let us take a table named employee.
To find Nth highest salary is:
1. select distinct(salary) from employee order by salary desc limit n-1,1
if you want to find 3rd largest salary:
1. select distinct(salary) from employee order by salary desc limit 2,1
26) What is MySQL default port number?
MySQL default port number is 3306.
27) What is REGEXP?
REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.
28) How many columns can you create for an index?
You can create maximum of 16 indexed columns for a standard table.
29) What is the difference between NOW() and CURRENT_DATE()?
NOW() command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE() shows the current year with month and date only.
30) What is the query to display top 20 rows?
1. SELECT * FROM table_name LIMIT 0,20;
31) Write a query to display current date and time?
If you want to display current date and time, use -
1. SELECT NOW();
If you want to display current date only, use:
1. SELECT CURRENT_DATE();
32) What is save point in MySQL?
A defined point in any transaction is known as savepoint.
SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.
33) What is SQLyog?
SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.
34) How do you backup a database in MySQl?
It is easy to backing up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to backup. Then specify the option you want under export and save the output.
35) What are the different column comparison operators in MySQL?
The =, <>, <=, <, >=, >, <<, >>, < = >, AND, OR or LIKE operator are the comparison operators in MySQL. These operators are generally used with SELECT statement.
36) Write a query to count the number of rows of a table in MySQL.
SELECT COUNT user_id FROM users;
37) Write a query to retrieve a hundred books starting from 20th.
SELECT book_title FROM books LIMIT 20, 100;
38) Write a query to select all teams that won either 1, 3, 5 or 7 games.
SELECT team_name FROM team WHERE team_won IN (1, 3, 5, 7);
39) What is the default port of MySQL Server?
The default port of MySQL Server is 3306.
40) How is MyISAM table stored?
MyISAM table is stored on disk in three formats.
- '.frm' file : storing the table definition
- '.MYD' (MYData): data file
- '.MYI' (MYIndex): index file
41) What is the usage of ENUMs in MySQL?
ENUMs are used to limit the possible values that go in the table:
CREATE TABLE months (month ENUM 'January', 'February', 'March'); INSERT months VALUES ('April').
42) What are the advantages of MyISAM over InnoDB?
MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compresses, if required. On the other hand, InnoDB stores the tables in tablespace. Its further optimization is difficult.
43) What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?
Mysql_fetch_object is used to retrieve the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.
Using mysql_fetch_object field can be accessed as $result->name.
Using mysql_fetch_array field can be accessed as $result->[name].
Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.
1. $result = mysql_query("SELECT * from students");
2. while($row = mysql_fetch_row($result))
4. Some statement;
44) What is the difference between mysql_connect and mysql_pconnect?
Mysql_connect() is used to open a new connection to the database while mysql_pconnect() is used to open a persistent connection to the database. It specifies that each time the page is loaded mysql_pconnect() does not open the database.
45) What is the use of mysql_close()?
Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().
46) What is MySQL data directory?
MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.
47) How do you determine the location of MySQL data directory?
The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.
48) What is the usage of regular expressions in MySQL?
In MySQL, regular expressions 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 1 instances of the string preceding it.
- . Matches a single character.
- [abc] matches a or b or z
- | separates strings
- ^ anchors the match from the start.
- "." Can be used to match any single character. "|" can be used to match either of the two strings
- REGEXP can be used to match the input characters with the database.
The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):
1. Select employee_name
2. From employee
3. Where employee_name REGEXP '1000'
4. Order by employee_name
49) What is the usage of i-am-a-dummy flag in MySQL?
In MySQL, the i-am-a-dummy flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.
50) Which command is used to view the content of the table in MySQL?
The SELECT command is used to view the content of the table in MySQL.