SQL Fundamentals: Operators and Data Types

Comparison operators allow you to compare values and filter data based on these comparisons. These operators are crucial for selecting specific data from your tables. Here's a table of the most common ones:

• = (Equals): Selects rows where a column's value is equal to a specified value.
• != or <> (Not Equals): Selects rows where a column's value is not equal to a specified value.
• < (Less Than): Selects rows where a column's value is less than a specified value.
• > (Greater Than): Selects rows where a column's value is greater than a specified value.
• <= (Less Than or Equal To): Selects rows where a column's value is less than or equal to a specified value.
• >= (Greater Than or Equal To): Selects rows where a column's value is greater than or equal to a specified value.

Example (using a hypothetical Customers table):

-- Select customers with a city of 'New York'
SELECT * FROM Customers WHERE city = 'New York';

-- Select customers NOT in 'London'
SELECT * FROM Customers WHERE city != 'London';

-- Select customers with an age greater than 30
SELECT * FROM Customers WHERE age > 30;

--Select customers with an age less than or equal to 25
SELECT * FROM Customers WHERE age <= 25;

Logical operators let you combine multiple conditions within your WHERE clause, creating more complex and precise filtering. The main logical operators are:

• AND: Requires both conditions to be true. WHERE condition1 AND condition2
• OR: Requires at least one of the conditions to be true. WHERE condition1 OR condition2
• NOT: Negates a condition (makes it false if it was true, and true if it was false). WHERE NOT condition

Example:

-- Select customers in 'New York' AND with an age over 40
SELECT * FROM Customers WHERE city = 'New York' AND age > 40;

-- Select customers in 'Paris' OR 'Tokyo'
SELECT * FROM Customers WHERE city = 'Paris' OR city = 'Tokyo';

-- Select customers NOT in 'Berlin'
SELECT * FROM Customers WHERE NOT city = 'Berlin'; -- or WHERE city != 'Berlin'

--Combining AND and OR
SELECT * FROM Customers WHERE (city = 'Paris' OR city = 'Tokyo') AND age < 30;

SQL uses different data types to store different kinds of data. Understanding these data types is essential for writing accurate and efficient queries. Here are some of the most common:

• INT: Whole numbers (integers), e.g., 10, -5, 1000.
• VARCHAR(size): Variable-length character strings (text). size specifies the maximum number of characters. e.g., VARCHAR(50) allows up to 50 characters.
• CHAR(size): Fixed-length character strings. size specifies the exact number of characters. Less common than VARCHAR.
• DATE: Dates, e.g., '2023-10-27'. Format varies slightly by database system (e.g., MySQL, PostgreSQL, SQL Server).
• DATETIME or TIMESTAMP: Date and time, e.g., '2023-10-27 10:30:00'.
• FLOAT or DECIMAL: Numbers with decimal points. DECIMAL is generally preferred for precision (especially with financial data).
• BOOLEAN: Stores TRUE or FALSE values (often implemented as 1/0 or other numeric representations).

Example (assuming a Products table):

-- The product price is stored as decimal(10,2) (ten total digits, two after the decimal)
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    launch_date DATE
);