What is the difference between SELECT * and listing specific columns?

SELECT * returns all columns, while listing specific columns returns only those columns.

Problems with SELECT *:

• Returns unnecessary data (wastes bandwidth)

• Breaks code when table structure changes

• Prevents index-only scans (slower queries)

• May expose sensitive columns accidentally

Best practice: Always list specific columns in production code. SELECT * is OK for ad-hoc queries only.

Explain how DISTINCT works and when to use it

DISTINCT removes duplicate rows from result set. It compares ALL columns in SELECT list.

Example:

SELECT DISTINCT department FROM employees;

Returns unique departments.

SELECT DISTINCT first_name, last_name FROM employees;

Returns unique combinations of first+last name.

Performance: DISTINCT requires sorting/hashing. Use GROUP BY for aggregations instead.

Use when: Need unique values for reporting, dropdowns, data analysis.

What are column aliases and why are they useful?

Column aliases rename columns in result set using AS keyword.

Benefits:

• Readable output: salary * 12 AS annual_salary

• API responses: Match frontend expectations

• Complex expressions: CONCAT(first_name, ' ', last_name) AS full_name

• Required for: ORDER BY calculated columns

Syntax: SELECT column AS alias or SELECT column alias (AS optional)

How does SELECT work with expressions and calculations?

SELECT can include expressions, calculations, and functions:

Arithmetic: SELECT salary * 12 AS annual_salary

String: SELECT CONCAT(first_name, ' ', last_name) AS full_name

Date: SELECT YEAR(hire_date) AS hire_year

Conditional: SELECT CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_level

Expressions calculated for each row. Use aliases for readability.