SQL Database Management Flashcards

Question 1

What is a table in a relational database?

Accepted Answer

A table is a named collection of rows and columns that stores data about one kind of thing, such as customers. Each column has a name and a data type, and each row holds one record. Tables relate to each other through shared key values, so information can be stored once and referenced elsewhere.

Question 2

What is a primary key and why is it useful?

Accepted Answer

A primary key is a column or set of columns whose values uniquely identify each row in a table. It cannot contain duplicates or missing values, so every row can be located reliably. Primary keys also give other tables a stable target to reference when they need to link to a specific record.

Question 3

What is a foreign key and what job does it do?

Accepted Answer

A foreign key is a column in one table that holds values matching the primary key of another table. It creates a link between related records, such as an order pointing to the customer who placed it. The database can enforce that every foreign key value refers to an existing row, preventing orphaned records.

Question 4

What is a database schema, and what does it describe?

Accepted Answer

A schema is the structure that describes a database, including its tables, columns, data types, keys, and constraints. Some systems also use the word for a container grouping related objects, so one database can hold several schemas. The schema defines what data may be stored, while the rows hold the actual values.

Question 5

Which SQL commands belong to data definition language, and what do they do?

Accepted Answer

Data definition language, or DDL, includes commands such as CREATE, ALTER, DROP, and TRUNCATE that define or change database structures. These commands affect the shape of objects like tables and indexes rather than the individual rows they contain. Many DDL statements take effect immediately and in some systems cannot be rolled back.

Question 6

What are the basic data types used when defining columns?

Accepted Answer

Data types tell the database what kind of value a column can hold, such as whole numbers, decimal numbers, text, dates, or true and false values. Choosing the right type protects data quality, because the database rejects values that do not fit. Types also affect storage size and allowed operations.

Question 7

What does NOT NULL mean in a column definition?

Accepted Answer

NOT NULL is a constraint that requires every row to hold an actual value in that column. Without it, a column may contain NULL, which represents a missing or unknown value rather than zero or an empty string. Adding the constraint keeps required information complete, such as an email address every customer must provide.

Question 8

Why do designers normalize tables, and what problem does it solve?

Accepted Answer

Normalization is the process of organizing tables so each fact is stored in one place, which reduces redundancy. It addresses problems called update, insert, and delete anomalies, where changing repeated data in one spot leaves inconsistent copies elsewhere. Common normal forms give stepwise rules, and the first three are taught as a starting point.

Question 9

What is the basic structure of a SELECT statement?

Accepted Answer

A SELECT statement names the columns to return, the table or tables to read, and optional conditions that filter rows. The basic form is SELECT columns FROM table, followed by clauses such as WHERE, GROUP BY, HAVING, and ORDER BY when needed. The database returns a result set shaped like a table.

Question 10

How does the WHERE clause filter rows in a query?

Accepted Answer

The WHERE clause keeps only the rows for which a condition evaluates to true. Conditions use comparison operators such as equal, not equal, greater than, and less than, and they can be combined with AND, OR, and NOT. Rows where the condition is false or unknown, including comparisons with NULL, are excluded.

Question 11

What do the DISTINCT and ORDER BY keywords do?

Accepted Answer

DISTINCT removes duplicate rows from a result so each distinct combination of selected values appears once. ORDER BY sorts the result rows by one or more columns, either ascending or descending. Without ORDER BY, SQL does not guarantee any particular row order, so sorting should be requested explicitly when order matters.

Question 12

How do aggregate functions like COUNT and SUM summarize data?

Accepted Answer

Aggregate functions take many input values and return a single summary value, such as a count, total, average, minimum, or maximum. COUNT(*) counts rows, while SUM and AVG operate on numeric values and ignore NULLs. Used with GROUP BY, these functions produce one summary per group, such as total sales for each region.

Question 13

What is the difference between WHERE and HAVING in a query?

Accepted Answer

WHERE filters individual rows before any grouping takes place, so it cannot test aggregate results. HAVING filters groups after aggregation, so it can test conditions such as a count greater than five. A query can use both, with WHERE narrowing the rows first and HAVING then removing groups that fail its condition.

Question 14

How does a subquery work inside a SELECT statement?

