MySQL Interview Questions

A list of top frequently asked MySQL interview questions and answers are given below.

1) What is MySQL?

MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. It is the world's second most popular and widely-used open source database. It is interesting how MySQL name was given to this query language. The term My is coined by the name of the daughter of co-founder Michael Widenius's daughter, and SQL is the short form of Structured Query Language. Using MySQL is free of cost for the developer, but enterprises have to pay a license fee to Oracle.

Formerly MySQL was initially owned by a for-profit firm MySQL AB, then Sun Microsystems bought it, and then Oracle bought Sun Microsystems, so Oracle currently owns MySQL.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) based on structured query language. MySQL supports a wide range of operating systems, most famous of those include Windows, Linux & UNIX. Although it is possible to develop a wide range of applications with MySQL, it is only used for web applications & online publishing. It is a fundamental part of an open-source enterprise known as Lamp.

What is the Lamp?

The Lamp is a platform used for web development. The Lamp uses Linux, Apache, MySQL, and PHP as an operating system, web server, database & object-oriented scripting language. And hence abbreviated as LAMP.

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
Drivers
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) 5. What is the difference between the database and the 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 to each other and create a dataset. This dataset will be used in the database. The data 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 features used to access them, whereas the table is a collection of rows and columns used to store the data.

6) Why do we use the MySQL database server?

First of all, the MYSQL server is free to use for developers and small enterprises.

MySQL server is open source.

MySQL's community is tremendous and supportive; hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market for a long time. All bugs arising in the previous builds have been continuously removed, and a very stable version is provided after every update.

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:

MyISAM
Heap
Merge
INNO DB
ISAM

8) How to install MySQL?

Installing MySQL on our system allows us to safely create, drop, and test web applications without affecting our live website's data. There are many ways to use MySQL on our system, but the best way is to install it manually. The manual installation allows us to learn more about the system and provides more control over the database. To see the installation steps of MySQL in Windows goes to the below link:

https://www.javatpoint.com/how-to-install-mysql

Manual installation of MySQL has several benefits:

Backing up, reinstalling, or moving databases from one location to another can be achieved in a second.
It provides more control to how and when MySQL server starts and closes.
We can install MySQL anywhere, like in a portable USB drive.

9) How to check the MySQL version?

We can check the MySQL version on Linux using the below command:

If we use the MySQL in windows, opening the MySQL command-line tool displayed the version information without using any flags. If we want to know more about the server information, use the below statement:

It will return the output as below:

In this output, we can see the additional version information about the installed MySQL software like innodb_version, protocol_version, version_ssl_library, etc.

10) How to add columns in MySQL?

A column is a series of cells in a table that stores one value for each row in a table. We can add columns in an existing table using the ALTER TABLE statement as follows:

ALTER TABLE table_name   
    ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

To read more information, click here.

11) How to delete a table in MySQL?

We can delete a table in MySQL using the Drop Table statement. This statement removes the complete data of a table, including structure and definition from the database permanently. Therefore, it is required to be careful while deleting a table. After using the statement, we cannot recover the table in MySQL. The statement is as follows:

To read more information, click here.

12) How to add foreign keys in MySQL?

The foreign key is used to link one or more tables together. It matches the primary key field of another table to link the two tables. It allows us to create a parent-child relationship with the tables. We can add a foreign key to a table in two ways:

Using the CREATE TABLE Statement
Using the ALTER TABLE Statement

Following is the syntax to define a foreign key using CREATE TABLE OR ALTER TABLE statement:

[CONSTRAINT constraint_name]  
    FOREIGN KEY [foreign_key_name] (col_name, ...)  
    REFERENCES parent_tbl_name (col_name,...)  

To read more information, click here.

13) How to connect to the MySQL database?

MySQL allows us to connect with the database server in mainly two ways:

Using Command-line Tool

We can find the command-line client tool in the bin directory of the MySQL's installation folder. To invoke this program, we need to navigate the installation folder's bin directory and type the below command:

