SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEIntermediate

SQL Data Types: CAST, CONVERT & Type Guide

Introduction & Core Concept

Every column in a relational database schema holds a strict, predefined datatype (e.g., INTEGER, TEXT, REAL, DATE). However, when ingesting third-party data or legacy files, attributes are frequently imported as the wrong type (such as currency values stored as text strings).

To convert datatypes dynamically on the fly during query compilation, we write a CAST expression:

CAST(column_name AS new_datatype)
Common Datatype Casting Transformations
  • CAST(price_text AS REAL) ➔ Converts string representations into floating-point decimals for mathematical calculations.
  • CAST(id AS TEXT) ➔ Converts numerical indices into text strings for string concatenation.
    • Why & Where We Use It
  • Why We Use It: Comparing numbers against text strings triggers slow implicit conversions that bypass indexes. Explicit casting guarantees high-speed, predictable data handling.
  • Where We Use It: Data cleaning ETL pipelines, formatting presentation strings, and standardizing third-party API data imports.
    • Real-World Example

    ApexBank needs to display interest rates on their online banking portal as beautifully formatted text strings appended with percentage symbols. They cast the numeric interest_rate column to text and concatenate it with '%' .

    Best Practices: What to Do & What NOT to Do
  • What to Do: Use casting defensively when performing integer division to ensure floating-point precision is maintained (e.g., CAST(col1 AS REAL) / col2).
  • What NOT to Do: Avoid casting column values inside your WHERE clause if possible (e.g., WHERE CAST(id AS TEXT) = '10'). Casting column attributes inside filtering constraints prevents the database engine from utilizing B-Tree index scans!
    • Syntax & Pro Tips
    SELECT 'Account: ' || CAST(id AS TEXT) FROM users;
    Interactive Sandboxed Terminal (Preloaded DB Schema: APEXBANK)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1