MySQL interview questions and answers

Home >> MySQL interview questions and answers >>

  MySQL interview questions and answers part - 1

This article will give you an overview of MySQL questions and answers that may be helpful for you in an interview or online test.

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

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database.

Question: What is the definition of RDBMS?
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as invented by E. F. Codd, of IBM's San Jose Research Laboratory.

NOTE : It can be used for storing large amounts of data, entities are related to each other which makes it easy and quick to access results.

Question: What are the Codd's 12 rules
Rule 0: The Foundation rule
    This rule states that for a relational data base management system, that system must be able to manage data bases entirely through its relational capabilities.
    
Rule 1: The information rule
    All information(including metadata) is to be represented as stored data in cells of tables. The rows and columns have to be strictly unordered.    

Rule 2: The guaranteed access rule
    Each and every data (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.
    
Rule 3: Systematic treatment of null values
    Null has several meanings, it can mean missing data, not applicable or no value. It should be handled consistently. Primary key must not be null.

Rule 4: Active Online Catalog
    The structure description of the entire database must be stored in an online catalog, known as data dictionary, which can be accessed by authorized users. Users can use the same query language to access the catalog which they use to access the database itself.
    
Rule 5: Comprehensive Data Sub-Language Rule
    One well defined language must be there to provide all manners of access to data. Example: SQL. If a file supporting table can be accessed by any manner except SQL interface, then its a violation to this rule.
    
Rule 6: View Updating Rule
    All the views of a database, which can theoretically be updated, must also be updatable by the system.    

Rule 7 : Relational Level Operation
    There must be Insert, Delete, Update operations at each level of relations. Set operation like Union, Intersection and minus should also be supported.
    
Rule 8 : Physical Data Independence
    The physical storage of data should not matter to the system. If say, some file supporting table were renamed or moved from one disk to another, it should not effect the application.

Rule 9 : Logical Data Independence
    If there is change in the logical structure(table structures) of the database the user view of data should not change. Say, if a table is split into two tables, a new view should give result as the join of the two tables. This rule is most difficult to satisfy.    
    
Rule 10 : Integrity Independence
    A database must be independent of the application that uses it. All its integrity constraints can be independently modified without the need of any change in the application. This rule makes a database independent of the front-end application and its interface.

Rule 11 : Distribution Independence
    The end-user must not be able to see that the data is distributed over various locations. Users should always get the impression that the data is located at one site only. This rule has been regarded as the foundation of distributed database systems.

Rule 12 : Nonsubversion rule
    If a system has an interface that provides access to low-level records, then the interface must not be able to subvert the system and bypass security and integrity constraints.

Question: What are the rules for dbms to become rdbms?
If a DBMS follows more than 7 codes rules than it become rdbms.oracle follows 10 rules which is considered as ideal rdbms.no rdbms exist till now which follows all the 12 code.

Question: What is difference between DBMS vs RDBMS

DBMS

1. Introduced in 1960s.

2. During introduction it followed the navigational modes (Navigational DBMS) for data storage and fetching.

3. Data fetching is slower for complex and large amount of data.

4. Used for applications using small amount of data.

5. Data Redundancy is common in this model leading to difficulty in maintaining the data.

6. Example systems are dBase, Microsoft Acces, LibreOffice Base, FoxPro.

RDBMS

1. Introduced in 1970s.

2. This model uses relationship between tables using primary keys, foreign keys and indexes.

3. Comparatively faster because of its relational model.

4. Used for huge applications using complex and large amount of data.

5. Keys and indexes are used in the tables to avoid redundancy.

6. Example systems are SQL Server, Oracle, MySQL, MariaDB, SQLite.

Question: What are the technical features of MySQL?
The following list shows the most important features of MySQL.

1. Relational Database System: Like almost all other database systems on the market, MySQL is a relational database system.     

2. Client/Server Architecture: MySQL is a client/server system. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they query data, save changes, etc. The clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).

3. SQL compatibility: MySQL supports as its database language, SQL is a standardized language for querying and updating data and for the administration of a database.

4. Views: Views relate to an SQL query that is viewed as a distinct database object and makes possible a particular view of the database. 

