Learn SQL for Developers/

...

/

Basic Data Retrieval

Basic Data Retrieval

Learn about retrieving data from databases.

We'll cover the following...

Retrieving information from a database is like searching for items in a well-organized warehouse. Each table, such as Products or Categories, represents a shelf where specific types of items are stored. To find what we need, we'll have to use a precise map that leads us to the exact shelf and item, we are looking for. Specifically, when there is a large amount of data. This is where SQL's data retrieval capabilities come into play to keep our operations running smoothly. The ability to pinpoint and retrieve specific data quickly and accurately is essential for tasks such as displaying available products to customers, analyzing sales trends, or managing inventory effectively, ensuring smooth and efficient operations.

Let's explore how to retrieve data in SQL. We'll learn how to:

  • Use the SELECT statement to retrieve data.

  • Select specific columns from a table.

  • Select data based on calculated columns

  • Apply aliases to tables and columns for clarity.

  • Utilize the DISTINCT clause to eliminate duplicate records.

The SELECT statement

The SELECT statement is the foundation of data retrieval in SQL. It allows us to specify which columns to retrieve and how many of them from a table. Let's explore the possible ways to use SELECT.

Selecting all columns

There are scenarios where we might need all the data from a table to perform more complex operations or provide a comprehensive overview. For example, in an online store, the admin might want to view a complete list of all products, including ProductID, Name, Category, Price, and Stock, to analyze inventory and sales trends, or update product information. Retrieving all columns allows for flexibility in these cases, where one may need to access multiple pieces of information for each product.

In SQL, we can select all the columns as follows:

Press + to interact
SELECT *
FROM TableName;

In the code above, the * symbol in the SELECT statement is a wildcard character that represents all columns in a specified table. It's a convenient shorthand that allows us to retrieve every column without having to list them individually.

Selecting specific columns

At times, we are only concerned with a few details and not the entire data. For example, a customer is browsing for products in the Books category, but they only care about the name and price of the books, not the ProductID, stock, or category. Instead of querying the entire Products table and retrieving all columns for every book, we can specify the exact columns we need. This approach not only enhances the efficiency of our queries but also improves the readability and security of our data retrieval processes.

In SQL, to select specific columns, list the column names separated by commas after the SELECT keyword and specify the table from which to retrieve them using the FROM clause. The basic syntax is as follows:

Press + to interact
SELECT Column1, Column2, ...
FROM TableName;

For example, we can retrieve the ProductName and Price from the Products table in our OnlineStore database as follows:

Press + to interact
SELECT ProductName, Price
FROM Products;

The query above fetches only the ProductName and Price columns for all products, excluding any other column.

Now, let's run this example in the code widget below. After running it for specific columns, you can comment out code lines 12–13, and uncomment code lines 15–16 to retrieve all columns of the Products table.

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT ProductName, Price
FROM Products;
-- SELECT *
-- FROM Products;

While SELECT * allows us to conveniently retrieve all columns from a table, it doesn't give us control over the order in which they are extracted. The output follows the same column order as defined during table creation. This is where selecting specific columns helps. Using SELECT col1, col2, ... ensures that the output displays columns in the order specified in the SELECT statement.

Using aliases

Aliases allow us to rename tables or columns temporarily for clarity and brevity in queries. For example, a table named EmployeeRecordsOf2024 can have an alias of er24. Aliases improve readability and simplify complex queries, especially when using long column names or joining tables.

Aliases for tables

In SQL, aliases can be assigned either by using the keyword AS or simply by using a blank space. We can assign an alias to a table using the AS keyword, as shown below:

Press + to interact
SELECT p.ProductName, p.Price
FROM Products AS p;

In the code above, Products is aliased as p, allowing us to reference it more succinctly. We can use this alias, p, to access columns of Products such as p.ProductName.

Now, we can achieve the same results by using a space instead of AS, as shown below:

