Mysql Interview Questions And Answers - Atomicity, Dedlock, Client Program, Mysql Mode, Mysql Sensitivity Of Identifiers And Mysql Errors

Question: what is the difference between where and having clause in mysql?
Answer: where can restrict each row or record
having clause restrict group of records.


Question: What is Atomicity?
Answer: This means whether execution of "all" statements or "no" statements.


Question: What is Deadlock?
Answer: A failure or inability to proceed due to two transactions having some data that the other needs.


Question: What is mysql client program ?
Answer: It is a command-line program that acts as a text-based front end for the MySQL Server. It's used for issuing queries and viewing the results in the terminal window.

mysql --help
mysql --version
mysql --V

In many cases, a given option has both a long and a short form. Long options consist of a word preceded by double dashes(e.g. --) whereas short options consist of a single letter preceded by a single dash(e.g. -).


Question: What is difference between Interactive Mode and Batch Mode of mysql client?
Answer: In Interactive modes, you put simple query to get the result, In this results is return in window.
In Batch Mode, you put your all the queries in sql file and run it from mysql client OR from scheduler job.  


Question: What is difference between \g and \G?
Answer: Both are the statement terminator alike semicolon(;). 
\g end the query statement and output the result in horizontal.
\G end the query statement and output the result in vertically and mainly used when output is large. 


Question: How to manage MySQL case sensitivity of identifiers?
Answer: Database name and table name are stored as file, So In window its case-insenstive and in unix it is case senstive.
Column name, index, and trigger are not case sensitive.
Column aliases are not case sensitive
 
Question: What is difference between identifier-quoting- character and string-quoting-character?
Answer: backtick(`) are identifer quoting character and used  quote for dbname, tableName and filedName
Single Quote('), Double Quote(") are string quoting character
  

Question: What is SQL Query to print the warnings?
Answer: show warnings;

Question: What is SQL Query to print the warnings in Vertically?
Answer: show warning\G;display the warnings in vertical tabs;

Question: How to use limit in mysql warning?
Answer: show warning limit 1,2 \G;display the warnings in vertical tabs for 2nd & 3rd;

Question: In Mysql, How to disable the sql notes?
Answer: set sql_notes=0;

Question: How to get the detail of error in mysql?
Answer: perror 13; 

show the error message detail