Introduction

MySQL is an open-source relational database management system (RDBMS) that utilizes Structured Query Language (SQL) for managing and manipulating data. It is widely used for storing and manipulating data. MySQL was developed by the Swedish company MySQL AB. The first version of MySQL was released in 1995. It is widely used for storing and organizing data in a structured, tabular format consisting of rows and columns.

MySQL Database is a very popular choice among developers for managing large datasets because of its reliability, performance, scalability, and ease of use. In todays time a very large amount of data is generated, and it is very essential to protect that data and store it safely so that data stored is not hand over to unauthorized users who can take the advantage of the data.

Features of MySQL Database

MySQL Database comes with various features. Given below is the list of features in MySQL.

1. Open-Source and Free: MySQL is an open-source database, and hence it is free under the General Public License (GPL), allowing for modification and distribution of data.

1. High Performance: MySQL provides high-speed data retrieval and processing. Features like efficient indexing, cache machanism, and optimize query execution contribute to it’s performance.

3. Scalability: MySQL database can handle a large amount of data and high traffic loads. It is very efficient for managing the datasets with features such as partitioning and clustering.

4. Security Features: MySQL includes advances security system with user authentication, access control mechanism, host-based verification, and support for secure connection.

5. Client-Server Architecture: MySQL operates on a client-server model, where client applications connect to the MySQL server to interact with databases.

Data Structure of MySQL                                   

• Database: A Database is an organized collection of structured information. It is managed by a database management system. Structured Query Language (SQL) is the standard language used to interact with and manage relational databases.

• Table: The data is stored in a database in rows and columns format. In simple words, a table is an arrangement of information of data, typically in rows and columns. The data stored in a form of data is well structured and easy to retrieve while performing an operation.

• Row(Record): A row is a single record for storing data in a database. Data is stored in a table in rows and columns, where rows are a horizontal arrangement of data and columns are the vertical arrangement of data.

• Columns(Fields): A Column is a specific attribute of the data. A Column represents a vertical set of data values within a table. All values within a single column share the same data type, such as INTEGER, VARCHAR, DATE, or DECIMAL. This ensures data integrity and consistency.

• Primary Key: It is a column or set of columns within a table that uniquely identifies the properties combining NOT NULL and UNIQUE. It is a unique identifier for each row. It serves as a crucial element for ensuring data integrity , efficient data retrieval, and relationships in relational database systems.

• Foreign Key: A foreign key links or establishes the relationship between two tables. In simple words, it is a field that links one table to another. A foreign key is a field in one table that references the primary key of another table.

Basic Questions on MySQL

To understand a topic very well, it is essential to solve questions on that topic. So here are some basic questions on MySQL, which will give you an idea about how to write a query in MySQL to solve a particular problem.

1. Recyclable and Low-Fat Products

In this question, you are provided a table consisting of two columns named Products

  Table: Products

Where product_id is the primary key (column with unique values) for this table, low_fats is an enum category of type(‘Y’ , ‘N’) where ‘Y’ means this product is low_fat and ‘N’ means it is not, recyclable is an ENUM (category) of type (‘Y’, ‘N’) where ‘Y’ means this product is recyclable and ‘N’ means it is not.

Write a solution to find the ids of product that are both low fat and recyclable.

select product_id from Products where low_fats = ‘Y’ and recyclable = ‘Y’   

Example:

Input:

Products Table

Output:

2. Find Users With Valid E-Mails

In this question, you will be provided with a table named Users

Table: Users

Where user_id is the primary key (column with unique values) for this table. This table contains information about the users who signed up for in a website. Some e-mails are invalid.

Write a solution to find the users who have valid emails.

• The prefix name is a string that may contain letters(upper or lower case), digits, underscore ‘_’ , period ‘. ‘, and/or dash ‘-‘. The prefix name must start with a letter.
• The domain is ‘@gmail.com’ .

Query

SELECT * FROM Users WHERE mail REGEXP  ‘^[a-zA-Z][a-zA-Z0-9_.-]*gmail\\.com$’ COLLATE utf8mb4_bin

Example:

Input:

Users table:

Output:

3. Big Countries with higher area and population

In this question, you are provided with a table named World

Table: World

Where name is the primary key (column with unique values) for this table. Each row for this table gives information about the name of a country, the continent to which it belongs, its area, its population, and its GDP value.

A country is big if:

• It has an area of at least three million (i.e., 3000000 km^2), or
• It has a population of at least twenty-five million (i.e., 25000000).

Write a solution to find the name, population, and area of the big countries.

select name, population, area from World where population = 25000000 or area = 3000000;

Example

Input:

World table:

Output: The above MySQL query will give the output as

4. Article Viewed by the author

In this question, you are provided with a table name, Views

Table: Views

There is no primary key (column with unique values) for this table; the table may have duplicate rows.

Each row of this table indicates that some viewer viewed an article (written by some author) on some date.

Note: Equal author_id and viewer_id indicate the same person.

Write a solution to find all the authors who viewed at least one of their own articles.

Query:

select distinct author_id as id from Views where author_id = viewer_id order by id asc;

Example:

Input:

Views Table

Output: The above MySQL query will give the output as

5. Invalid Tweets of higher number of characters

In this question, you are provided with a table named Tweets

Table: Tweets

Tweet_id is the primary key (column with unique values) for this table. Content consists of alphanumeric characters, ‘!’, or ‘ ‘ ,and no other special characters. This table contains all the tweets in a social media app.

Write a solution to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15.

Query:

select tweet_id from Tweets where char_length(content)15;

Example:

Input:

Tweets Table:

Output:

6. Not Boring Movies with description not equal to boring and id should be

In this question, you are provided a table named Cinema

Table: Cinema

Where id is the primary key (column with unique values) for this table.

Each row contains information about the name of a movie, its genre, and its rating.

In the given table rating is a 2 decimal places float in the range [0, 10].

Write a solution to report the movies with an odd-numbered ID and description that is not “boring”.

Query:

select * from Cinema where id % 2 = 1 and description !=  ‘boring’ order by rating desc;

Example:

Input:

Cinema Table:

Output: The above query will give the output as

7. Average Selling Price for each product

In this question, you are given a table named Prices

Table: Prices

Where (product_id,  start_date, end_date) is the primary key (combination of columns with unique values) for this table.

Each row of this table indicates the price of the product_id in the period from start_date to end_date.

For each product_id there  will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.

Table: UnitsSold

This table may contain duplicate rows.

Each row of this table indicates the date, units, and product_id of each product sold.

Write a solution to find the average selling price for each product. The average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.

Query:

select

p.product_id,

ifnull(round(sum(units * price) / sum(units) ,2) ,0) as average_price

from

Prices p left join UnitsSold u

on p.product_id = u.product_id and u.purchase_date between start_date and end_date

group by product_id;

Example:

Input:

Prices Table:

UnitsSold table:

Output: Writing the above MySQL query will give the result

Conclusion

This article gives a description of the MySQL database. Its important features and data structure of MySQL. The data structure section explains the important terms used in MySQL database, and also the practice questions give you an understanding of how MySQL queries are written, which solves real-life problems.

I hope this article has provided you a valuable information.

If you are looking for more such kind of articles, I suggest you visit the Tpoint Tech Website, where you can find various articles in programming, along with other technology, as well as interview questions, working codes, and an online compiler where you can run and test your code.