Press + to interact
SELECT p.ProductName, p.Price
FROM Products p;

Aliases for columns

Column aliases rename the output columns in the result set, enhancing clarity and presentation. In SQL, we can assign an alias directly after the column name:

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT ProductName AS Name, Price AS Cost
FROM Products;

The code above makes the ProductName displayed as Name and the Price as Cost in the output.

Let's look at some ways in which using aliases can be convenient:

  • When working multiple tables that contain the same column names, aliases help distinguish between them, and avoids any ambiguity. For example:

Press + to interact
SELECT e.name AS EmployeeName, m.name AS ManagerName
FROM Employees AS e, Managers AS m

  • Aliases shorten the long table or column names which improves readability. This also includes adjusting column names to match reporting standards. When we write or maintain queries, especially in reports or larger applications, using aliases can simplify updates, such as changing table names without affecting the rest of the query.

Calculated columns in a SELECT statement

Calculated columns in SQL are columns in a query that are not directly stored in the database but are derived using an expression, formula, or computation performed on other columns. These columns are ephemeral, that is, they exist only within the result set of that query, allowing us to perform a calculation or transformation on existing data for each row returned, without altering the underlying table.

We can create calculated columns for output based on calculations or expressions using existing columns while using the SELECT statement. For example, to calculate the total stock value for each product in the Products, we can multiply the Price by the Stock, as shown below:

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT ProductName, Price, Stock, (Price * Stock)
FROM Products;

The code above displays this computation as a new column named (Price * Stock). This is a simple computation, but for more complex ones—such as Price/10 * Stock + Price - Stock/5—using column names alone may not be helpful. This is where aliases come in. Aliases can be used for calculating columns or simplifying complex expressions, making the results easier to understand. For example, in the code above, we can use an alias to display the computation (Price * Stock) in a new column named TotalValue.

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT ProductName, Price, Stock, (Price * Stock) AS TotalValue
FROM Products;

The code above adds a new column TotalValue that represents the total value of stock for each product.

The DISTINCT clause

When working with data, we often need unique values to understand diversity, groupings, or distinct entries. The DISTINCT clause eliminates duplicate values from the result set.

We can use DISTINCT with SELECT as follows:

Press + to interact
SELECT DISTINCT ColumnName
FROM TableName;

Now, let's run an example in the code widget below. It is to retrieve the unique values from the CategoryID column of the Products table. First, execute the query without the DISTINCT clause, and then comment out code lines 12–13, and uncomment code lines 15–16 to run it with DISTINCT and see the difference.

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT CategoryID
FROM Products;
-- SELECT DISTINCT CategoryID
-- FROM Products;

The code lines 15–16 above retrieves unique values from the CategoryID column of the Products table, removing any duplicates.

Best practices for data retrieval

Here are a few key tips to keep in mind when retrieving data from a database:

  • Always select only the needed columns rather than using SELECT *. Using SELECT * excessively can lead to inefficient queries and unnecessary data retrieval. Additionally, it can sometimes make code maintenance more challenging and may lead to application crashes.

  • Choose aliases that clearly represent the table or column's purpose.

  • When selecting the same column from multiple tables, forgetting to alias can cause confusion.

  • Use DISTINCT only when necessary, as it can impact query performance.

Code exercise

We have explored the concept of retrieving data from a database and how queries act as precise instructions to fetch information. Now, let’s apply this knowledge with a practical exercise.

Note: Write your solution code in the code widgets provided at the end of each task. Click "Run" to execute your code and compare the results with the expected outputs to see if your solution is correct.

We’ll use the Customers table (its definition is provided in the code widgets) for this exercise.

Task 1

Write a query to retrieve data from all columns of Customers.

Expected output:

CustomerID

CustomerName

Email

Phone

Address

1

John Doe

john.doe@smail.com

413-456-6862

123 Elm Street, Springfield, 01103

2

Jane Smith

