(1) 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.
(2) What are the different engine 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

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

"MyISAM" and "InnoDB" are two different storage engines for the MySQL relational database management system. Each storage engine has its own strengths, weaknesses, and features, making them suitable for different use cases. Here's a comparison of MyISAM and InnoDB:

MyISAM:

  1. Table-level Locking: MyISAM uses table-level locking, which means that when a write operation (such as an UPDATE or DELETE) is performed on a table, the entire table is locked. This can lead to contention and performance bottlenecks in high-concurrency environments.

  2. No Foreign Keys or Transactions: MyISAM doesn't support foreign keys or transactions. This means that you cannot enforce referential integrity between tables using foreign keys, and you can't group multiple statements into a single transaction.

  3. Full-Text Search: MyISAM has built-in support for full-text search indexes, making it suitable for applications that require powerful text search capabilities.

  4. Memory Usage: MyISAM generally uses less memory compared to InnoDB, which can be advantageous if you have memory constraints.

  5. Fast Reads: MyISAM can be faster for read-heavy workloads or scenarios where the data doesn't change frequently.

  6. Non-ACID Compliant: MyISAM is not ACID compliant, which means it doesn't provide the same level of data integrity and reliability as InnoDB.

InnoDB:

  1. Row-level Locking: InnoDB uses row-level locking, allowing multiple transactions to work on different rows of a table simultaneously without blocking each other.

  2. Foreign Keys and Transactions: InnoDB supports foreign keys, allowing you to define relationships between tables and enforce referential integrity. It also supports transactions, which are crucial for maintaining data consistency and reliability in multi-step operations.

  3. ACID Compliance: InnoDB is ACID compliant (Atomicity, Consistency, Isolation, Durability), providing a higher level of data integrity and reliability compared to MyISAM.

  4. Crash Recovery: InnoDB offers better crash recovery and automatic data recovery mechanisms. It keeps a transaction log and can recover data to a consistent state after a crash.

  5. Concurrency: InnoDB performs better in high-concurrency environments due to its row-level locking and multi-version concurrency control (MVCC) architecture.

  6. Resource Usage: InnoDB may use more memory due to its additional features, but this can be managed using configuration settings.

  7. Foreign Key Performance: While InnoDB supports foreign keys, enforcing them can sometimes lead to performance overhead due to the need to maintain referential integrity.

In summary, if you need ACID compliance, support for transactions, foreign keys, and better data integrity, InnoDB is usually the better choice. On the other hand, if you require full-text search capabilities and have read-heavy workloads, MyISAM might be more suitable. However, as of my last update in September 2021, InnoDB has become the default storage engine for MySQL, and it is generally recommended for most use cases due to its advanced features and better performance in modern database scenarios. Always consider your specific requirements and perform benchmarking before choosing a storage engine.

(3) 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];  
(4) What is 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;   
 

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

  1. Before Insert
  2. After Insert
  3. Before Update
  4. After Update
  5. Before Delete
  6. After Delete
(5) 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;   
(6) How to check database size in MySQL?
MySQL allows us to query the information_schema.tables table to get information about the tables and databases. It will return 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;  
(7) What is indexing 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

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

(8) 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:

ALTER TABLE table_name AUTO_INCREMENT = value;
(9) 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); 

(10) What is the difference between TRUNCATE and DELETE in MySQL?
  1. TRUNCATE is a DDL command, and DELETE is a DML command.
  2. It is not possible to use Where command with TRUNCATE QLbut you can use it with DELETE command.
  3. TRUNCATE cannot be used with indexed views, whereas DELETE can be used with indexed views.
  4. 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.
(11) 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.
(12) 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.

  1. TINYBLOB
  2. BLOB
  3. MEDIUMBLOB
  4. 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.

  1. TINYTEXT
  2. TEXT
  3. MEDIUMTEXT
  4. LONGTEXT
(13) 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).

(14) 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.
(15) 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.
(16) 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.
(17) 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.
(18) 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.
(19) 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

(20) 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.
(21) hat 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.

(22) 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').

(23) 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.
(24) How can we run batch mode in MySQL?

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

mysql;  
mysql mysql.out;  
(25) What are federated tables?
Federated tables are tables that point to the tables located on other databases on some other server.
(26) 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.

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

(28) What is Common Table Expression (CTE) in mysql?
A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.