SQL Fundamentals
SQL (Structured Query Language) is the standard language for querying and manipulating data in relational databases. It covers SELECT statements, filtering, joins, aggregation, subqueries, data modification, and the declarative approach to describing what data you want rather than how to get it.
itProgramming languages | OpenSkills.info
Intro
SQL Fundamentals
What SQL is
SQL (Structured Query Language) is the language you use to define, query, and
change data stored in a relational database. "Relational" means the data
lives in tables — rows and columns — and tables relate to each other through
shared key values instead of nested structures. You write what result you
want; the database figures out how to get it. That's what makes SQL
declarative: a SELECT statement describes the shape of the answer, not the
steps to produce it.
SQL was built for exactly this job in the early 1970s at IBM, based on Edgar F. Codd's relational model, and it has stayed the dominant way to work with structured data ever since. Nearly every relational database — PostgreSQL, MySQL, SQL Server, Oracle, SQLite — speaks a dialect of it.
Why it exists
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.postgresql.org/docs/current/tutorial-sql.html
Supports
- DDL vs DML distinction and what each covers
- Creating tables, inserting rows, querying, updating, deleting
- The basic shape of a SELECT statement
- https://www.postgresql.org/docs/current/sql-commands.html
Supports
- Categorization of SQL commands into DDL, DML, transaction control, access control, and other groups
- Quiz answer distinguishing CREATE/ALTER (DDL) from INSERT/DELETE (DML), GRANT/REVOKE (DCL), and BEGIN/COMMIT (TCL)
- https://www.postgresql.org/docs/current/queries-table-expressions.html
Supports
- INNER, LEFT, RIGHT, FULL, and CROSS JOIN semantics
- ON vs. WHERE clause evaluation order and its effect on outer joins
- USING and NATURAL JOIN shorthand
- GROUP BY and HAVING behavior, and the FROM/WHERE/GROUP BY/HAVING/SELECT/ORDER BY evaluation order
- Quiz answers on LEFT JOIN NULL-filling and WHERE-after-outer-join behavior
- https://www.postgresql.org/docs/current/ddl-constraints.html
Supports
- PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraint semantics
- Foreign key referential actions (CASCADE, RESTRICT, NO ACTION, SET NULL, SET DEFAULT)
- UNIQUE allowing multiple NULLs by default
- Quiz answers on what PRIMARY KEY and FOREIGN KEY guarantee
- https://www.postgresql.org/docs/current/tutorial-transactions.html
Supports
- Definition of a transaction and atomicity
- BEGIN/COMMIT/ROLLBACK behavior and savepoints
- Implicit per-statement transactions
- Quiz answer on why transactions matter for a bank transfer
- https://www.postgresql.org/docs/current/indexes-intro.html
Supports
- Why indexes speed up lookups vs. full table scans
- CREATE INDEX basic usage
- Write-overhead tradeoff of maintaining indexes
- Quiz answer on index write cost with frequent INSERTs
- https://www.postgresql.org/docs/current/functions-subquery.html
Supports
- EXISTS, IN, NOT IN, ANY/SOME, ALL subquery expression semantics
- NULL handling and three-valued logic in NOT IN
- Quiz answer on NOT IN silently returning no rows when the subquery contains NULL
- https://www.postgresql.org/docs/current/datatype.html
Supports
- Major SQL data type categories (numeric, character, date/time, boolean, JSON, and others)
- https://www.sqlite.org/lang.html
Supports
- SQLite as a representative example of vendor SQL dialect variation
- https://dev.mysql.com/doc/refman/8.4/en/
Supports
- MySQL as a representative example of vendor SQL dialect variation
- https://en.wikipedia.org/wiki/SQL
Supports
- SQL's origin at IBM, SEQUEL naming, Codd's relational model, System R
- ANSI SQL-86 and ISO/IEC 9075:1987 standardization timeline
- Later standard revisions (SQL-92 through SQL:2023) and their headline features
- DDL/DML/DQL/DCL sublanguage terminology
- Known limitations: duplicate rows, ordered results, three-valued NULL logic, incomplete standards compliance, vendor divergence
- Quiz answers on declarative nature and incomplete standard compliance across vendors
- https://en.wikipedia.org/wiki/Database_normalization
Supports
- Definitions of 1NF, 2NF, 3NF, and BCNF
- Purpose of normalization (redundancy reduction, anomaly prevention)
- Denormalization tradeoff for read-heavy systems
- Quiz answer distinguishing a 2NF-but-not-3NF transitive dependency from a 1NF or 2NF violation