book

Learn SQL in a Month of Lunches

Name: Learn SQL in a Month of Lunches
Author: Jeff Iannucci
ISBN: 9781633438576

by Jeff Iannucci

February 2025

Beginner

304 pages

8h 8m

English

Manning Publications

Audiobook available

Read now

Unlock full access

Learn SQL in a Month of Lunches
Copyright
dedication
contents
front matter
prefaceacknowledgmentsabout this bookWho should read this bookHow this book is organized: A road mapAbout the codeliveBook discussion forumabout the author
1 Before you begin
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 Your first SQL query
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 Querying data
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 Sorting, skipping, and commenting data
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 Filtering on specific values
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 Filtering with multiple values, ranges, and exclusions
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 Filtering with wildcards and null values
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 Querying multiple tables
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 Using different kinds of joins
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 Combining queries with set operators
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 Using subqueries and logical operators
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 Grouping data
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 Using variables
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 Querying with functions
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 Combining or calculating values with functions
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 Inserting data
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 Updating and deleting data
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 Storing data in tables
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 Creating constraints and indexes
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 Reusing queries with views and stored procedures
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 Making decisions in queries
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 Using cursors
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 Using someone else’s script
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 Never the end
24.1 More SQL24.2 Other SQL resources24.3 Farewell
index

Content preview from Learn SQL in a Month of Lunches

12 Grouping data

If you’re accustomed to working with spreadsheets, rather than relational data in a database, the past three chapters may have been a bit challenging for you. After all, in spreadsheets you often work with a single set of data instead of multiple sets. If the concepts in those chapters are new to you, take heart; this chapter covers concepts that should be very familiar to most spreadsheet users.

One useful aspect of spreadsheets is that they allow us to do mathematical calculations on a range of data quickly. If we want to find the total of all values in a column, for example, we can click the AutoSum button, which places the desired sum amount in a particular cell. If we highlight that cell, we see that the spreadsheet used ...

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.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781633438576Publisher Support Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Learn SQL in a Month of Lunches

by Jeff Iannucci

12 Grouping data

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.