MySQL Interview Questions – Set 13

by

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;

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];

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.

What is the MySQL default port number?

MySQL default port number is 3306.

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

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.

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];

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

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:

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