book

Learn SQL in a Month of Lunches

by Jeff Iannucci

February 2025

Beginner

304 pages

8h 8m

English

Manning Publications

Read now

Unlock full access

prefaceacknowledgmentsabout this bookWho should read this bookHow this book is organized: A road mapAbout the codeliveBook discussion forumabout the author
1.1 Why SQL matters1.2 Is this book for you?1.2.1 The many uses for SQL1.2.2 The many flavors of SQL1.2.3 A word about AI and SQL1.3 How to use this book1.3.1 The main chapters1.3.2 Hands-on labs1.3.3 Further exploration1.4 Setting up your lab environment1.4.1 Installing MySQL and MySQL Workbench1.4.2 Executing the lab scripts1.5 Online resources1.6 Being immediately effective with SQL
2.1 You know tables if you already know spreadsheets2.2 Learning SQL is like taking an English class2.3 Writing your first SQL query2.4 Key terms and keywords2.5 Lab2.6 Lab answers
3.1 Rules for the SELECT statement3.1.1 SELECT requirements3.1.2 Keywords and reserved words3.1.3 Case insensitivity3.1.4 Formatting and whitespace3.2 Retrieving data from a table3.2.1 Retrieving an individual column3.2.2 Retrieving multiple columns3.2.3 Renaming output columns using aliases3.2.4 Retrieving all columns3.3 Lab3.4 Lab answers
4.1 Sorting data4.1.1 Sorting by one column4.1.2 Sorting by multiple columns4.1.3 Specifying sort direction4.1.4 Sorting by hidden columns4.1.5 Sorting by position4.2 Skipping data4.2.1 Using LIMIT to reduce results4.2.2 Using OFFSET to select a different limited set4.2.3 Limiting data in another RDBMS4.3 Commenting data4.4 Lab4.5 Lab answers
5.1 Filtering on a single condition5.1.1 Filtering on numeric values5.1.2 Filtering on string values5.1.3 Filtering on date values5.2 Filtering on multiple conditions5.2.1 Filtering that requires all conditions5.2.2 Filtering that requires any one of many conditions5.2.3 Controlling the order of multiple filters5.2.4 Filtering and using ORDER BY5.3 Lab5.4 Lab answers

