Mysql Interview Questions and Answers

Answer: Following tables (Storage Engine) we can create 1. MyISAM(The default storage engine IN MYSQL Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension. ) 2. InnoDB(InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.) 3. Merge 4. Heap (MEMORY)(The MEMORY storage engine creates tables with contents that are stored in memory...

Answer: A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side.Triggers will ...

Answer: If the operating system or filesystem places a limit on the number of files in a directory, MySQL is bound by that constraint.The efficiency of the operating system in handling large numbers of files in a directory can place a practical limit on the number of tables in a database. If the time required to open a file in the directory increases significantly as the number of files increases, database performance can be adversely affected. The amount of available disk space limits the number of tables. MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 6553...

Answer: Major differences between the having and where clause are: 1>where clause is not used with aggregate function like sum, avg etc but having we can 2>having clause is slow filtering the results of aggregate functions in the result set of the query as compare to where clause used to comparing values in the base table as it can be  call on index 3>The difference between the two is in the relationship to the GROUP BY clause: WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records. HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.   The difference between the having and where ...

Answer: MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)

Answer: MySQL database software is a client or server system which includes Multithreaded SQL server supporting various client programs and libraries Different backend Wide range of application programming interfaces and Administrative tools.

Answer: MySQL database server is reliable, fast and very easy to use.  This software can be downloaded as freeware and can be downloaded from the internet.

Answer: HEAP tables are present in memory and they are used for high speed storage on temporary basis. • BLOB or TEXT fields are not allowed • Only comparison operators can be used =, <,>, = >,=< • AUTO_INCREMENT is not supported by HEAP tables • Indexes should be NOT NULL

Answer: The default port for MySQL server is 3306.

Answer: MySQL is open source software which is available at any time and has no cost involved. MySQL is portable GUI with command prompt. Administration is supported using MySQL Query Browser

Answer: CHAR_LENGTH  is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

Answer: ENUMs and SETs are used to represent powers of two because of storage optimizations.

Answer: ENUM is a string object used to specify set of predefined values and that can be used during table creation. Create table size(name ENUM('Small', 'Medium','Large'); 1 Createtablesize(nameENUM('Small','Medium','Large');  

Answer: REGEXP is a pattern match in which  matches pattern anywhere in the search value.

Answer: Following are the differences between CHAR and VARCHAR: CHAR and VARCHAR types differ in storage and retrieval CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255 When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

Answer: The string types are: SET BLOB ENUM CHAR TEXT VARCHAR

Answer: 1 SELECTVERSION(); is used to get the current version of MySQL.

Answer: Storage engines are called table types and data is stored in files using various techniques. Technique involves: Storage mechanism Locking levels Indexing Capabilities and functions.

Answer: TIMESTAMP column is updated with Zero when the table is created.  UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to  current time whenever there is a change in other fields of the table.

Answer: Every row of a table is identified uniquely by primary key. There is only one primary key for a table. Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

Answer: Federated tables which allow access to the tables located on other databases on other servers.

Answer: Timestamp field gets the current timestamp whenever the row gets altered.

Answer: It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

Answer: LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.

Answer: % corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

Answer: UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

Answer: The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

Answer: Number of rows can be obtained by SELECT COUNT (user_id) FROM users; 1 SELECT COUNT(user_id)FROMusers;

Answer: LIKE and REGEXP operators are used to express with ^ and %.   SELECT * FROM employee WHERE emp_name REGEXP "^b"; SELECT * FROM employee WHERE emp_name LIKE "%b"; 1 2 SELECT *FROM employee WHERE emp_name REGEXP"^b"; SELECT *FROM employee WHERE emp_name LIKE"%b";

Answer: If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

Answer: BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

Answer: You need the following tools to install a MySQL binary distribution: GNU gunzip to uncompress the distribution.A reasonable tar to unpack the distribution. GNU tar is known to work. Sun tar is known to have problems.An alternative installation method under Linux is to use RPM (RedHat Package Manager) distributions.If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `bi...

Answer: A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep reissuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table ...

Answer: Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables: SELECT * from table1,table2 where table1.id = table2.id; The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors' CREATE TABLE commands; it doesn't do anything. The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is so...

Answer: Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.MySQL uses Access Control Lists (ACLs) security for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many...

Answer: When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication. All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (i...

Answer: It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method: Never give a normal user access to the mysql.user table. Knowing the encrypted password for a user makes it possible to login as this user. The passwords are only scrambled so that one shouldn't be able to see the real password you used (if you happen to use a similar password with your other applications).Use a -pyour_pass or --password=your_pass option on the command line. This is convenient but in...

Answer: Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database (that is, in the database named mysql).The names used in this manual to refer to the privileges provided by MySQL are shown below, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies:Privilege Column Contextselect Select_priv tablesinsert Insert_priv tablesupdate Update_priv tablesdelete Delete_priv tablesindex Index_priv tablesalter Alter_priv tablescreate Create_priv databases, tables, or indexesdrop Drop_priv databases or tablesgrant Gran...

Answer: SELECT VERSION();

Answer: The name “MySQL” is sometimes used to refer to the MySQL database management system and sometimes to the company behind it. The company is more properly known as MySQL AB. MySQL is the DBMS product that MySQL AB owns, develops, and sells-that is, the database server software called “MySQL” and related products, such as client programs for communicating with the server and programming interfaces for developing new clients.As a side note, the “AB” part of the company name is the acronym for the Swedish “aktiebolag,” or “stock company.” Thus, the name translates to “MySQL Inc.&rdq...

Answer: Select the table from the table list on the left side of the SQL screen. The table appears in a spreadsheet like format in the main part of the screen. We can edit the contents of the table in this window. Edit or delete a record by clicking the appropriate icon displayed near the record. Add a row by clicking the corresponding link near the bottom of the table. Leave the cell you edited or press the Enter key. Any changes you make on the table data are automatically converted into the appropriate SQL code.

Answer: CHAR VARCHAR TINY TEXT TEXT MEDIUM TEXT LONG TEXT BINARY VARBINARY TINYBLOB MEDIUMBLOG BLOB LONGBLOB ENUM SET

Answer: SELECT can serve to evaluate expressions, but it’s more commonly used to retrieve values from a table. To retrieve information from a table, it’s necessary to identify the table by adding a FROM table_name clause following the list of values to display.

Answer: Comments and portability Case-sensitivity Escape characters Naming limitations Quoting Time zones Character sets and collations

Answer: There are five levels that privileges can be granted. Global - Global privileges apply to all databases on a MySQL server. These privileges are stored in the mysql.user table. Database - Database privileges apply to all objects of a specified database. These privileges are stored in the mysql.db and mysql.host tables. Table - Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. The GRANT ALL ON db_name.table_name and REVOKE ALL ON db_name.table_name commands grant and revoke only table level privileges. Column - Column level privileges apply to one or more columns in a given table...