Synopses & Reviews
Is your data dragging you down? Are your tables all tangled up? Well we've got the tools to teach you just how to wrangle your databases into submission. Using the latest research in neurobiology, cognitive science, and learning theory to craft a multi-sensory SQL learning experience, Head First SQL has a visually rich format designed for the way your brain works, not a text-heavy approach that puts you to sleep.
Maybe you've written some simple SQL queries to interact with databases. But now you want more, you want to really dig into those databases and work with your data. Head First SQL will show you the fundamentals of SQL and how to really take advantage of it. We'll take you on a journey through the language, from basic INSERT statements and SELECT queries to hardcore database manipulation with indices, joins, and transactions. We all know "Data is Power" - but we'll show you how to have "Power over your Data". Expect to have fun, expect to learn, and expect to be querying, normalizing, and joining your data like a pro by the time you're finished reading!
With its visually rich format designed for the way the brain works, this series of engaging narrative lessons that build on each other gives readers hands-on experience working with the SQL database language.
About the Author
Lynn Beighley is a fiction writer stuck in a technical book writer's body. Upon discovering that technical book writing actually paid real money, she learned to accept and enjoy it.
After going back to school to get a Masters in Computer Science, she worked for the acronyms NRL and LANL. Then she discovered Flash, and wrote her first bestseller.
A victim of bad timing, she moved to Silicon Valley just before the great crash. She spent several years working for Yahoo! and writing other books and training courses. Finally giving in to her creative writing bent, she moved to the New York area to get an MFA in Creative Writing.
Her Head First-style thesis was delivered to a packed room of professors and fellow students. It was extremely well received, and she finished her degree, finished Head First SQL, and can't wait to begin her next book.
Lynn loves traveling, cooking, and making up elaborate background stories about complete strangers. She's a little scared of clowns.
Table of Contents
; Advance Praise for Head First SQL; Praise for other Head First books; Praise for the Head First Approach; Author of Head First SQL; How to use this Book: Intro; Who is this book for?; Who should probably back away from this book?; We know what you're thinking.; And we know what your brain is thinking.; Metacognition: thinking about thinking; Here's what WE did; Here's what YOU can do to bend your brain into submission; Read me; The technical review team; Acknowledgments; Chapter 1: Data and Tables: A place for everything; 1.1 Defining your data; 1.2 Look at your data in categories; 1.3 What's in a database?; 1.4 Your database viewed through x-ray specs...; 1.5 Databases contain connected data; 1.6 Take command!; 1.7 Setting the table: the CREATE TABLE statement; 1.8 Creating a more complicated table; 1.9 Look how easy it is to write SQL; 1.10 Create the my_contacts table, finally; 1.11 Your table is ready; 1.12 Take a meeting with some data types; 1.13 Your table, DESCribed; 1.14 You can't recreate an existing table or database!; 1.15 Out with the old table, in with the new; 1.16 To add data to your table, you'll use the INSERT statement; 1.17 Create the INSERT statement; 1.18 Variations on an INSERT statement; 1.19 Columns without values; 1.20 Peek at your table with the SELECT statement; 1.21 Controlling your inner NULL; 1.22 NOT NULL appears in DESC; 1.23 Fill in the blanks with DEFAULT; 1.24 Your SQL Toolbox; Chapter 2: The SELECT Statement: Gifted data retrieval; 2.1 Date or no date?; 2.2 Making contact; 2.3 A better SELECT; 2.4 What the * is that?; 2.5 How to query your data types; 2.6 More punctuation problems; 2.7 Unmatched single quotes; 2.8 Single quotes are special characters; 2.9 INSERT data with single quotes in it; 2.10 SELECT specific data; 2.11 The old way; 2.12 SELECT specific columns to limit results; 2.13 SELECT specific columns for faster results; 2.14 Doughnut ask what your table can do for you...; 2.15 Ask what you can do for your doughnut; 2.16 Combining your queries; 2.17 Finding numeric values; 2.18 Once is enough; 2.19 Smooth Comparison Operators; 2.20 Finding numeric data with Comparison Operators; 2.21 Text data roping with Comparison Operators; 2.22 Selecting your ingredients; 2.23 To be OR not to be; 2.24 The difference between AND and OR; 2.25 Use IS NULL to find NULLs; 2.26 Meanwhile, back at Greg's place...; 2.27 Saving time with a single keyword: LIKE; 2.28 The call of the Wild(card); 2.29 That's more LIKE it; 2.30 Just BETWEEN us... there's a better way; 2.31 After the dates, you are either IN...; 2.32 ... or you are NOT IN; 2.33 More NOT; 2.34 Your SQL Toolbox; Chapter 3: DELETE and UPDATE: A change will do you good; 3.1 Clowns are scary; 3.2 Clown tracking; 3.3 How our clown data gets entered; 3.4 Bonzo, we've got a problem; 3.5 Getting rid of a record with DELETE; 3.6 Using our new DELETE statement; 3.7 DELETE rules; 3.8 The INSERT-DELETE two step; 3.9 Be careful with your DELETE; 3.10 The trouble with imprecise DELETE; 3.11 Change your data with UPDATE; 3.12 UPDATE rules; 3.13 UPDATE is the new INSERT-DELETE; 3.14 UPDATE in action; 3.15 UPDATE your prices; 3.16 All we need is one UPDATE; 3.17 Your SQL Toolbox; Chapter 4: Smart Table Design: Why be normal?; 4.1 Two fishy tables; 4.2 A table is all about relationships; 4.3 Atomic data; 4.4 Atomic data and your tables; 4.5 Reasons to be normal; 4.6 The benefits of normal tables; 4.7 Clowns aren't normal; 4.8 Halfway to 1NF; 4.9 PRIMARY KEY rules; 4.10 Getting to NORMAL; 4.11 Fixing Greg's table; 4.12 The CREATE TABLE we wrote; 4.13 Show me the; 4.14 Time-saving command; 4.15 The CREATE TABLE with a PRIMARY KEY; 4.16 1, 2, 3... auto incrementally; 4.17 Adding a PRIMARY KEY to an existing table; 4.18 ALTER TABLE and add a PRIMARY KEY; 4.19 Your SQL Toolbox; Chapter 5: Alter: Rewriting the Past; 5.1 We need to make some changes; 5.2 Table altering; 5.3 Extreme table makeover; 5.4 Renaming the table; 5.5 We need to make some plans; 5.6 Retooling our columns; 5.7 Structural changes; 5.8 ALTER and CHANGE; 5.9 Change two columns with one SQL statement; 5.10 Quick! DROP that column; 5.11 A closer look at the non-atomic location column; 5.12 Look for patterns; 5.13 A few handy string functions; 5.14 Use a current column to fill a new column; 5.15 How our UPDATE and SET combo works; 5.16 Your SQL Toolbox; Chapter 6: Advanced Select: Seeing your data with new eyes; 6.1 Dataville Video is reorganizing; 6.2 Problems with our current table; 6.3 Matching up existing data; 6.4 Populating the new column; 6.5 UPDATE with a CASE expression; 6.6 Looks like we have a problem; 6.7 Tables can get messy; 6.8 We need a way to organize the data we SELECT; 6.9 Try a little ORDER BY; 6.10 ORDER a single column; 6.11 ORDER with two columns; 6.12 ORDER with multiple columns; 6.13 An orderly movie_table; 6.14 Reverse the ORDER with DESC; 6.15 The Girl Sprout® cookie sales leader problem; 6.16 SUM can add them for us; 6.17 SUM all of them at once with GROUP BY; 6.18 AVG with GROUP BY; 6.19 MIN and MAX; 6.20 COUNT the days; 6.21 SELECT DISTINCT values; 6.22 LIMIT the number of results; 6.23 LIMIT to just second place; 6.24 Your SQL Toolbox; Chapter 7: Multi-Table Database Design: Outgrowing your table; 7.1 Finding Nigel a date; 7.2 Why change anything?; 7.3 The query worked really well; 7.4 It worked too well; 7.5 Ignoring the problem isn't the answer; 7.6 Too many bad matches; 7.7 Use only the first interest; 7.8 A possible match; 7.9 Mis-matched; 7.10 Add more interest columns; 7.11 Starting over; 7.12 All is lost...; 7.13 ... But wait; 7.14 Think outside of the single table; 7.15 The multi-table clown tracking database; 7.16 The clown_tracking database schema; 7.17 An easier way to diagram your tables; 7.18 How to go from one table to two; 7.19 Linking your tables in a diagram; 7.20 Connecting your tables; 7.21 Foreign key facts; 7.22 Constraining your foreign key; 7.23 Why bother with foreign keys?; 7.24 CREATE a table with a FOREIGN KEY; 7.25 Relationships between tables; 7.26 Patterns of data: one-to-one; 7.27 Patterns of data: when to use one-to-one tables; 7.28 Patterns of data: one-to-many; 7.29 Patterns of data: getting to many-to-many; 7.30 Patterns of data: we need a junction table; 7.31 Patterns of data: many-to-many; 7.32 Patterns of data: fixing gregs_list; 7.33 Not in first normal form; 7.34 Finally in 1NF; 7.35 Composite keys use multiple columns; 7.36 Even superheros can be dependent; 7.37 Shorthand notations; 7.38 Superhero dependencies; 7.39 Partial functional dependency; 7.40 Transitive functional dependency; 7.41 Second normal form; 7.42 We might be 2NF already...; 7.43 Third normal form (at last); 7.44 And so, Regis (and gregs_list) lived happily ever after; 7.45 The End; 7.46 Your SQL Toolbox; Chapter 8: Joins and Multi-Table Operations: Can't we all just get along?; 8.1 Still repeating ourselves, still repeating...; 8.2 Prepopulate your tables; 8.3 We got the "table ain't easy to normalize" blues; 8.4 The special interests (column); 8.5 Keeping interested; 8.6 UPDATE all your interests; 8.7 Getting all the interests; 8.8 Many paths to one place; 8.9 CREATE, SELECT and INSERT at (nearly) the same time; 8.10 CREATE, SELECT and INSERT at the same time; 8.11 What's up with that AS?; 8.12 Column aliases; 8.13 Table aliases, who needs 'em?; 8.14 Everything you wanted to know about inner joins; 8.15 Cartesian join; 8.16 Releasing your inner join; 8.17 The inner join in action: the equijoin; 8.18 The inner join in action: the non-equijoin; 8.19 The last inner join: the natural join; 8.20 Joined-up queries?; 8.21 Your SQL Toolbox; Chapter 9: Subqueries: Queries within queries; 9.1 Greg gets into the job recruiting business; 9.2 Greg's list gets more tables; 9.3 Greg uses an inner join; 9.4 But he wants to try some other queries; 9.5 Subqueries; 9.6 We combine the two into a query with a subquery; 9.7 Subquery rules; 9.8 Subquery rules; 9.9 A subquery construction walkthrough; 9.10 A subquery as a SELECT column; 9.11 Another example: Subquery with a natural join; 9.12 A noncorrelated subquery; 9.13 A noncorrelated subquery with multiple values: IN, NOT IN; 9.14 Correlated subqueries; 9.15 A (useful) correlated subquery with NOT EXISTS; 9.16 EXISTS and NOT EXISTS; 9.17 Greg's Recruiting Service is open for business; 9.18 On the way to the party; 9.19 Your SQL Toolbox; Chapter 10: Outer Joins, Self-Joins, and Unions: New maneuvers; 10.1 Cleaning up old data; 10.2 It's about left and right; 10.3 Here's a left outer join; 10.4 Outer joins and multiple matches; 10.5 The right outer join; 10.6 While you were outer joining...; 10.7 We could create a new table; 10.8 How the new table fits in; 10.9 A self-referencing foreign key; 10.10 Join the same table to itself; 10.11 We need a self-join; 10.12 Another way to get multi-table information; 10.13 You can use a UNION; 10.14 UNION is limited; 10.15 UNION rules in action; 10.16 UNION ALL; 10.17 Create a table from your union; 10.18 INTERSECT and EXCEPT; 10.19 We're done with joins, time to move on to...; 10.20 Subqueries and joins compared; 10.21 Turning a subquery into a join; 10.22 A self-join as a subquery; 10.23 Greg's company is growing; 10.24 Your SQL Toolbox; Chapter 11: Constraints, Views, and Transactions: Too many cooks spoil the database; 11.1 Greg's hired some help; 11.2 Jim's first day: Inserting a new client; 11.3 Jim avoids a NULL; 11.4 Flash forward three months; 11.5 CHECK, please: Adding a CHECK CONSTRAINT; 11.6 CHECKing the gender; 11.7 Frank's job gets tedious; 11.8 Creating a view; 11.9 Viewing your views; 11.10 What your view is actually doing; 11.11 What a view is; 11.12 Inserting, updating, and deleting with views; 11.13 The secret is to pretend a view is a real table; 11.14 View with CHECK OPTION; 11.15 Your view may be updatable if...; 11.16 When you're finished with your view; 11.17 When bad things happen to good databases; 11.18 What happened inside the ATM; 11.19 More trouble at the ATM; 11.20 It's not a dream, it's a transaction; 11.21 The classic ACID test; 11.22 SQL helps you manage your transactions; 11.23 What should have happened inside the ATM; 11.24 How to make transactions work with MySQL; 11.25 Now try it yourself; 11.26 Your SQL Toolbox; Chapter 12: Security: Protecting your assets; 12.1 User problems; 12.2 Avoiding errors in the clown tracking database; 12.3 Protect the root user account; 12.4 Add a new user; 12.5 Decide exactly what the user needs; 12.6 A simple GRANT statement; 12.7 GRANT variations; 12.8 REVOKE privileges; 12.9 REVOKING a used GRANT OPTION; 12.10 REVOKING with precision; 12.11 The problem with shared accounts; 12.12 Using your role; 12.13 Role dropping; 12.14 Using your role WITH ADMIN OPTION; 12.15 Combining CREATE USER and GRANT; 12.16 Greg's List has gone global!; 12.17 Your SQL Toolbox; 12.18 How about a Greg's List in your city?; 12.19 Use SQL on your own projects, and you too could be like Greg!; Leftovers: The Top Ten Topics (we didn't cover); #1. Get a GUI for your RDBMS; #2. Reserved Words and Special Characters; #3. ALL, ANY, and SOME; #4. More on Data Types; #5. Temporary tables; #6. Cast your data; #7. Who are you? What time is it?; #8. Useful numeric functions; #9. Indexing to speed things up; #10. 2-minute PHP/MySQL; MySQL Installation: Try it out for yourself; Get started, fast!; Instructions and Troubleshooting; Steps to Install MySQL on Windows; Steps to Install MySQL on Mac OS X; Tools Roundup: All your new SQL tools; ; Symbols; A; B; C; D; E; F; G; I; L; M; N; O; P; S; T; U; V; W;