1.1 Why SQL matters · 1.2 Is this book for you? · 1.2.1 The many uses for SQL · 1.2.2 The many flavors of SQL · 1.2.3 A word about AI and SQL · 1.3 How to use this book · 1.3.1 The main chapters · 1.3.2 Hands-on labs · 1.3.3 Further exploration · 1.4 Setting up your lab environment · 1.4.1 Installing MySQL and MySQL Workbench · 1.4.2 Executing the lab scripts · 1.5 Online resources · 1.6 Being immediately effective with SQL
2.1 You know tables if you already know spreadsheets · 2.2 Learning SQL is like taking an English class · 2.3 Writing your first SQL query · 2.4 Key terms and keywords · 2.5 Lab · 2.6 Lab answers
4.1 Sorting data · 4.1.1 Sorting by one column · 4.1.2 Sorting by multiple columns · 4.1.3 Specifying sort direction · 4.1.4 Sorting by hidden columns · 4.1.5 Sorting by position · 4.2 Skipping data · 4.2.1 Using LIMIT to reduce results · 4.2.2 Using OFFSET to select a different limited set · 4.2.3 Limiting data in another RDBMS · 4.3 Commenting data · 4.4 Lab · 4.5 Lab answers
5.1 Filtering on a single condition · 5.1.1 Filtering on numeric values · 5.1.2 Filtering on string values · 5.1.3 Filtering on date values · 5.2 Filtering on multiple conditions · 5.2.1 Filtering that requires all conditions · 5.2.2 Filtering that requires any one of many conditions · 5.2.3 Controlling the order of multiple filters · 5.2.4 Filtering and using ORDER BY · 5.3 Lab · 5.4 Lab answers
6.1 Filtering on specific values · 6.2 Filtering on a range of values · 6.2.1 Filtering on an open-ended range · 6.2.2 Filtering a defined range · 6.3 Negating filter conditions · 6.3.1 Negating a specific value · 6.3.2 Negating any filter condition · 6.4 Combining types of filter conditions · 6.5 Reviewing comparison operators · 6.6 Lab · 6.7 Lab answers
7.1 Filtering with wildcards · 7.1.1 Filtering with the percent sign · 7.1.2 Filtering with an underscore · 7.2 Filtering with null values · 7.2.1 How not to search for null values · 7.2.2 How to search for null values correctly · 7.2.3 How to search for values that are not null · 7.3 Lab · 7.4 Lab answers
8.1 The rules of data relationships · 8.1.1 Data without relationships · 8.1.2 Data with relationships · 8.2 The way to join data · 8.2.1 Joining two tables · 8.2.2 Joining more tables · 8.3 Table aliases · 8.4 The other way to join data · 8.5 Lab · 8.6 Lab answers
10.1 Using set operators · 10.2 UNION · 10.3 UNION ALL · 10.4 Emulating FULL OUTER JOIN in MySQL · 10.5 INTERSECT · 10.6 EXCEPT · 10.7 Lab · 10.8 Lab answers
11.1 A simple subquery · 11.2 Logical operators and subqueries · 11.2.1 The ANY and IN operators · 11.2.2 The ALL and NOT IN operators · 11.2.3 The EXISTS and NOT EXISTS operators · 11.3 Subqueries in other parts of a query · 11.3.1 Subqueries in the FROM clause · 11.3.2 Subqueries in the SELECT clause · 11.4 Lab · 11.5 Lab answers
12.1 Aggregate functions · 12.1.1 The SUM function · 12.1.2 The COUNT function · 12.1.3 The MIN function · 12.1.4 The MAX function · 12.1.5 The AVG function · 12.1.6 Filtering and aggregating combined values · 12.2 Aggregating data with GROUP BY · 12.2.1 GROUP BY requirements · 12.2.2 GROUP BY and null values · 12.3 Filtering with HAVING · 12.4 Logical query processing · 12.5 The DISTINCT keyword · 12.6 Lab · 12.7 Lab answers
13.1 User-defined variables · 13.1.1 Declaring your first user-defined variable · 13.1.2 Understanding rules for user-defined variables · 13.1.3 Using your first user-defined variable · 13.2 Filtering with variables in FROM and HAVING clauses · 13.3 Assigning an unknown value to a variable · 13.3.1 Reviewing how a query works · 13.3.2 Assigning an unknown variable with SELECT · 13.3.3 Considering performance with variables · 13.3.4 Troubleshooting considerations with variables · 13.4 Other notes about variables · 13.4.1 Assigning a literal value using SELECT · 13.4.2 Assigning a value of NULL to a variable · 13.4.3 Changing the type of data used by a variable · 13.5 Lab · 13.6 Lab answers
14.1 The problems with functions · 14.1.1 Function commands vary for each RDBMS · 14.1.2 Function commands can be inefficient · 14.2 String functions · 14.2.1 Case functions · 14.2.2 Trim functions · 14.2.3 Other string functions · 14.3 Date and time functions · 14.3.1 Date functions that return numeric values · 14.3.2 Date functions that return string values · 14.3.3 Other date and time functions · 14.4 Informational functions · 14.4.1 Date and time information · 14.4.2 Connection information · 14.5 Lab · 14.6 Lab answers
16.1 Inserting specific values · 16.1.1 Inserting a new row · 16.1.2 Inserting multiple new rows · 16.1.3 Inserting a partial row · 16.1.4 A word of caution about omitting columns · 16.2 Inserting a row with a query · 16.3 Inserting a row with variables · 16.4 Lab · 16.5 Lab answers
17.1 Updating values · 17.1.1 Working with data manipulation in real time · 17.1.2 Requirements for updates · 17.1.3 Updating values in one or more columns · 17.1.4 Updating values with a multitable query · 17.2 Deleting rows · 17.2.1 Deleting one or more rows · 17.2.2 Deleting a row with a multitable query · 17.2.3 Deleting all rows in a table · 17.3 One big tip for data manipulation · 17.4 Lab · 17.5 Lab answers
18.1 Creating a table · 18.1.1 Considerations before creating a table · 18.1.2 Creating a table · 18.1.3 Adding values to an empty table · 18.2 Altering a table · 18.2.1 Adding a column to a table · 18.2.2 Considerations before adding a column · 18.3 Primary keys · 18.3.1 Considerations for primary keys · 18.3.2 Adding a primary key · 18.4 Foreign keys and constraints · 18.4.1 Data diagrams · 18.4.2 Adding a foreign-key constraint · 18.5 Deleting a table, column, or constraint · 18.5.1 Deleting a constraint · 18.5.2 Deleting a column · 18.5.3 Deleting a table · 18.6 Lab · 18.7 Lab answers
primary keyA primary key constraint is a database rule requiring a table's primary-key values to uniquely identify rows and not be null.
columnA column is a vertical set of table fields in which each position represents the same attribute across rows.
foreign keyA foreign key constraint is a database rule requiring designated column values to reference valid rows identified by a key in another table.
cursorA database cursor is a database programming construct that enables query-result data to be processed one row at a time.
filteringA filtering condition is a Boolean predicate that determines which rows satisfy a query's selection criteria.
nested queryA subquery is an SQL query nested within another SQL statement whose result can supply data for joining, filtering, or other operations.
database updateAn UPDATE statement is an SQL statement that modifies values in existing rows of a table, optionally subject to filtering conditions.
stored procedureA stored procedure is a named sequence of database statements stored on a database system for execution as a unit.
joinA join is an SQL operation that combines rows from multiple tables according to a specified relationship or predicate.
data typeA data type classifies values and determines the operations and rules applicable to them.
set operatorA set operator is an SQL operator that combines or compares the result sets of two or more compatible queries into one result set.
where clauseA WHERE clause is the SQL clause that restricts query results by requiring rows to satisfy one or more conditions.
not-null constraintA NOT NULL constraint is a database rule that requires a column to contain a non-null value in every row.
data aggregationAggregation is the SQL operation of combining or summarizing data values to produce grouped or otherwise consolidated results.
loopA SQL procedural loop repeatedly executes a block of statements while its control conditions permit.
selectSELECT is the SQL keyword that begins a query and specifies the data or expressions to retrieve from a data source.
SET statementA SET statement is an SQL statement that assigns a value to a variable or configures a database or session setting.
predicateA predicate is a Boolean SQL expression that evaluates whether a condition holds for data involved in a query operation.
union all clauseUNION ALL is an SQL set operator that combines rows from multiple compatible query results while retaining duplicate rows.
set-based programmingSet-based programming processes and transforms sets of matching rows through relational operations while allowing the database system to determine the execution strategy.
from clauseA FROM clause is the SQL clause that specifies the table or other data source from which data is retrieved or related data is selected.
viewA database view is a named, query-defined representation of data that can be accessed like a table.
MySQL WorkbenchMySQL Workbench is a graphical development and administration environment for MySQL databases.
database result setA query result, or result set, is the tabular collection of rows returned by executing a database query.
alter table commandALTER TABLE is an SQL statement that changes the structure or definition of an existing database table.
recordA row is a horizontal record in a table containing values for a related set of attributes.
relational database management systemA relational database management system is software that stores, organizes, and manages structured data represented in related tables.
wildcardA percent wildcard (%) is an SQL pattern-matching character that matches any sequence of characters, including an empty string.
data conversionType conversion is the operation of changing a value from one data type to another.
check constraintA CHECK constraint is a database rule that permits a column value only when it satisfies a specified Boolean expression.
stringA string is a sequence of characters treated as text data.
order by clauseORDER BY is the SQL keyword sequence that introduces a clause specifying the order of query results.
nonclustered indexA nonclustered index is a separate database object that stores an ordering or lookup path for table rows without defining the table's physical order.
clustered indexA clustered index is a table-organizing structure that determines the physical order in which rows are stored on a storage medium.
left outer joinA left outer join is an SQL join that returns all rows from the left table together with matching rows from the right table, representing missing right-side matches with null values.
membership operatorThe IN operator is an SQL predicate that tests whether an expression matches any value in a specified list or subquery result.
fetchFETCH is a cursor operation that retrieves a row from the cursor result and assigns its values to variables.
commentAn SQL comment is text in an SQL statement or script that is ignored during execution and can document intent or context.
endEND is a procedural SQL keyword that terminates a compound statement or stored-procedure body.
one-to-many relationshipA one-to-many relationship is a relationship in which one record on one side can correspond to multiple records on the other side.
temporary tableA temporary table is a database table created for transient use and typically removed automatically or explicitly after its relevant session or operation ends.
unique constraintA unique constraint is a database rule requiring non-null values in a column or column set to be distinct.
database functionA database function is a named database routine or SQL operation that computes and returns a value, optionally from supplied arguments.
delimiterDELIMITER is a MySQL-specific command that changes the marker used to recognize the end of SQL statements or stored procedures.
having clauseA HAVING clause is the SQL clause that filters groups or aggregate results produced by a grouped query.
is not null operatorIS NOT NULL is an SQL predicate that tests whether an expression has a non-null value.
any operatorThe ANY operator is an SQL quantified comparison operator that requires a comparison to hold for at least one value returned by a list or subquery.
CREATE TABLE statementA CREATE TABLE statement is an SQL statement that creates a new database table with specified columns and constraints.
varchar data typeVARCHAR is a variable-length SQL character data type that stores text using only the space needed for each value.
sorting algorithmResult ordering is the specification of the sequence in which rows are returned by a query.
literalA literal value is a fixed constant written directly in an SQL statement rather than retrieved from a column or calculated dynamically.
constraintA database constraint is a rule that restricts which data operations or values are permitted for a table.
on clauseON is an SQL keyword that introduces the predicate defining how rows from joined sources are related.
qualified column nameA qualified column name identifies a column by combining its table name and column name, such as table.column.
DECLAREDECLARE is an SQL keyword used to declare a variable or other procedural object and, where required, specify its data type.
single quotation markA single quote is the quotation delimiter used in SQL to enclose literal character and date values.
NOT INThe NOT IN operator is an SQL membership operator that tests whether an expression matches none of the values in a specified list or set.
else clauseELSE is a procedural SQL control-flow keyword that introduces the fallback branch executed when preceding conditions are not satisfied.
multitable queryA multitable query is an SQL query that references more than one table to retrieve or filter data.
join conditionA join condition, or join predicate, is a Boolean condition that specifies how rows from joined tables correspond to one another.
closeCLOSE is a cursor operation that terminates cursor use and releases its associated resources.
openOPEN is a cursor operation that executes or activates the cursor's query and makes its result available for row retrieval.
concatenationCONCAT is an SQL string function that concatenates multiple values into a single string.
logical ORThe OR operator is a logical SQL operator that evaluates to true when at least one connected condition is true.
outer joinA full outer join returns matching rows and preserves unmatched rows from both joined inputs, filling missing columns with null values.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.
