Selecting Display Fields and Specifying the Data Source
Explore how to precisely select and reorder display fields in SQL using the SELECT clause. Understand how to specify the data source with FROM and learn the advantages and limitations of using SELECT * to retrieve all columns from a table.
We'll cover the following...
Selecting specific display fields
Let’s assume that the following dataset is stored in a relational database table named SalesData.
We want to generate the following report:
Let’s start with finding the answer to the first question:
(Select all that apply.) What are the fields/attributes we want to show in our result set? Multi-select
SaleID
SalesPerson
ProductCategory
SaleDate
ProductName
SalesAmount
When creating an SQL query, the goal is to precisely select the necessary information. Often, it’s as straightforward as shown in this exercise. Yet, occasionally, it can be quite challenging.
Let’s answer the following question:
Below is a list of keywords. Your task is to choose the keyword that corresponds to retrieving the identified field in the result set:
SalesPerson, SalesData, ProductName, SalesAmount
FROM
SELECT
WHERE
Now let’s answer the second question:
(Select all that apply.) What are the data source(s)? Multi-select
SaleID
SaleDate
SalesData
FROM
As demonstrated here, answering the second question is incredibly easy. It stands out as one of the most straightforward elements within an SQL statement.
Let’s answer the following question:
Given below is a list of keywords. You have to pick the appropriate keyword that is used before the data source.
SalesData
FROM
SELECT
WHERE
The SELECT clause
The SELECT clause in an SQL query defines the columns from which the query fetches data values, restricting the data retrieval to the specified columns.
Once we’ve identified our display fields and determined the data sources, we can proceed to construct our SQL query to obtain the desired results. The SELECT and FROM clauses in an SQL query adhere to the following structure:
Note: In many SQL systems, it's advised to conclude SQL statements with a semicolon (
;) for clarity. We’ve composed queries without semicolons for simplicity, you can opt to add them for consistency and better code comprehension.
Let’s begin by constructing our first query. The report is reproduced below for your reference:
You need to ensure that the column names are arranged in the exact order displayed in the report.
Let’s execute this query reproduced below:
Great!
But what if we wanted these fields to be shown in a different order? You guessed it right! All we need to do is just change the order in the SELECT clause.
For improved clarity, let’s rewrite the query by rearranging the fields to display results in the following order: SaleDate, SalesPerson, ProductName, SalesAmount.
Selecting all columns
What if we wanted to retrieve all columns of a table? Sure enough, we can do it by naming all the fields in our SELECT clause. Let’s do this:
You are doing a great job!
Selecting all columns with *
This appears quite straightforward, but when dealing with tables that have numerous columns, it becomes challenging to list all those names in the SELECT clause. It can be a tedious task, and if you happen to overlook a name, it might impact the results you’re aiming for. Fortunately, there’s a workaround for this. Instead of painstakingly listing each column name, you can simply use SELECT * to effortlessly obtain the desired results. Let’s try it out.
That’s fantastic! However, there’s a slight caveat: you cannot rearrange the column names in your result set.
A word of caution: While it’s not suitable to delve into the details at this point, if possible, it’s recommended to minimize the use of SELECT *.
Is it necessary for the field and table names to precisely match the case as they’re written?