Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/mysql-interview-questions/ below:

MySQL Interview Questions - GeeksforGeeks

MySQL is an open-source Relational Database Management System(RDMS) that organizes data in tables using rows and columns. It uses Structured Query Language (SQL) for managing and manipulating databases. It was originally developed by MySQL AB, a Swedish company, and is now owned by Oracle Corporation. It's known for its high performance, reliability, and ease of use, making it one of the most widely used databases globally for web applications, data storage, and software development.

In this article, we have compiled 50+ MySQL Interview Questions and answers commonly asked in Data Analyst/SQL Developer and other database-related interviews, especially at MAANG and other high-paying companies. Whether we are a fresher or an experienced professional with 1, 5, or 10+ years of experience, this article gives you all the confidence you need to ace your next interview.

This section covers fundamental concepts that are essential for anyone preparing for a MySQL interview. These questions focus on the core features and basic operations of MySQL, such as data types, creating databases, performing basic queries, and understanding key database concepts. Mastering these basics will help us build a strong foundation for more advanced topics.

MySQL is an open-source relational database management system (RDBMS) that is widely used for managing structured data. It utilizes SQL (Structured Query Language) for querying and managing data. MySQL is known for its reliability, scalability, and performance, making it a popular choice for various applications

To create a database in MySQL, we can use the CREATE DATABASE statement followed by the name we want to give to our database. For example:

CREATE DATABASE mydatabase;

CHAR

• Fixed-length storage. If the string is shorter than the defined length, the remaining spaces are padded with blanks.
• Suitable for storing fixed-length data (e.g., country codes, fixed-length phone numbers).
• CHAR may be faster for small, fixed-length strings.

VARCHAR

• Variable-length storage. Only the exact number of characters is stored, saving space.
• Suitable for storing strings where the length varies, like names or descriptions.
• VARCHAR uses less space and is ideal for large strings of varying length.

SQL

MySQL

It is a structured query language that manages the relational database management system.

It is a relational database management system that uses SQL.

It is not an open-source language.

MySQL is an open-source platform. It allows access to anyone.

SQL supports XML and user defined functions.

It doesn't support XML and any user defined functions

SQL can be implemented in various RDBMS such as PostgreSQL, SQLite, Microsoft SQL Server, and others.

MySQL is a specific implementation of an RDBMS that uses SQL for querying and managing databases.

SQL itself is not a product and doesn't have a license. It's a standard language.

MySQL is open-source and available under the GNU General Public License (GPL).

The default port for MySQL server is 3306. This is the port number used by MySQL to communicate with clients and other services by default, unless it is configured to use a different port in the MySQL configuration file (my.cnf or my.ini).

Batch mode in MySQL is used to execute a series of SQL queries from a file (often referred to as a batch file). The file contains multiple SQL statements that are executed in sequence. Here's the correct way to run MySQL in batch mode:

mysql -u username -p database_name < batch-file.sql

There are several storage engines in MySQL, and the table types depend on which storage engine is used. The common storage engines are:

• InnoDB (default in MySQL for transactional data)
• MyISAM (older, non-transactional storage engine)
• MEMORY (stores data in memory)
• CSV (stores data in CSV files)
• ARCHIVE (used for storing large amounts of data, read-only)

• Storage and retrieval have been different for CHAR and VARCHAR.
• Column length is fixed in CHAR but VARCHAR length is variable.
• CHAR is faster than VARCHAR.
• CHAR datatype can hold a maximum of 255 characters while VARCHAR can store up to 4000 characters.

• LENGTH: Returns the byte count of a string. For multi-byte characters, it counts the total number of bytes used.
• CHAR_LENGTH: Returns the character count of a string, counting each character regardless of byte size.

Example:

SELECT LENGTH('Hello');       -- Returns 5 (1 byte per character in ASCII)
SELECT CHAR_LENGTH('Hello');  -- Returns 5 (5 characters)

In the LIKE statement, the % and _ symbols are used for pattern matching:

• %: Represents zero or more characters. It matches any sequence of characters, including an empty sequence.
• _: Represents exactly one character. It matches any single character.

Example:

SELECT * FROM Employees WHERE Name LIKE 'J%n';    -- Matches names starting with 'J' and ending with 'n', like 'John' or 'Jack'
SELECT * FROM Employees WHERE Name LIKE 'J_n';    -- Matches names starting with 'J' and having exactly one character after 'J', like 'Jon'

There are 16 indexed columns can be created in a table.

There are six string types available for the column.

• SET
• BLOB
• TEXT
• ENUM
• CHAR
• VARCHAR

