Top 20 MySql Interview Question & Answers

by · Published · Updated

MYSQL is the structured query language. It is the language to query the database. Here are some of the important questions being asked from beginners.

  • Q1. What is MySQL?
  • A1. MySQL is an open-source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle). 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.
  • Q2. What is the difference between DBMS and RDBMS?
  • A2.

[table caption=”Difference between DBMS and RDBMS” width=”100%” colwidth=”50%|50%” colalign=”left|left”]DBMS , RDBMS
It stores data as file, Application stores data in tabular form.
There are tables in DBMS also but there is no relation between tables in it., It maintains a relationship between tables with the help of Primary and Foreign key etc. Data is stored in the form of tables.
Data is stored either in Hierarchical or Navigational form,
Small sets of data can be managed by DBMS, RDBMS solution is required by a large set of data.
[/table]

  • Q3. What are different Storage Engines in Mysql ?
  • A3. Below are they
  1. MYISAM
  2. INNODB
  3. MEMORY
  4. CSV
  5. MERGE
  • Q4. What are the differences between Order by and Group by Clause?
  • A4.

[table caption=”Difference between Order by and Group by Clause” width=”100%” colwidth=”50%|50%” colalign=”left|left”]
ORDER BY,GROUP BY
Order by is used to sort the result either in ASCENDING and DESCENDING order, It is used to group the result with aggregate functions like Count() or Sum() or Min() or Max() or AVG() etc to group the result seet by one or more columns.
By default, its value is ASCENDING,
[/table]

  • Q5. What are DDL commands in SQL?
  • A5. DDL is the short name of Data Definition Language. DDL is a standard for commands that are used to define the different database structures.
    DDL statements create, modify, and remove database objects such as tables, indexes, and users.
    Common DDL statements are CREATE, ALTER, and DROP.
    which deals with database schemas and descriptions, of how the data should reside in the database.

    1. CREATE – It is used to create a database and its objects for e.g table, index, views, store procedure, function, and triggers.
    2. ALTER – It is used to alters the structure of the existing database.
    3. DROP – It is used to delete objects from the database.
    4. TRUNCATE – It is used to remove all records from a table, including all spaces allocated for the records are removed.
    5. COMMENT – It is used to add comments to the data dictionary.
    6. RENAME – It is used to rename an object.
  • Q6. What are DML commands in SQL?
  • A6. DML is the short name of Data Manipulation Language which deals with data manipulation. Data manipulation includes Storing, Modifying, Deleting and Updating database objects. and includes most common SQL statements such as SELECT, INSERT, UPDATE, DELETE, etc.,
    1. SELECT – It is used to fetch the data from a database.
    2. INSERT – It is used to insert data into a table.
    3. UPDATE – It is used to updates existing data within a table
    4. DELETE – It is used to Delete all records from a database table
    5. MERGE – It is used for UPSERT operation (insert or update)
    6. CALL – It is used to call a Stored procedure.
    7. EXPLAIN PLAN – It is used to interpretation of the data access path
    8. LOCK TABLE – It is used to concurrency Control
  • Q7. What are DCL commands in sql?
  • A7. DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.
  • GRANT – This command allow users to access privileges to the database.
  • REVOKE – This command allow withdraw users access privileges given by using the GRANT command.
  • Q8. What are TCL command in sql?
  • A8. TCL is short name of Transaction Control Language which deals with a transaction within a database.
  • COMMIT – It allows to save/commits a Transaction to a specific point.
  • ROLLBACK – It allows to rollback a transaction in case of any error occurs.
  • SAVEPOINT – It allows to rollback the transaction making points within groups.
  • SET TRANSACTION – It allows to specify characteristics of the transaction.
  • Q9. What are DCL commands in sql?
  • A9. DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.GRANT – allow users access privileges to the database
  • REVOKE – withdraw users access privileges given by using the GRANT command
  • TCL is short name of Transaction Control Language which deals with a transaction within a database.COMMIT – commits a Transaction
  • ROLLBACK – rollback a transaction in case of any error occurs
  • SAVEPOINT – to rollback the transaction making points within groups
  • SET TRANSACTION – specify characteristics of the transaction
  • Q10. How to get Total Numbers of rows?
  • A10. SELECT COUNT(*) from TABLE_NAME.


  • Q11. How to Delete column from a table?
  • A11. ALTER TABLE_NAME [COLUMN_NAME] DROP COLUMN [COLUMN_NAME].
  • Q12. How to Add Column for a table?
  • A12. ALTER TABLE_NAME [COLUMN_NAME] ADD COLUMN [COLUMN_NAME] [DATA_TYPE LENGTH].
  • Q13. Command to check whether table exist or not ?
  • A13. CHECK TABLE TABLE_NAME.
  • Q14. SQL query to start name with ‘A’?
  • A14. SELECT FIELD_NAME FROM TABLE_NAME WHERE FIELD_NAME LIKE ‘A%’;

Example :

SELECT NAME FROM EMPLOYEE WHERE NAME LIKE ‘A%’;

  • Q15. Which Mysql datatype should be used for storing boolean values ?
  • A15. MYSQL 5.0.3 AND HIGHER, use BIT
    Lower version 5.0.3 use bool and boolean which are alias of tinyint(1).

Also find out more Interview and Questions for getting regular information now!

Tags:

You may also like...