Oracle / MySQL Interview Questions

1. Explain about MySQL and its features.

Answer

MySQL is a relational database management system which is an open source database.

Features:

Because of its unique storage engine architecture MySQL performance is very high.
Supports large number of embedded applications which makes MySql very flexible.
Use of Triggers, Stored procedures and views which allows the developer to give a higher productivity.
Allows transactions to be rolled back, commit and crash recovery.



2. What are the disadvantages of MySQL?

Answer

MySQL does not support a very large database size as efficiently
MySQL does not support ROLE, COMMIT, and Stored procedures in versions less than 5.0
Transactions are not handled very efficiently.


3. What are the security recommendations while using MySQL?

Answer

Access to the user table should never be given to avoid SQL injection attacks.
Privileges such as GRANT and REVOKE must be made use of.
SHOW GRANTS can be used to see the list of users who have access
Never run the MySQL server as the Unix root user


4. What are MyISAM tables?

Answer

In MySQL MyISAM is the default storage engine. MyISAM tables store data values with the low byte first. Even though MyISAM tables are very reliable, corrupted tables can be expected if there is a hardware failure, the pc shuts down unexpectedly. MyISAM tables are reliable because any change made to a table is written before the sql statement returns. Even though MyISAM is the default storage engine it is advisable to specify ENGINE= MYISAM

5. Explain the difference between MyISAM Static and MyISAM Dynamic.

Ans

MyISAM Static tables have fields of fixed width while the MyISAM Dynamic can accommodate variable lengths such as TEXT, BLOB etc. MyISAM allows easier restoration of data as compared to MyISAM dynamic.

6. What does myisamchk do?

Ans 

Myisamchk gets is used to fetch information about the database tables. It is also used to check, repair and optimize the tables. From shell, Myisamchk can be invoked by typing:

Shell> myisamchk [options] tbl_name.
Here options explains what is expected from the Myisamchk.


7. Explain advantages of InnoDB over MyISAM.

Ans

InnoDb supports locking of rows while MyISAM supports only table locking.
Data integrity is more in InnoDB.
Transactional nature of InnoDB enables easy and online backups.


8. Explain advantages of MyISAM over InnoDB.

Ans

Data is not cached by the MySQL query browser.
MyISAM is a simple storage engine.
MyISAM provides more optimization.
MyISAM has a low relative memory use.


9. How to use myisamchk to check or repair MyISAM tables?

Ans 

Myisamchk gets is used to fetch information about the database tables. It is also used to check, repair and optimize the tables. From shell, Myisamchk can be invoked by typing:

Shell> myisamchk [options] tbl_name.

Here options explains what is expected from the Myisamchk.



10. How to Check MyISAM Tables for Errors?

Ans 

Using the –myisamchk table_name command can be used to find all errors. it cannot find corruption that involves only the data file. The different options are:

Myisamchk –m table_name – checks index entries for errors and calculates checksum. This checksum that is calculated for all key values of rows is verified with the keys in the index tree.

Myisamchk –e table_name – performs and extended check. does a complete and thorough check of all data

11. Explain the options of myisamchk to improve the performance of a table.

Ans

 Myisamchk has a couple of options to optimize a table.

Using myisamchk –r table_name, runs myisamchk in recovery mode. This option combines the fragmented rows and gets rid of wasted space.
--Analyze, -a is used for analyze the distribution of key values.
--sort-index, -S – Sorts index tree.
--sort-records=index_num, -R index_num – sorts recods according to particularr index.


12. Discuss about MyISAM Key Cache.

Ans

MyISAM keeps a key cache to minimize disk I/O. it keeps the most frequently accessed table blocks in memory. For most frequently accessed index blocks, key cache is used. The key cache is not used for data blocks. Multiple threads can access the cache concurrently. The size of the key cache is restricted using key_buffer_size system variable. If the value of this variable is 0, no key cache is used.

13. Discuss about MyISAM Index Statistics Collection.

Ans

MyISAM’s Index statistics collection is based on a set of rows with the same key prefix value. Here, these set of rows are called as value group. The statistics about the tables is used by the optimizer. The average value of the group size plays an improtant role. The group size is used estimate how many rows must be read for each ref access and how many rows will a particular join will produce. Myisam_stats_method system variable if set as global, affects statistics collection for all tables.


14. How many TRIGGERS are allowed in MySql table?

Ans

MySql table allows following 6 triggers:

-BEFORE INSERT
-AFTER INSERT
-BEFORE UPDATE
-AFTER UPDATE
-BEFORE DELETE and
-AFTER DELETE

15. Differentiate between FLOAT and DOUBLE.

Ans

FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.

16. Tell us something about Heap tables.

Ans

- HEAP tables are found in memory.
- They are used for high speed storage on temporary basis.

Some of their characteristics are:
- They do not allow BLOB or TEXT fields.
- Only comparison operators like =, <,>, = >,=< , can be used with them.
- AUTO_INCREMENT is not supported by HEAP tables
- Indexes should be NOT NULL

17. How do you control the max size of a HEAP table?

Ans

- Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size.

18. What are the advantages of MySQL in comparison to Oracle?

Ans

- MySQL is open source software available at zero cost.
- It is portable
- GUI with command prompt.
- Administration is supported by MySQL Query Browser

19. What does myisamchk do?

Ans

- It compresses the MyISAM tables, which reduces their disk or memory usage.

How can we convert between Unix & MySQL timestamps?

- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.

20. What is BLOB?

Ans

- BLOB stands for binary large object.
- It that can hold a variable amount of data.

There are four types of BLOB based on the maximum length of values they can hold:

- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB

21. What is TEXT?

Ans

TEXT is case-insensitive BLOB. The four types of TEXT are:

- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT

22. What is the difference between BLOB and TEXT?

Ans

- In BLOB sorting and comparison is performed in case-sensitive for BLOB values
- In TEXT types sorting and comparison is performed case-insensitive.

23. How is MyISAM table stored?

Ans

MyISAM table is stored on disk in three formats.
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file

24. Explain advantages of MyISAM over InnoDB?

Ans

- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.

25. How would concatenate strings in MySQL?

Ans

With the use of - CONCAT (string1, string2, string3)

26. How would you get the current date in Mysql?

Ans

By using SELECT CURRENT_DATE();

27. How would you enter Characters as HEX Numbers?

Ans

- To enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X)
- Alternatively, just prefix HEX numbers with (Ox).

28. How are MySQL timestamps seen to a user?

Ans

- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.