Q1: What are the tables in MySQL? Explain the types.

Ans: Everything in MySQL is stored in logical tables. Tables may be considered as MySQL's primary storage structure. As a result, tables are often referred to as storage engines. MySQL offers the following storage engines:

• MyISAM –   MySQL's default storage engine is MyISAM. It expands on the previous ISAM storage engine. MyISAM has large storage capacities of up to 256TB! Tables can also be compacted to get more storage space. MyISAM tables do not support transactions.
• MERGE –   A MERGE table is a virtual table that combines multiple MyISAM tables that have similar structures. Because MERGE tables lack their indexes, they rely on the indexes of the underlying tables.
• ARCHIVE –   As the name implies, Archive aids in archiving tables by compressing them, therefore decreasing storage space. As a result, the Archive may hold a large number of records. When creating and reading table records, it employs the compression-decompression method. It is accomplished through the use of the Zlib library.
• CSV –  This is more of a file format. The values are stored in the Comma-separated values (CSV) format by the CSV engine. This engine facilitates the migration of tables into a non-SQL pipeline.
• InnoDB –  When it comes to selecting an engine to drive performance, InnoDB is the best option. InnoDB is a transactional database engine. As a result, it is ACID-compliant and capable of rapidly restoring your database to the most stable state in the event of a crash.
• Memory–   Memory tables were previously referred to as HEAP. Memory tables can provide a speed advantage since they are kept in memory. However, it does not function with huge data tables for the same reason.
• Federated –   Federated tables allow you to access tables on distant MySQL servers. It is possible to do so without the need for any third-party integration or cluster technologies.

Q2: What is a foreign key? Write a query to implement the same in MySQL.

Ans: A foreign key is used to link two tables together. A FOREIGN KEY is a field (or set of fields) in one database that refers to the PRIMARY KEY in another. The FOREIGN KEY requirement is used to prevent behaviors that might crush table joins. It is necessary to specify a foreign key while establishing the table. The FOREIGN KEY query can be used to allocate it. Something along the lines of:
FOREIGN KEY (Any_ID) REFERENCES Table_to_reference(Any_ID)

Q3: What is the difference between the database and the table?

Ans: There is a significant distinction between a database and a table. The distinctions are as follows:

• Tables represent data division in a database, whereas the database is a collection of tables and data.
• Tables are used to organize data and generate a dataset by grouping it concerning other data. This dataset will be incorporated into the database. In whatever manner it is kept, the data in the table is a part of the database, but the opposite is not true.
• A database collects structured data and access features, whereas a table is a collection of rows and columns used to store the data.

Q4: What are the different ways to join tables in MySQL?

Ans: A join is used to connect two or more tables by using the values of a common column in both tables. There are four basic types of joins:

• Inner Join – A join predicate, a condition utilized to join, is used in an inner join. The syntax is as follows:

SELECT something FROM table name INNER JOIN another table ON condition;

• Left Join –  A join condition is also required for a left join. The left join selects data starting from the left table. The left table compares each entry in the right table to each entry in the left table. The syntax is as follows:

SELECT something FROM table name LEFT JOIN another table ON condition;

• Right Join –  Left join is the inverse of right join, with one distinction in the query being the name of the join. The arrangement of the tables should be carefully considered here. The syntax is as follows:

SELECT something FROM table name LEFT JOIN another table ON condition;

• Cross Join –  There is no join condition for a cross join. It creates a cartesian of the rows from both tables. The syntax is as follows:

SELECT something FROM table name CROSS JOIN another table;