Interview Questions.

Question 1. Does Strict Mode Impact Performance?

Answer :

The extensive validation of enter statistics that a few settings calls for more time than if the validation is not executed. While the performance effect is not that notable, in case you do no longer require such validation (possibly your utility already handles all of this), then MySQL offers you the option of leaving strict mode disabled. However, if you do require it, strict mode can offer such validation.

Question 2. Can Mysql five.6 Do Sub Queries?

Answer :

Yes ,A subquery is a SELECT declaration inside another assertion.

All subquery forms and operations that the SQL preferred calls for are supported, as well as a few functions that are MySQL-specific.

Here is an instance of a subquery:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

Oracle 10g Interview Questions
Question three. What Are The Advantages Of Sub Queries?

Answer :

They allow queries that are structured so that it's miles viable to isolate each a part of a assertion.
They offer alternative ways to perform operations that could otherwise require complex joins and unions.
Many people discover subqueries greater readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave humans the unique concept of calling the early SQL “Structured Query Language.”
Question four. Does Mysql five.6 Have A Query Cache? Does It Work On Server, Instance Or Database?

Answer :

Yes. The query cache operates at the server degree, caching complete end result units matched with the original query string. If an precisely identical query is made (which frequently takes place, particularly in internet applications), no parsing or execution is necessary; the end result is sent immediately from the cache. Various tuning alternatives are to be had.

Oracle 10g Tutorial
Question five. Does Mysql five.6 Have Sequences?

Answer :

No. However, MySQL has an AUTO_INCREMENT system, which in MySQL five.6 can also take care of inserts in a multi-master replication setup. With the auto_increment_increment and auto_increment_offset device variables, you may set every server to generate car-increment values that don't struggle with different servers. The auto_increment_increment price should be greater than the variety of servers, and each server have to have a completely unique offset.

Python Interview Questions
Question 6. Does Mysql 5.6 Work With Multi-middle Processors?

Answer :

Yes. MySQL is fully multi-threaded, and will make use of multiple CPUs, furnished that the working machine helps them.

Question 7. Why Do I See Multiple Processes For Mysqld?

Answer :

When using LinuxThreads, you should see at least three mysqld(mysqld, also referred to as MySQL Server, is the primary software that does maximum of the work in a MySQL installation.

MySQL Server manages get admission to to the MySQL records directory that consists of databases and tables. The data directory is also the default area for other statistics such as log documents and standing documents). Strategies running.

These are in fact threads. There is one thread for the LinuxThreads manager, one thread to handle connections, and one thread to deal with alarms and alerts.

Python Tutorial C++ Interview Questions
Question eight. An Mysql 5.6 Perform Acid Transactions?

Answer :

Yes. All cutting-edge MySQL versions assist transactions. The InnoDB storage engine gives full ACID transactions with row-level locking, multi-versioning, nonlocking repeatable reads, and all 4 SQL standard isolation tiers.

The NDB garage engine supports the READ COMMITTED transaction isolation degree simplest.

Question nine. Have Any Storage Engines Been Removed In Mysql five.6?

Answer :

No.

C Interview Questions
Question 10. What Are Server Sql Modes?

Answer :

Server SQL modes outline what SQL syntax MySQL need to help and what form of statistics validation tests it have to perform. This makes it easier to apply MySQL in extraordinary environments and to apply MySQL together with other database servers. The MySQL Server observe these modes for my part to exclusive clients.

C++ Tutorial
Question eleven. Does Mysql five.6 Support Stored Procedures And Functions?

Answer :

Yes. MySQL 5.6 supports two styles of stored routines, saved processes and saved functions.

SQL Server 2008 Interview Questions
Question 12. What Limitations Exist For Replicating Stored Procedure And Function Actions?

Answer :

Nondeterministic (random) or time-based moves embedded in saved tactics might not replicate well. By their very nature, randomly produced results aren't predictable and can't be precisely reproduced, and therefore, random actions replicated to a slave will now not mirror those accomplished on a grasp. Declaring saved capabilities to be DETERMINISTIC or putting the log_bin_trust_function_creators machine variable to zero will no longer permit random-valued operations to be invoked.

In addition, time-primarily based actions cannot be reproduced on a slave because the timing of such moves in a saved system isn't always reproducible thru the binary log used for replication. It records handiest DML occasions and does not aspect in timing constraints.

Finally, nontransactional tables for which mistakes occur at some stage in big DML moves (together with bulk inserts) may additionally revel in replication problems in that a master can be in part updated from DML hobby, but no updates are finished to the slave due to the mistakes that happened. A workaround is for a feature's DML actions to be carried out with the IGNORE keyword in order that updates at the master that purpose mistakes are overlooked and updates that don't reason mistakes are replicated to the slave.

Oracle 10g Interview Questions
Question 13. Are There Special Security Requirements For Using Stored Procedures And Functions Together With Replication?

Answer :

Yes. Because a slave server has authority to execute any statement study from a grasp's binary log, unique protection constraints exist for using saved features with replication. If replication or binary logging in preferred (for the cause of factor-in-time healing) is lively, then MySQL DBAs have two safety alternatives open to them:

Any person wishing to create stored features need to be granted the SUPER privilege.