Accepted Answer

A subquery is a query nested inside another statement, often inside a WHERE clause or a FROM clause. The database runs the inner query, then uses its result as a value, a list, or a temporary table for the outer query. Subqueries let you answer multistep questions, such as finding rows above an average.

Question 15

What is the purpose of aliases in a query?

Accepted Answer

An alias gives a column or table a temporary, shorter name for the duration of one query. Table aliases make long statements easier to read and are required when the same table appears twice, such as when comparing rows within it. Column aliases rename output headings, so the result shows a label like total_sales.

Question 16

How do you use LIKE and IN to match patterns or lists?

Accepted Answer

LIKE tests whether a text column matches a pattern, using the percent sign for any run of characters and the underscore for a single character. IN tests whether a value matches any item in a supplied list, which is shorter than chaining several OR conditions. Adding NOT reverses either test.

Question 17

What is a join and why is it needed?

Accepted Answer

A join combines rows from two or more tables based on a related condition, usually matching a foreign key to a primary key. It is needed because normalized designs store facts in separate tables, so queries reassemble them for questions spanning several tables. The join condition is written in the ON clause.

Question 18

What does an INNER JOIN return when two tables are combined?

Accepted Answer

An INNER JOIN returns only the rows where the join condition matches in both tables. Rows from either table that have no match on the other side are left out of the result. It is the default meaning when someone writes JOIN without a qualifier.

Question 19

How does a LEFT OUTER JOIN differ from an INNER JOIN?

Accepted Answer

A LEFT OUTER JOIN keeps every row from the left table, even when no matching row exists in the right table. For those unmatched rows, columns from the right table are filled with NULL. This is useful for questions such as listing all customers, including those who have never placed an order.

Question 20

What is a RIGHT OUTER JOIN and when would you use it?

Accepted Answer

A RIGHT OUTER JOIN keeps every row from the right table and fills unmatched left-side columns with NULL. It produces the same result as a LEFT OUTER JOIN with the two tables swapped in the statement. Many developers prefer LEFT joins and reverse the table order instead, for clarity.

Question 21

What is a CROSS JOIN and what does it produce?

Accepted Answer

A CROSS JOIN pairs every row from the first table with every row from the second table, producing a Cartesian product. If one table has 10 rows and the other has 5, the result has 50 rows. Accidental cross joins often signal a missing join condition.

Question 22

How do you join a table to itself, and why would you?

Accepted Answer

A self join connects a table to itself by giving it two different aliases, so rows can be compared with each other. An employees table with a manager_id column is a typical example. Joining the table to itself lets a query display each employee alongside the name of their manager.

Question 23

Why do joined queries sometimes return duplicate rows?

Accepted Answer

Duplicates appear when one row matches several rows in the other table, such as a customer with three orders. Each matching combination produces its own output row, so the customer's name appears three times. Checking join columns for uniqueness, or using DISTINCT or grouping, helps.

Question 24

How can you combine results from two queries with UNION?

Accepted Answer

UNION stacks the rows returned by two SELECT statements into one result, removing duplicate rows. Both queries must return the same number of columns, with compatible data types in matching positions. UNION ALL performs the same stacking but keeps duplicates, which can be faster because the database skips the removal step.

Question 25

What do INSERT, UPDATE, and DELETE statements do?

Accepted Answer

INSERT adds new rows to a table, UPDATE changes values in existing rows, and DELETE removes rows. UPDATE and DELETE usually need a WHERE clause, since without one they apply to every row in the table. Together these statements are called data manipulation language, or DML.

Question 26

What is a transaction and what do COMMIT and ROLLBACK do?

Accepted Answer

A transaction is a group of statements that the database treats as one unit of work, so either all of them take effect or none do. COMMIT makes the changes made in the transaction permanent. ROLLBACK discards those uncommitted changes and restores the earlier state of the data.

Question 27

What do the four ACID properties of a transaction describe?

Accepted Answer

The ACID properties describe the guarantees a reliable transaction system provides. Atomicity means a transaction completes fully or not at all, and consistency means it moves data from one valid state to another. Isolation means concurrent transactions do not interfere in ways that expose partial work, and durability means committed changes survive system failures.