Next, we need to run the below command to connect to the MySQL Server:

Finally, type the password for the selected user account root and press Enter:

After successful connection, we can use the below command to use the:

Using MySQL Workbench

We can make a connection with database using MySQL Workbench, simply clicking the plus (+) icon or navigating to the menu bar -> Database -> Connect to Database, the following screen appears. Now, you need to fill all the details to make a connection:

Once we finished this setup, it will open the MySQL Workbench screen. Now, we can double click on the newly created connection to connect with the database server.

To read more information, click here.

14) How to change the MySQL password?

We can change the MySQL root password using the below statement in the new notepad file and save it with an appropriate name:

Next, open a Command Prompt and navigate to the MySQL directory. Now, copy the following folder and paste it in our DOS command and press the Enter key.

Next, enter this statement to change the password:

Finally, we can log into the MySQL server as root using this new password. After launches the MySQL server, it is to delete the C:\myswl-init.txt file to ensure the password change.

To read more information, click here.

15) How to create a database in MySQL Workbench?

To create a new database in MySQL Workbench, we first need to launch the MySQL Workbench and log in using the username and password. Go to the Navigation tab and click on the Schema menu. Right-click under the Schema menu and select Create Schema or click the database icon (red rectangle), as shown in the following screen.

A new popup screen appears where we need to fill all the details. After entering the details, click on the Apply button and then the Finish button to complete the database creation.

To read more information, click here.

16) How to create a table in MySQL Workbench?

Launch the MySQL Workbench and go to the Navigation tab and click on the Schema menu where all the previously created databases are shown. Select any database and double click on it. It will show the sub-menus where we need to select the Tables option.

Select Tables sub-menu, right-click on it and select Create Table option. We can also click on create a new table icon (shown in red rectangle) to create a table. It will open the new popup screen where we need to fill all the details to create a table. Here, we will enter the table name and column details. After entering the details, click on the Apply button and then the Finish button to complete the table creation.

To read more information, click here.

17) How to change the table name in MySQL?

Sometimes our table name is non-meaningful. In that case, we need to change or rename the table name. MySQL provides the following syntax to rename one or more tables in the current database:

If we want to change more than one table name, use the below syntax:

RENAME TABLE old_tab1 TO new_tab1,  
             old_tab2 TO new_tab2, old_tab3 TO new_tab3; 

To read more information, click here.

18) How to change the database name in MySQL?

Sometimes we need to change or rename the database name because of its non-meaningful name. To rename the database name, we need first to create a new database into the MySQL server. Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database. The following is the syntax of using mysqldump command:

Now, use the below command to import the data into the newly created database:

19) How to import a database in MySQL?

Importing database in MySQL is a process of moving data from one place to another place. It is a very useful method for backing up essential data or transferring our data between different locations. For example, we have a contact book database, which is essential to keep it in a secure place. So we need to export it in a safe place, and whenever it lost from the original location, we can restore it using import options.

In MySQL, we can import a database in mainly two ways:

Command Line Tool
MySQL Workbench

To read more information for importing databases, click here.

20) How to change the column name in MySQL?

While creating a table, we have kept one of the column names incorrectly. To change or rename an existing column name in MySQL, we need to use the ALTER TABLE and CHANGE commands together. The following are the syntax used to rename a column in MySQL:

ALTER TABLE table_name   
    CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER existing_column];  

Suppose the column's current name is S_ID, but we want to change this with a more appropriate title as Stud_ID. We will use the below statement to change its name:

21) How to delete columns in MySQL?

We can remove, drop, or delete one or more columns in an existing table using the ALTER TABLE statement as follows:

To read more information, click here.

22) How to insert data in MySQL?

We can insert data in a MySQL table using the INSERT STATEMENT. This statement allows us to insert single or multiple rows into a table. The following is the basic syntax to insert a record into a table:

INSERT INTO table_name ( field1, field2,...fieldN )  
VALUES  ( value1, value2,...valueN );  

If we want to insert more than one rows into a table, use the below syntax:

INSERT INTO table(field1, field2,...fieldN)
VALUES 
   (value1, value 2, ...),
   (value1, value2, ...),
    ...
   (value1, value2, ...);

To read more information, click here.

23) How to delete a row in MySQL?

We can delete a row from the MySQL table using the DELETE STATEMENT within the database. The following is the generic syntax of DELETE statement in MySQL to remove one or more rows from a table:

It is noted that if we have not specified the WHERE clause with the syntax, this statement will remove all the records from the given table.

To read more information, click here.

24) How to join two tables in MySQL?

We can connect two or more tables in MySQL using the JOIN clause. MySQL allows various types of JOIN clauses. These clauses connect multiple tables and return only those records that match the same value and property in all tables. The following are the four easy ways to join two or more tables in MySQL:

Inner Join
Left Join
Right Join
Cross Join

To read more information, click here.

25) How to join three tables in MySQL?

Sometimes we need to fetch data from three or more tables. There are two types available to do these types of joins. Suppose we have three tables named Student, Marks, and Details.

Let's say Student has (stud_id, name) columns, Marks has (school_id, stud_id, scores) columns, and Details has (school_id, address, email) columns.

1. Using SQL Join Clause

This approach is similar to the way we join two tables. The following query returns result from three tables:

SELECT name, scores, address, email FROM Student s 
INNER JOIN Marks m on s.stud_id = m.stud_id 
INNER JOIN Details d on d.school_id = m.school_id;

2. Using Parent-Child Relationship

It is another approach to join more than two tables. In the above tables, we have to create a parent-child relationship. First, create column X as a primary key in one table and as a foreign key in another table. Therefore, stud_id is the primary key in the Student table and will be a foreign key in the Marks table. Next, school_id is the primary key in the Marks table and will be a foreign key in the Details table. The following query returns result from three tables:

SELECT name, scores, address, email 
FROM Student s, Marks m, Details d 
WHERE s.stud_id = m.stud_id AND m.school_id = d.school_id;

To read more information about the foreign key, click here.

26) How to update the table in MySQL?

We can update existing records in a table using the UPDATE statement that comes with the SET and WHERE clauses. The SET clause changes the values of the specified column. The WHERE clause is optional, which is used to specify the condition. This statement can also use to change values in one or more columns of a single row or multiple rows at a time. Following is a generic syntax of UPDATE command to modify data into the MySQL table:

UPDATE table_name   
SET field1=new-value1, field2=new-value2, ...  
[WHERE Clause]  

To read more information, click here.

27) What is MySQL Workbench?

MySQL Workbench is a unified visual database designing or GUI tool used for working on MySQL databases. It is developed and maintained by Oracle that provides SQL development, data migration, and comprehensive administration tools for server configuration, user administration, backup, etc. We can use this Server Administration to create new physical data models, E-R diagrams, and SQL development. It is available for all major operating systems. MySQL provides supports for it from MySQL Server version v5.6 and higher.

It is mainly available in three editions, which are given below:

Community Edition (Open Source, GPL)
Standard Edition (Commercial)
Enterprise Edition (Commercial)

To read more information, click here.

28) How to drop the primary key in MySQL?

MySQL primary key is a single or combination of the field used to identify each record in a table uniquely. A primary key column cannot be null or empty. We can remove or delete a primary key from the table using the ALTER TABLE statement. The following syntax is used to drop the primary key:

To read more information, click here.

29) How to create a Stored Procedure in MySQL?

A stored procedure is a group of SQL statements that we save in the database. The SQL queries, including INSERT, UPDATE, DELETE, etc. can be a part of the stored procedure. A procedure allows us to use the same code over and over again by executing a single statement. It stores in the database data dictionary.

We can create a stored procedure using the below syntax:

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]  
BEGIN  
    Body_section of SQL statements
END;  

This statement can return one or more value through parameters or may not return any result. The following example explains it more clearly:

DELIMITER $$  
CREATE PROCEDURE get_student_info()  
BEGIN  
SELECT * FROM Student_table;  
END$$  

To read more information, click here.

30) How to execute a stored procedure in MySQL?

We can execute a stored procedure in MySQL by simply CALL query. This query takes the name of the stored procedure and any parameters we need to pass to it. The following is the basic syntax to execute a stored procedure:

Let's understand it with this example:

Here, a stored procedure named Product_Pricing calculates and returns the lowest and highest product prices.

31) How to create a View in MySQL?

A view is a database object whose values are based on the base table. It is a virtual table created by a query by joining one or more tables. It is operated similarly to the base table but does not contain any data of its own. If any changes occur in the underlying table, the same changes reflected in the View also.

Following is the general syntax of creating a VIEW in MySQL:

CREATE [OR REPLACE] VIEW view_name AS  
SELECT columns  
FROM tables  
[WHERE conditions];

To read more information, click here.

32) How to create a Trigger in MySQL?

A trigger is a procedural code in a database that automatically invokes whenever certain events on a particular table or view in the database occur. It can be executed when records are inserted into a table, or any columns are being updated. We can create a trigger in MySQL using the syntax as follows:

CREATE TRIGGER trigger_name  
    [before | after]  
   {insert | update | delete}  
    ON table_name [FOR EACH ROW]  
    BEGIN  
        --variable declarations  
        --trigger code  
    END; 

To read more information, click here.

33) How to clear screen in MySQL?

If we use MySQL in Windows, it is not possible to clear the screen before version 8. At that time, the Windows operating system provides the only way to clear the screen by exiting the MySQL command-line tool and then again open MySQL.

After the release of MySQL version 8, we can use the below command to clear the command line screen:

34) How to create a new user in MySQL?

A USER in MySQL is a record in the USER-TABLE. It contains the login information, account privileges, and the host information for MySQL account to access and manage the databases. We can create a new user account in the database server using the MySQL Create User statement. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts.

The following is the basic syntax to create a new user in MySQL:

To read more information, click here.

35) How to check USERS in MySQL?

If we want to manage a database in MySQL, it is required to see the list of all user's accounts in a database server. The following command is used to check the list of all users available in the database server:

To read more information, click here.

36) How to import a CSV file in MySQL?

MySQL allows us to import the CSV (comma separated values) file into a database or table. A CSV is a plain text file that contains the list of data and can be saved in a tabular format. MySQL provides the LOAD DATA INFILE statement to import a CSV file. This statement is used to read a text file and import it into a database table very quickly. The full syntax to import a CSV file is given below:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv'   
INTO TABLE tablename   
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\r\n'   
IGNORE 1 ROWS;  

To read more information, click here.

37) How to insert Date in MySQL?

MySQL allows us to use the INSERT STATEMENT to add the date in MySQL table. MySQL provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME, and YEAR. The default format of the date in MySQL is YYYY-MM-DD. Following is the basic syntax to insert date in MySQL table:

INSERT INTO table_name (column_name, column_date) VALUES ('DATE: Manual Date', '2008-7-04'); 

If we want to insert a date in the mm/dd/yyyy format, it is required to use the below statement:

38) How to check database size in MySQL?

MySQL allows us to query the information_schema.tables table to get the information about the tables and databases. It will return the information about the data length, index length, collation, creation time, etc. We can check the size of the database on the server using the below syntax:

SELECT table_schema AS 'Database Name',
SUM(data_length + index_length) 'Size in Bytes',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MB'
FROM information_schema.tables
WHERE table_schema = 'testdb'
GROUP BY table_schema;

It will return the output as follows:

If we want to check the size of the table in a specific database, use the following statement:

SELECT table_name AS 'Table Name',
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size in MB'
FROM information_schema.TABLES
WHERE table_schema = 'testdb'
ORDER BY (data_length + index_length) DESC;

It will return the output as follows:

39) How does indexing works in MySQL?

Indexing is a process to find an unordered list into an ordered list. It helps in maximizing the query's efficiency while searching on tables in MySQL. The working of MySQL indexing is similar to the book index.

