Structured Query Language Study Guide

📖 Core Concepts SQL – a domain‑specific, declarative language for managing relational data (querying, inserting, updating, deleting, schema definition, and access control). Four‑generation language – primarily declarative, but also offers procedural constructs. Clauses – optional or mandatory building blocks (e.g., SELECT, FROM, WHERE). Expressions – produce scalar values or whole tables (rows × columns). Predicates – evaluate to TRUE, FALSE, or UNKNOWN (three‑valued logic). NULL – a special marker meaning “value unknown/absent”; it is not zero, empty string, or a default. Data‑type categories – predefined, constructed, and user‑defined. Constructed types – ARRAY, MULTISET, REF, ROW. Primary key / UNIQUE constraint – enforces row uniqueness, preventing duplicate rows. --- 📌 Must Remember SQL removes the need to specify how to locate a record; you declare what you want. Vendor implementations often diverge from the ANSI/ISO standard (date literals, string concatenation, NULL handling, identifier case‑sensitivity). Three‑valued logic: any predicate involving NULL yields UNKNOWN, which behaves like FALSE in WHERE filters. Duplicate rows are allowed unless a primary key or UNIQUE constraint is defined. Exact numeric types: NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT. Approximate numeric types: FLOAT, REAL, DOUBLE PRECISION, DECFLOAT. Date/Time hierarchy: DATE → calendar date; TIME → clock time; TIMESTAMP → both; INTERVAL → duration. Character vs. Unicode: CHAR/VARCHAR = non‑Unicode; NCHAR/NCHAR VARYING = Unicode. Binary vs. Large Object: BINARY/VARBINARY = fixed/variable length; BLOB = large binary objects. --- 🔄 Key Processes Writing a basic SELECT query SELECT <columns> – choose columns (or ). FROM <table> – specify source table(s). WHERE <predicate> – filter rows (TRUE keeps row, FALSE/UNKNOWN discards). Optional: GROUP BY, HAVING, ORDER BY, LIMIT. Creating a table with constraints CREATE TABLE <name> ( <col definitions>, PRIMARY KEY (<col>), UNIQUE (<col>) ); Inserting data INSERT INTO <table> (col1, col2, …) VALUES (val1, val2, …); Updating rows UPDATE <table> SET col = expr WHERE <predicate>; Deleting rows DELETE FROM <table> WHERE <predicate>; (All steps follow the declarative “what, not how” principle.) --- 🔍 Key Comparisons CHAR vs. VARCHAR – fixed‑length padding vs. variable‑length storage. NCHAR vs. NCHAR VARYING – fixed‑length Unicode vs. variable‑length Unicode. BINARY vs. VARBINARY – fixed‑length binary vs. variable‑length binary. BLOB vs. VARBINARY – BLOB for large objects (images, files) vs. VARBINARY for moderate binary data. Exact numeric vs. Approximate numeric – precise decimal arithmetic (NUMERIC, DECIMAL) vs. floating‑point representation (FLOAT, REAL). SQL Standard vs. Vendor Implementation – standard defines syntax & semantics; vendors may alter literals, concatenation (|| vs. +), NULL handling, identifier case‑sensitivity. --- ⚠️ Common Misunderstandings NULL = 0 – false; NULL is “unknown", not a numeric value. Duplicate rows are illegal – they are allowed unless a primary key/unique constraint exists. Result order is guaranteed without ORDER BY – SQL tables are unordered sets; order must be explicit. SQL is purely procedural – it is mainly declarative; procedural extensions are optional. Identifiers are always case‑insensitive – case‑sensitivity varies by vendor (e.g., PostgreSQL vs. SQL Server). --- 🧠 Mental Models / Intuition “What, not how” – Think of SQL as a recipe: you list ingredients (SELECT, FROM) and conditions (WHERE), and the DB engine figures out the best way to cook it. Three‑valued logic – Treat UNKNOWN like a gray area that never passes a WHERE filter; only TRUE rows survive. NULL as a hole – Like a missing puzzle piece; you can’t compare it directly, you must test with IS NULL / IS NOT NULL. Data‑type fit – Match the shape of your data (fixed vs. variable length, text vs. binary, exact vs. approximate) to the appropriate type to avoid wasted space or precision loss. --- 🚩 Exceptions & Edge Cases Vendor‑specific date/time literals – some accept 'YYYY‑MM‑DD', others require DATE 'YYYY‑MM‑DD'. String concatenation – ANSI uses ||; many vendors (e.g., SQL Server) use +. Identifier case‑sensitivity – MySQL default is case‑insensitive; PostgreSQL treats unquoted identifiers as lower‑case. NULL handling in aggregates – COUNT() counts rows including NULLs; COUNT(column) ignores NULLs. LIMIT without ORDER BY – returns an arbitrary subset; order is not defined. --- 📍 When to Use Which Choose CHAR when all values have the same length (e.g., ISO country codes). Choose VARCHAR for variable‑length text where storage efficiency matters. Choose NCHAR/NCHAR VARYING when storing Unicode characters (multilingual text). Choose BLOB for large binary objects (images, PDFs). Use DATE when time of day is irrelevant; TIMESTAMP when both date and time are needed. Pick exact numeric (NUMERIC, DECIMAL) for monetary values; approximate (FLOAT, DOUBLE PRECISION) for scientific measurements where rounding is acceptable. Add PRIMARY KEY or UNIQUE whenever row uniqueness is a logical requirement. --- 👀 Patterns to Recognize Missing primary key → possible duplicate‑row issues. NULL in a predicate → result becomes UNKNOWN, causing row exclusion. Vendor‑specific syntax clues (e.g., SELECT TOP → SQL Server; LIMIT → MySQL/PostgreSQL). Use of INTERVAL usually indicates a duration calculation rather than a timestamp. Presence of CLOB/NCLOB or BLOB signals handling of large text or binary data. --- 🗂️ Exam Traps “NULL = NULL” – tempting but always yields UNKNOWN; correct test is IS NULL. Assuming ORDER BY is implicit – questions may omit ORDER BY; answer choices that rely on row order are wrong. Choosing the wrong concatenation operator – pick || for standard SQL unless the vendor is explicitly stated. Believing all vendors enforce case‑insensitivity – watch for vendor‑specific wording; case‑sensitive identifiers can change answer correctness. Confusing COUNT() with COUNT(column) – the latter ignores NULLs; a common distractor in aggregate questions. ---