SQL And Databases

This deck introduces the foundational concepts behind databases and SQL, starting with the basics of what a database is and how relational database management systems work. It then moves into core SQL topics like table structures, primary and foreign keys, common data types, and the DDL statements such as CREATE TABLE, ALTER TABLE, and DROP TABLE. You'll also explore important concepts like UNIQUE and CHECK constraints, which help keep your data accurate and reliable. Together, these cards build a solid mental model of how data is organized, defined, and manipulated.

It's a great fit if you're brand new to SQL, preparing for a technical interview, taking an introductory database course, or simply want a refresher on the terminology you keep running into at work. The questions are phrased in a clear, definition-style format, so even if you have no prior experience with databases, you can work through them at your own pace and build confidence as you go.

To get the most out of these flashcards, try reviewing a small batch of cards each day rather than cramming everything at once. Spaced repetition works especially well for vocabulary-heavy topics like database terminology, since the terms tend to build on one another. When you come across a concept that feels fuzzy, jot down a quick example in your own words; writing out how a primary key or a CHECK constraint works in plain language will help the definition stick far longer than simply memorizing it.

A database is an organized collection of structured data stored and accessed electronically from a computer system. To work with this data efficiently, applications rely on a Database Management System, or DBMS, which provides the software layer for storage, retrieval, and administration. When the data is organized into tables that are related to one another through keys, the system is called a Relational Database Management System, or RDBMS. Popular relational engines include PostgreSQL, Oracle, and Microsoft SQL Server.

Databases come in many shapes beyond the relational model. The two broad families are relational databases, which use SQL and a fixed schema (for example, MySQL), and non-relational databases, often called NoSQL, which include document stores like MongoDB and wide-column stores like Cassandra. Specialized forms such as hierarchical, network, and graph databases serve particular use cases where relationships or hierarchies are central.

SQL, the Structured Query Language, is the standard language for managing relational databases. It is organized into sublanguages, including Data Definition Language for schemas, Data Manipulation Language for working with data, and Data Control Language for permissions. While there is an ANSI SQL standard, real-world systems implement their own dialects, such as MySQL, PostgreSQL, SQLite, Oracle SQL, and T-SQL from SQL Server. PostgreSQL tracks the standard closely, whereas MySQL adds convenient extensions like GROUP BY shortcuts.

Inside a relational database, the table is the primary unit of structured storage. A table is composed of rows, which are individual records, and columns, which are named attributes. Every column has a defined data type that determines what kind of values it can hold, and together the tables are linked to one another through keys.

The primary key is the unique identifier for each row in a table. It guarantees that no two records are identical and makes lookups efficient. A primary key may consist of a single column or several columns combined into a composite key. To model relationships between tables, a foreign key is used: it is a column, or set of columns, in one table that points to the primary key of another. For example, an orders.customer_id foreign key can be linked to customers.id, enforcing referential integrity so that orders cannot reference customers that do not exist.

SQL provides a rich set of data types that vary slightly between systems. Numeric types include INT and FLOAT, string types include VARCHAR and TEXT, date and time types include DATE and TIMESTAMP, and boolean values are represented with BOOLEAN. Beyond data types, constraints shape what values a column can accept. A CHECK constraint enforces domain integrity by restricting values, for example requiring age >= 18. A UNIQUE constraint guarantees that all values in a column are distinct, which is useful for fields like email addresses; unlike PRIMARY KEY, UNIQUE allows NULL values.

Data Definition Language, or DDL, is the part of SQL used to define and manage the structure of a database, including tables, indexes, and views. The three commands at the heart of DDL are CREATE, ALTER, and DROP. They let a developer build new structures, modify existing ones, and remove structures that are no longer needed.

The CREATE TABLE statement defines a brand-new table by specifying its columns, their data types, and any constraints. For example, CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); creates a table with two columns and a primary key on the id. Once a table exists, its structure can evolve using ALTER TABLE, which adds or drops columns and constraints. An example is ALTER TABLE users ADD email VARCHAR(100);, which appends a new email column to the users table.

When a table is no longer needed, the DROP TABLE command permanently removes it along with all of its data. The command DROP TABLE temp_table; is final, so it should be used with care. Together, CREATE, ALTER, and DROP give administrators full control over the database schema, from initial creation through ongoing maintenance.

Data Manipulation Language, or DML, is the part of SQL used to read and change the data stored in tables. Its four main commands are SELECT for reading, INSERT for adding new rows, UPDATE for changing existing rows, and DELETE for removing rows. Although the syntax is simple, careful use of filtering clauses is essential to avoid unintended changes.

The SELECT statement retrieves data from one or more tables. A query like SELECT column1, column2 FROM table_name; returns specific columns, while SELECT * FROM users; returns every column. Rows can be filtered using a WHERE clause, which supports operators such as =, >, LIKE, and IN; for instance, SELECT * FROM users WHERE age > 18; returns only adults. Results can be sorted with ORDER BY, either ascending (ASC) or descending (DESC), and the number of rows returned can be limited with LIMIT, optionally combined with OFFSET for pagination. NULL values require special handling because they are not equal to anything: IS NULL and IS NOT NULL are used for testing, while functions like COALESCE(value, default) and IFNULL(value, default) substitute a fallback when a value is missing.

Adding new data uses the INSERT INTO statement, which can add a single row or multiple rows at once, such as INSERT INTO users (name, age) VALUES ('Alice', 25);. To change existing rows, the UPDATE statement modifies values, always paired with a WHERE clause to avoid rewriting every row in the table, as in UPDATE users SET age = 26 WHERE id = 1;. The DELETE FROM statement removes rows, again with a WHERE clause to target specific records; omitting the WHERE clause deletes every row in the table. Used together, these commands cover the full lifecycle of working with data.