Suppose we have a book and want to get information about, say, searching. Without indexing, it is required to go through all pages one by one, until the specific topic was not found. On the other hand, an index contains a list of keywords to find the topic mentioned on pages. Then, we can flip to those pages directly without going through all pages.

40) Who owns MySQL?

MySQL is the most popular free and open-source database software which comes under the GNU General Public License. In the beginning, it was owned and sponsored by the Swedish company MySQL AB. Now, it is bought by Sun Microsystems (now Oracle Corporation), who is responsible for managing and developing the database.

To read more information, click here.

41) How to view the database in MySQL?

Working with the MySQL server, it is a common task to view or list the available databases. We can view all the databases on the MySQL server host using the following command:

To read more information, click here.

42) How to set auto increment in MySQL?

Auto Increment is a constraint that automatically generates a unique number while inserting a new record into the table. Generally, it is used for the primary key field in a table. In MySQL, we can set the value for an AUTO_INCREMENT column using the ALTER TABLE statement as follows:

43) How to find the second highest salary in MySQL?

MySQL uses the LIMIT keyword, which can be used to limit the result set. It will allow us to get the first few rows, last few rows, or range of rows. It can also be used to find the second, third, or nth highest salary. It ensures that you have use order by clause to sort the result set first and then print the output that provides accurate results. The following query is used to get the second highest salary in MySQL:

SELECT salary 
FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;

There are some other ways to find the second highest salary in MySQL, which are given below:

This statement uses subquery and IN clause to get the second highest salary:

SELECT MAX(salary) 
FROM employees 
WHERE salary NOT IN ( SELECT Max(salary) FROM employees);

This query uses subquery and < operator to return the second highest salary:

SELECT MAX(salary) From employees 
WHERE salary < ( SELECT Max(salary) FROM employees);

44) What is the difference between TRUNCATE and DELETE in MySQL?

TRUNCATE is a DDL command, and DELETE is a DML command.
It is not possible to use Where command with TRUNCATE QLbut 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 a very dangerous command and should be used carefully because it deletes every row permanently from a table.

45) How many Triggers are possible in MySQL?

There are only six Triggers allowed to use in the MySQL database.

Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete

46) What is the 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.

47) What is BLOB and TEXT in MySQL?

BLOB is an acronym that stands for a large binary object. It is used to hold a variable amount of data.

There are four types of the BLOB.

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

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.

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

48) What is a trigger in MySQL?

A trigger is a set of codes that executes in response to some events.

49) What is the difference between the heap table and the temporary table?

Heap tables:

Heap tables are found in memory that is used for high-speed storage temporarily. They do not allow BLOB or TEXT fields.

Heap tables do not support AUTO_INCREMENT.

Indexes should be NOT NULL.

Temporary tables:

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.

Main differences:

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).

50) What is the difference between FLOAT and DOUBLE?

FLOAT stores floating-point numbers with accuracy up to 8 places and allocate 4 bytes. On the other hand, DOUBLE stores floating-point numbers with accuracy up to 18 places and allocates 8 bytes.

51) 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.

52) 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.
The functionality of MySQL is highly dependent on other addons.
Development is not community-driven.

53) What is the difference between CHAR and VARCHAR?

CHAR and VARCHAR have differed in storage and retrieval.
CHAR column length is fixed, while VARCHAR length is variable.
The maximum no. of character CHAR data types can hold is 255 characters, while VARCHAR can hold up to 4000 characters.
CHAR is 50% faster than VARCHAR.
CHAR uses static memory allocation, while VARCHAR uses dynamic memory allocation.

54) What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

It opens a new connection to the database.
Every time you need to open and close the database connection, depending on the request.
Opens page whenever it is loaded.

Mysql_pconnect:

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 whenever the page is loaded.

55) What does "i_am_a_dummy flag" do in MySQL?

The "i_am_a_dummy flag" enables the 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.

56) How to get the current date in MySQL?

To get current date, use the following syntax:

