Week 20 - SQL Chips and Chunks Newsletter

The Power of Data Types

1. SQL Chip: Think of data types as the labels on your storage containers. They tell the database what kind of information a column can hold—like a TEXT label for notes or an INT label for whole numbers. By choosing the right "container," you ensure your data is stored correctly and efficiently.

2. SQL Chunk: When you retrieve data, it's important to remember what kind of information you're working with. Here's a clean example.

SELECT
    product_id,     -- INTEGER, a whole number
    product_name,   -- VARCHAR(255), a string up to 255 characters
    list_price      -- DECIMAL(10, 2), a number with 10 total digits and 2 decimal places
FROM
    products
WHERE
    list_price > 50.00;

(In this example, the database uses DECIMAL for list_price to handle monetary values precisely, avoiding rounding errors.)

3. SQL Challenge: Matching data types is crucial when joining tables. Joining on columns with different data types can cause performance issues or fail entirely. For example, joining a numeric employee_id with a text employee_id might not work as expected.

-- SQL Mistake Example: Joining on mismatched data types
SELECT
    E.first_name,
    D.department_name
FROM
    employees AS E      -- employee_id is INTEGER
INNER JOIN
    departments AS D    -- department_id is VARCHAR
ON
    E.employee_id = D.department_id; -- This will likely fail!

-- The FIX: Proper foreign key relationship with matching data types
SELECT
    E.first_name,
    D.department_name
FROM
    employees AS E      -- employee_id is INTEGER, department_id is INTEGER
INNER JOIN
    departments AS D    -- department_id is INTEGER
ON
    E.department_id = D.department_id; -- Correct relationship and matching types

4. SQL Mistake: A very common mistake is using a string data type (VARCHAR) for a date column. This makes it impossible to use powerful date functions or sort the data chronologically, which can lead to unexpected results.

-- SQL Mistake Example: 'order_date' is stored as VARCHAR
-- String comparisons are not the same as chronological date comparisons.
SELECT
    order_id,      -- INTEGER
    order_date,    -- VARCHAR(10)
    customer_name  -- VARCHAR(255)
FROM
    orders
WHERE
    order_date > '2023-10-01';

-- The FIX: Always store dates as a proper DATE or DATETIME data type
-- This ensures filters and sorting work as intended.
SELECT
    order_id,      -- INTEGER
    order_date,    -- DATE
    customer_name  -- VARCHAR(255)
FROM
    orders
WHERE
    order_date > '2023-10-01';

5. SQL in Practice: In healthcare, a patient's medical records are a mix of different data types. Their unique ID is a number (INTEGER), their name is text (VARCHAR), and their birthdate is a date (DATE). Using the correct data type is critical for ensuring data integrity and allowing quick, accurate searches by doctors and administrators.

SELECT
    patient_id,         -- INTEGER, a unique identifier
    patient_name,       -- VARCHAR(100), the patient's full name
    date_of_birth,      -- DATE, for chronological sorting and age calculations
    COALESCE(address, 'N/A') AS address_info -- VARCHAR(255) with a default
FROM
    patients
ORDER BY
    date_of_birth DESC;

(This query correctly handles a variety of data types, and using the DATE type allows you to easily sort patients from oldest to youngest.)

6. SQL Resource: Must Know Data Types in SQL (Structured Query Language) - Coding with Mosh https://www.youtube.com/watch?v=bIcLOItLQlY This video is a great resource that clearly explains the most important SQL data types. The instructor provides a solid overview of character, numeric, and date/time data types, helping you understand the foundational concepts without getting lost in technical jargon. This resource will help you make better decisions when designing tables and writing queries.