Kellner - SQL With a Smile

Copyright

2016 Elaine Kellner

All rights reserved.

This book or parts thereof may not be reproduced in any form, stored in any retrieval system, or transmitted in any form by any meanselectronic, mechanical, photocopy, recording, or otherwisewithout prior written permission of the publisher, except as provided by United States of America copyright law.

This ebook is licensed for your personal enjoyment only. If you would like to share this book with another person, please purchase an additional copy for each recipient. If you're reading this book and did not purchase it, please visit your favorite ebook retailer and purchase a copy. Thank you for respecting the hard work of this author.

For more SQL tips and bad jokes, visit the authors blog at www.elainekellner.com/sql

This is a work of nonfiction. Any resemblance to actual SQL queries is intentional. Names of actual people and places are all used without permission, but with a smile.

ISBN 978-0-9974015-0-9

Dedication

To Kiefer, Lar, Larry, and Karl.

I owe you all a drink.

Preface

Why bother learning SQL?

Whats the point of this SQL stuff anyway? Why would anyone bother? Why should YOU bother?

The short answer is that data is seriously important. Businesses need data to operate, and they generate tons of it every day. That means they need people who can help organize, access, and interrogate the data they need. Management needs to know whether their widgets are selling and what can be done to sell more of them. Customers need to see what products or services are available. And everyone needs to know if theyre meeting their quarterly performance goals.

If youre already doing some data analysis, being able to write SQL yourself will make you faster. You wont have to wait for the SQL guy to set up a query for you, or for the database gal to update your spreadsheet.

For devs, learning SQL will make you more efficient. Youll have a much better idea of whats going on with your data storage layer, youll be better able to troubleshoot your applications, and you wont get hung up waiting for someone else to fix things.

Best of all, it looks darn good on the resume. Knowing SQL will make you a more valuable and more efficient employee.

Just how many SQLs are there?

Like human languages, computer languages can come in a variety of dialects. In the same way that British, Australian, and American English are all slightly different, Oracle, T-SQL, and MySQL are all versions of SQL that are broadly the same - but just a little different.

For this book, were going to try and keep things as standardized as possible. If youd like to follow along by running the sample queries yourself, I highly recommend that you download a copy of Microsoft SQL Server Express. Its free and 100% compatible with the SQL code in this book.

Be aware that some more advanced commands might look a little different, depending on which flavor of SQL youre working with. Ive noted the biggest differences where applicable.

The audience for this book

Sure, some people learn SQL at school, but many of us end up running in to it while on the job and need a crash course.

Perhaps youve found you need to write a few queries to get the numbers you need to do your job. Maybe youre already a programmer and you find you need to write some SQL queries to support your application. Or it might be that youve suddenly found yourself stuck with some legacy SQL code you now have to support.

The goal of this book is to get you writing and understanding SQL queries quickly. And to have fun while were at it. However you found yourself here, this book will get you up and running with the basics of SQL, so you can get your job done and look brilliant in front of your boss.

Ready to see how it works? Lets get started!

Introduction

What is SQL, anyway?

SQL is the language that you use to tell the database what data you want, and how that data is related.

SQL (which stands for Structured Query Language, if youre wondering) can help you get all kinds of insights out of your data. While it is a computer language, its a bit different than, say, C# or Java. With SQL, youre asking the database a question, and the database does all the heavy lifting to get you an answer.

Learning SQL is really just a matter of figuring out how to ask the right questions.

Whats a database?

While SQL is the language, a Relational Database (RDB) is what SQL talks to. A relational database is a collection of tables that are related to each other. Tables, in turn, are columns and rows of data.

You can think of a database as a big collection of spreadsheets. That grid of columns and rows, with each cell containing a different piece of data, is actually a pretty good way to picture a SQL table - and the results of SQL queries.

A database is the bucket that holds those tables, just like a workbook holds several spreadsheets.

Whats the big deal with databases?

Well, spreadsheets can get big and clunky very quickly. Working with SQL is a little like having a turbocharged spreadsheet.

Every business needs to store a lot of data - what employees you have, what products you sell, who your customers are, etc., etc. If all that data was crammed into a single workbook, answering simple questions like What products are our top sellers? would take forever. Theres just too much data to sift through.

Whats more, its really tough to do calculations between different spreadsheets in Excel. If you have your sales data in one spreadsheet and your customers in another, youd have to do a lot of work to figure out which customers ordered what.

Let me give you an example. Here are two spreadsheets:

Sales

Customers

Theres some good data in there, but its kind of hard to work with. Theres no simple, programmatic way in a spreadsheet to get answers to questions like

• Whats the name of the customer who purchased the most Widgets?
• To which state have the most items been shipped?
• Which customer(s) didnt make any purchases in February?

The answers to each of those questions are there, but since theres no connection between these two spreadsheets, youd have to go back and forth between them to find the answer. The more data you put in, the harder it is to get answers back.

Thats exactly where relational databases come in. A relational database is like a bunch of fancy Excel spreadsheets that know how to talk to each other.

In a relational database, you can relate data from different tables, so you can combine all of the details together. You can tell the computer that the CustomerID in the Sales table is the same as the CustomerID in the Customers table - and then the computer can take it from there.