57) 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 the root username and password Restrict or disable remote access.

58) How to change a password for an existing user via mysqladmin?

Mysqladmin -u root -p password "newpassword".

59) 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 the readable format of YYYY-MM-DD HH:MM:SS format.

60) How to display the 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

61) What is the MySQL default port number?

MySQL default port number is 3306.

62) What is REGEXP?

REGEXP is a pattern match using a regular expression. The 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.

63) How many columns can you create for an index?

You can a create maximum of 16 indexed columns for a standard table.

64) 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.

65) What is the query to display the top 20 rows?

SELECT * FROM table_name LIMIT 0,20;

66) Write a query to display the current date and time?

If you want to display the current date and time, use -

SELECT NOW();

If you want to display the current date only, use:

SELECT CURRENT_DATE();

67) What is the 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 savepoint with the name of the identifier.

68) 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.

69) 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.

70) 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.

71) Write a query to count the number of rows of a table in MySQL.

SELECT COUNT user_id FROM users;

72) Write a query to retrieve a hundred books starting from 20th.

SELECT book_title FROM books LIMIT 20, 100;

73) 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);

74) What is the default port of MySQL Server?

The default port of MySQL Server is 3306.

75) How is the 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

76) What is the usage of ENUMs in MySQL?

ENUMs are string objects. By defining ENUMs, we allow the end-user to give correct input as in case the user provides an input that is not part of the ENUM defined data, then the query won't execute, and an error message will be displayed which says "The wrong Query". For instance, suppose we want to take the gender of the user as an input, so we specify ENUM('male', 'female', 'other'), and hence whenever the user tries to input any string any other than these three it results in an error.

ENUMs are used to limit the possible values that go in the table:

For example:

CREATE TABLE months (month ENUM 'January', 'February', 'March'); INSERT months VALUES ('April').

77) 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 compressed if required. On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.

78) 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.

For example:

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.

Example:

$result = mysql_query("SELECT * from students");  
while($row = mysql_fetch_row($result))  
{  
        Some statement;  
}  

79) 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.

80) What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().

81) 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 the data directory.

82) 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.

83) 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 one 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.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

Select employee_name  
From employee  
Where employee_name REGEXP '1000'  
Order by employee_name  

84) What is the usage of the "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.

85) 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.

Explain Access Control Lists.

An ACL is a list of permissions that are associated with an object. MySQL keeps the Access Control Lists cached in memory, and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object, and if the permissions are available, then execution completes successfully.

86) What is InnoDB?

InnoDB is a storage database for SQL. The ACID-transactions are also provided in InnoDB and also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.

87) What is ISAM?

It is a system for file management developed by IBM, which allows records to access sequentially or even randomly.

88) How can we run batch mode in MySQL?

To perform batch mode in MySQL, we use the following command:

mysql;
mysql mysql.out;

89) What are federated tables?

Federated tables are tables that point to the tables located on other databases on some other server.

90) What is the difference between primary key and candidate key?

To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.

A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.

91) 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
Ado.net5.mxz

92) What are DDL, DML, and DCL?

Majorly SQL commands can be divided into three categories, i.e., DDL, DML & DCL. Data Definition Language (DDL) deals with all the database schemas, and it defines how the data should reside in the database. Commands like CreateTABLE and ALTER TABLE are part of DDL.

Data Manipulative Language (DML) deals with operations and manipulations on the data. The commands in DML are Insert, Select, etc.

Data Control Languages (DCL) are related to the Grant and permissions. In short, the authorization to access any part of the database is defined by these.

Java Basics Interview Questions	Java OOPs Interview Questions
Java Multithreading Interview Questions	Java String & Exception Interview Questions
Java Collection Interview Questions	JDBC Interview Questions
Servlet Interview Questions	JSP Interview Questions
Spring Interview Questions	Hibernate Interview Questions
PL/SQL Interview Questions	SQL Interview Questions
Oracle Interview Questions	Android Interview Questions
SQL Server Interview Questions	MySQL Interview Questions