MySQL Interview Question Part 2

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:

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.

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;

  1. 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;

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]    

MySQL Interview Question

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)

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;    

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$$   

Advance MySQL Interview Question

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.

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:

  1. CREATE [OR REPLACE] VIEW view_name AS    
  2. SELECT columns    
  3. FROM tables    
  4. [WHERE conditions];  

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:

  1. CREATE TRIGGER trigger_name    
  2.     [before | after]    
  3.    {insert | update | delete}    
  4.     ON table_name [FOR EACH ROW]    
  5.     BEGIN    
  6.         –variable declarations    
  7.         –trigger code    
  8.     END;   

MySQL Interview Question

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:

  1. mysql> SYSTEM CLS;  

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:

  1. CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ‘password’;   

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:

  1. mysql> SELECT USER FROM mysql.user; 

Advance MySQL Interview Question

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:

  1. LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv’     
  2. INTO TABLE tablename     
  3. FIELDS TERMINATED BY ‘,’    
  4. OPTIONALLY ENCLOSED BY ‘”‘    
  5. LINES TERMINATED BY ‘\r\n’     
  6. IGNORE 1 ROWS;    

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:

  1. 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:

  1. INSERT INTO table_name VALUES (STR_TO_DATE(date_value, format_specifier));  

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:

  1. SELECT table_schema AS ‘Database Name’,  
  2. SUM(data_length + index_length) ‘Size in Bytes’,  
  3. ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) ‘Size in MB’  
  4. FROM information_schema.tables  
  5. WHERE table_schema = ‘testdb’  
  6. GROUP BY table_schema;  

It will return the output as follows:

MySQL Interview Questions

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

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

It will return the output as follows:

MySQL Interview Questions

MySQL Interview Question

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.

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.

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:

  1. mysql> SHOW DATABASES;  

Advance MySQL Interview Question

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:

  1. ALTER TABLE table_name AUTO_INCREMENT = value;  

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:

  1. SELECT salary   
  2. 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:

  1. SELECT MAX(salary)   
  2. FROM employees   
  3. WHERE salary NOT IN ( SELECT Max(salary) FROM employees);  

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

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

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.

MySQL Interview Question

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.

What is a trigger in MySQL?

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

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

Advance MySQL Interview Question

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.

What are the advantages of MySQL in comparison to Oracle?

  1. 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.
  2. MySQL uses only just under 1 MB of RAM on your laptop, while Oracle 9i installation uses 128 MB.
  3. MySQL is great for database enabled websites while Oracle is made for enterprises.
  4. MySQL is portable.

What are the disadvantages of MySQL?

  1. MySQL is not so efficient for large scale databases.
  2. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
  3. Transactions are not handled very efficiently.
  4. The functionality of MySQL is highly dependent on other addons.
  5. Development is not community-driven.

MySQL Interview Question

What is the difference between CHAR and VARCHAR?

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

What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

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

Mysql_pconnect:

  1. In Mysql_pconnect, “p” stands for persistent connection, so it opens the persistent connection.
  2. The database connection cannot be closed.
  3. 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.

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.

Advance MySQL Interview Question

How to get the current date in MySQL?

To get current date, use the following syntax:

  1. SELECT CURRENT_DATE();  

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.

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

Mysqladmin -u root -p password “newpassword”.

MySQL Interview Question

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.

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

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.

Advance MySQL Interview Question

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.

What is the query to display the top 20 rows?

SELECT * FROM table_name LIMIT 0,20;

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

MySQL Interview Question

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.

 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.

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.

Advance MySQL Interview Question

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.

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

SELECT COUNT user_id FROM users;

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

SELECT book_title FROM books LIMIT 20, 100;

MySQL Interview Question

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)

MySQL Part 1MySQL Part 3MySQL Part 4
Back to top