MySQL is one of the most popular open-source systems for managing relational database management systems (RDBMS - Relational Database Management System). It uses SQL (Structured Query Language) for data management and is particularly popular in combination with PHP for web application development. MySQL is part of the famous LAMP (Linux, Apache, MySQL, PHP) stack and supports all major operating systems.
For development with MySQL, we need a suitable development environment (IDE - Integrated Development Environment). The most popular tools include:
In this tutorial, we will use XAMPP, which includes phpMyAdmin, but we can also work with the PHPStorm Database tool. MySQL supports two ways of commenting code:
Single-line comments: start with two hyphens (--)Multi-line comments: start with /* and end with */-- This is a single-line comment SELECT * FROM users; -- Show all users
/* This is a multi-line comment It can span across multiple lines */ SELECT * FROM products;
To help you learn and test your SQL queries, a rich collection of test tables is already pre-configured in the simulator. Below you can find a list of all available tables, their columns, and a short description of their purpose.
How to start? There is no need to load or create any tables. Simply type a selection command
into the simulator (e.g., SELECT * FROM first_table; or SELECT * FROM employees LIMIT 5;)
to explore the structure and data hidden within them. Try out different combinations and learn through practical
examples!
first_table (Columns: id, first_name, last_name, age) Description: Basic table with a few employees for testing the first steps.
duplicated_table (Columns: table_id, first_name, last_name, city, country) Description: Ideal table for learning how to remove duplicates using DISTINCT or GROUP BY.
sorting (Columns: first_name, last_name, department, salary) Description: Large table with plenty of data, designed for practicing ORDER BY, LIMIT, and department filtering.
merchandise and products_on_example (Columns: name, price) Description: List of items in a store, suitable for mathematical operators (>, <, BETWEEN).
employees (Columns: id, first_name, last_name, department, salary, hire_year) Description: Main table for practicing aggregate functions (COUNT, AVG, SUM) and grouping.
customers (Columns: id, first_name, last_name, email, phone, city) Description: Customer data from various cities.
news (Columns: id, title, date, content) Description: Sample blog database. Excellent for working with dates and searching text patterns using LIKE.
orders (Columns: id, order_number, date, product, quantity, price_per_unit, total_price) Description: Transactions overview. Suitable for complex calculations and filtering timestamps (DATETIME).
grades and student_grades (Columns: id, subject / course_id, grade) Description: School grades for simple average calculations or finding the lowest/highest scores (MIN, MAX).
sales (Columns: id, salesperson, monthly_sales, monthly_target, month) Description: Comparison table for performance tracking (e.g., who exceeded the monthly target).
inventory (Columns: id, product, stock_quantity, average_monthly_sales, minimum_stock, price_per_unit) Description: Logistics table for advanced conditions (e.g., which products dropped below the minimum stock level).
efficiency (Columns: id, employee, hours_worked, tasks_completed) Description: Table for productivity analytics.
customer_satisfaction (Columns: id, customer, satisfaction_score, number_of_purchases, number_of_complaints) Description: Table with decimal satisfaction scores and integers for complaint counts.
promotions (Columns: product, price, discount, stock, season, promotion_date) Description: Seasonal discount data, ideal for filtering by season or calculating final prices with discounts.
students, courses, professors, faculties Description: Relational set of tables, perfectly ready for learning how to combine data using JOIN statements (INNER JOIN, LEFT JOIN).
sales_on_example and orders_on_example Description: Additional tables for learning explicit ON conditions when joining data.
change_log (Columns: id, table, record_id, change_type, date) Description: Empty table prepared to demonstrate system logging or INSERT/UPDATE history tracking.
access_rights (Columns: user_id, module, permission_type, grant_date) Description: Table for simulating user security setups and access permissions.
texts (Columns: id, text) Description: Table with spaces and asterisks, specifically created for learning string functions like TRIM(), REPLACE(), and SUBSTRING().
products (Columns: id, name, price, category) Description: Classic product catalog divided by merchandise categories.
dates and events Description: Tables designed to test date and time functions (NOW(), DATE_FORMAT(), YEAR()).
invoices (Columns: invoice_id, description, amount, date) Description: Financial table containing positive and negative values, ideal for the ABS() function or separating revenue from expenses.
sqrt_table (Columns: id, number, description) Description: Table with numbers, ready for testing mathematical functions like SQRT() (square root) and ROUND().
null_table (Columns: id, name, value, note, status) Description: Crucial table for learning how to handle NULL values. Users practice using IS NULL, IS NOT NULL, and the COALESCE() function here.