My SQL Interview Questions and Answers
Question - 101 : - What is the core difference between Oracle and MySQL?
Answer - 101 : -
The core difference is that MySQL works on a single-model database. That means it can only work with one base structure, while Oracle is a multi-model database. It means it can support various data models like graph, document, key-value, etc.
Another fundamental difference is that Oracle’s support comes with a price tag for industrial solutions. While MySQL is open-source.
Question - 102 : - How to add users in MySQL?
Answer - 102 : -
To simply put, the user can be added by using the CREATE command and specifying the necessary credentials. First, log in to the MySQL account and then apply the syntax. Something like this:
CREATE USER ‘testuser’ IDENTIFIED BY ‘sample password’;
Users can be granted permissions, by the following commands:
GRANT SELECT ON * . * TO ‘testuser’;
Question - 103 : - What is a trigger in MySQL?
Answer - 103 : -
A trigger is a table-associated database object in MySQL. It is activated when a specified action takes place.
A trigger can be invoked after or before the event takes place. It can be used on INSERT, DELETE, and UPDATE. It uses the respective syntax to define the triggers. For example, BEFORE INSERT, AFTER DELETE, etc.
Question - 104 : - What are Procedures in MySQL?
Answer - 104 : -
Procedures (or stored procedures) are subprograms, just like in a regular language, embedded in the database. A stored procedure consists of a name, SQL statement(s) and parameters. It utilises the caching in MySQL and hence saves time and memory, just like the prepared statements.
Question - 105 : - How to get multiple condition results from data in MySQL?
Answer - 105 : -
There are two ways to do so. The first is to use the keyword OR while using the WHERE condition. The other is to use a list of values to check and use IN with WHERE.
Question - 106 : - What are the different file formats used by MyISAM?
Answer - 106 : -
Typically, a MyISAM table is stored using three files on disk. The data file and the index file, which are defined with extensions .MYD and .MYI, respectively. There is a table definition file that has .frm extension.
Question - 107 : - How does DISTINCT work in MySQL?
Answer - 107 : -
DISTINCT is used to avoid the problem of duplicity while fetching the results of a particular query. DISTINCT is used to make sure the results do not contain repeated values. DISTINCT can be used with the SELECT clause. Here is the syntax for it:
SELECT DISTINCT something FROM tablename;
Question - 108 : - Is there any upper limit for the number of columns in a table?
Answer - 108 : -
Although the exact size limitation depends on many factors, MySQL has a hard limit on max size to be 4096 columns. But as said, for a given table, the effective-maximum may be less.
Question - 109 : - What are Access Control Lists or ACLs, in accordance with MySQL?
Answer - 109 : -
The ACLs or Access control lists are used in a way to give a guideline for security in the MySQL database. MySQL provides security based on ACLs for all the tasks performed by users like connection requests, queries, and any other operation.
Question - 110 : - How to make connections persistent in MySQL?
Answer - 110 : -
While making a connection request, if Mysql_pconnect is used rather than mysql_connect, then it can make the connection persistent. Here ‘p’ means persistent. The database connection is not closed every time.