MySQL is an open-source Database Management System (DBMS) for managing and organizing the data in a tabular format. These data can be manipulated using MySQL programming language. In every industry, the database is used for different functions, so there is a huge demand for professionals having knowledge of MySQL programming language.
MySQL has stand-alone clients that allow users to interact directly with a MySQL database using SQL, but more often MySQL is used with other programs to implement applications that need relational database capability. MySQL has more than 11 million installations.
To build a career in MySQL programming, candidates need to crack the interview in which they are asked various MySQL interview questions. Here is a list of questions for the interview:
1. What is Data?
Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc. In computing, data is information that can be translated into a form for efficient movement and processing. Data is interchangeable.
2. What is Database?
A database is an organized collection of data so that it can be easily accessed and managed. You can organize data into tables, rows, and columns, and index it to make it easier to find relevant information. Database handlers create a database in such a way that only one set of software programs provides access to data to all the users.
The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data. There are many dynamic websites on the World Wide Web nowadays which are handled through databases. For example, a model that checks the availability of rooms in a hotel. It is an example of a dynamic website that uses a database.
There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.
3. What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulates databases
- SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987
4. What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records into a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
5. What is MySQL?
MySQL is an open-source DBMS that is built, supported, and distributed by MySQL AB (now acquired by Oracle). MySQL is an Oracle-supported Relational Database Management System (RDBMS) based on structured query language. It is supported by the most popular operating systems, such as Windows, Linux, etc. It can be used to develop different types of applications but it is mainly used for developing web applications. MySQL is a database management system for web servers. It can grow with the website as it is highly scalable. Most websites today are powered by MySQL.
6. What are the main features of MySQL?
- Open-Source
- Quick and Reliable
- Scalable
- Contains multiple Data types
- Supports different Character Sets
- Secure
- Support for large databases
- Allows roll-back
- Easy to use
- Compatible with many operating systems
7. 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
8. 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.
9. 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 |
10. What is the difference between SQL and NoSQL?
| SQL | NoSQL |
|---|---|
| RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS) | Non-relational or distributed database system. |
| These databases have fixed or static or predefined schema | They have dynamic schema |
| These databases are not suited for hierarchical data storage. | These databases are best suited for hierarchical data storage. |
| These databases are best suited for complex queries | These databases are not so good for complex queries |
| Vertically Scalable | Horizontally scalable |
| Follows ACID property | Follows CAP(consistency, availability, partition tolerance) |
11. Why do we use the MySQL database server?
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.
12. What is the default port for MySQL Server and how it can change?
The default port is 3306. We can change it in /etc/MySQL/my.conf there is a port variable. We can update this port according to our need
13. What is the difference between delete, drop and truncate?
TRUNCATE
- It removes all rows from a table.
- It does not require a WHERE clause.
- Truncate cannot be used with indexed views.
- It is performance wise faster.
DELETE
- It removes Some or All rows from a table.
- A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query.
- It removes rows one by at a time.
- It can be used with indexed views.
DROP
- It removes a table from the database.
- All table’s rows, indexes, and privileges will also be removed when we used this command.
- The operation cannot be rolled back.
14. What is the difference between the primary and unique key in Mysql?
| Primary Key | Unique Key |
|---|---|
| A table can hold only one primary key | It can be more than one unique key in one table |
| A Primary Key cannot be NULL. | A Unique key can have NULL. |
Practical Questions
15. 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%";
16. How do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
17. How do you create a database in MySQL?
Use the following command to create a new database called ‘books’:
CREATE DATABASE books;18. 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;
19. 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, ...);
20. How do you remove a column from a database?
You can remove a column by using the DROP keyword:
ALTER TABLE classics DROP pages;21. 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;
22. 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
23. How to display top 10 rows in Mysql?
SELECT * FROM 'TableName' WHERE 'status' = 1 LIMIT 1024. How do I count the number of records in a MySQL query?
select count(*) from TABLE_NAME where 'status' = 1;25. 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';
26. How to check USERS in MySQL?
If we want to manage a database in MySQL, it is required to see the list of all user’s accounts in a database server. The following command is used to check the list of all users available in the database server:
mysql> SELECT USER FROM mysql.user;
27. How to view the database in MySQL?
Working with the MySQL server, it is a common task to view or list the available databases. We can view all the databases on the MySQL server host using the following command:
mysql> SHOW DATABASES;
28. How can I see all indexes on a table in MySQL Database?
SHOW INDEX FROM TABLE_NAME;29. Write a query to fetch common records between two tables using MySQL?
Using INTERSECT
SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary30. How to check database size in MySQL?
MySQL allows us to query the information_schema.tables table to get the information about the tables and databases. It will return the information about the data length, index length, collation, creation time, etc. We can check the size of the database on the server using the below syntax:
SELECT table_schema AS 'Database Name', SUM(data_length + index_length) 'Size in Bytes', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MB' FROM information_schema.tables WHERE table_schema = 'testdb' GROUP BY table_schema;