Books & Videos

Table of Contents

  1. Chapter 1 Data and Tables: A place for everything

    1. Defining your data

    2. Look at your data in categories

    3. What’s in a database?

    4. Your database viewed through x-ray specs...

    5. Databases contain connected data

    6. Take command!

    7. Setting the table: the CREATE TABLE statement

    8. Creating a more complicated table

    9. Look how easy it is to write SQL

    10. Create the my_contacts table, finally

    11. Your table is ready

    12. Take a meeting with some data types

    13. Your table, DESCribed

    14. You can’t recreate an existing table or database!

    15. Out with the old table, in with the new

    16. To add data to your table, you’ll use the INSERT statement

    17. Create the INSERT statement

    18. Variations on an INSERT statement

    19. Columns without values

    20. Peek at your table with the SELECT statement

    21. Controlling your inner NULL

    22. NOT NULL appears in DESC

    23. Fill in the blanks with DEFAULT

    24. Your SQL Toolbox

  2. Chapter 2 The SELECT Statement: Gifted data retrieval

    1. Date or no date?

    2. Making contact

    3. A better SELECT

    4. What the * is that?

    5. How to query your data types

    6. More punctuation problems

    7. Unmatched single quotes

    8. Single quotes are special characters

    9. INSERT data with single quotes in it

    10. SELECT specific data

    11. The old way

    12. SELECT specific columns to limit results

    13. SELECT specific columns for faster results

    14. Doughnut ask what your table can do for you...

    15. Ask what you can do for your doughnut

    16. Combining your queries

    17. Finding numeric values

    18. Once is enough

    19. Smooth Comparison Operators

    20. Finding numeric data with Comparison Operators

    21. Text data roping with Comparison Operators

    22. Selecting your ingredients

    23. To be OR not to be

    24. The difference between AND and OR

    25. Use IS NULL to find NULLs

    26. Meanwhile, back at Greg’s place...

    27. Saving time with a single keyword: LIKE

    28. The call of the Wild(card)

    29. That’s more LIKE it

    30. Just BETWEEN us... there’s a better way

    31. After the dates, you are either IN...

    32. ... or you are NOT IN

    33. More NOT

    34. Your SQL Toolbox

  3. Chapter 3 DELETE and UPDATE: A change will do you good

    1. Clowns are scary

    2. Clown tracking

    3. How our clown data gets entered

    4. Bonzo, we’ve got a problem

    5. Getting rid of a record with DELETE

    6. Using our new DELETE statement

    7. DELETE rules

    8. The INSERT-DELETE two step

    9. Be careful with your DELETE

    10. The trouble with imprecise DELETE

    11. Change your data with UPDATE

    12. UPDATE rules

    13. UPDATE is the new INSERT-DELETE

    14. UPDATE in action

    15. UPDATE your prices

    16. All we need is one UPDATE

    17. Your SQL Toolbox

  4. Chapter 4 Smart Table Design: Why be normal?

    1. Two fishy tables

    2. A table is all about relationships

    3. Atomic data

    4. Atomic data and your tables

    5. Reasons to be normal

    6. The benefits of normal tables

    7. Clowns aren’t normal

    8. Halfway to 1NF

    9. PRIMARY KEY rules

    10. Getting to NORMAL

    11. Fixing Greg’s table

    12. The CREATE TABLE we wrote

    13. Show me the

    14. Time-saving command

    15. The CREATE TABLE with a PRIMARY KEY

    16. 1, 2, 3... auto incrementally

    17. Adding a PRIMARY KEY to an existing table

    18. ALTER TABLE and add a PRIMARY KEY

    19. Your SQL Toolbox

  5. Chapter 5 Alter: Rewriting the Past

    1. We need to make some changes

    2. Table altering

    3. Extreme table makeover

    4. Renaming the table

    5. We need to make some plans

    6. Retooling our columns

    7. Structural changes

    8. ALTER and CHANGE

    9. Change two columns with one SQL statement

    10. Quick! DROP that column

    11. A closer look at the non-atomic location column

    12. Look for patterns

    13. A few handy string functions

    14. Use a current column to fill a new column

    15. How our UPDATE and SET combo works

    16. Your SQL Toolbox

  6. Chapter 6 Advanced Select: Seeing your data with new eyes

    1. Dataville Video is reorganizing

    2. Problems with our current table

    3. Matching up existing data

    4. Populating the new column

    5. UPDATE with a CASE expression

    6. Looks like we have a problem

    7. Tables can get messy

    8. We need a way to organize the data we SELECT

    9. Try a little ORDER BY

    10. ORDER a single column

    11. ORDER with two columns

    12. ORDER with multiple columns

    13. An orderly movie_table

    14. Reverse the ORDER with DESC

    15. The Girl Sprout® cookie sales leader problem

    16. SUM can add them for us

    17. SUM all of them at once with GROUP BY

    18. AVG with GROUP BY

    19. MIN and MAX

    20. COUNT the days

    21. SELECT DISTINCT values

    22. LIMIT the number of results

    23. LIMIT to just second place

    24. Your SQL Toolbox

  7. Chapter 7 Multi-Table Database Design: Outgrowing your table

    1. Finding Nigel a date

    2. Why change anything?

    3. The query worked really well

    4. It worked too well

    5. Ignoring the problem isn’t the answer

    6. Too many bad matches

    7. Use only the first interest

    8. A possible match

    9. Mis-matched

    10. Add more interest columns

    11. Starting over

    12. All is lost...

    13. ... But wait

    14. Think outside of the single table

    15. The multi-table clown tracking database

    16. The clown_tracking database schema

    17. An easier way to diagram your tables

    18. How to go from one table to two

    19. Linking your tables in a diagram

    20. Connecting your tables

    21. Foreign key facts

    22. Constraining your foreign key

    23. Why bother with foreign keys?

    24. CREATE a table with a FOREIGN KEY

    25. Relationships between tables

    26. Patterns of data: one-to-one

    27. Patterns of data: when to use one-to-one tables

    28. Patterns of data: one-to-many

    29. Patterns of data: getting to many-to-many

    30. Patterns of data: we need a junction table

    31. Patterns of data: many-to-many

    32. Patterns of data: fixing gregs_list

    33. Not in first normal form

    34. Finally in 1NF

    35. Composite keys use multiple columns

    36. Even superheros can be dependent

    37. Shorthand notations

    38. Superhero dependencies

    39. Partial functional dependency

    40. Transitive functional dependency

    41. Second normal form

    42. We might be 2NF already...

    43. Third normal form (at last)

    44. And so, Regis (and gregs_list) lived happily ever after

    45. The End

    46. Your SQL Toolbox

  8. Chapter 8 Joins and Multi-Table Operations: Can’t we all just get along?

    1. Still repeating ourselves, still repeating...

    2. Prepopulate your tables

    3. We got the “table ain’t easy to normalize” blues

    4. The special interests (column)

    5. Keeping interested

    6. UPDATE all your interests

    7. Getting all the interests

    8. Many paths to one place

    9. CREATE, SELECT and INSERT at (nearly) the same time

    10. CREATE, SELECT and INSERT at the same time

    11. What’s up with that AS?

    12. Column aliases

    13. Table aliases, who needs ’em?

    14. Everything you wanted to know about inner joins

    15. Cartesian join

    16. Releasing your inner join

    17. The inner join in action: the equijoin

    18. The inner join in action: the non-equijoin

    19. The last inner join: the natural join

    20. Joined-up queries?

    21. Your SQL Toolbox

  9. Chapter 9 Subqueries: Queries within queries

    1. Greg gets into the job recruiting business

    2. Greg’s list gets more tables

    3. Greg uses an inner join

    4. But he wants to try some other queries

    5. Subqueries

    6. We combine the two into a query with a subquery

    7. Subquery rules

    8. Subquery rules

    9. A subquery construction walkthrough

    10. A subquery as a SELECT column

    11. Another example: Subquery with a natural join

    12. A noncorrelated subquery

    13. A noncorrelated subquery with multiple values: IN, NOT IN

    14. Correlated subqueries

    15. A (useful) correlated subquery with NOT EXISTS

    16. EXISTS and NOT EXISTS

    17. Greg’s Recruiting Service is open for business

    18. On the way to the party

    19. Your SQL Toolbox

  10. Chapter 10 Outer Joins, Self-Joins, and Unions: New maneuvers

    1. Cleaning up old data

    2. It’s about left and right

    3. Here’s a left outer join

    4. Outer joins and multiple matches

    5. The right outer join

    6. While you were outer joining...

    7. We could create a new table

    8. How the new table fits in

    9. A self-referencing foreign key

    10. Join the same table to itself

    11. We need a self-join

    12. Another way to get multi-table information

    13. You can use a UNION

    14. UNION is limited

    15. UNION rules in action

    16. UNION ALL

    17. Create a table from your union

    18. INTERSECT and EXCEPT

    19. We’re done with joins, time to move on to...

    20. Subqueries and joins compared

    21. Turning a subquery into a join

    22. A self-join as a subquery

    23. Greg’s company is growing

    24. Your SQL Toolbox

  11. Chapter 11 Constraints, Views, and Transactions: Too many cooks spoil the database

    1. Greg’s hired some help

    2. Jim’s first day: Inserting a new client

    3. Jim avoids a NULL

    4. Flash forward three months

    5. CHECK, please: Adding a CHECK CONSTRAINT

    6. CHECKing the gender

    7. Frank’s job gets tedious

    8. Creating a view

    9. Viewing your views

    10. What your view is actually doing

    11. What a view is

    12. Inserting, updating, and deleting with views

    13. The secret is to pretend a view is a real table

    14. View with CHECK OPTION

    15. Your view may be updatable if...

    16. When you’re finished with your view

    17. When bad things happen to good databases

    18. What happened inside the ATM

    19. More trouble at the ATM

    20. It’s not a dream, it’s a transaction

    21. The classic ACID test

    22. SQL helps you manage your transactions

    23. What should have happened inside the ATM

    24. How to make transactions work with MySQL

    25. Now try it yourself

    26. Your SQL Toolbox

  12. Chapter 12 Security: Protecting your assets

    1. User problems

    2. Avoiding errors in the clown tracking database

    3. Protect the root user account

    4. Add a new user

    5. Decide exactly what the user needs

    6. A simple GRANT statement

    7. GRANT variations

    8. REVOKE privileges

    9. REVOKING a used GRANT OPTION

    10. REVOKING with precision

    11. The problem with shared accounts

    12. Using your role

    13. Role dropping

    14. Using your role WITH ADMIN OPTION

    15. Combining CREATE USER and GRANT

    16. Greg’s List has gone global!

    17. Your SQL Toolbox

    18. How about a Greg’s List in your city?

    19. Use SQL on your own projects, and you too could be like Greg!

  1. Appendix Leftovers: The Top Ten Topics (we didn’t cover)

    1. #1. Get a GUI for your RDBMS

    2. #2. Reserved Words and Special Characters

    3. #3. ALL, ANY, and SOME

    4. #4. More on Data Types

    5. #5. Temporary tables

    6. #6. Cast your data

    7. #7. Who are you? What time is it?

    8. #8. Useful numeric functions

    9. #9. Indexing to speed things up

    10. #10. 2-minute PHP/MySQL

  2. Appendix MySQL Installation: Try it out for yourself

    1. Get started, fast!

    2. Instructions and Troubleshooting

    3. Steps to Install MySQL on Windows

    4. Steps to Install MySQL on Mac OS X

  3. Appendix Tools Roundup: All your new SQL tools

  4. Appendix

    1. Symbols

    2. A

    3. B

    4. C

    5. D

    6. E

    7. F

    8. G

    9. I

    10. L

    11. M

    12. N

    13. O

    14. P

    15. S

    16. T

    17. U

    18. V

    19. W