book

Head First SQL, 2nd Edition

by Kimberly Fessel

June 2026

Beginner

700 pages

16h 50m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Defining your dataThink about your data in categoriesBuild a table of informationWhat is SQL?The anatomy of a tableA table of travel dataWhat’s a database?Databases contain related informationTake command!Your first SQL commandsCreating a SQL tableYour table is readyA selection of common data typesData types for textData types for numbersData types for dates and timesA data type for other thingsYour table, DESCribedChanging table propertiesCode MagnetsCode Magnets SolutionDropping and recreating tablesAdding a new columnHitting your cruising altitudeSQLcrossYour SQL toolboxSQLcross Solution
Setting up a database with tablesChecking back in with AbbyInserting data into tablesCreating your INSERT statementSee your table with SELECTCode MagnetsA new recommendationRecords with missing valuesRevisiting your tableControlling NULLs...... and Setting DEFAULTsVariations on INSERT #1Variations on INSERT #2Variations on INSERT #3A punctuation problemUnmatched single quotesINSERT data with single quotes in itIt’s only up from here!Your SQL toolboxCode Magnets Solution
Searching for a destinationSELECT specific columnsSpecify columns... and their orderSELECT specific columns for faster resultsAn even better SELECTWHERE to next?WHERE filters out rowsWHERE resultsFinding numeric valuesExpanding a numeric searchShort travel times using less thanComparison OperatorsFinding numeric data with comparison operatorsComparison operators for textWho am I?Who am I? SolutionSQLcrossYour SQL ToolboxSQLcross Solution
Misplaced identificationMultiple row requirementsCombining your queriesUse AND to satisfy two conditionsTo be OR not to beCode MagnetsCode Magnets SolutionFireside ChatsThe difference between AND and ORBE the ConditionalBe the Conditional SolutionUse IS NULL to find NULLsIS NULL in your consoleSelecting ranges using AND and comparison operatorsJust BETWEEN us... there’s a better wayMeanwhile, Abby dreams of sunshine...Save time with LIKEI’d LIKE to buy a wildcard, pleaseYou’re either IN...... or you are NOT INAnd that’s NOT allWHERE for the winSQLcrossYour SQL toolboxSQLcross Solution
What’s for dinner?Extracting informationGetting to know CRUDCubicle conversationChange your data with UPDATEStaying up-to-date with UPDATEUPDATE in your consoleUpdating multiple columns or rowsThe price is wrongRevisiting CRUDDisheartening newsGetting rid of rows with DELETEDELETE rulesDELETE (+SELECT) in your consoleTest your WHERE with SELECTDELETE with the same WHERECRUD completeSQLcrossYour SQL toolboxSQLcross Solution
Shopping aroundORDER (BY) in the court!ORDER with WHEREAn orderly stores tableReverse the ORDER with DESCLIMIT the number of resultsHitting the LIMITSUM to add values for usSUM all of them at once with GROUP BYImagine GROUP BY splitting your dataGetting group averagesMIN and MAXThe COUNT of Monty BristowSELECT DISTINCT valuesFiltering groupsHAVING the right filter mechanismEasy as S.F.W.G.H.O.Your very own categorizationA CASE for new groupsSELECTing a CASE expressionGo with the CASE flowAlias your columns with ASEven more power with aliasingAn impressive list of abilitiesSQLcrossYour SQL toolboxSQLcross Solution
Two mountainous tablesA table is all about relationshipsAtomic data30 minutes or it’s freeLocation, location, locationAsking atomic questionsMore atomic updatesGet into a normal routineWhat’s normal and 1NF?PRIMARY KEY to successGetting to normalThe CREATE TABLE with a PRIMARY KEY1, 2, 3... automaticallyAdding a PRIMARY KEY to an existing tableALTER TABLE to add a PRIMARY KEYThink outside of the single tableAbby’s database schemaGoing from one table to twoQuerying two tablesConnecting your tablesFOREIGN KEY to successConstraining your foreign keyCREATE a table with a FOREIGN KEYOne-to-oneWhen to use one-to-one tablesOne-to-manyMany-to-manyWe need a junction tableFixing data patternsHow normal can you be?Composite keys use multiple columnsEven superheroes have dependenciesPartial functional dependencyTransitive functional dependencySecond Normal Form (2NF)Third Normal Form (3NF)Your SQL toolbox
Summer camp chaosReady for renovationsTable alteringRenaming our tableMaking a CHANGEChoosing your data typesMinimizing disk spaceStorage considerations for textMODIFY a data typeSlowing to a crawlINDEX for faster queriesTwo ways to INDEXComposite index orderingALTER your awkward columnsLook for patternsA few handy string functionsTo SELECT everything before the commaTo SELECT the last two charactersUPDATE with string functionsCreate data-filled tablesYour SQL toolbox
Time to JOIN the partyThe CROSS JOINFiltering a CROSS JOINReleasing your INNER JOINThe inner join in actionOther ON conditionsOUTER this world!Left (outer) joinThe left join in actionFlipping your table orderOuter joins and multiple matchesRight (outer) joinFULL OUTER JOINA self-referencing foreign keyJoining a table to itselfWe’re ready for a self joinAnother way to combine multiple tablesYou can use a UNIONUNION bylawsUNION rules in actionUNION ALLJOINing the fun at Camp CallowayYour SQL toolbox

