SQL Operators

SQL operators are reserved words or characters used primarily in a WHERE clause to perform operations, such as comparisons and arithmetic. They are the building blocks for filtering data and creating conditions in your queries.

Database Preparation

Run this block to set up the tables used in the examples below.

DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1, 'Widget', 50.00),
       (2, 'Gadget', 100.00);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 'Fantasy', 1937),
       (2, '1984', 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 'Fantasy', 1954),
       (4, 'Dune', 'Sci-Fi', 1965);

SELECT 'Ready' AS Status;

1. Arithmetic Operators

Example Query: Calculate the final price of a product after a $10 discount.

SELECT ProductName, Price - 10 AS DiscountedPrice FROM Products;

2. Comparison Operators

Example Query: Find all books published on or after 1950.

SELECT Title, PublishedYear FROM Books WHERE PublishedYear >= 1950;

3. Logical Operators

Example Query: Find all books in the 'Fantasy' genre that were published before 1940.

SELECT Title, Genre, PublishedYear
FROM Books
WHERE Genre = 'Fantasy' AND PublishedYear < 1940;

(For more detailed examples, see the pages on Logical Operators, WHERE Clause, and Advanced Queries.)

4. Bitwise Operators

Bitwise operators are used to perform bit manipulations between two integer expressions. They convert the integers to binary bits and then perform the operation. These are less common in general data analysis but are used in specific low-level applications.

Example: Imagine you have an integer 5 (binary 0101) and 3 (binary 0011).

• SELECT 5 & 3;
  • 0101 AND 0011 results in 0001, which is 1.
• SELECT 5 | 3;
  • 0101 OR 0011 results in 0111, which is 7.
• SELECT 5 ^ 3;
  • 0101 XOR 0011 results in 0110, which is 6.

Hands-on Exercise

Task: Find all books in the 'Fantasy' genre that were published before 1950.

Your Query:

-- Write your query here

Expected Results: