Retrieving Rows from a Table Using SELECT

Introduction

The SQL SELECT statement is one of the most essential commands for interacting with databases. It allows you to retrieve data from a table, apply filters, and customize the output. In this guide, you’ll learn how to:

Write basic and advanced SELECT queries.
Filter results using the WHERE clause.

What is a Query?

A query is a question you ask a database to retrieve specific information. The database responds with a result set, typically represented as rows from a table. SQL, or Structured Query Language, is the tool you use to write these queries.

Think of it like asking for a specific book in a library. The query (your request) searches the catalog (the database) and returns the book (the result set).

Setting Up the Example Table

For this guide, we’ll use a table named puppies, which contains data about dogs. Use the following SQL to create the table and populate it with data:

CREATE TABLE puppies (
  id INTEGER,
  name VARCHAR(100),
  age_yrs DECIMAL(2,1),
  breed VARCHAR(100),
  weight_lbs INT,
  microchipped BOOLEAN
);

INSERT INTO puppies 
VALUES 
  (1, 'Cooper', 1, 'Miniature Schnauzer', 18, 1),
  (2, 'Indie', 0.5, 'Yorkshire Terrier', 13, 1),
  (3, 'Kota', 0.7, 'Australian Shepherd', 26, 0),
  (4, 'Zoe', 0.8, 'Korean Jindo', 32, 1),
  (5, 'Charley', 1.5, 'Basset Hound', 25, 0),
  (6, 'Ladybird', 0.6, 'Labradoodle', 20, 1),
  (7, 'Callie', 0.9, 'Corgi', 16, 0),
  (8, 'Jaxson', 0.4, 'Beagle', 19, 1),
  (9, 'Leinni', 1, 'Miniature Schnauzer', 25, 1),
  (10, 'Max', 1.6, 'German Shepherd', 65, 0);

Note: In SQLite, BOOLEAN values are stored as 1 (TRUE) or 0 (FALSE).

Basic SELECT Query

The SELECT statement retrieves data from a table. Its basic syntax is:

SELECT column1, column2 FROM table_name;

This query selects specific columns from a table. For example, to retrieve the name, age_yrs, and weight_lbs columns from the puppies table:

SELECT name, age_yrs, weight_lbs FROM puppies;

Result:

name	age_yrs	weight_lbs
Cooper	1	18
Indie	0.5	13
Kota	0.7	26
Zoe	0.8	32
Charley	1.5	25
Ladybird	0.6	20
Callie	0.9	16
Jaxson	0.4	19
Leinni	1	25
Max	1.6	65

SELECT All Columns

To retrieve all columns, use the wildcard *:

SELECT * FROM puppies;

This will return the full table. While convenient for exploration, avoid using * in production queries as it may retrieve unnecessary data and impact performance.

Using the WHERE Clause

To filter results, use the WHERE clause. The basic syntax is:

SELECT column1, column2 FROM table_name
WHERE condition;

Example: Filter by a Single Value

To find the row where id is 5:

SELECT * FROM puppies
WHERE id = 5;

Result:

id	name	age_yrs	breed	weight_lbs	microchipped
5	Charley	1.5	Basset Hound	25	0

Note: String values in WHERE conditions must be enclosed in single quotation marks.

What You Learned

How to write basic SELECT queries to retrieve specific columns.
How to use SELECT * to retrieve all columns from a table.
How to filter results using the WHERE clause.

The SELECT statement is a powerful tool for retrieving and filtering data. Practice crafting precise queries to improve your efficiency and data analysis skills.