6.1 Filtering on specific values6.2 Filtering on a range of values6.2.1 Filtering on an open-ended range6.2.2 Filtering a defined range6.3 Negating filter conditions6.3.1 Negating a specific value6.3.2 Negating any filter condition6.4 Combining types of filter conditions6.5 Reviewing comparison operators6.6 Lab6.7 Lab answers
7.1 Filtering with wildcards7.1.1 Filtering with the percent sign7.1.2 Filtering with an underscore7.2 Filtering with null values7.2.1 How not to search for null values7.2.2 How to search for null values correctly7.2.3 How to search for values that are not null7.3 Lab7.4 Lab answers
8.1 The rules of data relationships8.1.1 Data without relationships8.1.2 Data with relationships8.2 The way to join data8.2.1 Joining two tables8.2.2 Joining more tables8.3 Table aliases8.4 The other way to join data8.5 Lab8.6 Lab answers
9.1 Inner joins9.2 Outer joins9.2.1 Left outer joins9.2.2 Right outer joins9.2.3 Using outer joins to find rows without matching values9.2.4 Interchanging left and right joins9.2.5 The USING keyword9.2.6 Natural joins9.3 Cross joins9.4 Lab9.5 Lab answers
10.1 Using set operators10.2 UNION10.3 UNION ALL10.4 Emulating FULL OUTER JOIN in MySQL10.5 INTERSECT10.6 EXCEPT10.7 Lab10.8 Lab answers
11.1 A simple subquery11.2 Logical operators and subqueries11.2.1 The ANY and IN operators11.2.2 The ALL and NOT IN operators11.2.3 The EXISTS and NOT EXISTS operators11.3 Subqueries in other parts of a query11.3.1 Subqueries in the FROM clause11.3.2 Subqueries in the SELECT clause11.4 Lab11.5 Lab answers
12.1 Aggregate functions12.1.1 The SUM function12.1.2 The COUNT function12.1.3 The MIN function12.1.4 The MAX function12.1.5 The AVG function12.1.6 Filtering and aggregating combined values12.2 Aggregating data with GROUP BY12.2.1 GROUP BY requirements12.2.2 GROUP BY and null values12.3 Filtering with HAVING12.4 Logical query processing12.5 The DISTINCT keyword12.6 Lab12.7 Lab answers
13.1 User-defined variables13.1.1 Declaring your first user-defined variable13.1.2 Understanding rules for user-defined variables13.1.3 Using your first user-defined variable13.2 Filtering with variables in FROM and HAVING clauses13.3 Assigning an unknown value to a variable13.3.1 Reviewing how a query works13.3.2 Assigning an unknown variable with SELECT13.3.3 Considering performance with variables13.3.4 Troubleshooting considerations with variables13.4 Other notes about variables13.4.1 Assigning a literal value using SELECT13.4.2 Assigning a value of NULL to a variable13.4.3 Changing the type of data used by a variable13.5 Lab13.6 Lab answers
14.1 The problems with functions14.1.1 Function commands vary for each RDBMS14.1.2 Function commands can be inefficient14.2 String functions14.2.1 Case functions14.2.2 Trim functions14.2.3 Other string functions14.3 Date and time functions14.3.1 Date functions that return numeric values14.3.2 Date functions that return string values14.3.3 Other date and time functions14.4 Informational functions14.4.1 Date and time information14.4.2 Connection information14.5 Lab14.6 Lab answers
15.1 Combining string values15.1.1 CONCAT15.1.2 CONCAT_WS15.1.3 COALESCE15.2 Converting values15.2.1 REPLACE15.2.2 CONVERT and CAST15.3 Numeric calculations with functions15.4 Lab15.5 Lab answers
16.1 Inserting specific values16.1.1 Inserting a new row16.1.2 Inserting multiple new rows16.1.3 Inserting a partial row16.1.4 A word of caution about omitting columns16.2 Inserting a row with a query16.3 Inserting a row with variables16.4 Lab16.5 Lab answers
17.1 Updating values17.1.1 Working with data manipulation in real time17.1.2 Requirements for updates17.1.3 Updating values in one or more columns17.1.4 Updating values with a multitable query17.2 Deleting rows17.2.1 Deleting one or more rows17.2.2 Deleting a row with a multitable query17.2.3 Deleting all rows in a table17.3 One big tip for data manipulation17.4 Lab17.5 Lab answers
18.1 Creating a table18.1.1 Considerations before creating a table18.1.2 Creating a table18.1.3 Adding values to an empty table18.2 Altering a table18.2.1 Adding a column to a table18.2.2 Considerations before adding a column18.3 Primary keys18.3.1 Considerations for primary keys18.3.2 Adding a primary key18.4 Foreign keys and constraints18.4.1 Data diagrams18.4.2 Adding a foreign-key constraint18.5 Deleting a table, column, or constraint18.5.1 Deleting a constraint18.5.2 Deleting a column18.5.3 Deleting a table18.6 Lab18.7 Lab answers
19.1 Constraints19.1.1 NOT NULL constraints19.1.2 DEFAULT constraints19.1.3 UNIQUE constraints19.1.4 CHECK constraints19.2 Automatically incrementing values for a column19.3 Indexes19.3.1 Clustered indexes19.3.2 Nonclustered indexes19.4 Lab19.5 Lab answers
20.1 Views20.1.1 Creating views20.1.2 Filtering with views20.1.3 Joining views20.1.4 Considerations for views20.2 Stored procedures20.2.1 Creating stored procedures20.2.2 Using variables with stored procedures20.2.3 Considerations for stored procedures20.3 Differences between views and stored procedures20.4 Lab20.5 Lab answers
21.1 Conditional functions and expressions21.1.1 COALESCE function21.1.2 IFNULL function21.1.3 CASE expression21.2 Decision structures21.2.1 IF and THEN21.2.2 ELSE21.2.3 Multiple conditions21.3 Lab21.4 Lab answers
22.1 Reviewing variables and parameters22.1.1 Variables inside stored procedures22.1.2 Output parameters22.2 Cursors22.2.1 Anatomy of a cursor22.2.2 Creating a cursor22.3 Alternatives to cursors22.3.1 Using WHILE22.3.2 Temporary tables22.4 Considerations for using cursors22.4.1 Thinking in sets22.4.2 Thinking about cursor use22.5 Lab22.6 Lab answers
23.1 Someone else’s script: Creating a table23.1.1 The CREATE TABLE script23.1.2 Reviewing the CREATE TABLE script23.1.3 Improving the CREATE TABLE script23.2 Someone else’s script: Inserting data23.2.1 The INSERT stored procedure23.2.2 Reviewing the INSERT stored procedure23.2.3 Improving the INSERT stored procedure23.2.4 Improving the INSERT stored procedure even more
24.1 More SQL24.2 Other SQL resources24.3 Farewell

Content preview from Learn SQL in a Month of Lunches

19 Creating constraints and indexes

We talked about two important constraints in chapter 18: PRIMARY KEY and FOREIGN KEY constraints. This chapter looks at a few more constraints that help us ensure the integrity of the data in our tables.

We’ll also discuss indexes, which are table-related objects that help with the performance of our queries. Just as indexes in books like this one can help you quickly find the subject you’re looking for, indexes in a database can reduce the time it takes queries to find specific data.

I hope that you’ve enjoyed creating tables and associated constraints because you’re about to create more.

19.1 Constraints

By completing the examples in chapter 18, you learned that you can create constraints in two ways: by ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Publisher Resources

ISBN: 9781633438576Supplemental Content Publisher Support Other Publisher Website Supplemental Content Purchase Link

Learn SQL in a Month of Lunches

by Jeff Iannucci

19 Creating constraints and indexes

19.1 Constraints

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

SQL Cookbook, 2nd Edition

Learn Docker in a Month of Lunches

Head First SQL, 2nd Edition

Publisher Resources

19 Creating constraints and indexes

19.1 Constraints

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

SQL Cookbook, 2nd Edition

Learn Docker in a Month of Lunches

Head First SQL, 2nd Edition

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.