MySQL Interview Questions and Answers

The default port is 3306. We can change it in /etc/MySQL/my.conf there is a port variable. We can update this port according to our need

IS NULL checks to see if the cell is empty but IS NOT NULL checks to see if the cell is not empty.

Example :

SELECT id FROM users WHERE 'user_type' IS NOT NULL;

SELECT id FROM users WHERE 'user_type' NOT IS NULL;

Note: This is very essential MySQL query interview questions.

In MySQL, standards-based drivers for JDBC, ODBC, and .Net are provided in order to enable developers in building database applications with their language of choice.

Available Drivers in MySQL:

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxj

JDBC, ODBC, and .Net drivers are necessary for MySQL as they enable the developers for building their database applications.

In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table.

It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. Its activation time can be BEFORE or AFTER.

Types of Triggers in MySQL

• Before Insert: This is done before inserting data into the table.
• After Insert: This is after data has been entered into the table.
• Before update: This is activated prior to the updating of the data in the table.
• After update: This function is activated upon the updating of the table's data.
• Before delete: This is done before any data are removed from the table.
• After delete: This function is activated when the data has been deleted from the table.

What is the purpose of triggers in MySQL?

• It is easy to set up triggers.
• Triggers allow us to validate data before it is inserted or updated.
• Triggers allow us to keep track of records, such as audit trails for tables.
• Triggers increase the performance of SQL queries as it doesn't need to compile every time the query executes.
• Triggers can be used to reduce client-side code, which saves time and effort.
• It allows us to scale our applications across multiple platforms.
• SQL triggers are an alternative method to verify the integrity of data.
• Triggers are an alternative method to perform the task.

Syntax

CREATE TRIGGER trigger_name    
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON table_name FOR EACH ROW    
         BEGIN
        --trigger code    // You can write here your code
        END;

Mysql is not case sensitive. Its case-sensitivity relies on the underlying operating system as OS determines the case sensitivity of tables names and database. In windows, database and table names are not case sensitive but in the case of UNIX, it is case sensitive in nature. Especially on the UNIX host, database accepts the upper case and lower-case table names.

SELECT id, name FROM users WHERE phone IS NULL;

To insert new records into a table, use the INSERT INTO statement. You can write the INSERT INTO sentence in one of two ways.

• You will need to specify both column names and values that you wish to insert:
  INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
• You don't need to include the column names in your SQL query if you are adding values to all columns in the table. But, ensure that the order of your values matches the order of the columns in the table. The INSERT INTO syntax is as follows:
  INSERT INTO table_name VALUES (value1, value2, value3, ...);

Example

INSERT INTO Customers (name, email, salary) VALUES ('MySQL Query Interview Questions', '[email protected]', 15000);

In MySQL, the time period cardinality refers to the specialty of facts values that can be put into columns. It is a type of property that influences the potential to search, cluster and kind data.

Cardinality can be of two sorts which are as follows

• Low Cardinality − All values for a column have to be the same.
• High Cardinality − All values for a column ought to be unique.

Binary data can be stored in Mysql in different ways such as

• The short length of binary data can be stored in the form of string as a VARCHAR
• A BLOB data type allows users to store an arbitrary amount of binary data.