MySQL Interview Questions

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

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.

AD

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:

AD

  • 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:

  1. mysql -v  

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:

  1. SHOW VARIABLES LIKE “%version%”;  

It will return the output as below:

MySQL Interview Questions

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:

  1. ALTER TABLE table_name     
  2.     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:

  1. DROP TABLE  table_name;   

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:

  1. [CONSTRAINT constraint_name]    
  2.     FOREIGN KEY [foreign_key_name] (col_name, …)    
  3.     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:

  1. mysql  

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

  1. shell>mysql -u root -p  

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

  1. Enter password: ********  

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

  1. USE database_name;  

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:

MySQL Interview Questions

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:

  1. ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘NewPassword’;  

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.

  1. C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin    

Next, enter this statement to change the password:

  1. mysqld –init-file=C:\\mysql-notepadfile.txt  

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.

MySQL Interview Questions

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.

MySQL Interview Questions

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:

  1. mysql> RENAME old_table TO new_table;    

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

  1. RENAME TABLE old_tab1 TO new_tab1,    
  2.              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:

  1. mysqldump -u username -p “password” -R oldDbName > oldDbName.sql  

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

  1. mysql -u username -p“password” newDbName < oldDbName.sql   

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:

  1. ALTER TABLE table_name     
  2.     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:

  1. ALTER TABLE Student CHANGE COLUMN S_ID Stud_ID varchar(10);  

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:

  1. ALTER TABLE table_name DROP COLUMN column_name1, column_name2….;   

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:

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

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

  1. INSERT INTO table(field1, field2,…fieldN)  
  2. VALUES   
  3.    (value1, value 2, …),  
  4.    (value1, value2, …),  
  5.     …  
  6.    (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:

  1. DELETE FROM table_name WHERE Condition_specified;    

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:

  1. SELECT name, scores, address, email FROM Student s   
  2. INNER JOIN Marks m on s.stud_id = m.stud_id   
  3. 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:

  1. SELECT name, scores, address, email   
  2. FROM Student s, Marks m, Details d   
  3. 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:

  1. UPDATE table_name     
  2. SET field1=new-value1, field2=new-value2, …    
  3. [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:

  1. ALTER TABLE table_name  DROP 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:

  1. CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]    
  2. BEGIN    
  3.     Body_section of SQL statements  
  4. END;    

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

  1. DELIMITER $$    
  2. CREATE PROCEDURE get_student_info()    
  3. BEGIN    
  4. SELECT * FROM Student_table;    
  5. 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:

  1. CALL stored_procedure_name (argument_list);  

Let’s understand it with this example:

  1. CALL Product_Pricing (@pricelow, @pricehigh);  

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

Shubham Patil

Typically replies within a day

Powered by WpChatPlugins

Learn to code by doing. Try hands-on Coding with RLchats PRO.

Claim Discount Now!
X