Alternatively, a DBA can set the log_bin_trust_function_creators machine variable to 1, which enables all and sundry with the same old CREATE ROUTINE privilege to create saved capabilities.

C Tutorial
Question 14. How Are Actions That Take Place Inside Stored Procedures And Functions Replicated?

Answer :

MySQL facts every DML event that occurs in a stored system and replicates the ones character moves to a slave server. The actual calls made to execute saved tactics are not replicated.

Stored features that change facts are logged as function invocations, not as the DML events that occur inside each feature.

Question 15. Are Stored Procedures And Functions Created On A Master Server Replicated To A Slave?

Answer :

Yes, introduction of saved processes and features completed thru regular DDL statements on a master server are replicated to a slave, so the gadgets will exist on each servers. ALTER and DROP statements for saved methods and functions also are replicated.

Oracle DBA Interview Questions
Question 16. Can I Commit Or Roll Back Transactions Inside A Stored Procedure?

Answer :

Yes. However, you cannot perform transactional operations within a saved characteristic.

SQL Server 2008 Tutorial
Question 17. When Should The Change Buffer Not Be Used?

Answer :

You might bear in mind disabling the exchange buffer if the whole statistics set suits within the InnoDB buffer pool, if you have fairly few secondary indexes, or in case you are using strong-country storage, in which random reads are approximately as speedy as sequential reads. Before making configuration adjustments, it's far advocated which you run tests using a consultant workload to decide if disabling the change buffer affords any gain.

Oracle 11g Interview Questions
Question 18. When Should The Change Buffer Be Used?

Answer :

The trade buffer is a function designed to reduce random I/O to secondary indexes as indexes develop larger and now not fit in the InnoDB buffer pool. Generally, the exchange buffer need to be used whilst the entire facts set does no longer fit into the buffer pool, while there's big DML interest that modifies secondary index pages, or whilst there are lots of secondary indexes that are often modified with the aid of DML activity.

Python Interview Questions
Question 19. When Is The Change Buffer Flushed?

Answer :

Updated pages are flushed by the equal flushing mechanism that flushes the alternative pages that occupy the buffer pool.

Oracle DBA Tutorial
Question 20. When Does Change Buffer Merging Occur?

Answer :

When a web page is study into the buffer pool, buffered changes are merged upon finishing touch of the read, earlier than the page is made available.

Change buffer merging is completed as a heritage mission. The innodb_io_capacity parameter sets an higher restriction at the I/O activity achieved by way of InnoDB history tasks including merging statistics from the alternate buffer.

A trade buffer merge is carried out during crash recovery. Changes are applied from the alternate buffer (in the device tablespace) to leaf pages of secondary indexes as index pages are read into the buffer pool.

The alternate buffer is fully long lasting and could survive a gadget crash. Upon restart, alternate buffer merge operations resume as part of ordinary operations.

A complete merge of the trade buffer may be compelled as part of a gradual server shutdown the use of --innodb-rapid-shutdown=0.

Database Testing Interview Questions
Question 21. How Do I Determine The Current Size Of The Change Buffer?

Answer :

The modern-day size of the change buffer is pronounced with the aid of SHOW ENGINE INNODB STATUS G, under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading. For example:

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: length 1, unfastened listing len 0, seg length 2, 0 merges

Relevant records factors include:

size: The range of pages used within the trade buffer. Change buffer length is equal to seg size - (1 + free listing len). The 1 + fee represents the exchange buffer header page.

Seg length: The size of the change buffer, in pages.

Question 22. How Much Space Does Innodb Use For The Change Buffer?

Answer :

Prior to the creation of the innodb_change_buffer_max_size configuration alternative in MySQL 5.6, the most length of the on-disk alternate buffer in the machine tablespace become 1/3 of the InnoDB buffer pool size.

In MySQL five.6 and later, the innodb_change_buffer_max_size configuration alternative defines the most size of the exchange buffer as a percent of the overall buffer pool size. By default, innodb_change_buffer_max_size is ready to twenty-five. The maximum setting is 50.

InnoDB does now not buffer an operation if it might reason the on-disk trade buffer to exceed the defined limit.

Change buffer pages aren't required to persist in the buffer pool and can be evicted by means of LRU operations.

Oracle 11g Tutorial
Question 23. Does The Change Buffer Support Other Types Of Indexes?

Answer :

No. The alternate buffer only supports secondary indexes. Clustered indexes, complete-text indexes, and spatial indexes aren't supported. Full-text indexes have their very own caching mechanism.

MYSQL DBA Interview Questions
Question 24. What Is The Benefit Of The Innodb Change Buffer?

Answer :

Buffering secondary index modifications whilst secondary index pages aren't inside the buffer pool avoids highly-priced random access I/O operations that might be required to at once read in affected index pages from disk. Buffered adjustments can be carried out later, in batches, as pages are examine into the buffer pool with the aid of other examine operations.

C++ Interview Questions
Question 25. What Types Of Operations Modify Secondary Indexes And Result In Change Buffering?

Answer :

INSERT, UPDATE, and DELETE operations can alter secondary indexes. If an affected index page is not inside the buffer pool, the changes can be buffered in the trade buffer.

Database Testing Tutorial