• FLOAT: A single-precision floating-point number. It stores a 4-byte representation of the number with a precision of 7 decimal digits.
• DOUBLE: A double-precision floating-point number. It stores an 8-byte representation of the number with a precision of 15 decimal digits.

Example:

CREATE TABLE example (
    float_value FLOAT,
    double_value DOUBLE
);

BLOB: Binary Large Object is used to store large amounts of binary data, such as images or files. The sorting and comparison of BLOB values are case-sensitive and done in binary format. Types:

• TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

TEXT: Used to store large amounts of text data. Unlike BLOB, TEXT data is case-insensitive and sorted lexicographically.

• Types: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

• WHERE statement is used to filter rows but HAVING statement is used to filter groups.
• GROUP BY is not used with WHERE. HAVING clause is used with GROUP BY.

REGEXP (Regular Expression) is used in MySQL to perform pattern matching with string values. It allows you to use regular expressions for string searches within a query. The pattern can match anywhere in the string and is case-insensitive by default.

Syntax:

SELECT * FROM table_name WHERE column_name REGEXP 'pattern';

A column is a series of table cells that store a value for table's each row. we can add column by using ALTER TABLE statement.

ALTER TABLE tab_name

ADD COLUMN col_name col_definition [FIRST|AFTER exist_col];

We can remove columns in MySQL by using ALTER TABLE statement.

Syntax:

ALTER TABLE table_name DROP COLUMN column1, column2....;   

We can delete a table by using DROP TABLE statement. This statement deletes complete data of table.

DROP TABLE table-name;

mysql_fetch_array() Gets a result row as a related array or a regular array from database. mysql_fetch_object gets a result row as an object from the database.

The following query will be used to get top 10 rows.

SELECT * FROM table_name LIMIT 0,10;

current year, month, and date with hours, minutes, and seconds is shown by using NOW() command while CURRENT_DATE shows current year current month, and current date.

Syntax:

SELECT NOW();

SELECT CURRENT_DATE();

The DISTINCT keyword is used to remove duplicate rows from the result set, returning only unique values for the specified columns. The DISTINCT keyword is used with the SELECT statement.

Syntax:

SELECT DISTINCT colu1, colum2..

FROM table_name;

Storage engines are also called table types. Data is stored in a file using multiple techniques.

Below are some techniques.

• Locking Level
• Indexing
• Storage mechanism
• Capabilities and functions

To create a table in MySQL, the CREATE TABLE statement is used. Here's the syntax:

Syntax:

CREATE TABLE Employee (

Employee_Name VARCHAR(128),

Employee_ID VARCHAR(128),

Employee_Salary VARCHAR(16),

Designation CHAR(4)

);

We can add data to a table using the INSERT INTO statement .

Syntax:

INSERT INTO table_name ( field1, field2, field3 )  

VALUES  ( value1, value2, value3 );  

The below statement is used to find duplicate rows.

SELECT Table_Name, Category

FROM Product

GROUP BY Name, Category

HAVING COUNT(id) > 1;

There are three types of relationships used in MySQL.

• One-to-one: Elements with a one to one relationship can be included as columns in the table.
• One-to-many: One to many or many to one relationships both are same. It will occur when one row in a table is related to multiple rows in different table.
• Many-to-many: Many rows in a table are related to many rows in different table is called many to many relationship.

We can use INSERT statement to insert date in MySQL table. MySQL default date format is YYYY-MM-DD. Automatic MySQL consist many data types to store dates.

• DATE
• DATETIME
• TIMESTAMP
• YEAR

Syntax:

INSERT INTO table_name (column_name, column_date) VALUES ('DATE: Manual Date', '2023-5-20');   

JOINs in MySQL are used to combine rows from two or more tables based on a related column. The most common types of joins are:

1. INNER JOIN: Returns records that have matching values in both tables.

SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.id;

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. If there’s no match, NULL values are returned for the right table.

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. If there’s no match, NULL values are returned for the left table.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

4. FULL JOIN (MySQL does not support FULL JOIN natively): Returns all records when there is a match in either left (table1) or right (table2).

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

A primary key in MySQL is a single field or a group of fields that are used to uniquely identify each record in a table. A primary key cannot be null or empty. ALTER TABLE statement is used to delete a primary key from a table.

Syntax:

ALTER TABLE table_name  DROP PRIMARY KEY;    

A heap table is usually used for temporary and fast temporary storage.

• BLOB or TEXT fields are not permitted in the heap table.
• comparison operator like =, <,>, = >,=< can be used only.
• Heap table didn't support the AUTO_INCREMENT command.
• Indexes should be NOT NULL in the heap table.

