Top 40 MySql Interview Questions and Answers: Here are the top interview questions of mysql. Be prepared for the interview. You can download the entire questions in a pdf format. so guys All the best for your interview.
1) What is MySQL?
MySQL is an Oracle-supported Relational Database Management System (RDBMS) which is based on structured query language. MySQL supports wide ranges of operating systems most famous of those include Windows, Linux & UNIX. Although it is possible to develop a wide range of application with MySQL, it is only used for web applications & online publishing. It is a fundamental part of an open source enterprise known as Lamp.
2) What is Lamp?
Lamp is a platform used for web development. Lamp uses Linux, Apache, MySQL, and PHP as an operating system, web server, database & object-oriented scripting language respectively. And hence abbreviated as LAMP.
You might also like: Top 50 Java Interview Question and Answers pdf Download
2) In which language MySQL has been written?
MySQL is written in C and C++, and its SQL parser is written in yacc.
3) What are the technical specifications 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
- Graphical Tools
- MySQL Enterprise Monitor
- MySQL Enterprise Security
- JSON Support
- Replication & High-Availability
- Manageability and Ease of Use
- OLTP and Transactions
- Geo-Spatial Support
4) What is the difference between MySQL and SQL?
SQL is known as the 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.
SQL is a computer language, whereas MySQL is a software or an application
SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data
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 the 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.
- A database is a collection of organized data and also features which are used to access them, whereas table is a collection of rows and columns which are used to store the data.
Q6) What is the function of myisamchk?
myisamchk is a useful database utility tool that is used to get information about MyISAM database tables.
It is also used for checking, debugging, repairing and optimizing database tables. It is better to use this command when the server is down or when the required tables are not in use by the server.
myisamchk [OPTION] table_name…
The available options of this tool can be retrieved by using the following command.
To check or repair all MyISAM tables, the following command will be required for executing from the database directory location.
7) What are the purposes of using ENUM and SET data types?
Top 40 MySql Interview Questions and Answers: ENUM data type is used in the MySQL database table to select any one value from the predefined list.
The value of a particular field can be restricted by defining the predefined list as the field which is declared as ENUM will not accept any value outside the list.
The SET data type is used to select one or more or all values from the predefined list. This data type can also be used to restrict the field for inserting only the predefined list of values like ENUM.
8) What are the differences between CHAR and VARCHAR data types?
Both CHAR and VARCHAR data types are used to store string data in the field of the table.
The differences between these data types are mentioned below:
- CHAR data type is used to store fixed-length string data and VARCHAR data type is used to store variable-length string data.
- The storage size of CHAR data type will always be the maximum length of this data type and the storage size of VARCHAR will be the length of the inserted string data. Hence, it is better to use the CHAR data type when the length of the string will be the same length for all the records.
- CHAR is used to store small data whereas VARCHAR is used to store large data.
- CHAR works faster and VARCHAR works slower.
Q 9) What is the purpose of using TIMESTAMP data type?
A TIMESTAMP data type is used to store the combination of date and time value which is 19 characters long.
The format of TIMESTAMP is YYYY-MM-DD HH:MM: SS. It can store data from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. By default, the current date and time of the server get inserted in the field of this data type when a new record is inserted or updated.
10) What is the difference between mysql_fetch_array() and ysql_fetch_object() ?
Both mysql_fetch_array() and mysql_fetch_object() are built-in methods of PHP to retrieve records from MySQL database table.
The difference between these methods is that mysql_fetch_array() returns the result set as an array and mysql_fetch_object() returns the result set as an object.
11) What is the difference between TRUNCATE and DELETE in MySQL?
TRUNCATE is a DDL command, DELETE is a DML command.
It is not possible to use Where command with TRUNCATE but you can use it with DELETE command.
TRUNCATE cannot be used with indexed views whereas DELETE can be used with indexed views.
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.
12) 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
13) 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 a temporary basis. They do not allow BLOB or TEXT fields.
14) What is BLOB and TEXT in MySQL?
BLOB is an acronym stands for a large binary 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 a 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.
15) What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.
16) What is the difference between heap table and temporary table?
Heap tables are found in memory. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.
Top 40 MySql Interview Questions and Answers: The temporary tables are used to keep the transient data. Sometimes it is beneficial in cases to hold temporary data. The Temporary table is deleted after the 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).
17) 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.
18) 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.
19) 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.
- Functionality of MySQL is highly dependent of other addons.
- Development is not community driven.
20) 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.
21) 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 whenever it is loaded.
- In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.
- The database connection cannot 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.
22) 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. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.
23) How to get the current date in MySQL?
To get current date, use the following syntax:
- SELECT CURRENT_DATE();
24) 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.
25) How to change a password for an existing user via Mysqladmin?
Mysqladmin -u root -p password “newpassword”.
26) 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.
27) How to display Nth highest salary from a table in a MySQL query?
Let us take a table named the employee.
To find Nth highest salary is:
select distinct(salary)from employee order by salary desc limit n-1,1
if you want to find 3rd largest salary:
select distinct(salary)from employee order by salary desc limit 2,1
28) What is MySQL default port number?
MySQL default port number is 3306.
29) What is REGEXP?
REGEXP is a pattern match using a regular expression. A Regular expression is a powerful way of specifying a pattern for a sophisticated search.
Basically it is a special text string for describing a search pattern. To understand it better you can think of a situation of daily life when you search for .txt files to list all text files in the file manager. The regex equivalent for .txt will be .*\.txt.
30) How many columns can you create for an index?
You can create maximum of 16 indexed columns for a standard table.
31) 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.
32) What is the query to display top 20 rows?
SELECT * FROM table_name LIMIT 0,20;
33) Write a query to display current date and time?
If you want to display current date and time, use –
If you want to display current date only, use:
34) 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.
35) What is SQLyog?
Top 40 MySql Interview Questions and Answers: 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.
36) How do you backup a database in MySQl?
It is easy to back 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 back up. Then specify the option you want under export and save the output.
37) 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.
You might also like: Top 20 MongoDB Interview Questions and Answers
38) Write a query to count the number of rows of a table in MySQL.
SELECT COUNT user_id FROM users;
39) Write a query to retrieve a hundred books starting from 20th.
SELECT book_title FROM books LIMIT 20, 100;
40) 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);
41) What is the default port of MySQL Server?
The default port of MySQL Server is 3306.
42) 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
Top 40 MySql Interview Questions and Answers: Thanks for stopping by.!