MariaDB Tutorial / Select Columns, Aliases, Calculations, and DISTINCT

SELECT reads chosen columns and calculated results from a table. AS names an output column and DISTINCT removes duplicate combinations from the selected result; neither feature renames stored columns or deletes the source rows.

Separate the stored structure from the output shape

A product listing needs names and prices, whereas a stock valuation needs price multiplied by stock. The expressions after SELECT define output columns; FROM identifies the table to read. Explicit columns make a query’s contract clearer than SELECT *, especially when an application or export must remain stable after a new column is added. Specify output order using Sort and Paginate Results with ORDER BY and LIMIT; to summarize several rows into one value, use the aggregates in Summarize Rows with COUNT, SUM, AVG, MIN, and MAX rather than per-row arithmetic.

If row order matters, add ORDER BY. Observing insertion order in one run does not establish a guarantee for later runs.

Check the MariaDB documentation for the syntax and conditions used here: SELECT.

Prepare an independent product table

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_14_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_14_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p14_price CHECK (price >= 0),
  CONSTRAINT ck_p14_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_14_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

The six rows deliberately include equal categories and prices, zero stock and NULL notes. Repeated SELECT queries leave these initial rows unchanged. After the setup file finishes, run each query in the same connection to compare output headings and values.

Name output columns and calculate inventory values

SELECT product_id, product_name AS 상품명, price AS 판매가
FROM products_14_lab ORDER BY product_id;

The first query demonstrates Korean output aliases meaning product name and sale price. Those headings do not change product_name or price in the table, and cannot replace the actual column names in a later INSERT or UPDATE. Use backtick-delimited identifiers when an alias contains spaces, and distinguish identifiers from string values.

SELECT product_id, product_name, price, stock,
       price * stock AS inventory_value
FROM products_14_lab ORDER BY product_id;

Each result uses that product’s current price. A total across products requires an aggregate such as SUM. Actual costing, tax and discount rules require an explicit business definition; this simple multiplication does not establish those policies.

Apply DISTINCT to the complete selected combination

SELECT DISTINCT category FROM products_14_lab ORDER BY category;
SELECT DISTINCT category, price
FROM products_14_lab ORDER BY category, price;

The first query returns three category strings: stationery, household and electronics in the original Korean data. The second returns five category–price combinations because the stationery/1500.00 combination appears only once. Selecting the unique product_id as well would keep all six rows.

DISTINCT compares all selected values together. It does not merely modify the first selected column, and does not choose a representative product from each category. A request for one representative row needs a selection rule implemented through ordering, aggregation or another suitable query.

Distinguish WHERE expressions from output aliases

SELECT product_name, price * stock AS inventory_value
FROM products_14_lab
WHERE price * stock >= 100000
ORDER BY inventory_value DESC, product_id ASC;

Only product 4, the lamp, has inventory_value at least 100000; its value is 125000.00. WHERE repeats price * stock, while ORDER BY can refer to the output alias. An alias created by the same SELECT is generally unavailable in its WHERE clause.

This is a logical way to understand query processing, not a claim about the optimizer’s physical access order. A derived table may help when an expression becomes cumbersome, but repeating a simple, visible expression is adequate here.

Diagnose unexpected columns, values and row counts

For Unknown column errors, inspect DESCRIBE products_14_lab, spelling, commas, aliases and the selected database. If a calculation returns NULL, inspect its inputs before substituting zero. Compare a full query including the primary key with the DISTINCT result: missing output rows do not imply deleted stored data. Unexpected counts can also mean that setup data was inserted twice.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_14_lab;