After inserting records into a database table, we often need to retrieve and view that information.
In PostgreSQL, the SELECT statement is used to retrieve data from one or more tables.
Real-life example:
Imagine a university database containing thousands of student records. The Registrar Office may need to:
All these tasks can be performed using the SELECT statement.
SELECT column1, column2
FROM table_name;
Understanding the syntax:
SELECT: Specifies which columns you want to retrieve.column1, column2: Names of the columns.FROM: Specifies the table name.table_name: The table from which data is retrieved.Let’s create a students table.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
email VARCHAR(100),
age INTEGER,
city VARCHAR(50)
);
Insert some sample records:
INSERT INTO students
(student_id, student_name, email, age, city)
VALUES
(1, 'Ali', 'ali@example.com', 20, 'Multan'),
(2, 'Ahmad', 'ahmad@example.com', 21, 'Lahore'),
(3, 'Usman', 'usman@example.com', 22, 'Dera Ghazi Khan'),
(4, 'Sara', 'sara@example.com', 19, 'Multan'),
(5, 'Ayesha', 'ayesha@example.com', 20, 'Lahore');
To retrieve all columns from a table, use the asterisk (*) symbol.
SELECT *
FROM students;
Understand the code:
SELECT tells PostgreSQL to retrieve data.* means all columns.FROM students specifies the students table.This query displays all columns and records from the students table.
Sometimes we do not need all the information.
For example, if we only want student names:
SELECT student_name
FROM students;
To retrieve student names and email addresses:
SELECT student_name, email
FROM students;
To retrieve student IDs, names, and cities:
SELECT student_id, student_name, city
FROM students;
Important: Write column names separated by commas.
We can use the AS keyword to give a temporary name to a column in the query result.
This is called a column alias.
SELECT student_name AS name
FROM students;
The result displays the column heading as name instead of student_name.
Another example:
SELECT
student_name AS name,
city AS hometown
FROM students;
Important: An alias changes the display name in the query result. It does not rename the actual table column.
Sometimes a column contains repeated values.
For example, multiple students may belong to the same city.
To display each city only once, use DISTINCT.
SELECT DISTINCT city
FROM students;
To retrieve unique combinations of cities and ages:
SELECT DISTINCT city, age
FROM students;
Important: DISTINCT removes duplicate combinations of the selected columns.
Suppose we want to retrieve all student ages.
SELECT age
FROM students;
This query displays the age of every student, including repeated values.
To display only unique ages:
SELECT DISTINCT age
FROM students;
Consider the following query:
SELECT student_name, city
FROM students;
Think of it as asking two questions:
SELECT: What information do I want?FROM: Where should PostgreSQL get that information?This is the basic idea behind retrieving data from a database.
Try the following queries using the students table.
Example 1: Display all students
SELECT *
FROM students;
Example 2: Display only student names
SELECT student_name
FROM students;
Example 3: Display student names and ages
SELECT student_name, age
FROM students;
Example 4: Display unique cities
SELECT DISTINCT city
FROM students;
Example 5: Display student names with readable column headings
SELECT
student_name AS "Student Name",
email AS "Email Address"
FROM students;
Use the students table created in this lesson.
Exercise 1: Retrieve All Records
Write a query to display all records from the students table.
Exercise 2: Select Specific Columns
Write queries to display:
Exercise 3: Use DISTINCT
Write queries to display:
Exercise 4: Use Column Aliases
Write a query that displays:
student_name as Name.email as Email Address.city as Home City.Exercise 5: Think and Practice
What is the difference between these two queries?
SELECT city
FROM students;
SELECT DISTINCT city
FROM students;
*) represent?| SQL Keyword | Purpose |
|---|---|
SELECT |
Retrieves data. |
* |
Selects all columns. |
FROM |
Specifies the source table. |
AS |
Assigns a temporary column alias. |
DISTINCT |
Removes duplicate results. |
Next Lesson: WHERE Clause in PostgreSQL
Students will learn how to filter records using conditions, comparison operators, and logical operators.