Below are few major features of Mysql
Q1. What is mysqlcheck do?
mysqlcheck is a command-line interface that allows you to check, repair, analyze and optimize the multiple tables.
Below are some commands:
Q2. What does tee command do in MySQL?
tee command is used to start the logging process and send the output to the console and text file.
mysql> tee /tmp/my.out;
Q3. Write an sql statement to count number of rows in table?
The SQL COUNT () command is used to count the number of rows. It returns the number of rows that satisfies the condition specified in where clause.
COUNT(*) returns all rows (duplicated as well null rows) COUNT( [ALL|DISTINCT] expression ): returns Distinct rows only.
select count(*) from posts where status='published'
Q4. What is maximum number of columns allowed in a table?
The hard limit is 4096 columns per table but the effective number of columns is less. The exact limit depends on various factors which are:
Q5. What is use of IGNORE keyword in MySQL?
If you want to insert duplicate records in a table in the presence of a unique index, it will return an error. So, to discard it silently, use the INSERT IGNORE command instead of INSERT. It will tell MySQL to discard the record if it is duplicate.
INSERT IGNORE INTO Table (field1, field2) -> VALUES( 'value1', 'value2');
Q6. What is difference between MyISAM Static and MyISAM Dynamic?
The types of the column can explain the difference in MyISAM static and MyISAM dynamic we use in our table.
When the column of the table varies by using specific formats like (VARCHAR, VARBINARY, TEXT, BLOB and their variants), it is called dynamic MyISAM.
Whereas in static MyISAM, the columns are fixed, and any type of format can not change the columns.
Q7. What are different type of Joins available in MySQL?
Joins in Mysql are used to retrieve the data from more than two tables. There are three types of joins available in MySQL:
Q8. What is difference between denormalization and normalization?
Difference between denormalization and normalization
Normalization is a technique to eliminate the redundant data by minimizing the insertion, deletion and update anomalies.
Denormalization is a technique to add the redundancy to improve the performance of an application In short, Denormalization is the inverse process of Normalization.
Q9. Write a query to find second highest salary of an employee?
Below is a simple MySQL query to find the second highest salary of an employee
SELECT name, MAX(salary) AS salary FROM table_name WHERE salary < (SELECT MAX(salary) FROM table_name);
Q10. How many rows will return from dual table?
As the Dual table has only one row so it returns only one row. DUAL tables are the special type of table that is used for evaluating expressions or calling functions.
Q11. What is the difference between TRUNCATE and DELETE commands?
Difference between TRUNCATE and DELETE commands
|It is a DML command.||It is a DDL command.|
|It deletes the data temporarily from the database.||It deletes the data permanently from the database.|
|It won’t reinitialize the table.||It will reinitialize the table.|
|Command is: Delete from < table name>||Syntax is: Truncate table <table_name>|
Q12. What are scrollable cursor?
The scrollable cursor is a flexible type of cursors which when placed in a table, the cursor can be moved forward, backward or even to the start of the table. But in the standard cursor, this facility is not available. The normal cursor does not allow us to move the cursor down and up of the table also.
Q13. What are HEAP Tables?
Heap tables also known as memory tables are tables without a Clustered Index. These tables are in memory and used for fast data retrieval. Data types like "BLOB" or "TEXT" are not supported by Heap tables.
Q14. What is difference between SUBSTR and INSTR?
Difference between SUBSTR and INSTR in Mysql
Q15. What is Pragma EXECPTION_INIT ? Explain its usage?
Pragma EXCEPTION_INIT allows you to instruct the compiler to combine your message to Oracle predefined message during compile time.
It is used for better readability of the program and helps you to handle exceptions in your way.
Q16. How to use mysqldump to create a copy of the database?
mysqldump is used to duplicate the database including the tables and the data with a new name.
mysqldump -uroot -p my_project -r my_project.sql
Q17. What is "i_am_a_dump" flag in MySql?
"i_am_a_dump" is a flag that supports MySQL engine when WHERE clause is missing. It refuses to perform any update or delete statements.
Q18. Explain advantages of InnoDB over MyISAM?
Advantages of InnoDB over MyISAM:
Q19. What is the default port for MySQL Server?
The default port no for MySQL server if 3306.
Q20. What is the difference between CHAR and VARCHAR?
Difference between CHAR and VARCHAR
Q21. Enlist the storage engine available in MySQL?
MySQL supports following storage engines
Q22. How to use the MySQL slow query log?
MYSQL slow query log is used to find those queries which take a long time to execute and selected for optimization.
To use Mysql slow query log you need to enable the slow query log by running the below query.
SET GLOBAL slow_query_log = 'ON';
Q23. What is the difference between BLOB and TEXT?
BLOB stands for Binary Large Objects used for storing binary data whereas TEXT is used for storing strings.
Q24. How many types of triggers are available in MySQL?
There are six different types of triggers in MySQL:
Q25. What is the difference between FLOAT and DOUBLE?
Difference between FLOAT and DOUBLE
Q26. How to get the current date in MySQL?
In MySQL, CURRENT_DATE () command is used to get the current date in the 'YYYY-MM-DD' format.
Q27. What is MySQL data directory?
MySQL data directory specifies the location, source, usage, destination of the data elements present in the database.
Q28. What are federated tables?
A federated table is a table that points to a table in another MySQL database instance. In short, it is a view of another table.