Linux Interview Question and Answer - Mysql part 4

1. Explain the difference between BOOL, TINYINT and BIT.

    

 Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
 

2. Explain the difference between FLOAT, DOUBLE and REAL. – 

    FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
 

3. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table? 

 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
 

4. What happens if a table has one column defined as TIMESTAMP? 

 That field gets the current timestamp whenever the row gets altered.
 

5. But what if you really want to store the timestamp data, such as the publication date of the article? 

 Create two columns of type TIMESTAMP and use the second one for your real data.
 

6. Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  

    The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.

7. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do? 

  On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

         

        8. Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP. 

 A default value is used on initialization, a current timestamp is inserted on update of the row.

 9. If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table? 

CHAR(3), since MySQL automatically adjusted the data type.

By: Unknown On 10:27 AM

Continue Reading

Linux Interview Question and Answer - Mysql part 3

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

     In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record. 

2. What does myisamchk do? 

 It compressed the MyISAM tables, which reduces their disk usage.
 

3. Explain advantages of InnoDB over MyISAM? 

Row-level locking, transactions, foreign key constraints and crash recovery.   

4. Explain advantages of MyISAM over InnoDB? 

    Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

5. What are HEAP tables in MySQL?

    

    HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

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

   

    MySQL config variable max_heap_table_size. 

7. What are CSV tables?

     Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
 

8. Explain federated tables ?

   

     Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
 

9. What is SERIAL data type in MySQL? 

 BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
 

10. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?

  It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

By: Unknown On 10:35 AM

Continue Reading

Linux Interview Question and Answer - Mysql part 2

1.    How do you start and stop MySQL on Windows?

net start MySQL, net stop MySQL
 

2. How do you start MySQL on Linux? 

/etc/init.d/mysql start
 

3. Explain the difference between mysql and mysqli interfaces in PHP? 

mysqli is the object-oriented version of mysql library functions.
 

4. What’s the default port for MySQL Server? 

 3306
 

5. What does tee command do in MySQL? 

 tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
 

6. Can you save your connection settings to a conf file? 

Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
 

7. How do you change a password for an existing user via mysqladmin? 

 mysqladmin -u root -p password “newpassword”
 

8. Use mysqldump to create a copy of the database? 

 mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
 

9. Have you ever used MySQL Administrator and MySQL Query Browser? 

 Describe the tasks you accomplished with these tools.
 

10. What are some good ideas regarding user security in MySQL?

There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access

By: Unknown On 10:20 AM

Continue Reading

Linux Interview Question and Answer - Mysql part 1

1. State some of the features of MYSQL?

Some of the features presented by MYSQL are
Multiple storage engines, Native storage engines, Partner developed storage engines, custom storage engines, community developed storage engines and commit grouping.
Some of these features present in MYSQL are not present in most popular RDBMS.

2. State some of the disadvantages present in MYSQL?

MYSQL standard has diverted on the standards of implementation of Null and default values. Arithmetic operations are susceptible to integer over flow. Mode of operation of SQL is set to unusual tolerant state which developers hate.

3. What happens when we don’t use Console option?

If console option is not present or used during the start of MYSQL then all the output will be written to the error log file. It is always better to use the console option because it reduces problem on the server. Error log file will be stored with an extension .err. Any text editor can read this file.

4. How to create MYSQL new users?

There are many different ways to establish users and privileges in MYSQL. Client and GRANT command assure you about a safe connection. The syntax for establishing new users and privileges is as follows
GRANT privileges ON database.* TO
‘username’@’hostname’ This can be identified by the password. Privileges can be assigned one by one or by specifying all.

5. Explain about the rules which should be followed while assigning a username?

A username has a maximum length of 16 characters. Spaces should be avoided while creating username because they are case sensitive. Hostname will be the computer from which you are going to connect. The best way to specify a username is to connect through a local host.

6. Explain about a security flaw which is present while creating a username?

Naming MYSQL databases has to be very careful because any database starting with the test name can be accessed by every one on the network. Make sure that you don’t start the databases naming with test. It should be used only for experimental purposes only.

7. State some security recommendations while using MYSQL?

Some of the security recommendations which should be followed while using MYSQL are as follows: -
1) Minimal privileges to users in the network.
2) Super and process privileges should be granted minimally.
3) File privileges should be granted minimally to administrators.
4) Validation of data and queries should be thoroughly checked.

8. Explain about database design?

Database design is also called as Data modeling. It is used for long-term management of database. This process is used to store information and to keep data for long term. Creating an efficient structure helps you to channelize information into good channels.

9. Explain about creating database?    

CREATE DATABASE command will create you a database with the assigned name by the user. This is an optional statement but when you actually assign a name it checks for similarity and gives error if it encounters one. CREATE DATABASE models help you to create classic models.

10. Explain about primary keys?

MYSQL allows only one primary key. This primary key can be used on multiple tables. There are many rules which should be followed such as it shouldn’t be null and it can never change. Primary key assigned should be unique it cannot be matched with any other keys.

11. Explain about normalization?

Applying specific rules (normal forms) to the database is the primary process. These rules should be applied in the order specified starting with the first normal form. These rules should be adhered by every database they are
1) Each column should have only one value
2) Repeating columns of data cannot be done.

12.  State two considerations which can improve the performance of MYSQL?

Two considerations which can improve the performance of MYSQL are as follows: -
1) Fixed length fields take up more space than variable length fields but they are a bit faster.
2) Size of the field should be restricted to the smallest possible value based upon the largest input value.

13. Explain about the time stamp field?

TIMESTAMP filed occurs when an INSERT and UPDATE field occurs when there is no value specified for the field. There are many behaviors for TIMESTAMP field and it depends upon the version of MYSQL.

14. Explain about MyISAM table?

This feature is a default type for tables. This table is not so much considered for transactions because it is not considered as safe but this kind of table is very fast in execution. The maximum key length is 1024 bytes and 64 keys per table. Size of this table entirely depends upon the operating system.

15. Explain about HEAP table?

 This type of table is stored in the memory. Speed of execution of this table is very commendable. There are associated disadvantages associated with this table the primary one being loss of stored memory which occurs when there is power failure and can cause the server to run out of memory. Columns with AUTO_INCREMENT, TEXT characteristics and BLOB are not supported.

By: Unknown On 12:45 PM

Continue Reading