Selecting Data and Performing Operations Using Logical Operators in PostgreSQL

Introduction

In this tutorial, we will focus on retrieving specific data from a database using the SELECT statement in SQL, with an emphasis on filtering data using logical operators such as AND, OR, and NOT. These operators allow us to combine multiple conditions to select precisely the data we need. We will use PostgreSQL as our database management system and pgAdmin 4 as our interface for running queries.

By the end of this tutorial, you will be able to:

  • Write basic SELECT statements to retrieve data from a table.
  • Use the WHERE clause to filter data based on specific conditions.
  • Combine conditions using the logical operators AND, OR, and NOT.
  • Understand the importance of parentheses in complex queries.

This tutorial assumes that you have already covered the following topics:

  • Installing and configuring PostgreSQL.
  • Designing databases and understanding entity-relationship diagrams.
  • Creating, modifying, and deleting tables in SQL.

Setting Up the Sample Database

To follow along with the examples, we will first create a sample table called students and populate it with data. This table will serve as the basis for all queries in this tutorial.

  1. Open pgAdmin 4 and connect to your PostgreSQL server.
  2. Select Tools > Query Tool to open the SQL editor.
  3. Run the following SQL commands to create and populate the students table:
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INTEGER,
    major VARCHAR(50),
    gpa DECIMAL(3,2)
);

INSERT INTO students (first_name, last_name, age, major, gpa) VALUES
('John', 'Doe', 20, 'Computer Science', 3.5),
('Jane', 'Smith', 22, 'Mathematics', 3.8),
('Alice', 'Johnson', 19, 'Physics', 3.2),
('Bob', 'Brown', 21, 'Chemistry', 2.9),
('Charlie', 'Davis', 23, 'Biology', 3.6),
('Diana', 'Miller', 20, 'Computer Science', 3.7),
('Eve', 'Wilson', 22, 'Mathematics', 3.9),
('Frank', 'Taylor', 19, 'Physics', 3.1),
('Grace', 'Anderson', 21, 'Chemistry', 3.0),
('Hank', 'Thomas', 23, 'Biology', 3.4);

This creates a table with 10 student records, each containing a student ID, first name, last name, age, major, and GPA.

The SELECT Statement

The SELECT statement is used to retrieve data from a database. Its basic syntax is:

SELECT column1, column2, ... FROM table_name WHERE condition;
  • column1, column2, ...: The columns you want to retrieve. Use * to select all columns.
  • table_name: The name of the table from which to retrieve data.
  • WHERE condition: An optional clause to filter data based on specific criteria.

For example, to select all columns from the students table:

SELECT * FROM students;

To select only the first_name and last_name columns:

SELECT first_name, last_name FROM students;

Filtering Data with the WHERE Clause

The WHERE clause allows you to filter the data based on specific conditions. For instance, to select students majoring in ‘Computer Science’:

SELECT * FROM students WHERE major = 'Computer Science';

This query returns all students whose major is ‘Computer Science’.

Important Notes:

  • String values in conditions must be enclosed in single quotes (e.g., 'Computer Science').
  • Numeric values do not require quotes (e.g., 20 or 3.5).
  • String comparisons in PostgreSQL are case-sensitive, so 'Computer Science' is different from 'computer science'.

Logical Operators: AND, OR, and NOT

Logical operators allow you to combine multiple conditions in the WHERE clause:

  • AND: Returns true only if both conditions are true.
  • OR: Returns true if at least one of the conditions is true.
  • NOT: Negates a condition, returning true if the condition is false.
Using AND

The AND operator combines two or more conditions, and the query returns rows only if all conditions are true.

Example: To select students majoring in ‘Computer Science’ with a GPA greater than 3.5:

SELECT * FROM students WHERE major = 'Computer Science' AND gpa > 3.5;

You can also use multiple AND operators:

SELECT * FROM students WHERE major = 'Computer Science' AND gpa > 3.5 AND age < 22;

This query selects students who meet all three conditions.

Using OR

The OR operator combines two or more conditions, and the query returns rows if at least one condition is true.

Example: To select students majoring in either ‘Computer Science’ or ‘Mathematics’:

SELECT * FROM students WHERE major = 'Computer Science' OR major = 'Mathematics';

Using NOT

The NOT operator negates a condition.

Example: To select students who are not majoring in ‘Physics’:

SELECT * FROM students WHERE NOT major = 'Physics';

This is equivalent to:

SELECT * FROM students WHERE major <> 'Physics';

However, for this tutorial, we will focus on using NOT with conditions.

Combining Logical Operators

You can combine AND, OR, and NOT in a single query. When doing so, it is crucial to use parentheses to specify the order of operations and avoid confusion.

Example: To select students who are majoring in ‘Computer Science’ or ‘Mathematics’ and have a GPA greater than 3.5:

SELECT * FROM students WHERE (major = 'Computer Science' OR major = 'Mathematics') AND gpa > 3.5;

Without parentheses, the query might be misinterpreted due to operator precedence.

Operator Precedence: In SQL, AND has higher precedence than OR. This means that in an expression without parentheses, AND is evaluated before OR. For example:

SELECT * FROM students WHERE major = 'Computer Science' OR major = 'Mathematics' AND gpa > 3.5;

This is equivalent to:

SELECT * FROM students WHERE major = 'Computer Science' OR (major = 'Mathematics' AND gpa > 3.5);

To avoid ambiguity, always use parentheses in complex conditions.

Another Example with NOT: To select students who are not majoring in ‘Computer Science’ or ‘Mathematics’:

SELECT * FROM students WHERE NOT (major = 'Computer Science' OR major = 'Mathematics');

This query returns students majoring in any other field.

Practice Exercises

To reinforce your understanding, try writing and running the following queries:

  1. Select students who are older than 20 and have a GPA less than 3.5.
SELECT * FROM students WHERE age > 20 AND gpa < 3.5;
  1. Select students who are majoring in ‘Biology’ or ‘Chemistry’ and are younger than 22.
SELECT * FROM students WHERE (major = 'Biology' OR major = 'Chemistry') AND age < 22;
  1. Select students who are not majoring in ‘Physics’.
SELECT * FROM students WHERE NOT major = 'Physics';
  1. Select students who are majoring in ‘Computer Science’ with a GPA greater than 3.5 or majoring in ‘Mathematics’ with a GPA greater than 3.8.
SELECT * FROM students WHERE (major = 'Computer Science' AND gpa > 3.5) OR (major = 'Mathematics' AND gpa > 3.8);

Experiment with modifying these queries to see how the results change. This will help you understand how logical operators affect data selection.