5. Stored procedures: Here we are dealing with SQL code that is stored in the database system.

6. Triggers: Triggers are SQL commands that are automatically executed by the server in certain database operations (INSERT, UPDATE, and DELETE).

7. Full-text search: Full-text search simplifies and accelerates the search for words that are located within a text field.

8. Replication: Replication allows the contents of a database to be copied (replicated) onto a number of computers. In practice, this is done for two reasons: to increase protection against system failure (so that if one computer goes down, another can be put into service) and to improve the speed of database queries.

9. Transactions: In the context of a database system, a transaction means the execution of several database operations as a block. The database system ensures that either all of the operations are correctly executed or none of them.

10. Foreign key constraints: MySQL supports foreign key constraints for InnoDB tables.

Question: 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.

Question: What are Heap tables?
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

Question: What is the difference between heap table and temporary table?

Heap tables:
Heap tables are found in memory. They are used for high speed storage on temporary basis. 

Temporary tables:
The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.

Main differences:
The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

Create an Heap Table

Example:
CREATE TABLE IF NOT EXISTS `heap_demo_table` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MEMORY;

Create an Temporary Table

Example:
CREATE TEMPORARY TABLE IF NOT EXISTS  AS (SELECT * FROM )

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Question: What is MySQL data directory? How to determine the location of the data directory.

MySQL data directory is most important location in which all MySQL databases are stored. The default data directory is located in "data" folder inside mysql, inside "data" directory you can see the same folder name as your database name.

 Example:
 // For Example let me show the data folder path in WAMP server.
 wamp\bin\mysql\mysql\data

Question: What is the default port for MySQL Server?
The default port for MySQL server is 3306.

Question: 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.

Question: Differentiate between FLOAT and DOUBLE? 
They both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers.

Question: Differentiate CHAR_LENGTH and LENGTH?
LENGTH() returns the length of the string measured in bytes. 
CHAR_LENGTH() returns the length of the string measured in characters.

Question: What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values and that can be used during table creation.

 Example:
 CREATE TABLE months (name ENUM('JAN', 'FEB'));
 
 ALTER TABLE `technology` ADD `enum_test` ENUM('PHP', 'JAVA', 'Android') NOT NULL AFTER `technology_content`;

 

Comments

appreciation

Hi There,


Gratitude for putting up this prolific article! You truly make everything a cake walk. Genuinely good stuff, saving time and energy.

I have a table whereby I would like to delete rows based on 2 different issues. My table below has records I would like to delete whereby if I have an issue LC logged and then issue Error is logged the same day or after, then delete those 2 rows.
CREATE TABLE #TEST
(
ID INT,
DT DATETIME,
ISSUE VARCHAR(MAX)

)

Insert into #TEST Values(1,'4/2/18 11:06 AM','SC')
Insert into #TEST Values(1,'4/2/18 11:05 AM','Error')
Insert into #TEST Values(1,'4/2/2018 10:10:15 AM','Rqst')
Insert into #TEST Values(1,'4/2/18 9:48 AM','LC')
Insert into #TEST Values(2,'4/10/18 11:49 AM','Rqst')
Insert into #TEST Values(2,'4/10/18 8:22 AM','SC')
Insert into #TEST Values(2,'4/10/18 8:22 AM','Error')
Insert into #TEST Values(2,'4/9/18 11:07 AM','LC')
Insert into #TEST Values(2,'4/5/18 8:40 AM','Rqst')
Insert into #TEST Values(2,'4/5/18 8:24 AM','Rqst')
Insert into #TEST Values(2,'4/3/18 12:45 PM','Rqst')
Insert into #TEST Values(2,'4/2/18 11:06 AM','SC')
Insert into #TEST Values(3,'4/2/18 11:05 AM','Error')
Insert into #TEST Values(3,'4/2/18 10:10 AM','Rqst')
Insert into #TEST Values(3,'4/2/18 9:48 AM','LC')






Awesome! Thanks for putting this all in one place. Very useful!


Thank you,
Irene Hynes

  2 months ago      Irene Hynes      

Write your comment now