Hands-on Practical: Writing Basic SELECT Queries

Writing basic SELECT queries, which includes specifying columns, using aliases, and limiting results, requires hands-on practice. Practical examples help solidify understanding and build confidence in constructing SQL queries.

In these exercises, we will assume we are working with a database containing a few simple tables: Customers, Products, and Orders. Let's briefly look at their structure:

• Customers Table: Contains customer information.
  • Columns: CustomerID, FirstName, LastName, Email, City, Country
• Products Table: Contains product information.
  • Columns: ProductID, ProductName, Category, Price
• Orders Table: Contains order information.
  • Columns: OrderID, CustomerID, OrderDate, TotalAmount

Let's start writing some queries.

Exercise 1: Selecting Specific Columns

Your first task is to retrieve the first name, last name, and email address for all customers in the Customers table.

Query:

SELECT
  FirstName,
  LastName,
  Email
FROM
  Customers;

Explanation:

• SELECT FirstName, LastName, Email: This part specifies exactly which columns you want to retrieve. We list the desired column names separated by commas.
• FROM Customers: This indicates the table from which you want to fetch the data.

Expected Result (Sample):

FirstName	LastName	Email
Anna	Smith	[email protected]
Ben	Jones	[email protected]
Clara	Davis	[email protected]
...	...	...

This query returns only the three specified columns for every row present in the Customers table.

Exercise 2: Selecting All Columns with a Limit

Next, imagine you need a quick overview of the first few product records. Retrieve all available information for the first 5 products listed in the Products table.

Query:

SELECT
  *
FROM
  Products
LIMIT 5;

Explanation:

• SELECT *: The asterisk (*) is a shorthand way to select all columns from the table without having to list them individually.
• FROM Products: Specifies that we are querying the Products table.
• LIMIT 5: This clause restricts the output to the first 5 rows encountered by the database. The exact order might vary unless an ORDER BY clause (covered later) is used, but LIMIT guarantees the maximum number of rows returned. Note that the specific keyword for limiting results might differ slightly in some SQL dialects (e.g., TOP 5 in SQL Server, FETCH FIRST 5 ROWS ONLY in Oracle). We'll use LIMIT as it's common in systems like PostgreSQL and MySQL.

Expected Result (Sample):

ProductID	ProductName	Category	Price
1	Laptop	Electronics	1200.00
2	Keyboard	Accessories	75.00
3	Mouse	Accessories	25.50
4	Monitor	Electronics	300.00
5	Web Development Book	Books	49.99

This query fetches all columns (ProductID, ProductName, Category, Price) but only for the first 5 product rows found.

Exercise 3: Using Column Aliases for Clarity

Sometimes, the original column names aren't ideal for a report or analysis. Let's retrieve the product names and their prices from the Products table, but display the columns as Item and Cost respectively.

Query:

SELECT
  ProductName AS Item,
  Price AS Cost
FROM
  Products;

Explanation:

• SELECT ProductName AS Item: We select the ProductName column but use the AS keyword to assign it the alias Item in the output.
• Price AS Cost: Similarly, the Price column is selected and given the alias Cost.
• FROM Products: We are still querying the Products table.

Expected Result (Sample):

Item	Cost
Laptop	1200.00
Keyboard	75.00
Mouse	25.50
Monitor	300.00
Web Development Book	49.99
Data Science Handbook	59.95
...	...

The result set now uses the aliases Item and Cost as column headers, making the output potentially more user-friendly depending on the context.

Exercise 4: Combining Aliases and Limiting Results

Let's combine what we've learned. Suppose you need a list of just a few order identifiers and their dates. Retrieve the OrderID and OrderDate from the Orders table for any 3 orders. Display OrderID as OrderNumber and OrderDate as DatePlaced.

Query:

SELECT
  OrderID AS OrderNumber,
  OrderDate AS DatePlaced
FROM
  Orders
LIMIT 3;

Explanation:

This query demonstrates combining multiple concepts from this chapter:

• SELECT OrderID AS OrderNumber, OrderDate AS DatePlaced: Selects specific columns and assigns aliases (OrderNumber, DatePlaced) for better readability using AS.
• FROM Orders: Specifies the Orders table as the data source.
• LIMIT 3: Restricts the output to the first 3 rows returned by the database for this query.

Expected Result (Sample):

OrderNumber	DatePlaced
101	2023-10-26
102	2023-10-27
103	2023-10-27

This practical query efficiently retrieves a small, clearly labeled sample of order data.

These exercises cover the core ways to retrieve data using SELECT, select specific or all columns, rename columns with aliases, and limit the size of your results. As you proceed, you'll build upon these foundations to perform more complex filtering, sorting, and calculations. Experiment with these queries, try changing the column names, aliases, and LIMIT values to see how the results change.