Mysql Interview Question and Answer
Mysql is a widely used database. Currently, it is a widely used database. Here We are putting a few MySQL questions and answers, which generally asked.
1. What is Mysql?
Ans:- Mysql is widely used, 2nd largest, most popular open-source database. Which we can use free and distribute it.
2. Difference between Mysql and SQL Server?
Ans:- Mysql is an Internet server and open-source software. It is a high-speed database.
SQL Server is a Corporate and Enterprise market place software. It uses Administrator graphic modeling, so its speed is slow.
3. Difference between Mysql and Oracle?
Ans:- Main difference between MySQL and Oracle is:-
1. Oracle Database is an object-relational database management system (ORDBMS). MySQL is an open-source relational database management system (RDBMS).
2. MySQL is great for database enabled websites while Oracle is made for enterprises.
3. MySQL is a free, fast, reliable, open-source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
4. MySQL is not so efficient for large scale databases. But Oracle is efficient for large scale database.
5. Mysql takes very low memory but oracle takes a huge amount of memory.
4. In which language MySQL is written?
Ans:- Mysql is written in C/C++ and its SQL parser is written in Yaccc
5. Write the technical specification of Mysql?
Ans:- Following are the technical specification of Mysql:-
• Flexible structure
• High performance
• Manageable and easy to use
• Replication and high availability
• Security and storage management
6. Difference between SQL and Mysql?
Ans:- SQL is a Structure query language, Which interacts with databases like Mysql.
Mysql is a database, which stores data in a specific format and keeps it safe.
7. What is the difference between the database and the table?
Ans:- Database is a collection of tables. The table is a way to represent the division of data in a database.
8. Why do we use the Mysql server?
Ans:- Mysql server is free, fast, reliable and easy to use.
9. How many database engines in Mysql?
Ans:- In Mysql several database engines are available. Some popular database engines are:-
10. Define DDL, DML, DCL?
Ans:- DDL:- DDL stands for Data definition language. It deals with database schemas as well as the description of how data resides in the database. Example:- CREATE Table command.
DML:- DML stands for Data Manipulation Language. Example :- SELECT, INSERT etc.
DCL:- DCL stands for Data Control Language. Example:- GRANT, REVOKE, etc.
11. What is the difference between CHAR and VARCHAR?
Ans:- When the table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1-255. VARCHAR command is given to adjust the column and table length as required.
12. What is the difference between Truncate and Delete table?
Ans:- Truncate command delete every row of the table permanently. So, using this command carefully.
Delete command delete the only row of data of a particular table.
13. Write all possible triggers in Mysql?
Ans:- There are following triggers in Mysql:-
1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete
14. Define the heap table in Mysql?
Ans:- A Mysql table which presents in memory is called a heap table. It is a temporary table. It does not use TEXT and BLOB data types. For creating the Heap table. It required to write TYPE HEAP.
15. Explain BLOB and TEXT data type in Mysql?
Ans:- BLOB is an acronym stands for a binary large object. It is used to hold a variable amount of data.
There are four types of a BLOB.
The differences among all these are the maximum length of values they can hold.
TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.
There are four types of TEXT.
16. Define Trigger in SQL?
Ans:- Trigger is a set of code which executes after some event.
17. What is the difference between the heap and temporary tables in MySQL?
Ans:- Heap table is a memory table. It is another storage engine. The heap table is shared among the client. The heap table can only delete when restarting the database.
The temporary table will be useful when storing data temporarily. When the current client session will terminate then the temporary table will delete. That’s the reason a temporary table cannot be shared among the client. For creating a temporary table its required special privilege “create temporary table”.
18. What is the difference between Float and Double?
Ans:- Float store floating-point number with accuracy 8 place and take 4 bytes. Double store floating-point number with accuracy 16 place and take 8 bytes.
19. What is the difference between CHAR and VARCHAR?
Ans:- CHAR is fixed length datatype and VARCHAR is a variable-length data type.
When the size of the column data entries is consistent then we should use CHAR.
When the size of the column data entries is not consistent then we should use VARCHAR.
20. What is the difference between VARCHAR and TEXT?
Ans:-TEXT is a fixed-length data type. The TEXT is useful for storing long-form text strings that can take from 1 byte to 4 GB. Mysql provides four text types:- TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. In-text we cannot put an index (except for a full-text index)
VARCHAR is a variable max size of M characters where M is between 1 and 65535. VARCHAR can be part of the index.
21. What is the difference between TEXT and BLOB?
Ans:- BLOB uses a binary format for storing data but TEXT use string format for storing data.
Mysql provides four BLOB type:- TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB
Mysql provides four text types:- TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.
22. What is the difference between BLOB and CLOB?
Ans:- BLOBs are used to store large objects in binary format.
CLOBs are used to store character data. It is used in mainly Oracle.
23. Difference between MySQL connect and MySQL pconnect?
Ans:- mysql_connect() and mysql_pconnect() both are used for MySQL database connection.
mysql_coonect() is required to open and close the database connection, depending on the request.
mysql_pconnect() never close connection because it is persistent connection. It is useful for a very high traffic site.
24. What is the use of the ” i_am_a_dummy flag” in MySQL?
Ans:- if MySQL engine enables ” i_am_a_dummy flag” then without WHERE clause UPDATE and DELETE not execute.
25. What is the default port of MySQL?
Ans:- Default port of MySQL is 3306 But for the SQL server TCP/IP port is 1433
26. Tell the different sets of operations in Mysql?
Ans:- There are following a set of Operations in Mysql:-
UNION:- It returns all the distinct rows, which selected by the query.
UNION ALL:- It returns all rows including duplicate rows, selected by a query.
MINUS:- in our query select two tables t1 and t2 than in MINUS operation will return all rows of t1 distinct value.
INTERSECT:- In our query, Select two table t1 and t2 then In INTERSECT operation all common distinct rows of table t1 and t2.
27. What is the importance of Database testing?
Ans:- Database testing is one of the major testings, It mainly tests tables, writing query and procedures.
28. What is database white box testing?
Ans:- White box testing is also called glass box testing. It mainly tests the internal structure of the database. It test coding error, database consistency. It involves data module, trigger, views, query, functions, etc.
29. What is database black box testing?
Ans:- Black box testing mainly involved in the interface. It tests incoming and outgoing queries, Mapping of data, etc.
30. what is functional database testing?
Ans:- White box and Black box database testing is commonly known as Functional database testing.
31. What is object oriented version of mysql?
Ans:- Mysqli is an object Oriented version of Mysql and it interface with PHP.
32. What is Data Mining?
Ans:- Data Mining is a subcategory of Computer Science which aims at extraction of information from set of data and transform it into Human Readable structure, to be used later.
33. What is an ERD?
Ans:- ERD stands for Entity Relationship Diagram. Entity Relationship Diagram is the graphical representation of tables, with the relationship between them.
34. What are the differences between a primary key and foreign key?
Ans:- The primary key uniquely identifies a record, whereas foreign key refers to the primary key of another table.
The primary key can never accept a NULL value but foreign key accepts a NULL value.
35. What is the difference between Primary Key and Unique Key?
Ans:- Both Primary and Unique Key is implemented for Uniqueness of the column.
Primary Key creates a clustered index of column whereas a Unique creates a un-clustered index of the column.
Primary Key doesn’t allow NULL value, however, Unique Key does allow one NULL value.
36. What is the difference between primary key and candidate key?
Ans:- 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, the candidate key can be designated as primary and which can be used for any foreign key references.
37. What is the function of mysqldump?
Ans:- mysqldump is an useful utility tool of MySQL that is used to dump one or more or all databases from the server for backup or transfer to another database server.
For a single database,
mysqldump [OPTIONS] db_name [TABLES]
For multiple databases,
mysqldump [OPTIONS] –databases DB1 [DB2 DB3…]
For all databases,
mysqldump [OPTIONS] –all-databases
38.What is Data Warehousing?
Ans:- A Data Warehousing generally refereed as Enterprise Data Warehousing is a central Data repository, created using different Data Sources.
39.What are the drivers in MySQL?
Ans:- Following are the drivers available in MySQL:
40.What is the difference between MyISAM Static and MyISAM Dynamic?
Ans:- In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.
41. How many TRIGGERS are allowed in MySql table?
Ans:- SIX triggers are allowed in MySql table. They are as follows:
BEFORE DELETE and
42. Explain Access Control Lists?
Ans:- Access Control List(ACL) is a list that control Object permissions, determine which user can have what permission for execution.
Mysql permission work in two-stages first connects to the server and second access data using username and password.
List of MySQL permission are:- grant, select, update, insert, delete, create user, Revoke, etc.
43. What are indexes in a Database. What are the types of indexes?
Ans:- A database index is a data structure that improves the speed of operations in a table.
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.
44. How many TRIGGERS are possible in MySql?
Ans:- MySQL triggers are used for invoked in response to the INSERT, UPDATE or DELETE event. Mysql support two types of triggers: row-level triggers and statement-level triggers. A row-level trigger is activated for each row that is inserted, updated, or deleted. And A statement-level trigger is executed once for each transaction regardless of how many rows are inserted, updated, or deleted.
45. What is a CSV table?
Ans:- The CSV storage engine stores data in text files using comma-separated values format. The CSV storage engine is always compiled into the MySQL server. When you create a CSV table, the server creates a plain text data file having a name that begins with the table name and has a .CSV extension.
46. Explain the difference between delete and truncate.
Ans:- TRUNCATE are DDL commands, whereas DELETE is a DML command.
DELETE operations can be rolled back, while TRUNCATE operations cannot be rolled back.
The TRUNCATE TABLE statement is used to remove all records from a table in MySQL. It performs the same function as a DELETE statement without a WHERE clause.
The DELETE command is used to remove rows from a table based on WHERE condition. It maintains the log, so it slower than truncates.
47. What is a transaction? Describe MySQL transaction properties.
Ans:- A transaction is a logical unit of work that contains one or more SQL statements.
MySQL transaction allows you to execute a set of MySQL operations to ensure that the database never contains the result of partial operations.
These statements provide control over the use of transactions:
START TRANSACTION or BEGIN to start a new transaction.
COMMIT commits the current transaction, making its changes permanent.
ROLLBACK rolls back the current transaction, canceling its changes.
SET autocommit disables or enables the default autocommit mode for the current session.
In MySQL default auto-commit mode enable
48. What is CTE?
Ans:- A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.
49. What is the difference between B-Tree and Hash Indexes?
Ans:- A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
Hash index is used only for equality comparisons that use the = or <=> operators. They are not used for comparison operators such as < that find a range of values.
The optimizer cannot use a hash index to speed up ORDER BY operations. MySQL cannot determine approximately how many rows there are between two values. This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.
Only whole keys can be used to search for a row in hash index. But in a B-tree index, any leftmost prefix of the key can be used to find rows.
50. What is Normalization and list the different types of normalization?
Ans:- Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)