What is the default port of MySQL Server?
The default port of MySQL Server is 3306.
How is the MyISAM table stored?
MyISAM table is stored on disk in three formats.
- ‘.frm’ file : storing the table definition
- ‘.MYD’ (MYData): data file
- ‘.MYI’ (MYIndex): index file
What is the usage of ENUMs in MySQL?
ENUMs are string objects. By defining ENUMs, we allow the end-user to give correct input as in case the user provides an input that is not part of the ENUM defined data, then the query won’t execute, and an error message will be displayed which says “The wrong Query”. For instance, suppose we want to take the gender of the user as an input, so we specify ENUM(‘male’, ‘female’, ‘other’), and hence whenever the user tries to input any string any other than these three it results in an error.
ENUMs are used to limit the possible values that go in the table:
For example:
CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’); INSERT months VALUES (‘April’).
MySQL Interview Question
What are the advantages of MyISAM over InnoDB?
MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compressed if required. On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.
What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?
Mysql_fetch_object is used to retrieve the result from the database as objects, while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.
For example:
Using mysql_fetch_object field can be accessed as $result->name.
Using mysql_fetch_array field can be accessed as $result->[name].
Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.
- $result = mysql_query(“SELECT * from students”);
- while($row = mysql_fetch_row($result))
- {
- Some statement;
- }
What is the use of mysql_close()?
Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().
Advance MySQL Interview Question
What is MySQL data directory?
MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default, the information managed my MySQL = server mysqld is stored in the data directory.
How do you determine the location of MySQL data directory?
The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.
What is the usage of regular expressions in MySQL?
In MySQL, regular expressions are used in queries for searching a pattern in a string.
- * Matches 0 more instances of the string preceding it.
- + matches one more instances of the string preceding it.
- ? Matches 0 or 1 instances of the string preceding it.
- . Matches a single character.
- [abc] matches a or b or z
- | separates strings
- ^ anchors the match from the start.
- “.” Can be used to match any single character. “|” can be used to match either of the two strings
- REGEXP can be used to match the input characters with the database.
Example:
The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):
- Select employee_name
- From employee
- Where employee_name REGEXP ‘1000’
- Order by employee_name
MySQL Interview Question
What is the usage of the “i-am-a-dummy” flag in MySQL?
In MySQL, the “i-am-a-dummy” flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.
Which command is used to view the content of the table in MySQL?
The SELECT command is used to view the content of the table in MySQL.
Explain Access Control Lists.
An ACL is a list of permissions that are associated with an object. MySQL keeps the Access Control Lists cached in memory, and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object, and if the permissions are available, then execution completes successfully.
What is InnoDB?
InnoDB is a storage database for SQL. The ACID-transactions are also provided in InnoDB and also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.
Advance MySQL Interview Question
What is ISAM?
It is a system for file management developed by IBM, which allows records to access sequentially or even randomly.
How can we run batch mode in MySQL?
To perform batch mode in MySQL, we use the following command:
- mysql;
- mysql mysql.out;
What are federated tables?
Federated tables are tables that point to the tables located on other databases on some other server.
MySQL Interview Question
What is the difference between primary key and candidate key?
To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.
A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.
What are the drivers in MySQL?
Following are the drivers available in MySQL:
- PHP Driver
- JDBC Driver
- ODBC Driver
- C WRAPPER
- PYTHON Driver
- PERL Driver
- RUBY Driver
- CAP11PHP Driver
- Ado.net5.mxz
What are DDL, DML, and DCL?
Majorly SQL commands can be divided into three categories, i.e., DDL, DML & DCL. Data Definition Language (DDL) deals with all the database schemas, and it defines how the data should reside in the database. Commands like CreateTABLE and ALTER TABLE are part of DDL.
Data Manipulative Language (DML) deals with operations and manipulations on the data. The commands in DML are Insert, Select, etc.
Data Control Languages (DCL) are related to the Grant and permissions. In short, the authorization to access any part of the database is defined by these.
Advance MySQL Interview Question
Why is MySQL so popular?
First of all, MySQL is open-source. Second, it is widely adopted, so a lot of code is already available. Even entire developed systems are there that can be referred to for the upcoming projects. MySQL has relational databases; hence it makes it have methodical storage rather than a big dump of unorganized mess. And finally, as said earlier, MySQL is quick and robust.
What are the tables in MySQL? Explain the types.
This is a must-know MySQL interview question. Let’s see the answer-
MySQL stores everything in logical tables. Tables can be thought of as the core storage structure of MySQL. And hence tables are also known as storage engines. Here are the storage engines provided by MySQL:
· MyISAM – MyISAM is the default storage engine for MySQL. It extends the former ISAM storage engine. MyISAM offers big storage, up to 256TB! The tables can also be compressed to get extra storage. MyISAM tables are not transaction-safe.
· MERGE – A MERGE table is a virtual table that consolidates different MyISAM tables that have a comparable structure to one table. MERGE tables use the indexes of the base tables, as they do not have indexes of their own.
· ARCHIVE – As the name suggests, Archive helps in archiving the tables by compressing them, in-turn reducing the storage space. Hence, you can store a lot of records with the Archive. It uses the compression-decompression procedure while writing and reading the table records. It is done using the Zlib library.
· CSV – This is more like a storage format. CSV engine stores the values in the Comma-separated values (CSV) format. This engine makes it easier to migrate the tables into a non-SQL pipeline.
· InnoDB – InnoDB is the most optimal while choosing an engine to drive performance. InnoDB is a transaction-safe engine. Hence it is ACID-compliant and can efficiently restore your database to the most stable state in case of a crash.
· Memory– Memory tables were formerly known as HEAP. With memory tables, there can be a performance boost as the tables are stored in the memory. But it does not work with large data tables due to the same reason.
· Federated – Federated tables allow accessing remote MySQL server tables. It can be done without any third-party integration or cluster technology.
Write a query for a column addition in MySQL
For this, an ALTER TABLE query is required. Once invoked, simply mention the column and its definition. Something like this:
ALTER TABLE cars
ADD COLUMN engine VARCHAR(80) AFTER colour;
MySQL Interview Question
What is a foreign key? Write a query to implement the same in MySQL.
A foreign key is used to connect two tables. A FOREIGN KEY is a field (or assortment of it) in one table that alludes to the PRIMARY KEY in another table. The FOREIGN KEY requirement is utilised to forestall activities that would crush joins between tables.
To assign a foreign key, it is important to mention it while creating the table. It can be assigned by invoking the FOREIGN KEY query. Something like this:
FOREIGN KEY (Any_ID) REFERENCES Table_to_reference(Any_ID)
How does database import/export work in MySQL?
It can be done in two ways. One is to use phpMyAdmin, and the second is to use the command line access of MySQL. The latter can be done by using the command named mysqldump. It goes something like this:
· mysqldump -u username -p databasename > dbsample.sql
To import a database into MySQL, only a sign change is required, with a command of MySQL. The command goes something like this:
· mysql -u username -p databasename < dbsample.sql
How can we delete a column or a row in MySQL?
Now dropping a column can be simply done by using the ALTER TABLE command and then using the DROP command. It goes something like this:
ALTER TABLE table_name DROP column name;
To drop a row, first, an identification for the row is required. Once that is handy, use the DELETE command in conjunction with the conditional WHERE command. Something like this:
DELETE FROM cars WHERE carID = 3;
Advance MySQL Interview Question
What are the different ways to join tables in MySQL?
Join is used to link one or more tables together, with the common column’s values in both tables. Primarily there are four types of joins:
1. Inner Join – Inner join uses a join predicate, which is a condition used to make the join. Here is the syntax:
SELECT something FROM tablename INNER JOIN another table ON condition;
2. Left Join – Left join also requires a join condition. The left join chooses information beginning from the left table. For each entry in the left table, the left compares each entry in the right table. Here is the syntax:
SELECT something FROM tablename LEFT JOIN another table ON condition;
3. Right Join – Opposite to left join and, with one difference in the query, that is the name of join. Here care should be taken about the order of tables. Here is the syntax:
SELECT something FROM tablename LEFT JOIN another table ON condition;
4. Cross Join – Cross join has no join condition. It makes a cartesian of rows of both the tables. Here is the syntax:
SELECT something FROM tablename CROSS JOIN another table;
Note: While dealing with just one table, self-join is also possible.
It is one of the most dealt with MySQL interview questions. Interviewers do like to see if the candidate understands the basics or not and join one of the core concepts.
What are Procedures in MySQL?
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.
What is the core difference between Oracle and MySQL?
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.
Now this question is one of the MySQL interview questions that should be understood carefully. Because it directly deals with the industry standards and what the company wants.
MySQL Interview Question
Which drivers are necessary for MySQL?
There are many types of drivers in MySQL. Mostly they are used for connections with different computational languages. Some of them are listed below:
· PHP Driver
· JDBC
· OBDC
· Python Driver
· C – Wrapper
· Perl and Ruby Drivers
What is a LIKE statement? Explain % and _ in LIKE.
While using filters in commands like SELECT, UPDATE, and DELETE, conditions might require a pattern to detect. LIKE is used to do just that. LIKE has two wildcard characters, namely % (percentage) and _ (underscore). Percentage(%) matches a string of characters, while underscore matches a single character.
For example, %t will detect trees and tea both. However, _t will only detect one extra character, i.e., strings like ti or te.
How to convert timestamps to date in MySQL?
It is a rather simple question that requires knowledge on two commands, like DATE_FORMAT and FROM_UNIXTIME.
DATE_FORMAT(FROM_UNIXTIME(`date_in_timestamp`), ‘%e %b %Y’) AS ‘date_formatted’
Advance MySQL Interview Question
Can a query be written in any case in MySQL?
This MySQL interview question often confuses people who are just getting started with MySQL. Although most of the time, the queries are written in capital or some in small letters, there is no such case sensitivity to MySQL queries.
For example, both create table tablename and CREATE TABLE tablename, works fine.
However, if required, it is possible to make the query case sensitive by using the keyword BINARY.
This MySQL interview question can be tricky, especially when asked to make the query case-sensitive explicitly.
How to save images in MySQL?
Images can be stored in the MySQL database by converting them to BLOBS. But it is not preferred due to the large overhead it creates. Plus, it puts unnecessary load on the RAM while loading the entire database. It is hence preferred to store the paths in the database and store the images on disk.
How to get multiple condition results from data in MySQL?
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.
MySQL Interview Question
What are the different file formats used by MyISAM?
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.
How does DISTINCT work in MySQL?
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;
Is there any upper limit for the number of columns in a table?
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.
Advance MySQL Interview Question
What are Access Control Lists or ACLs, in accordance with MySQL?
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.
How to make connections persistent in MySQL?
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
What are the technical 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.
MySQL Interview 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.
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
Differentiate between FLOAT and DOUBLE?
Following are differences for FLOAT and DOUBLE:
• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
Advance MySQL Interview Question
Differentiate CHAR_LENGTH and LENGTH?
CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.
How to represent ENUMs and SETs internally?
ENUMs and SETs are used to represent powers of two because of storage optimizations.
Define REGEXP?
REGEXP is a pattern match in which matches pattern anywhere in the search value.
MySQL Interview Question
Give string types available for column?
The string types are:
- SET
- BLOB
- ENUM
- CHAR
- TEXT
- VARCHAR
What storage engines are used in MySQL?
Storage engines are called table types and data is stored in files using various techniques.
Technique involves:
- Storage mechanism
- Locking levels
- Indexing
- Capabilities and functions.