Question 28

What is a constraint, and which kinds are commonly used?

Accepted Answer

A constraint is a rule the database enforces on column values or relationships between tables. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE for no duplicate values in a column, CHECK for a condition each value must meet, and NOT NULL for required values. Violating a constraint causes the change to be rejected.

Question 29

Why do database systems use locks during concurrent work?

Accepted Answer

Locks stop two sessions from changing the same data in conflicting ways at the same time. When one transaction holds a lock on a row, another transaction that needs to modify it must wait. Without locking or similar controls, updates could overwrite each other and produce incorrect totals.

Question 30

What is the difference between TRUNCATE and DELETE?

Accepted Answer

DELETE removes rows that match a WHERE condition and can be rolled back in most systems. TRUNCATE removes all rows from a table quickly, resets identity counters in many systems, and takes no WHERE clause. TRUNCATE is typically treated as a DDL-style operation, so its rollback behavior varies by system.

Question 31

What is a stored procedure and why do teams use one?

Accepted Answer

A stored procedure is a named set of SQL statements saved in the database and run by calling its name. Teams use them to reuse logic, centralize rules for changing data, and reduce traffic between applications and the server. Permissions can grant users an approved action without direct access to the underlying tables.

Question 32

Why should you test data changes on a copy before running them in production?

Accepted Answer

Testing on a copy lets you see the effect of a statement without risking live records. A missing WHERE clause or wrong join can change far more rows than intended. Checking row counts and running the statement inside a transaction you can roll back adds further protection.

Question 33

What is an index and how does it speed up queries?

Accepted Answer

An index is a separate structure that stores column values in sorted order with pointers to matching rows. With an index, the database can find matching rows without scanning the whole table. Indexes speed up reads, but each one must be updated during inserts, updates, and deletes.

Question 34

What does EXPLAIN or an execution plan show?

Accepted Answer

An execution plan shows the steps the database chose to answer a query, including estimated rows at each step. Reading the plan helps you find expensive operations, like full table scans on large tables. Many systems offer an EXPLAIN command to display the plan.

Question 35

What is a view, and what are its main uses?

Accepted Answer

A view is a saved SELECT statement that behaves like a virtual table when other queries reference it. Views can hide complex joins from users, present only certain columns, and limit access to sensitive data. A view usually stores no separate data, so it reflects changes made to the underlying tables.

Question 36

What is the difference between a full backup and an incremental backup?

Accepted Answer

A full backup copies the entire database, so it can restore everything on its own. An incremental backup copies only the changes made since the last backup of any kind, which takes less time and space. Restoring from incremental backups requires the last full backup plus every incremental backup made after it, in order.

Question 37

What is the principle of least privilege in database access?

Accepted Answer

The principle of least privilege means each user or application receives only the permissions needed for its work, such as SELECT on certain tables but not DROP. Database administrators grant and revoke privileges with GRANT and REVOKE statements. Limiting rights reduces the damage from mistakes, stolen credentials, or faulty application code.

Question 38

How do you keep SQL statements safe from injection attacks?

Accepted Answer

The most reliable defense is to use parameterized queries, where user input is sent as separate values rather than joined into the SQL text. Input should also be validated, and applications should use accounts with minimal privileges. Never building SQL by concatenating raw user input is the key habit to learn early.

Question 39

Why might a database intentionally denormalize some tables?

Accepted Answer

Denormalization deliberately stores some repeated data to reduce the number of joins needed for common reads. It is often used in reporting or read-heavy systems where query speed matters more than avoiding redundancy. The cost is that updates must keep duplicated values consistent, so it should be a conscious choice rather than a default.

Question 40

What routine maintenance tasks keep a database healthy?

Accepted Answer

Routine maintenance includes taking regular backups and testing that they can actually be restored. It also covers monitoring disk space and growth, updating statistics so the optimizer chooses good plans, rebuilding or reorganizing fragmented indexes, and reviewing slow queries. Scheduling these tasks regularly prevents small problems from becoming outages.

SQL Database Management Flashcards

1Relational database basics

2Writing queries with SELECT

3Combining tables with joins

4Changing data and transactions

5Performance, security, and maintenance

Learning Resources