Part 2: Mastering the SELECT Command | SQL Challenges & Capstone
SQL Challenges & Capstone · Part 2 · Lecture Notes
Mastering the SELECT Command
How to retrieve data from database tables — selecting all records, specific columns, and using aliases to make your output readable and report-ready.
Part 2 of 633% Complete
SELECT
Column Retrieval
Aliases with AS
8 Practice Questions
Learning Objectives
Use SELECT to retrieve all records from a table using the wildcard operator (*)
Select specific columns by name to produce focused, analyst-ready results
Apply the AS keyword to rename columns in query output for reports and dashboards
Read and interpret table structures to understand what data exists and how it can be used
Click any section header to open or close it
SELECT is the foundation of SQL. Every query you write as an analyst begins with it. SELECT tells the database: go into this table and bring back the data I ask for. Without SELECT, you cannot retrieve, inspect, report on, or analyse any data at all.
In industry, analysts use SELECT constantly to inspect datasets, retrieve business records, review transactions, analyse products and customers, and prepare reports. Every dashboard, every financial report, every operational summary starts with a SELECT query running against a database table.
The Basic Structure
Every SELECT query follows the same two-line pattern: first name the columns you want, then name the table they live in. The semicolon at the end tells the database the query is complete.
Basic SELECT Syntax
SELECT column_name
FROM table_name;
The asterisk (*) is a wildcard that means every column. When you write SELECT *, you are asking the database to return all columns from the specified table — no matter how many columns that table contains.
When Analysts Use SELECT *
SELECT * is most useful at the start of an investigation — when you want to see the full structure of a table before deciding which columns matter. It is the equivalent of opening a spreadsheet and viewing all the headers before filtering down to what you need.
Select All Columns from Users
SELECT *
FROM users;
Instead of retrieving every column, analysts typically select only the columns they need. Naming specific columns produces output that is easier to read, faster to load on large datasets, and immediately usable in a report or dashboard without further filtering.
The Rule
List the column names you want after SELECT, separated by commas. The database returns those columns — and only those columns — in the order you name them.
Retrieve Specific Columns from Users
SELECT first_name, country
FROM users;
Column names in a database are designed for the database — short, lowercase, and technical. The AS keyword lets you rename any column in your output without changing anything in the database itself. Aliases only affect the result of a single query — the table is unchanged.
Aliases Only Affect the Output
AS creates a temporary label for the column in your query result only. The next time you query that table, the column still has its original name. For multi-word aliases, wrap them in backticks in MySQL.
Single-Word Alias
SELECT first_name AS customer_name
FROM users;
Multi-Word Aliases with Backticks
SELECT
first_name AS `First Name`,
country AS `User Country`
FROM users;
One of the first tasks any analyst performs when working with a new database is understanding what tables exist and what data they contain. SELECT * is the quickest way to see every column name and every value available.
Know Your Tables Before You Query Them
In the sql_challenges_capstone_course database used throughout this course, there are ten tables: users, transactions, products, orders, order_items, sessions, campaigns, campaign_events, support_tickets, and subscriptions. Running SELECT * on each at the start of an analysis session confirms the data is present and the columns are as expected.
Inspect the Products Table
SELECT *
FROM products;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve all columns from the users table
AI Prompt
Write a MySQL query to show all records from the users table.
SQL Answer
SELECT *
FROM users;
Practice 2Retrieve first_name, last_name, and country from users
AI Prompt
Write a MySQL query to show first_name, last_name, and country from the users table.
SQL Answer
SELECT first_name, last_name, country
FROM users;
Practice 3Retrieve all records from the products table
AI Prompt
Write a MySQL query to show all records from the products table.
SQL Answer
SELECT *
FROM products;
Practice 4Retrieve product_name and unit_price from products
AI Prompt
Write a MySQL query to show product_name and unit_price from the products table.
SQL Answer
SELECT product_name, unit_price
FROM products;
Practice 5Use aliases to rename columns from users
AI Prompt
Write a MySQL query to show first_name as First Name and country as User Country from the users table.
SQL Answer
SELECT
first_name AS `First Name`,
country AS `User Country`
FROM users;
Practice 6Retrieve order_id, order_date, and order_status from orders
AI Prompt
Write a MySQL query to show order_id, order_date, and order_status from the orders table.
SQL Answer
SELECT order_id, order_date, order_status
FROM orders;
Practice 7Retrieve all records from transactions
AI Prompt
Write a MySQL query to show all records from the transactions table.
SQL Answer
SELECT *
FROM transactions;
Practice 8Retrieve transaction_id, amount, and transaction_type using aliases
AI Prompt
Write a MySQL query to show transaction_id as Transaction ID, amount as Transaction Amount, and transaction_type as Type from the transactions table.
SQL Answer
SELECT
transaction_id AS `Transaction ID`,
amount AS `Transaction Amount`,
transaction_type AS `Type`
FROM transactions;
Knowledge Check
Five questions — select the best answer for each.
1. What does SELECT * return from a table?
AOnly the first row of the table
BOnly the primary key column
CAll columns and all rows from the table
DOnly columns that contain numeric values
Correct. The asterisk (*) is a wildcard meaning every column. Combined with no WHERE filter, SELECT * returns all columns and all rows.
Not quite. SELECT * returns all columns and all rows — the * wildcard means every column in the table.
2. Which query correctly retrieves only product_name and unit_price from products?
ASELECT * FROM products WHERE product_name, unit_price;
BSELECT product_name, unit_price FROM products;
CSELECT products FROM product_name, unit_price;
DFROM products SELECT product_name AND unit_price;
Correct. Column names go after SELECT, separated by commas. The table name goes after FROM.
Not quite. The structure is SELECT columns FROM table: SELECT product_name, unit_price FROM products;
3. What does the AS keyword do in a SELECT query?
AIt permanently renames the column in the database
BIt filters rows based on a condition
CIt renames a column in the query output only, without changing the table
DIt joins two tables together
Correct. AS creates a temporary alias for the column in the query result only. The underlying table and its column names are completely unchanged.
Not quite. AS creates a temporary alias for the output only — the column keeps its original name in the database.
4. A new analyst wants to see the full structure of the orders table before writing any filters. What is the best first query?
ASELECT order_id FROM orders;
BSELECT * FROM orders;
CSELECT orders FROM *;
DFROM orders SELECT ALL;
Correct. SELECT * FROM orders returns every column and every row, giving the analyst a complete picture before narrowing down.
Not quite. SELECT * FROM orders is the right starting point — it returns everything in the table.
5. Which correctly uses an alias for a multi-word column label in MySQL?
ASELECT amount = ‘Transaction Amount’ FROM transactions;
BSELECT amount RENAME ‘Transaction Amount’ FROM transactions;
CSELECT amount AS `Transaction Amount` FROM transactions;
DSELECT amount AS ‘Transaction Amount’ FROM transactions;
Correct. In MySQL, multi-word aliases must be wrapped in backticks (`). Single quotes are for string values in WHERE clauses.
Not quite. In MySQL, multi-word aliases must be wrapped in backticks: amount AS `Transaction Amount`.
Part 2 Summary
The key ideas from this lecture — keep these in mind as you move forward.
SELECT retrieves data from tables and forms the foundation for all SQL analysis and reporting. Use SELECT * to inspect a full table. Use SELECT column1, column2 to retrieve only what you need. Use AS to rename columns for clean, readable output.
