1. Question:What is query caching? 

    Answer
    Query caching, available with the version 4.0.1 release, greatly improves the performace of selection queries by storing query results in memory and retrieving those results directly, rather than repeatedly querying the database for the same result set.

    1. Report
  2. Question:Name all storage engines that are available in MySQL. 

    Answer
    1. MyISAM
    2. IBMDB2I
    3. InnoDB
    4. MEMORY
    5. MERGE
    6. FEDERATED
    7. ARCHIVE
    8. CSV
    9. EXAMPLE
    10. BLACKHOLE

    1. Report
  3. Question:Briefly describe the following Engines: a. MyISAM b. InnoDB c. MEMORY  

    Answer
    MyISAM:
    1. MyISAM become MYSQL's default storage engine as of version 3.23. 
    2. MyISAM tables are operating system independent.
    3. Capable of sorting more data, but at a cost of less storage space than counterpart.
    4. Three MyISAM formats: static, dynamic, and compress are available.
    
    InnoDB
    1. Robust transactional storage engine
    2. Working with large data stores.
    3. It has been available to MySQL users since version 3.23 and effective solution for transactional applications
    
    MEMORY:
    1. To attain the fastest response time possible, the logical storage media is system memory.
    2. Sorting table data in memory does indeed offer impressive performance.
    3. If the mysqld daemon crashes, all MEMORY data will be lost.

    1. Report
  4. Question:Classify the MySQL datatypes. 

    Answer
    MySQL broken down datatypes in three broad categories: a) Date and time b) Numeric data types c) String data types
    
    a) Data types in Date and time  category:
    1. DATE
    2. DATETIME
    3. TIME
    4. TIMESTAMP
    5. YEAR
    
    b) Data types in Numeric  category:
    1. BOOL, BOOLEAN
    2. BIGINT
    3. INT
    4. MEDIUMINT
    5. SMALLINT
    6. TINYINT
    7. DECIMAL
    8. DOUBLE
    9. FLOAT ([M,D])
    10. FLOAT (precision)
    
    c) Data types in String category:
    1. CHAR
    2. VARCHAR
    3. LONGBLOB
    4. LONGTEXT
    5. MEDIUMBLOB
    6. MEDIUMTEXT
    7. BLOB
    8. TEXT
    9.TINYBLOB
    10. TINYTEXT
    11. ENUM
    12. SET

    1. Report
  5. Question:Name all the MySQL data type attributes. 

    Answer
    MySQL supported data type attributes are:
    1. AUTO_INCREMENT
    2. BINARY
    3. DEFAULT
    4. INDEX
    5. NATIONAL
    6. NOT NULL
    7. NULL
    8. PRIMARY KEY
    9. UNIQUE
    10. ZEROFILL

    1. Report
  6. Question:Write the examples for the following mysql commands on the table. a) SHOW b) CREATE c) INSERT d) UPDATE e) SELECT f) ALTER g) DESC h) DESCRIBE i) DROP j) DELETE 

    Answer
    a) example:
     mysql> show tables;
    
    b) example:
    mysql>create table book(
        id int(10) not null auto_increment primary key,
        title varchar(50) not null,
        author varchar(50)
    );
    
    c) example:
    mysql>insert into book(title,author)values('MySQL','Mikel Jone');
    
    d) example:
    mysql>update book set title='Advanced MySQL' where id=1;
    
    e) example:
    mysql>select id, title, author from book;
    
    f) example:
    mysql>alter table book add column isbn varchar(20);
    
    g) example:
    mysql>desc book;
    
    h) example:
    mysql>describe book
    
    i) example:
    mysql>drop table book;
    
    j) example:
    mysql>delete from book where id=3;

    1. Report
  7. Question:What is transaction? 

    Answer
    A transaction is an ordered group of database operations that are treated as a single unit. Successful transaction will be committed and unsuccessful transaction will be rolled back.

    1. Report
  8. Question:What is cursor? Why is it used? 

    Answer
    Iterating through a result set.  Known as a cursor, it allows us to retrieve each row in the set separately and perform multiple operations on that row without warring about affecting other rows in the set.

    1. Report
  9. Question:What is the difference between primary key and unique? 

    Answer
    The PRIMARY KEY attribute is used to guarantee uniqueness for a given row. No values residing in a column designated as a primary key are repeatable or nullable within that column whereas a column assigned the UNIQUE attribute will ensure that all values possess distinct values, excenpt that NULL values are repeatable.

    1. Report
  10. Question:What are the advantages of stored routine? 

    Answer
    Stored routines have a number of advantages, which are:
    1. Consistency
    2. Performance
    3. Security
    4. Architecture

    1. Report