jane.smith@inlook.com

708-567-5234

456 Maple Avenue, Riverside, 60546

3

Alice Johnson

alice.j@jmail.com

317-678-5717

789 Oak Lane, Greenwood, 46142

. . .

. . .

. . .

. . .

. . .

21

Charlotte Miller

charlotte.m@hotmail.com

512-234-1311

66 Pine Trail, Sunset Valley, 78745

22

Lucas Anderson

lucas.anderson@jmail.com

708-345-9809

77 Walnut Place, Lakeview, 48850

23

Emma Watson

emma.w@service.org

864-789-4731

303 Birch Road, Greenwood, 29646

Write your solution code in the widget given below.

Press + to interact
-- Definition for the Customers table
-- CREATE TABLE Customers (
--     CustomerID INT PRIMARY KEY  AUTO_INCREMENT,
--     CustomerName VARCHAR(50) NOT NULL,
--     Email VARCHAR(50),
--     Phone VARCHAR(15),
--     Address VARCHAR(100)
-- );
-- ------------------------------------------------------------------------------
-- Write your code here

We recommend that you try solving the exercise on your own first. However, if you still want to see the correct solution, click the "Show Solution" button below.

Task 2

Write a query to select the CustomerName and Address from the Customers table.

Expected output:

CustomerName

Address

John Doe

123 Elm Street, Springfield, 01103

Jane Smith

456 Maple Avenue, Riverside, 60546

Alice Johnson

789 Oak Lane, Greenwood, 46142

. . .

. . .

Charlotte Miller

66 Pine Trail, Sunset Valley, 78745

Lucas Anderson

77 Walnut Place, Lakeview, 48850

Emma Watson

303 Birch Road, Greenwood, 29646

Write your solution code in the widget given below.

Press + to interact
-- Definition for the Customers table
-- CREATE TABLE Customers (
--     CustomerID INT PRIMARY KEY  AUTO_INCREMENT,
--     CustomerName VARCHAR(50) NOT NULL,
--     Email VARCHAR(50),
--     Phone VARCHAR(15),
--     Address VARCHAR(100)
-- );
-- ------------------------------------------------------------------------------
-- Write your code here

We recommend that you try solving the exercise on your own first. However, if you still want to see the correct solution, click the "Show Solution" button below.

Task 3

Use table and column aliases to select the Email and Phone columns from the Customers table. In the output, these should appear as CustomerEmail and CustomerPhone to differentiate them from the Email and Phone columns in the Suppliers table.

Expected output:

CustomerEmail

CustomerPhone

john.doe@smail.com

413-456-6862

jane.smith@inlook.com

708-567-5234

alice.j@jmail.com

317-678-5717

. . .

. . .

charlotte.m@hotmail.com

512-234-1311

lucas.anderson@jmail.com

708-345-9809

emma.w@service.org

864-789-4731

Write your solution code in the widget given below.

Press + to interact
-- Definition for the Customers table
-- CREATE TABLE Customers (
--     CustomerID INT PRIMARY KEY  AUTO_INCREMENT,
--     CustomerName VARCHAR(50) NOT NULL,
--     Email VARCHAR(50),
--     Phone VARCHAR(15),
--     Address VARCHAR(100)
-- );
-- ------------------------------------------------------------------------------
-- Write your code here

We recommend that you try solving the exercise on your own first. However, if you still want to see the correct solution, click the "Show Solution" button below.

We've now explored the fundamentals of data retrieval using SQL. We've learned how to use the SELECT statement to fetch data, select specific columns, apply aliases for clarity, and eliminate duplicate records with the DISTINCT clause. These skills are essential building blocks that empower us to interact with our database effectively and extract the information we need with precision.

Keep practicing these concepts to become more comfortable with data retrieval. As we move forward, we'll build upon these foundations to tackle more complex queries and data manipulation techniques. Let's continue this momentum and explore the exciting world of SQL together!