What is the purpose of the `LIMIT` keyword in SQL?

[2:24:41] | The `LIMIT` keyword

in SQL is used to restrict the number of rows returned by a query. It's especially
useful in production databases with millions of rows to prevent pulling too much
data at once.
When using the `LIMIT` keyword, will a query with `LIMIT 5` always return exactly
five records? [2:27:48] | No, a query with `LIMIT 5` will not always return exactly
five records. It means "return at most five records." If the query would naturally
return fewer than five records (e.g., three records), then only those fewer records
will be returned.
What is the purpose of the `ORDER BY` clause in SQL? [2:29:01] | The `ORDER BY`
clause in SQL is used to sort the data returned by a query. It's often used in
conjunction with the `LIMIT` clause to get a specific subset of sorted data (e.g.,
the top 10 items).
How does `ORDER BY` sort data by default, and how can you specify a different
order? [2:29:56] | By default, `ORDER BY` sorts data in <b>ascending order
(ASC)</b>, placing the lowest values at the top. To sort in <b>descending order
(DESC)</b>, you must explicitly add the `DESC` keyword after the column name (e.g.,
`ORDER BY quantity DESC`).
When using both `ORDER BY` and `LIMIT` in an SQL query, what is the correct order
of these clauses? [2:33:36] | The correct order is to use `ORDER BY` first to sort
the data, and then `LIMIT` to truncate the sorted result set. This means the order
should be `ORDER BY ... LIMIT ...`.
What is an aggregation in SQL? [2:33:56] | An aggregation in SQL is when you take a
large amount of raw data and condense it down into a single summary value.
Why is it generally a good idea to store data in a raw format in a database and run
aggregation queries on the fly? [2:34:11] | Storing data in a raw format (as
opposed to pre-aggregated) ensures that you can always access the original,
granular information. If you only store pre-aggregated data, you lose the ability
to go back and analyze individual records or perform different aggregations later.
What is the `COUNT` function in SQL, and how is the wildcard `*` typically used
with it? [2:35:03] | The `COUNT` function is a simple aggregation that returns the
number of rows that match a specified condition. When used with `COUNT(*)`, the
wildcard `*` signifies that you want to count the total number of rows, regardless
of their specific field values.
What is the `SUM` function in SQL? [2:37:32] | The `SUM` function in SQL is an
aggregation that calculates the total sum of values within a specified numeric
column for a group of rows.
What is the `MAX` function in SQL? [2:39:05] | The `MAX` function in SQL is an
aggregation that returns the largest value from a set of values within a specified
column.
What is the `MIN` function in SQL? [2:40:45] | The `MIN` function in SQL is an
aggregation that returns the smallest value from a set of values within a specified
column.
What is the `GROUP BY` clause in SQL, and how does it enhance aggregation
functions? [2:41:38] | The `GROUP BY` clause in SQL allows you to divide the rows
returned by a query into groups based on the values in one or more columns. It
enables aggregation functions (like `SUM`, `COUNT`, `MAX`, `MIN`) to be applied to
each of these groups independently, resulting in multiple summary rows rather than
a single value for the entire dataset.
How do you alias a column in an SQL query? [2:43:46] | To alias a column in an SQL
query, you use the `AS` keyword followed by the desired alias name (e.g.,
`SUM(amount) AS balance`).
What is the `AVG` (average) aggregation function in SQL? [2:44:05] | The `AVG`
function in SQL is an aggregation that calculates the average (mean) of a set of
numeric values within a specified column. It ignores `NULL` values when performing
the calculation.
What is the key difference between the `WHERE` clause and the `HAVING` clause in
SQL? [2:45:05] | The key difference is the order of operation: The <b>`WHERE`
clause</b> filters individual rows <b>before</b> any aggregation or grouping takes
place. The <b>`HAVING` clause</b> filters groups of rows <b>after</b> the `GROUP
BY` clause has aggregated them, and it can use the results of those aggregations in
its filtering condition.
In an SQL query that includes `GROUP BY`, if you want to filter based on a
condition involving an aggregate function (e.g., count > 5), which clause should
you use? [2:52:21] | You should use the <b>`HAVING` clause</b> because it operates
on the grouped rows after the aggregation has been calculated. The `WHERE` clause
cannot filter on aggregate function results.
In an SQL query, if you want to filter based on a condition involving a non-
aggregate column (e.g., `class_ID = 101`) before grouping, which clause should you
use? [2:51:37] | You should use the <b>`WHERE` clause</b> because it filters
individual rows before they are grouped.
What is the `ROUND` function in SQL, and what does it do by default if no precision
is specified? [2:52:53] | The `ROUND` function in SQL is used to round a numeric
value to a specified number of decimal places. By default, if no precision is
passed, SQL (at least SQLite) will round the number to the nearest whole number
(integer).
What is a subquery in SQL? [2:54:22] | A subquery (or inner query) in SQL is a
query nested inside another SQL query. The results of the inner query are used by
the outer query. Subqueries are enclosed in parentheses and allow you to perform
operations that require intermediate result sets.
What is the difference in usage between `WHERE column = (subquery)` and `WHERE
column IN (subquery)`? [2:58:19] | `WHERE column = (subquery)` is used when the
subquery is expected to return <b>a single value (one row, one column)</b>. `WHERE
column IN (subquery)` is used when the subquery is expected to return <b>multiple
values (a list of values)</b> that the outer query column can match against.
Can SQL queries operate without referencing any tables? [3:00:55] | Yes, SQL can
operate without referencing any backing database or table. For example, `SELECT 5 +
10 AS sum;` will return a result without interacting with a table.
What are the three main types of relationships between entities in a relational
database? [3:03:43] | The three main types of relationships are:<br>1. <b>One-to-
One</b><br>2. <b>One-to-Many</b><br>3. <b>Many-to-Many</b>
How is a one-to-one relationship typically modeled in a database? [3:05:53] |
Generally, a one-to-one relationship is modeled by adding an additional field
(column) to an existing table. In some cases, for organizational or performance
reasons, it might be modeled as a one-to-one mapping of primary keys between two
separate tables, ensuring both tables have the same number of records and related
entities share the same ID.
How is a one-to-many relationship typically modeled in a database? [3:07:56] | A
one-to-many relationship is usually modeled by placing a <b>foreign key</b> on the
"many" side of the relationship. This foreign key in the "many" table references
the primary key of the "one" table. For example, a `tweets` table would have a
`user_ID` foreign key referencing the `users` table, allowing multiple tweets to
belong to one user.
How is a many-to-many relationship typically modeled in a database? [3:10:10] | A
many-to-many relationship is modeled using a <b>joining table</b> (also known as a
junction table or associative table). This third table sits in the middle and
contains only foreign keys that reference the primary keys of the two tables
involved in the many-to-many relationship. It often has a unique constraint across
both foreign key columns to prevent duplicate relationship entries.
What is database normalization? [3:25:11] | Database normalization is the process
of organizing the columns and tables of a relational database to minimize data
redundancy (duplication) and improve data integrity. It involves structuring the
database according to a series of "normal forms."
What are the four tiers of database normalization (normal forms)? [3:26:34] | The
four tiers of database normalization are:<br>1. <b>First Normal Form
(1NF)</b><br>2. <b>Second Normal Form (2NF)</b><br>3. <b>Third Normal Form
(3NF)</b><br>4. <b>Boyce-Codd Normal Form (BCNF)</b>
What are the two rules for a database table to be in First Normal Form (1NF)?
[3:28:14] | 1. Every row must have a <b>unique primary key</b> (meaning no
completely duplicate rows).<br>2. There can be <b>no nested tables</b> (each cell
should contain a single, atomic value).
What is the additional rule for a database table to be in Second Normal Form (2NF)?
[3:30:15] | In addition to adhering to 1NF, all non-primary key columns must be
<b>fully dependent on the entire primary key</b>, not just a part of it.
What is the additional rule for a database table to be in Third Normal Form (3NF)?
[3:34:27] | In addition to adhering to 1NF and 2NF, all non-primary key columns
must be <b>dependent only on the primary key</b> and not on any other non-primary
key column (i.e., no transitive dependencies).
What is the additional rule for a database table to be in Boyce-Codd Normal Form
(BCNF)? [3:40:51] | In addition to adhering to 1NF, 2NF, and 3NF, a column that is
part of the primary key <b>may not be dependent on a column that is not part of the
primary key</b>. This addresses specific cases of data redundancy that 3NF might
miss when a table has multiple overlapping candidate keys.
What is the primary purpose of database normalization in practical application
development? [3:41:16] | The primary purpose of database normalization in practice
is to reduce data duplication and improve data integrity, which leads to fewer
bugs, easier maintenance, and better application reliability.
Which normal form typically has the most duplicate data? [3:42:59] | <b>First
Normal Form (1NF)</b> typically has the most duplicate data because it is the least
normalized.
Which normal form encourages the most accurate and up-to-date information?
[3:45:36] | <b>Boyce-Codd Normal Form (BCNF)</b> encourages the most accurate and
up-to-date information because it is the most normalized and minimizes redundancy.
In the context of database normalization, how many columns can make up a primary
key? [3:45:53] | In the context of database normalization theory, a primary key can
be made up of <b>one to many columns</b> (a single column or a composite key of
multiple columns).
What is a composite key? [3:53:52] | A composite key is a primary key that is
formed by the combination of two or more columns in a table to uniquely identify
each row.