Databases 101
A DBMS organizes data into these two-dimensional structures of rows and columns.
What are tables?
The keyword used to retrieve data from a table.
What is SELECT?
The column that uniquely identifies each row in a table.
What is a primary key?
Running DELETE without this clause wipes every row.
What is WHERE?
A group of SQL operations treated as one unit of work.
What is a transaction?
The term for a single horizontal entry in a table.
What is a row (record)?
The clause that filters rows based on a condition.
What is WHERE?
A column that links to the primary key of another table.
What is a foreign key?
The special value representing missing or unknown data (and it breaks comparisons)
What is NULL?
The command that permanently saves a transaction's changes.
What is COMMIT?
This database model organizes data into related tables and is the most widely used.
What is relational?
What you use to combine rows from two tables based on a related column.
What is a JOIN?
The overall process of organizing tables to reduce redundancy.
What is normalization?
A JOIN with no proper condition can blow up into this. Every row matched with every row.
What is a Cartesian product (cross join)?
The command that undoes changes and reverts to the prior state.
What is ROLLBACK?
SQL stands for?
What is Structured Query Language
Which clause groups rows so aggregates like COUNT run per group? A) ORDER BY B) GROUP BY C) HAVING D) WHERE
What is B) GROUP BY?
First Normal Form (1NF) mainly requires that: A) every table has a foreign key B) each cell holds a single (atomic) value C) there are no nulls D) all columns are indexed
What is B) each cell holds a single (atomic) value?
Two transactions each wait forever for the other's lock. This is a: A) rollback B) deadlock C) commit D) crash
What is B) a deadlock?
The "A" in ACID, all operations succeed or none do. A) Availability B) Atomicity C) Accuracy D) Authorization
What is B) atomicity?
Which is NOT part of "CRUD"? A) Create B) Read C) Undo D) Delete
What is C) Undo? (CRUD is Create, Read, Update, Delete)
To list customers from highest to lowest spending: A) ORDER BY spending ASC B) SORT BY spending C) ORDER BY spending DESC D) GROUP BY spending
What is C) ORDER BY spending DESC?
Daily Double: In the case study, after breaking in through SQL injection, the attackers deployed this custom web shell to steal data from MOVEit servers.
What is LEMURLOOT?
A query crawls on a huge table. Most common fix? A) Add an index B) Delete the table C) Add more columns D) Rename the table
What is A) add an index?
Which property guarantees a committed transaction survives a power failure? A) Consistency B) Isolation C) Durability D) Atomicity
What is C) durability?