MySql Interview Questions

Level: Freshers

Q 1 - What is MySQL? 
 

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

Q 2 - What are the features of MySQL? 

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.

Q 3 - Why MySQL is used?.

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.

Q 4 - What are the advantages of MySQL when compared with Oracle?

  • 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
     

Q 5 - Difference between CHAR and VARCHAR? 

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.

Q 6 - How to get current MySQL version?
 

SELECT VERSION (); is used to get the current version of MySQL.

Q 7 - What is TEXT?

TEXT is case-insensitive BLOB. The four types of TEXT are:
  • TINYTEXT
  •  TEXT
  •  MEDIUMTEXT
  •  LONGTEXT

Q 8 - What is the difference between primary key and candidate key?

Primary Key: 
Every row of a table is identified uniquely by primary key and there is only one primary key fore each table. Candidate keys: 
These are those keys which is candidate for primary key of a table means a key which full fill all the requirements of primary key.
 

Q 9 -  What is ISAM?

ISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval.

Q 10 - What is command to check table is exist?

  CHECK TABLE table_name;

Q 11 - What are string datatype in MySQL?

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

Q 12 - How will you obtain list of all the databases?

To list all currently running databases run the command on mysql shell as: show databases;

Q 13 - What is BLOB?

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

Q 14 - How You Will Show All Data From A Table.
 

SELECT * FROM Tablename;
 

Q 15 - How To Show Certain Selected Rows With The Value “Abcd”.

SELECT * FROM Tablename WHERE Fieldname = “Abcd”;
 

Q 16 - How You Will Show Unique Records.

SELECT DISTINCT Columnname FROM Tablename;

Q 17 - How We Will Show Selected Records Sorted In An Ascending (Asc) Or Descending (Desc).

  • Mysql> SELECT Col1,Col2 FROM Tablename ORDER BY Col2 DESC;
  • Mysql> SELECT Col1,Col2 FROM Tablename ORDER BY Col2 ASC;
     

Q 18 - How To Return Total Number Of Rows.

SELECT COUNT(*) FROM Tablename;

Q 19 - What does UNION do? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION andUNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.
It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.