SQL is not limited to reading individual rows. Aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() perform calculations across sets of rows, producing summary values like totals or averages. When these aggregates are combined with GROUP BY, the table is partitioned into groups that share the same values in the listed columns, and an aggregate is computed per group. For example, SELECT department, AVG(salary) FROM employees GROUP BY department; gives the average salary for each department. While WHERE filters individual rows before grouping, HAVING filters the resulting groups; HAVING AVG(salary) > 50000 keeps only departments whose average salary exceeds that threshold.

Most real-world queries combine data from multiple tables. Joins make this possible by combining rows from two or more tables based on related columns. An INNER JOIN returns only rows with matching values in both tables, while a LEFT JOIN returns all rows from the left table along with their matches from the right, filling non-matches with NULLs. The RIGHT JOIN does the mirror image, and a FULL OUTER JOIN returns all rows from both sides, again with NULLs where no match exists. A special case is the self-join, where a table is joined to itself, which is helpful for traversing hierarchical relationships such as an employee-to-manager chain.

Sometimes a query needs to draw on the results of another query. A subquery is a SELECT statement nested inside another query, used in the WHERE, FROM, or SELECT clauses, such as SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);. A correlated subquery goes further by referencing columns from the outer query, so it executes once per outer row; this is useful for checks like SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = u.id);, which finds users who have placed at least one order. Finally, UNION combines the result sets of two queries and removes duplicates, while UNION ALL keeps duplicates; both require the queries to return compatible columns in the same order.

As tables grow, raw scans become expensive, and indexes are the primary tool for speeding up lookups. An index is a data structure, much like the index at the back of a book, that allows the database to find rows matching a column value without examining every record. It is created with a statement like CREATE INDEX idx_name ON table(column);. Different types of indexes serve different needs: B-tree indexes are the default and work well for equality and range queries, Hash indexes are optimized for exact matches, Bitmap indexes suit low-cardinality columns, and Full-text indexes support text searching. A clustered index physically sorts the table data according to the index key.

Views simplify complex queries and enhance security by exposing a virtual table defined by a SELECT statement. Creating CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1; gives developers a convenient name for a frequent filter. Window functions add another layer of analytical power; they compute values across a set of rows related to the current row without collapsing them. Examples include ROW_NUMBER() OVER (ORDER BY score DESC), RANK(), and LAG(). For very large tables, partitioning divides the data into smaller, manageable pieces by range, list, or hash, which improves both query performance and maintenance.

To keep queries running fast, query optimization is essential. The first step is usually inspecting an execution plan with EXPLAIN, or EXPLAIN ANALYZE in PostgreSQL, which reveals how the database intends to run a query and where the bottlenecks lie. From there, developers can add appropriate indexes, rewrite queries to reduce joins or subqueries, and rely on the database's statistics about data distribution to choose efficient plans.

A transaction is a unit of work that takes the database from one consistent state to another, and it must satisfy the ACID properties. Atomicity guarantees that all of the operations within a transaction either complete together or have no effect at all. Consistency ensures that the database ends in a valid state that respects all constraints. Isolation shields concurrent transactions from each other's intermediate results, and Durability guarantees that, once a transaction commits, its changes survive crashes and power loss. In SQL, transactions are managed with BEGIN, COMMIT, and ROLLBACK.

Isolation is tunable through isolation levels, which trade off consistency for performance. READ UNCOMMITTED permits dirty reads, where a transaction sees uncommitted changes from another. READ COMMITTED prevents dirty reads by only seeing committed data. REPEATABLE READ ensures that, within a transaction, the same query returns the same result each time. SERIALIZABLE offers the strictest isolation, effectively running transactions one after another. The level is set with SET TRANSACTION ISOLATION LEVEL;.

Good schema design supports these guarantees through normalization, a process that reduces redundancy and dependency. The First Normal Form, or 1NF, requires that every column hold atomic values, that there be no repeating groups, and that each table have a primary key. The Second Normal Form, 2NF, builds on 1NF by removing partial dependencies so that non-key attributes depend on the entire primary key. The Third Normal Form, 3NF, goes further by removing transitive dependencies so that non-key attributes do not depend on other non-key attributes. Beyond schema design, the database itself can host reusable logic: stored procedures are precompiled SQL routines that reduce network traffic, while triggers are special procedures that automatically fire on events such as INSERT, UPDATE, or DELETE.

The choice between SQL and NoSQL databases depends on the shape of the data and the demands of the application. SQL databases are relational and use a fixed schema, making them ideal for highly structured data where ACID compliance is essential. NoSQL databases are non-relational and offer flexible schemas, allowing them to handle unstructured or semi-structured data such as JSON documents. They are typically chosen for horizontal scalability and rapid iteration on data models. The two families are not mutually exclusive; many systems use each where it fits best.

Security is a constant concern, and SQL injection remains one of the most common attack vectors against database-backed applications. Injection occurs when untrusted input is concatenated directly into SQL, allowing attackers to alter the query's meaning. The defenses are well known: use prepared statements and parameterized queries wherever possible, lean on stored procedures that bind values rather than building SQL strings, validate and sanitize input at the application layer, and escape any input that must be embedded directly. Combined, these practices keep queries safe and predictable.

Across all of these topics, from foundational concepts like tables and keys to advanced features like transactions, indexes, and security, SQL and the relational model remain a cornerstone of modern data management. Mastering the language, the design principles, and the operational safeguards equips a developer to build reliable, efficient, and trustworthy data systems.