Mysql Interview Question Answers
1. What is the difference between MYSQL and SQL?
Answer: SQL is known as standard query language, as the name implies it is the language which is used to interact with the database like MySQL. Whereas, MySQL is a database that store various types of data and keep it safe. A PHP script is required to store and retrieve the values inside the database.
2. How are MySQL timestamps seen to a user?
Answer: - MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.
3. What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?
Answer: Mysql_fetch_object returns the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names. E.g. using mysql_fetch_object field can be accessed as $result->name and using mysql_fetch_array field can be accessed as $result->[name]. mysql_fetch_row($result):- where $result is the result resource returned from a successful query executed using the mysql_query() function. Example: $result = mysql_query(''SELECT * from students); while($row = mysql_fetch_row($result)) { Some statement; }
4. What is TEXT?
Answer: TEXT is case-insensitive BLOB. The four types of TEXT are: - TINYTEXT - TEXT - MEDIUMTEXT - LONGTEXT
5. Write a query to MYSQL in safe mode and to change the root password
Answer: To start MySQL in safe mode, mysqld_safe command is used which allow it to run in the safe mode and it also doesn't allow the reading of tables with the database passwords: [root@ ]# mysqld_safe --skip-grant-tables --skip-networking &[1] 13007 [root@ ]# Starting mysqld daemon with databases from /var/lib/mysql [root@ ]# After running the MySQL in safe mode the password protection gets removed and to use the password protection mechanism a command is used as follows: mysql -u root command [root@bigboy tmp]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.16 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> [ a message is being shown which allow user to take the control of the root]
6. Why to use CHAR instead of VARCHAR in the database?
Answer: CHAR is much more accurate and efficient to use. CHAR doesn't have to keep a count of the variable length. It is more efficient when you have to use it for a text column which is of an exact length. Char is used for the data which are fixed, but VARCHAR is used for data like password, which are variable.
7. How important is to list the column names when doing an INSERT?
Answer: It is not important to list the column names when doing using an INSERT command as you can provide the column information and values in the table in the same order in which they appear in the table structure. It is safer and convenient way to specify the column names as it will keep the count of the column you are visiting.
8. Explain advantages of MyISAM over InnoDB?
Answer: - 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.
9. What are ENUMs used for in MySQL?
Answer: ENUM is used to limit the possible values and store it together. It is a function that can be created to store the similar values together. It is used in creation of table. The syntax of it is as follows: CREATE TABLE months (month ENUM ''January'', ''February'', ''March'','�); INSERT months VALUES (''April'');
10. What does the file with the extension: frm, myd, and myi contain?
Answer: MySQL default table type is MyISAM, where there are three kind of files that are stored inside MyISAM. The file names begin with the table name and have the extensions such as frm, myd and myi. The explaination of each file is given below: .frm file consists of the table definition that are stored in the database .myd is an extension that is used by a data file. .myi is an extension that is used by index file.
11. Explain the difference between BOOL, TINYINT and BIT in MySQL.
Answer: BOOL: Used to store Boolean values, 0 being false and 1 being true. MySQL sets them as TINYINT type. TINYINT: The range of this data type is -128 - +127 or 0 - 256 and occupies 1 byte. BIT: Bit uses 8 bytes and stores only binary data.
12. When to use ORDER BY in DELETE statement?
Answer: The ORDER BY clause in DELETE statement is used when a deletion has to take place by in a specified order. The syntax is like this DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] ORDER BY clause is specified, the rows are deleted in the order that is specified.
13. COMMIT and ROLLBACK in MySQL
Answer: COMMIT: Commit statement commits the current transaction, which means making the changes permanent. A transaction may involve update and or delete and or insert statements. ROLLBACK: Rollback statement rolls back the present transaction, which means cancelling a transaction's changes. The statement SET AUTOCOMMIT enables or disables the committing transactions automatically for the current session.
14. How database are managed?
Answer: Database is a collection of data and it is managed by a database server, which is a special program that is also known as MySQL database server. Application that you create usually communicates with the database server in the language which it can understand; mostly SQL language is used for communication. Database server in return interacts with the web server on same server or computer. Database server and web server result in the data which is being shown on the web.
15. Primary Keys and Auto Increment Fields in MySQL
Answer: Primary key is used to uniquely identify a row in a table. Usually the primary key is an integer value that could be an auto incremented value. The primary key column can also be a combination of two columns. The column that is identified for PRIMARY key is defined in the schema as: PRIMARY KEY (employee_id) When a column is set to ''Auto Increment'', its value is automatically incremented in a sequence when a new row is inserted.
16. How many TRIGGERS are allowed in MySql table?
Answer: MySql table allows following 6 triggers: -BEFORE INSERT -AFTER INSERT -BEFORE UPDATE -AFTER UPDATE -BEFORE DELETE and -AFTER DELETE
17. Explain the difference between MyISAM Static and MyISAM Dynamic.
Answer: 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.
18. How to use myisamchk to check or repair MyISAM tables?
Answer: 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.
19. 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
20. What are the limitations of mysql in Comparison of Oracle?
Answer: • Transactions are better supported in Oracle as compared to Mysql. ROLE feature is available in Oracle and not in MySQL in versions less than 5.0 • Large Database size is supported more in Oracle in contrast to MySql.
21. Write a command to view the content of the table
Answer: To view all the data that is contained inside a table named sales use the select command. For example: to see the data of first row in a table using the command as: mysql> select * from sales limit 1; If the database is created recently then it will give a blank listing, but after the data is being entered it will show the full listing in a tabular form.
22. Write a command to view MySQL database table structure
Answer: To view the database table structure describe command is used that provides the list of all the data fields used in the database table. Example: a table named sales will have the four fields like: name, description, num, and date. mysql> describe test; | Field | Type | Null | Key | Default | Extra| | num | int(11)| |PRI | NULL | auto_increment | | date | date| | MUL | 0000-00-00 | | name | varchar(50) | MUL | | description | varchar(75) | YES | | NULL
23. Explain advantages of MyISAM over InnoDB.
Answer: • 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.
24. What are the steps required to view your MYSQL database?
Answer: There are certain steps that are required to before you can view MySQL database and they are as follows: 1. Login as database user: for example: [root@ tmp]# mysql -u mysqluser -p sales 2. List all your MySQL databases: use the show command to view the list of all available MySQL databases. For example to view sales database use the command shown below: mysql> show databases; | Database | | salesdata |
25. How will you export tables as an XML file in MySQL?
Answer: MYSQL's query browser has a provision called ''Export Result Set'' which allows the tables to be exported as XML.
26. What is the procedure to configure the application of MYSQL?
Answer: The procedure to configure the application of MySQL is: 1. First create a database 2. Test the database by informing about the database name, IP address of database client server, username and password of application. 3. Edit the special application specific configuration file using a GUI or command line. 4. Configure the language with which MySQL will interact.
27. Why phpMyAdmin is used for MYSQL?
Answer: PhpMyAdmin is a very popular and easy to use GUI tool that can allow SQL commands to be run to create database, create tables, insert data and retrieve it. It provides a web based interface to the user for the ease of use. phpMyAdmin allows user to manage everything from one place and no other installation is required in the computer after this.
28. What you can use Regular Expression for in MySQL? Support your answer with an example.
Answer: Regular expressions in MySql are used in queries for searching a pattern in a string. • * Matches 0 more instances of the string preceding it. • + matches 1 more instances of the string preceding it. • ? Matches 0 or 1instances of the string preceding it. • . Matches a single character. • [abc] matches a or b or z • | separates strings • ^ anchors the match from the start. REGEXP can be used to match the input characters with the database. Example: The following statement retrieves all rows where column employee_name contains the text 1000 (example salary): Select employee_name From employee Where employee_name REGEXP '1000' Order by employee_name ''.'' Can be used to match any single character. ''|'' can be used to match either of the two strings
29. Explain advantages of InnoDB over MyISAM.
Answer: • 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.
30. What are the different tables present in MySQL?
Answer: There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present: 1. MyISAM 2. Heap 3. Merge 4. INNO DB 5. ISAM
31. Write a query to stop MYSQL in unix
Answer: The query to stop MySQL is quite useful when an error is occurred or when data has to be saved from any mishap. It is also used for retrieving the root password because it is either easily forgotten or misplaced. To stop the service the following command is required: 1) Stop MySQL [root@ tmp]# service mysqld stop Stopping MySQL: [ OK ] [root@ tmp]#
32. What is MySQL data directory? How to determine the location of the data directory?
Answer: MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data..
33. How to find the unique values if the value in the column is repeated?
Answer: If the values in the column of a table are repeating and a unique value has to be found then the following command can be used in the query: SELECT DISTINCT user_firstname FROM users; There is another command which can be used to find the command to see the distinct values as: SELECT COUNT (DISTINCT user_firstname) FROM users;
34. How to connect a PHP script with the MySQL database?
Answer: PHP script can be connected with MySQL database by using the function called as mysqli_connect() function. You need to provide the information related to your location, username, and password to the server to get permission to interact with the MySQL database server. When database name will be asked provide the name and it will connect you to the database.
35. Explain MySQL Aggregate Functions.
Answer: Aggregate functions in MySQL are a group of functions that are used to operate on a set of values. These functions ignore NULL values unless specified. Functions like AVG(), MIN(), MAX(), COUNT() etc fall under this category. As they operate on a set of values, if no Group by clause is used, it applies to all rows.
36. Explain advantages of MyISAM over InnoDB
Answer: The following are the advantages of MyISAM over InnoDB: - MyISAM tables are stored in separate files in compressed mode, where as InnoDB tables are stored in table space. - More options for further optimization in MyISAM, where as there is no further optimization possible with InnoDB. - Data except for TEXT and BLOB can occupy at most 8000 bytes in InnoDB. - Full text indexing available in MyISAM, where as no full text indexing is available for InnoDB. - Count(*) function's execution is slower in InnoDB because of table space complexity.
37. How would you get the current date in Mysql?
Answer: By using SELECT CURRENT_DATE();
38. What is the purpose of -> in the MySQL terminal?
Answer: -> prompt in the command of MySQL indicates that a single statement is being entered across multiple lines. From this prompt MySQL interprets that you haven't finished entering the statements. It has no impact of enter which you might press to go to the next line. MySQL will execute the statement only when you will insert the semicolon in the end which it recognizes.
39. Where's database data actually stored? Is there a way to see the files which are stored?
Answer: Database data is usually get stored in the computer hard-disk and you can manage the data by the database program like MySQL and phpAdmin. The files can be seen but database files remain in binary format so it can be opened and read but, you have to put extra effort to understand it. SQL is given for the purpose of interacting with the database and read the database and retrieve the information out of it.
40. Discuss about MyISAM Key Cache.
Answer: 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.
41. What is required to create MYSQL database?
Answer: To create MySQL databse the first component which has to be present is a database server on which the queries of database will run and software tool through which you can access the applications. It also requires PHP scripts for communicating with the database using SQL commands.
42. Write a command with which MySQL table can be repaired
Answer: The command syntax with which mysql table can be repaired is as follows: REPAIR TABLE tablename; REPAIR TABLE tablename QUICK; REPAIR TABLE tablename EXTENDED; The command will just do as it says repair a specified table, but if QUICK or EXTENDED is used then the meaning of it changes. In case of QUICK it will repair only the index tree, whereas in case of EXTENDED it will create index row by row and repair it.
43. What is Query Cache in MySQL?
Answer: Query Cache in MySQL is used in scenarios when the same queries need to be executed on the same data set. These queries also return the same result. Query cache is most useful when there are tables that are not expected to change very often. It is important to note that the query cache does not return old data. If the tables are modified, any important entries in the query cache are flushed.
44. What is the use of i-am-a-dummy flag in MySQL?
Answer: Using the i-am-dummy flag makes the SQL engine refuse any Updates or deletes to execute if the WHERE clause is not present. It is very useful when using delete statements. Using i-am-dummy flag will not allow the following statement to execute: Delete from employee;
45. What are the applications required to support MYSQL?
Answer: The applications that are required to support MySQL are as follows: 1. php-mysql MySQL database is used specifically to support PHP 2. perl-DBI: provides generic Perl interface for interacting with relational databases 3. perl-DBD-MySQL MySQL database specific support for Perl 4. Web server is required to configure the database and its configuration 5. Programming language is required which supports MySQL.
46. What are the Performance and Scalability characteristics of MySQL?
Answer: MySQL has a unique storage engine architecture that makes it adaptable to most servers. MySQL meets the expectations of the most common applications, i.e. to run millions of transactions. Features like strong memory cache, indexing etc adds to the performance. MySQL being open source allows customization which allows users to add their own requirements. MySQL also supports large number of embedded applications. These features add to the scalability characteristic.
47. How is Exception Handling handled in MySQL?
Answer: Exception handling means changing the usual expected flow of the code. This needs to be done to avoid errors. The exceptions in MySql can be handled using the following syntax: DECLARE handler_type (Continue | Exit) HANDLER FOR condition_value[,...] sp_statement Here, whenever an error occurs, the handler type value will decide whether to continue or Exit. Continue will execute the next Sql statement while exit will exit the procedure. Condition_value can take number of levels of categories. set condition is used to define code to be performed if the handler is called.
48. How to Check MyISAM Tables for Errors?
Answer: 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
49. What is the difference between a database and a table?
Answer: There is a major difference between a database and a table. The differences are as follows: Tables are a way to represent the division of data in a database. Whereas, database is a collection of tables and data Tables group the data in relation with each other and create a dataset; this dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but opposite is not true.
50. What does myisamchk do?
Answer: - 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.
51. How can you make a database as your current database?
Answer: After making a database the first thing which has to be done is to create a table inside the database to test the new database that is being created. The command which is used to do that is: USE aliendatabase; This command allows us to make a database which is not a current database as my current. I have to just use the USE variable and the name of the database and it will become active for use.
52. Describe MySQL Connection using PHP Script.
Answer: Establishing connection to MySQL database using PHP can be done by using mysql_connect(server,user,passwd,new_link,client_flag). Here, server specifies the host name where the databae is present. User and password is the user name and password to connect. New_link if specified will not require a connection again. Client_flag can take a number of values like MySQL_CLIENT_SSL, MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE, MYSQL_CLIENT_INTERACTIVE. It is a good practice to close the connection using mysql_close()
53. ALTER command to add and drop INDEX in MySQL
Answer: Index in a database is used to improve the speed of operations in a table. Index can be done using single or multiple columns. There are 4 types of index commands to adding indexes to a table: ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : Adds a primary key which means adds indexes and does not allow null values. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): Creates an index whose values must be null with the exception of NULL values. ALTER TABLE tbl_name ADD INDEX index_name (column_list): Adds indexes in which one value can appear more than once ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): Adds an index for efficient text-searching purpose. Dropping indexes: To drop a primary key: ALTER TABLE testalter_tbl DROP PRIMARY KEY; To drop an index: DROP INDEX index_name ON tbl_name.
54. What is a Trigger in MySQL? Define different types of Trigger
Answer: A trigger in MySQL is a database object that is associated with a table. A trigger is fired, when a particular event occurs for the table. Triggers are used to perform validation checks on data values for insertion or to perform calculations on values for updatation. They are 2 types of triggers - Before Trigger and After Trigger. Before Trigger fires before the execution of INSERT, DELETE and UPDATE commands. After trigger fires after the execution of INSERT, DELETE and UPDATE commands.
55. What do you understand by MYSQL terminal?
Answer: MySQL terminal is used as a command line interface in many operating system. It provides a way to access the database and other resources using the SQL commands that are interpreted by the MySQL database server.
56. Tell us something about Heap tables.
Answer: - 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
57. Explain the options of myisamchk to improve the performance of a table.
Answer: 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.
58. What is the difference between Unix timestamps and MySQL timestamps?
Answer: The unix timestamp is stored as 32 bit integer whereas, MySQL timestamps are stored in 32 bit integers but represented differently then UNIX timestamps like YYYY-MM-DD HH:MM:SS format. Unix timestamp is given as month-day-year-HH:MM:SS..
59. What is the difference between truncate and delete?
Answer: Delete command is used to delete data from a table for example Remove emails where we write a script to delete the customer's data. It deletes the rows of data from a table. The syntax of it as follows: DELETE FROM table_name Whereas, truncate is very dangerous command and should be used carefully as it deletes every row from a table. The syntax of it as follows: TRUNCATE TABLE "table_name"
60. What is the difference between CHAR_LENGTH and LENGTH in MySQL?
Answer: CHAR_LENGTH, as the name suggests, returns the number of characters / character count. The LENGTH returns the number of bytes / bytes count. To count the Latin characters, both lengths are the same. To count Unicode and other encodings, the lengths are different.
61. MySQLvs. Oracle.
Answer: • Unlike oracle, mysql data types can store invalid dates and don't support time zones. • Transactions are better supported in Oracle as compared to Mysql. ROLE feature is available in Oracle and not in MySQL in versions less than 5.0 • Large Database size is supported more in Oracle in contrast to MySql. • Replication in oracle is two way while one way in mysql.
62. Differentiate between FLOAT and DOUBLE.
Answer: 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.
63. How is MyISAM table stored?
Answer: MyISAM table is stored on disk in three formats. - '.frm' file - storing the table definition - '.MYD' (MYData) - data file - '.MYI' (MYIndex) - index file
64. 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.
65. Explain the purpose of max_user_connections.
Answer: max_user_connections in MySql is used to limit the resources it limits only the number of simultaneous connections made using a single account. A value of 0 means no limit to the number of connections.
66. Transaction-Safe Table Types in MySQL.
Answer: Transaction-Safe tables allow the transaction to be compact. It should either complete the transaction or revert or roll back all changes. This property makes transaction-safe tables more safe compared to non transaction safe tables. When an update is performed and it fails, all changes are reverted. These tables can provide a better concurrency of tables.
67. Describe MySQL Connection using mysql binary.
Answer: The MySQL connection using mysql binary can be established by using the following command at os command prompt of unix. mysql -u root -p which connects to MySQL and launches mysql> prompt.
68. What is HEAP table?
Answer: Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory tables. These memory tables never have values with data type like ''BLOB'' or ''TEXT''. They use indexes which make them faster.
69. Is the syntax correct? Explain the meaning of the syntax given below
Answer: $dbc = mysqli_connect('data.aliensabductedme.com', 'owen', 'aliensrool', 'aliendatabase'); Yes the syntax is correct and this query is getting used to connect the database with a PHP script. PHP uses three functions to communicate with MySQL database: The three functions are: mysqli_connect(), mysqli_query(), and mysqli_close(). mysqli_connect() and mysqli_query() are used to connect to mysql database and issue a query to the database. mysqli_query() is also used to retrieve the string or any other data from the database. Mysqli_close() is used to close a connection with the database.
70. MySQL - Stored Procedures and Triggers
Answer: Stored Procedures: A set of SQL statements is called a stored procedure, which can be compiled and stored in the server. The purpose of stored procedures is that, reissuing the entire queries is avoided. The query is parsed only once, thus provides better performance and information passed between server and the client is very less. Triggers: A trigger is fired when a particular event occurs. It is also a stored procedure. A stored procedure can be installed which triggers automatically every time a record is deleted or updated or inserted.
71. How would you enter Characters as HEX Numbers?
Answer: - 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).
72. 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.
73. How do you control the max size of a HEAP table?
Answer: - Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size.
74. How to take MYSQL database backup?
Answer: To take the database backup use the following syntax: mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file] This command will take the database backup by knowing the username and password for the database connection and dropping any table which is being deleted or not in use. It is always a good practice to take the backup of mysql as it contains all the database information that a user can access. While using the command keep a note that there should not be any space between -p switch and password, if there is then you will get a syntax error.
75. What is the difference between BLOB and TEXT?
Answer: - In BLOB sorting and comparison is performed in case-sensitive for BLOB values - In TEXT types sorting and comparison is performed case-insensitive.
76. What areMyISAM 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
77. Write a query to create a database and a table?
Answer: MySQL comes up with some default database that can be used as a base to create a new one. The command that is used to create a new database is as follows: CREATE DATABASE SQL command The command has to be written in MySQL terminal. This command will create a new database and then you can create new tables and include data in it.
78. What is difference between mysql_connect and mysql_pconnect?
Answer: Mysql_connect() opens a new connection to the database while mysql_pconnect() opens a persistent connection to the database. This means that each time the page is loaded mysql_pconnect() does not open the database. Mysql_close() cannot be used to close the persistent connection. Though it can be used to close mysql_connect().
79. How would concatenate strings in MySQL?
Answer: With the use of - CONCAT (string1, string2, string3)
80. What are the advantages of MySQL in comparison to Oracle?
Answer: - MySQL is open source software available at zero cost. - It is portable - GUI with command prompt. - Administration is supported by MySQL Query Browser
81. Discuss about MyISAM Index Statistics Collection.
Answer: 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.
82. What is BLOB?
Answer: - 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