The primary key uniquely identified each row of a table. only one primary key is available for a table.

• A primary is also a candidate key.
• Candidate key that can be used for all foreign key references.

DELETE Command is used to delete rows from the table depending on given the condition. TRUNCATE command is used to DELETE all rows from the table. DELETE command is a Data Manipulation Language command. TRUNCATE command is a Data Definition Language command.

A SQL storage database is called InnoDB database. The InnoDB offers ACID transactions, row-level locking, and foreign key support. InnoDB is owned by Oracle Corporation.

During combining the results of more than one SELECT statement, the UNION operator deletes duplicate rows between the various SELECT statements. The UNION ALL also combines the result set of more than one SELECT statement, but it does not delete duplicate rows.

In MySQL, When a row is added to or updated in a table, a data type "timestamp" automatically records the time.

ENUM is a string object that can be used when creating tables to specify a set of predefined values.

CREATE table size(name ENUM('Small', 'Medium', 'Large');

MySQL config variable max_heap_table_size can be used to control the max size of heap.

Syntax:

SET max_heap_table_size = M

A database object that has no value is called view. Rows and columns exist in a view. A view is virtual table. it is created by combining one or more tables. The difference of a view and a table is that views are definition that build on other tables. If the underlying table changes, the View will also reflect those same changes.

Syntax:

CREATE VIEW view_name AS    

SELECT columns    

FROM tables    

[WHERE conditions];    

Every MyISAM table is stored on disk. 

There are three storage formats can be used .

• The .frm file can be used to store table definition.
• The .MYD( MYData) extension can be used for data files.
• The .MYI(MYIndex) extension can be used to Index files.

In MySQL, Blobs can be used to store images. All database images are first converted into blobs then saved and then they will be added to the database, and finally, it will later be stored on the disk.

A trigger is a procedural code in a database. Triggers are automatically triggered when specific events occur on a particular table. During column updating triggers are invoked automatically.

SIX triggers are available in MySQL table.

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

• Width of all fields is fixed in MyISAM Static table whereas width of all fields is not fixed in MyISAM Dynamic. In MyISAM Dynamic table width will be like TEXT, BOLD, etc.
• In case of corruption MyISAM static table is easy to store.

A list of permissions known as an Access Control List is connected to an object. It is MySQL server security model helps in troubleshooting issues like users being unable to connect. MySQL holds the ACL's cached in memory. ACL's also called grant tables. MySQL verifies the authentication data and permissions against the ACLs. It predetermined order whenever a user tries to log in or execute a command.

Normalization is used to avoid duplication and redundancy. it is a process of organizing data. There are many normal forms of normalization. which are also called successive levels. The first three regular forms are sufficient.

• First Normal Form (1NF): There are no repeating groups within rows.
• Second Normal form(2NF): Value of every supporting column depending on the whole primary key.
• Third Normal Form(3NF): It depends only on the primary key and no other value of non-key column.

There are many options to create an index as below:

• T-SQL statements can be used to create an index.
• The SQL Server Management Studio is available for use. we can use this to browse to the table where the index will be created, and then right-click on the Indexes node. We must select the New Index option over here.
• We can identify the index indirectly by specifying the PRIMARY KEY and the UNIQUE constraint in the CREATE TABLE or ALTER TABLE statement.

Cluster Index: An index type used to arrange data in a table is called a clustered index. The table's data are stored in a specific order based on the clustered index.

Non Cluster Index: A non-clustered index is also a type of index used to organize data in a table. The table's data are not stored in a specific order based on the non clustered index.

We can use the regular expressions function (REGEXP_LIKE) to validate emails. Below is the example of validate emails using a single query.

SELECT Email

FROM Vehicle

where NOT REGEXP_LIKE(Email, '[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}', 'i');

The MySQL Server is restricted from loading directories using the LOAD DATA INFILE command by the -secure-file-priv option. Use the SHOW VARIABLES LIKE "secure_file_priv" command to view the directory that has been configured.

There are two options to handle as below.

• Either transfer your file to the directory that secure-file-priv specifies.
• Or you can turn off secure-file-priv. This must be removed at the beginning and cannot be disabled later.

In conclusion, preparing well for MySQL interview questions is crucial for data analysts, data engineers, and business analysts. This guide provides important MySQL questions and answers to help you get ready for your interviews. By studying these, you can show that you are skilled and ready for roles that require strong MySQL knowledge. This preparation will help you stand out in your field.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4