What is MySQL?
MySQL is a database management system for web servers. It can grow with the website as it is highly scalable. Most of the websites today are powered by MySQL.
What are some of the advantages of using MySQL?
Flexibility: MySQL runs on all operating systems
Power: MySQL focuses on performance
Enterprise-Level SQL Features: MySQL had for some time been lacking in advanced features such as subqueries, views, and stored procedures.
Full-Text Indexing and Searching
Query Caching: This helps enhance the speed of MySQL greatly
Replication: One MySQL server can be duplicated on another, providing numerous advantages
Configuration and Security
What do you mean by ‘databases’?
A database is a structured collection of data stored in a computer system and organized in a way to be quickly searched. With databases, information can be rapidly retrieved.
MySQL Interview Question
What does SQL in MySQL stand for?
The SQL in MySQL stands for Structured Query Language. This language is also used in other databases such as Oracle and Microsoft SQL Server. One can use commands such as the following to send requests from a database:
SELECT title FROM publications WHERE author = ‘ J. K. Rowling’;
Note that SQL is not case sensitive. However, it is a good practice to write the SQL keywords in CAPS and other names and variables in a small case.
What does a MySQL database contain?
A MySQL database contains one or more tables, each of which contains records or rows. Within these rows are various columns or fields that contain the data itself.
How can you interact with MySQL?
There are three main ways you can interact with MySQL:
using a command line
via a web interface
through a programming language
Advance MySQL Interview Question
What are MySQL Database Queries?
A query is a specific request or a question. One can query a database for specific information and have a record returned.
What are some of the common MySQL commands?
| Command | Action |
| ALTER | To alter a database or table |
| BACKUP | To back-up a table |
| \c | To cancel Input |
| CREATE | To create a database |
| DELETE | To delete a row from a table |
| DESCRIBE | To describe a table’s columns |
| DROP | To delete a database or table |
| EXIT(ctrl+c) | To exit |
| GRANT | To change user privileges |
| HELP (\h, \?) | Display help |
| INSERT | Insert data |
| LOCK | Lock table(s) |
| QUIT(\q) | Same as EXIT |
| RENAME | Rename a Table |
| SHOW | List details about an object |
| SOURCE | Execute a file |
| STATUS (\s) | Display the current status |
| TRUNCATE | Empty a table |
| UNLOCK | Unlock table(s) |
| UPDATE | Update an existing record |
| USE | Use a database |
How do you create a database in MySQL?
Use the following command to create a new database called ‘books’:
CREATE DATABASE books;
MySQL Interview Question
How do you create a table using MySQL?
Use the following to create a table using MySQL:
CREATE TABLE history (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)) ENGINE InnoDB;How do you Insert Data Into MySQL?
The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)If we want to add values for all the columns of the table, we do not need to specify the column names in the SQL query. However, the order of the values should be in the same order as the columns in the table. The INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
How do you remove a column from a database?
You can remove a column by using the DROP keyword:
ALTER TABLE classics DROP pages;
Advance MySQL Interview Question
How to create an Index in MySQL?
In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. You can achieve fast searches with the help of an index. Indexes speed up performance by either ordering the data on disk so it’s quicker to find your result or, telling the SQL engine where to go to find your data.
Example: Adding indexes to the history table:
ALTER TABLE history ADD INDEX(author(10));
ALTER TABLE history ADD INDEX(title(10));
ALTER TABLE history ADD INDEX(category(5));
ALTER TABLE history ADD INDEX(year);
DESCRIBE history;How to Delete Data From a MySQL Table?
In MySQL, the DELETE statement is used to delete records from a table:
DELETE FROM table_name
WHERE column_name = value_name
How do you view a database in MySQL?
One can view all the databases on the MySQL server host using the following command:
mysql> SHOW DATABASES;
MySQL Interview Question
What are the Numeric Data Types in MySQL?
MySQL has numeric data types for integer, fixed-point, floating-point, and bit values, as shown in the table below. Numeric types can be signed or unsigned, except BIT. A special attribute enables the automatic generation of sequential integer or floating-point column values, which is useful for applications that require a series of unique identification numbers.
| Type Name | Meaning |
| TINYINT | Very Small Integer |
| SMALLINT | Small Integer |
| MEDIUMINT | Medium-sized Integer |
| INT | Standard Integer |
| BIGINT | Large Integer |
| DECIMAL | Fixed-point number |
| FLOAT | Single-precision floating-point number |
| DOUBLE | Double-precision floating-point number |
| BIT | Bit-field |
What are the String Data Types in MySQL?
| Type Name | Meaning |
| CHAR | fixed-length nonbinary(character) string |
| VARCHAR | variable-length nonbinary string |
| BINARY | fixed-length binary string |
| VARBINARY | variable-length binary string |
| TINYBLOB | Very small BLOB(binary large object) |
| BLOB | Small BLOB |
| MEDIUMBLOB | Medium-sized BLOB |
| LONGBLOB | Large BLOB |
| TINYTEXT | A very small nonbinary string |
| TEXT | Small nonbinary string |
| MEDIUMTEXT | Medium-sized nonbinary string |
| LONGTEXT | Large nonbinary string |
| ENUM | An enumeration; each column value is assigned, one enumeration member |
| SET | A set; each column value is assigned zero or more set members |
| NULL | NULL in SQL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. This value is different than a zero value or a field that contains spaces. |
What are the Temporal Data Types in MySQL?
| Type Name | Meaning |
| DATE | A date value, in ‘ CCYY-MM-DD ‘ Format |
| TIME | A Time value, in ‘ hh : mm :ss ‘ format |
| DATETIME | Date and time value, in ‘ CCYY-MM-DD hh : mm :ss ‘ format |
| TIMESTAMP | A timestamp value, in ‘ CCYY-MM-DD hh : mm :ss ‘ format |
| YEAR | A year value, in CCYY or YY format |
Example: To select the records with an Order Date of “2018-11-11” from a table:
SELECT * FROM Orders WHERE OrderDate='2018-11-11'Advance MySQL Interview Question
What is BLOB in MySQL?
BLOB is an acronym that stands for a binary large object. It is used to hold a variable amount of data.
There are four types of BLOB:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
A BLOB can hold a very large amount of data. For example – documents, images, and even videos. You could store your complete novel as a file in a BLOB if needed.
How to add users in MySQL?
You can add a User by using the CREATE command and specifying the necessary credentials. For example:
CREATE USER ‘testuser’ IDENTIFIED BY ‘sample password’;What are MySQL “Views”?
In MySQL, a view consists of a set of rows that is returned if a particular query is executed. This is also known as a ‘virtual table’. Views make it easy to retrieve the way of making the query available via an alias.
The advantages of views are:
- Simplicity
- Security
- Maintainability
MySQL Interview Question
How do you create and execute views in MySQL?
Creating a view is accomplished with the CREATE VIEW statement. As an example:
CREATE
[OR REPLACE]
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED }]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]What are MySQL Triggers?
A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event.
Triggers have many purposes, including:
- Audit Trails
- Validation
- Referential integrity enforcement
How many Triggers are possible in MySQL?
There are six Triggers allowed to use in the MySQL database:
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
Advance MySQL Interview Question
What is the MySQL server?
The server, MySQL, is the hub of a MySQL installation; it performs all manipulation of databases and tables.
What are the MySQL clients and utilities?
Several MySQL programs are available to help you communicate with the server. For administrative tasks, some of the most important ones are listed here:
• MySQL—An interactive program that enables you to send SQL statements to the server and to view the results. You can also use MySQL to execute batch scripts (text files containing SQL statements).
• mysqladmin—An administrative program for performing tasks such as shutting down the server, checking its configuration, or monitoring its status if it appears not to be functioning properly.
• mysqldump—A tool for backing up your databases or copying databases to another server.
• MySQL check and myisamchk—Programs that help you perform table checking, analysis, and optimization, as well as repairs if tables become damaged. MySQL check works with MyISAM tables and to some extent with tables for other storage engines. myisamchk is for use only with MyISAM tables.
What are the types of relationships used in MySQL?
There are three categories of relationships in MySQL:
- One-to-One: Usually, when two items have a one-to-one relationship, you just include them as columns in the same table.
- One-to-Many: One-to-many (or many-to-one) relationships occur when one row in one table is linked to many rows in another table.
- Many-to-Many: In a many-to-many relationship, many rows in one table are linked to many rows in another table. To create this relationship, add a third table containing the same key column from each of the other tables
MySQL Interview Question
Can you explain the logical architecture of MySQL?
The top layer contains the services most network-based client/server tools or servers need such as connection handling, authentication, security, and so forth.
The second layer contains much of MySQL’s brains. This has the code for query parsing, analysis, optimization, caching, and all the built-in functions.
The third layer contains the storage engines that are responsible for storing and retrieving the data stored in MySQL.
What is Scaling in MySQL?
In MySQL, scaling capacity is actually the ability to handle the load, and it’s useful to think of load from several different angles such as:
- Quantity of data
- Number of users
- User activity
- Size of related datasets
What is Sharding in SQL?
The process of breaking up large tables into smaller chunks (called shards) that are spread across multiple servers is called Sharding.
The advantage of Sharding is that since the sharded database is generally much smaller than the original; queries, maintenance, and all other tasks are much faster.
Advance MySQL Interview Question
What are Transaction Storage Engines in MySQL?
To be able to use MySQL’s transaction facility, you have to be using MySQL’s InnoDB storage engine (which is the default from version 5.5 onward).
In which language MySQL has been written?
MySQL is written in C and C++, and its SQL parser is written in yacc.
What are the technical specifications of MySQL?
MySQL has the following technical specifications –
- Flexible structure
- High performance
- Manageable and easy to use
- Replication and high availability
- Security and storage management
- Drivers
- Graphical Tools
- MySQL Enterprise Monitor
- MySQL Enterprise Security
- JSON Support
- Replication & High-Availability
- Manageability and Ease of Use
- OLTP and Transactions
- Geo-Spatial Support
MySQL Interview Question
What is the difference between MySQL and SQL?
SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.
A PHP script is required to store and retrieve the values inside the database.
SQL is a computer language, whereas MySQL is a software or an application
SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data
What is the difference between the database and the table?
There is a major difference between a database and a table. The differences are as follows:
- Tables are a way to represent the division of data in a database while the database is a collection of tables and data.
- Tables are used to group the data in relation to each other and create a dataset. This dataset will be used in the database. The data stored in the table in any form is a part of the database, but the reverse is not true.
- A database is a collection of organized data and features used to access them, whereas the table is a collection of rows and columns used to store the data.
Why do we use the MySQL database server?
First of all, the MYSQL server is free to use for developers and small enterprises.
MySQL server is open source.
MySQL’s community is tremendous and supportive; hence any help regarding MySQL is resolved as soon as possible.
MySQL has very stable versions available, as MySQL has been in the market for a long time. All bugs arising in the previous builds have been continuously removed, and a very stable version is provided after every update.
The MySQL database server is very fast, reliable, and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.
Advance MySQL Interview Question
How to check the MySQL version?
We can check the MySQL version on Linux using the below command:
- mysql -v
If we use the MySQL in windows, opening the MySQL command-line tool displayed the version information without using any flags. If we want to know more about the server information, use the below statement:
- SHOW VARIABLES LIKE “%version%”;
It will return the output as below:
In this output, we can see the additional version information about the installed MySQL software like innodb_version, protocol_version, version_ssl_library, etc.
How to add columns in MySQL?
A column is a series of cells in a table that stores one value for each row in a table. We can add columns in an existing table using the ALTER TABLE statement as follows:
- ALTER TABLE table_name
- ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];
How to add foreign keys in MySQL?
The foreign key is used to link one or more tables together. It matches the primary key field of another table to link the two tables. It allows us to create a parent-child relationship with the tables. We can add a foreign key to a table in two ways:
- Using the CREATE TABLE Statement
- Using the ALTER TABLE Statement
Following is the syntax to define a foreign key using CREATE TABLE OR ALTER TABLE statement:
[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (col_name, ...)
REFERENCES parent_tbl_name (col_name,...) MySQL Interview Question
How to connect to the MySQL database?
MySQL allows us to connect with the database server in mainly two ways:
Using Command-line Tool
We can find the command-line client tool in the bin directory of the MySQL’s installation folder. To invoke this program, we need to navigate the installation folder’s bin directory and type the below command:
- mysql
Next, we need to run the below command to connect to the MySQL Server:
- shell>mysql -u root -p
Finally, type the password for the selected user account root and press Enter:
- Enter password: ********
After successful connection, we can use the below command to use the:
- USE database_name;
Using MySQL Workbench
We can make a connection with database using MySQL Workbench, simply clicking the plus (+) icon or navigating to the menu bar -> Database -> Connect to Database, the following screen appears. Now, you need to fill all the details to make a connection:
Once we finished this setup, it will open the MySQL Workbench screen. Now, we can double click on the newly created connection to connect with the database server.
How to change the MySQL password?
We can change the MySQL root password using the below statement in the new notepad file and save it with an appropriate name:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
Next, open a Command Prompt and navigate to the MySQL directory. Now, copy the following folder and paste it in our DOS command and press the Enter key.
C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin
Next, enter this statement to change the password:
mysqld –init-file=C:\mysql-notepadfile.txt
Finally, we can log into the MySQL server as root using this new password. After launches the MySQL server, it is to delete the C:\myswl-init.txt file to ensure the password change.
How to create a database in MySQL Workbench?
To create a new database in MySQL Workbench, we first need to launch the MySQL Workbench and log in using the username and password. Go to the Navigation tab and click on the Schema menu. Right-click under the Schema menu and select Create Schema or click the database icon (red rectangle), as shown in the following screen.
A new popup screen appears where we need to fill all the details. After entering the details, click on the Apply button and then the Finish button to complete the database creation.
Advance MySQL Interview Question
How to change the table name in MySQL?
Sometimes our table name is non-meaningful. In that case, we need to change or rename the table name. MySQL provides the following syntax to rename one or more tables in the current database:
mysql> RENAME old_table TO new_table;
If we want to change more than one table name, use the below syntax:
RENAME TABLE old_tab1 TO new_tab1,
old_tab2 TO new_tab2, old_tab3 TO new_tab3;
How to change the database name in MySQL?
Sometimes we need to change or rename the database name because of its non-meaningful name. To rename the database name, we need first to create a new database into the MySQL server. Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database. The following is the syntax of using mysqldump command:
mysqldump -u username -p “password” -R oldDbName > oldDbName.sql
Now, use the below command to import the data into the newly created database:
mysql -u username -p”password” newDbName < oldDbName.sql
How to import a database in MySQL?
Importing database in MySQL is a process of moving data from one place to another place. It is a very useful method for backing up essential data or transferring our data between different locations. For example, we have a contact book database, which is essential to keep it in a secure place. So we need to export it in a safe place, and whenever it lost from the original location, we can restore it using import options.
In MySQL, we can import a database in mainly two ways:
Command Line Tool
MySQL Workbench
MySQL Interview Question
How to change the column name in MySQL?
While creating a table, we have kept one of the column names incorrectly. To change or rename an existing column name in MySQL, we need to use the ALTER TABLE and CHANGE commands together. The following are the syntax used to rename a column in MySQL:
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER existing_column];
Suppose the column’s current name is S_ID, but we want to change this with a more appropriate title as Stud_ID. We will use the below statement to change its name:
ALTER TABLE Student CHANGE COLUMN S_ID Stud_ID varchar(10);
How to delete columns in MySQL?
We can remove, drop, or delete one or more columns in an existing table using the ALTER TABLE statement as follows:
ALTER TABLE table_name DROP COLUMN column_name1, column_name2….;
How to insert data in MySQL?
We can insert data in a MySQL table using the INSERT STATEMENT. This statement allows us to insert single or multiple rows into a table. The following is the basic syntax to insert a record into a table:
INSERT INTO table_name ( field1, field2,…fieldN )
VALUES ( value1, value2,…valueN );
If we want to insert more than one rows into a table, use the below syntax:
INSERT INTO table(field1, field2,…fieldN)
VALUES
(value1, value 2, …),
(value1, value2, …),
…
(value1, value2, …);
Advance MySQL Interview Question
Why Should We Use MySQL Database?
These MySQL interview questions are best suited for beginners and intermediate users.
- Mysql server is fast, reliable and easy to use
- Protects sensitive data from intruders
- Scalable and can handle huge amount of data
- Compatible with almost every OS
- PHP friendly
- Large and extensive community support
What are the different tables present in the MySQL database?
Listed below are disparate MySQL table:
- MyISAM – default database engine
- Heap – used for fast database access
- Merge – removes the size limitation from MYISAM tables
- InnoDB – supports transaction using COMMIT and Rollback
- ISAM – deprecated and removed from version 5.x
Illustrate the order of MySQL Query execution?
It is one of the most dealt MySQL interview questions
- From and Joins
- Where
- Group by
- Having
- Select
- Order By
- Limit
MySQL Interview Question
How To Improve The Performance Of MySQL SELECT Query?
Now this question is one of the MySQL interview questions that should be understood carefully.
- Use Indexes
- Don’t use “*”
- Avoid unnecessary columns
- Use DISTINCT and Union solely if necessary
- Use short table aliases
- Avoid wildcard (%) at the start of LIKE pattern
What Is The Traditional Network Library For A System?
In either Windows or POSIX systems, the named pipes provide ways of inter-process communications to connect different processes running on the same machine. It dispenses with the necessity of using network stack, and data can be sent without affecting the performance.
What Are The Common MYSQL Functions?
Common MYSQL functions are as follows:
NOWO: The function for returning the current date and time as a single value CURRDATEO: The function for returning the current date or time
CONCAT(X, Y): The function to concatenate two strings values creating a single string output
DATEDIFF (X, Y): The function to determine the difference between two dates
Advance MySQL Interview Question
What Is The Syntax For Concatenating Tables In MYSQL?
In mysql database server the syntax for concatenating database table in MYSQL:
CONCAT(string 1, string 2, string 3)
How To Resolve The Problem Of The Data Disk That Is Full?
When the data disk is full and overloaded, the way out is to create a soft link and move the .frm and the .idb files into that link location.
What Is The TIMESTAMP Datatype ?
TIMESTAMP in MySQL server helps in row versioning. Row versioning is a type of concurrency that allows retaining the value until it is committed in the database. It shows the instant time of any event. It consists of both the date and time of the event. Also, TIMESTAMP helps in backing up data during the failure of a transaction.
MySQL Interview Question
What MySQL -U John -P Command Does?
- MySQL -u john -p command will prompt for the password for user john before allowing access to the database management system.
- If your database server requires a username and password to gain access the -u and -p command-line options.
How To Define The Testing Of Network Layers In MySQL?
For this, it is necessary to review the layered architecture and determine hardware and software configuration dependencies with respect to application put to test.
What Is The Difference Between Primary Key And Unique Key ?
While both are used to enforce the uniqueness of the column defined, the primary key would create a clustered index, whereas the unique key would create a non-clustered index on the column. The primary key does not allow ‘NULL’, but the unique key does.
Advance MySQL Interview Question
How Can You Restart SQL Server In The Single User Or The Minimal Configuration Modes?
The command line SQLSERVER.EXE used with ‘-m’ will restart SQL Server into single-user mode and the same with ‘-f’ will restart it in the minimal configuration mode.
What Are The Differences Between A Primary Key And A Foreign Key?
| Primary Key | Foreign Key |
|---|---|
| It helps in the unique identification of data in a database | It helps establish a link between tables |
| There can only be one primary key for a table | There can be more than one foreign key for a table |
| Primary key attributes cannot have duplicate values in a table | Duplicate values are acceptable for a foreign key |
| Null values are not acceptable | Null values are acceptable |
| We can define primary key constraints for temporarily created tables | It cannot be defined for temporary tables |
| The primary key index is automatically created | The index is not created automatically |
How Can You Filter The Duplicate Data While Retrieving Records From The Table?
A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.
MySQL Interview Question
How Can You Change The Name Of Any Existing Table By Using The SQL Statement?
The following SQL command is used to rename an existing table of the database.
RENAME TABLE table_name TO new_name;
How Can You Retrieve A Portion Of Any Column Value By Using A Select Query?
SUBSTR() function is used to retrieve the portion of any column.
How Can You Retrieve A Particular Number Of Records From A Table?
A LIMIT clause is used with the SQL statement to retrieve a particular number of records from a table. From which record and how many records will be retrieved are defined by the LIMIT clause.
Advance MySQL Interview Question
How Can You Count The Total Number Of Records Of Any Table?
COUNT() function is used to count the total number of records of any table.
What Is A Storage Engine? What Are The Differences Between InnoDB And MyISAM Engines?
One of the major components of MySQL server is the storage engine for doing different types of database operations. Each database table created is based on the specific storage engine MySQL supports two types of storage engine i.e. transactional and non-transactional. InnoDB is the default storage engine of MySQL which is a transactional storage engine. MyISAM storage engine is a non-transactional storage engine.
- MyISAM supports FULLTEXT index but InnoDB doesn’t support FULLTEXT index.
- MyISAM is faster and InnoDB is slower.
- InnoDB supports ACID(Atomicity, Consistency, Isolation, and Durability) properties but MyISAM doesn’t.
- InnoDB supports row-level locking and MyISAM supports table-level locking.
What are the primary storage models of OLAP?
The storage models in OLAP are as follows.
- MOLAP
- ROLAP
- HOLAP
MySQL Interview Question
What is collation sensitivity?
Collation sensitivity defines the rules to sort and compare the strings of character data, based on correct character sequence, case-sensitivity, character width, and accent marks, among others.
What is COALESCE?
COALESCE returns the first non-NULL expression within its arguments from more than one column in the arguments. The syntax for COALESCE is –
COALESCE (expression 1, expression 2, … expression n)
What is “scheduled job” or “scheduled task”?
Scheduled job or task allows automated task management on regular or predictable cycles. One can schedule administrative tasks and decide the order of the tasks.
Advance MySQL Interview Question
Name different DCL commands in MySQL.
DCL commands are –
- GRANT – Used to provide user access privileges to the database
- DENY – Used to deny permissions to users
- REVOKE – Used to withdraw user access by using the GRANT command
Name different TCL commands in SQL.
DCL commands include –
COMMIT – Used to commit a transaction
ROLLBACK – Used to roll back a transaction
SAVEPOINT – Used to roll back the transaction within groups
SET TRANSACTION – Used to specify transaction characteristics
What are the different types of Normalization?
There are six different types of Normalization –
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Fourth Normal Form (4NF)
- Fifth Normal Form (5NF)
MySQL Interview Question
Can you implement ‘AND’ with NOT (!) Operator?
Yes, we can use the AND operator when (=) is used, and the operator OR can be used when (!=) is used.
What is the difference between a clustered and non-clustered index?
Clustered Index – A clustered index is used to order the rows in a table. It has leaf nodes consisting of data pages. A table can possess only one clustered index.
Non-clustered index – A non-clustered index stores the data and indices at different places. It also has leaf nodes that contain index rows. A table can possess numerous non-clustered indexes.
What is ERD?
ERD or Entity Relationship Diagram is a visual representation of the database structures and shows a relationship between the tables. ERD or Enhanced Entity Relationship Diagram is the advanced version of ERD and has added functionalities.
Advance MySQL Interview Question
How will you find the unique values, if a value in the column is repeatable?
To find the unique values when the value in the column is repeatable, we can use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users. We can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;
How do you return a hundred books starting from 15th?
SELECT book_title FROM books LIMIT 15, 100. The first number in LIMIT is the offset, and the second is the number.
What will the query select all teams that lost either 1, 3, 5 or 7 games?
We will use-
SELECT team_name FROM teams WHERE team_lost IN (1, 3, 5, 7)
MySQL Interview Question
What is the possible way to add five minutes to a date?
By using:
ADDDATE(techpreparation_publication_date, INTERVAL 5 MINUTE)
What is the procedure to start and stop MySQL on Windows?
We can start and stop MySQL on Windows.NET by using start MySQL, net stop MySQL.
What is the use of tee command in MySQL?
Tee followed by a filename turns on MySQL logging to a specified file. It can be paused by a command note.
Advance MySQL Interview Question
How to convert numeric values to character strings?
We can convert numeric values to character strings by using the CAST(value AS CHAR) function, as shown in the following examples:
SELECT CAST(4123.45700 AS CHAR) FROM DUAL;
4123.45700
Use MySQL dump to create a copy of the database?
MySQL dump -h MySQL host -u username -p mydatabasename > dbdump.sql
Mention the groups of data types?
There are three groups of data types in MySQL, as listed below:
- String Data Types – BINARY, VARBINARY, TINYBLOB, CHAR, NCHAR, VARCHAR, NVARCHAR, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT, ENUM, SET, MEDIUMTEXT.
- Numeric Data Types – MEDIUMINT, INTEGER, BIGINT, FLOAT, BIT, TINYINT, BOOLEAN, SMALLINT, DOUBLE, REAL, DECIMAL.
- Date and Time Data Types – TIMESTAMP, TIME, DATE, DATETIME, YEAR.
MySQL Interview Question
What is the procedure to concatenate two character strings?
To concatenate various character strings into one, you can use the CONCAT() function. Example:
SELECT CONCAT(’Naukri’,’ Learning’) FROM DUAL;
Naukri Learning
SELECT CONCAT(‘Learner’,’Thing’) FROM DUAL;
Learner Thing
What is the procedure to change the database engine in MySQL?
By using:
ALTER TABLE EnterTableName ENGINE = EnterEngineName;
What is the default storage engine in MySQL?
InnoDB is the default storage engine in MySQL.
Advance MySQL Interview Question
What syntax can we use to get a version of MySQL?
By using the given query in your phpmyadmin-
SELECT version();
What syntax is used to create an index in MySQL?
By using-
CREATE INDEX [index name] ON [table name]([column name]);
What are flush privileges in MySQL?
If the GRANT tables are modified directly through the statements like Insert, Delete, and Update, the changes will have no effect if the server is not instructed to restart or reload all the tables. In this case, the flush privileges are used.
This can be done by executing a mysqladmin flush privileges statement.
MySQL Interview Question
What are prepared statements in MySQL?
A prepared statement is used to enhance the speed and efficiency of SQL statements by executing the same statements repeatedly. It makes use of the client-server binary protocol.
Three SQL statements are required to be executed along with the prepared statement:
- Prepare – This prepares the statement before running it.
- Execute – The statement is executed by this command.
- Deallocate Prepare – This releases the statement.
What is MySQL delimiter?
The delimiter in MySQL is used to define functions, stored procedures and make triggers. The semicolon is the default delimiter. This is helpful for the MySQL client to understand where the procedures and functions end and the procedures can be executed as a unit.
You can alter the delimiters to develop new procedures. In case of multiple statements different delimiters like // and $$.
What is MySQL pid file?
The pid file contains the pathname of the file where the server writes its Process ID. This file is created in the data directory by the server unless a different path name to another directory is provided.
Advance MySQL Interview Question
What are concatenated primary keys?
Concatenated primary keys are composite identifiers that are made from the combination of two or more columns in a table.
Eg. Consider the following Product table:
| PRODUCT | |||||
| OrderID | ProdID | Quantity | SellingPrice | Discount | Shipped |
Here no single column can be used as a primary key because a single order can have multiple products and the same product can be in multiple orders. Therefore, the possible solution is to create a composite or concatenated key on ‘OrderID’ and ‘ProdID’.
What are the various ways to create indexes?
There are two ways to create an index in MySQL:
- At the time of table creation using CREATE TABLE
SYNTAX:
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
column_n datatype [ NULL | NOT NULL ],
INDEX index_name [ USING BTREE | HASH ]
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],
...
index_col_n [(length)] [ASC | DESC])
);Using CREATE INDEX to add indexes to existing tables
SYNTAX:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],
...
index_col_n [(length)] [ASC | DESC]);How can you filter the duplicate data while retrieving records from the table?
The keyword DISTINCT filters duplicate data while retrieving data from the table.
Here is the syntax: SELECT DISTINCT field_name from table_name;
MySQL Interview Question
What is PL/SQL?
PL/SQL or Procedural Language for SQL was developed by Oracle. It is an extension of SQL and enables the programmer to write code in a procedural format. Both PL/SQL and SQL run within the same server process and have features like – robustness, security, and portability of the Oracle Database.