Low-budget optionsTwo-step troublesSubqueriesCombine the inner and outer queriesA subquery as a SELECT columnSubquery construction walk throughSubqueries with multiple resultsNon-correlated subqueriesCorrelated subqueriesA correlated subquery with NOT EXISTS......Or with EXISTSClick bait?Turning a subquery into a joinFROM subqueriesCommon table expressions (CTEs)Derived tables vs. CTEsSubquery successYour SQL toolbox
Hot and botheredQuery #1Query #2Query #3Creating a new table to queryTemporary tablesPermanent versus temporary storageReasons you might want a temporary tableCubicle conversationCreating a temporary tableUsing a temporary tableThe temporary of temporary tablesCubicle conversationBuilding a temporary table from scratchFilling your temporary tableA table for two?Your SQL toolbox
Repeating yourself...Utilize functions to reuse codeWriting your own functionUsing your user-defined functions (UDFs)UDFs with multiple parametersPlan your function with pseudocodeTranslating pseudocode to SQLStored procedures save code for laterCreating a stored procedureCALLing your procedureA procedure for any weatherAbby’s stored procedure in actionCorrecting mistakesAbby’s next adventure
Help wantedChef’s first day: Inserting IngersollChef avoids a NULLThree months later...CHECK, pleaseCHECKing the travel modeAna’s job gets tediousCreating a viewUsing your viewCubicle conversationWhat your view is actually doingWhat your view actually isWhy views are good for your databaseEditing or deleting your viewA quality teamYour SQL toolbox
Funding your next travelsWhat happened in the bank’s dataMore banking, more problemsSo, what is a transaction?The classic ACID testSQL transaction keywordsWhat SHOULD have happened in the bank’s dataComputational multitaskingConcurrency in SQLLocked in placeTable lockRow lockLocking tables......Or locking rowsCode MagnetsCode Magnets SolutionAvoiding blockingDeadlocksIsolation levelsConcurrent successesYour SQL toolbox
Coworker conundrumAvoiding data errorsThe root user accountAdding a passwordAdding a new userDeciding exactly what the user needsA simple GRANT statementGRANT variationsREVOKE privilegesIntroducing rolesAssigning rolesGetting rid of a roleGRANT yourself a round of applauseSQLcrossYour SQL toolboxSQLcross Solution
Abby’s head is in the cloudsWhat is the cloud?Why use the cloud?Reasons to NOT use the cloudOption #1: Infrastructure as a service (IaaS)Option #2: Platform as a service (PaaS)Option #3: Software as a service (SaaS)As-a-service recapCubicle conversationWho am I?Cloudy with a chance of other databasesYour SQL toolboxSQLcross Solution
Keeping your options openSQL is not limited to MySQLOther RDBMSsPostgreSQLSQLiteMicrosoft SQL ServerOracle DatabaseLinking Python to SQLWhat is SQLAlchemy?Executing SQL in PythonLet’s get non-relational?Meet the non-relational crewMongoDBMeet the non-relational crewRedisNeo4jEmbracing AIGenerative AI for SQLSteps for using a chatbotGoing placesSQLcrossYour SQL toolboxSQLcross Solution
#1. Get a GUI for your RDBMSMySQL WorkbenchOther GUI tools#2. Special characters and reserved wordsSpecial charactersReserved words#3. ALL and ANYUsing ALLUsing ANY#4. Working with dates and timesUsing DATE_FORMATExtracting other temporal parts#5. Convert your data typeCASTing around#6. Useful numeric functions#7. Window functionsAdding PARTITION BYOther aggregate functions#8. Who am I? What time is it?Getting the CURRENT_USERGetting the current date and time
Get started, fast!Instructions and troubleshootingSteps to install MySQL on Mac OSSteps to install MySQL on Mac OSSteps to install MySQL on WindowsSteps to install MySQL on Windows
SymbolABCDEFGHILMNOPRSTUVW

Content preview from Head First SQL, 2nd Edition

Chapter 9. Joins and Unions: Multiple-table Operations

Welcome to a multi-table world. It’s great to have more than one table in your database, but you’ll need to learn some new step sequences to get your tables to partner with each other. Joins help you reconnect all the data you’ve spread out. And as you’ll soon learn, there are several different join maneuvers for pairing your tables. The words cross join, inner join, left join, right join, and full outer join may not glide through your mind just yet, but by the end of this chapter, you’ll know them well enough to pick the best join for scoring big with the judges. You’ll also meet the self join for that solo work and UNION to combine the results of multiple queries. With these new tricks under your skates, you’ll soon know how to get all of your data back together again, ...