Let’s Start with SELECT

I must type “SELECT * FROM …..” hundreds of times a day. Would it be more efficient to specify the columns or save the SQL so that I don’t have to keep typing it over and over again? Probably. However, in my brain, typing is really fast– probably faster than navigating to the folder to try to find the snippet of code. So I end up typing it over again.

The SELECT command (and I almost always type this in all caps) performs the operation of finding the data from the table or groups of tables that you’re interested in. SQL follows English in many ways, such that what you’re going to enter into the database for a command is something like…

SELECT --something-- FROM --something-- WHERE --something--;

All SELECT commands start with select and have the word FROM. Unless, of course you’re just trying to do math or something, then you can just do SELECT, but that’s very infrequent in what I typically do.

SELECT * FROM Purchases;

This call in the database that my kids and I are working on will return all the things we’ve purchased– where it came from, how much it cost, how much was shipping, etc.  The * tells the database to give me all the columns that are in the table. The fact that I don’t have a WHERE after Purchases means that I get all the rows. So, if I wanted to see only those purchases that were profitable, I could do…

SELECT * FROM Purchases WHERE Profit > 0;

Then I can see that I should probably find other things to buy!

Since I have a lookup table for places I purchase from, I use a method to bring them together if I want to see the real name of where I purchased from…

SELECT * FROM Purchases INNER JOIN Sources ON Purchases.SourceID = Sources.SourceID WHERE Profit > 0;

This code would give me the name of the source as well as everything else about the purchases that were profitable. The only way this could be better is if I limited the output columns…

SELECT Sources.Name, Purchases.Price, Purchases.Shipping, Purchases.Profit
FROM Purchases
INNER JOIN Sources ON Purchases.SourceID = Sources.SourceID
WHERE Profit > 0;

That would be very close to what I’m looking for with this query, and has a lot of what I do a bunch of– inner joining items. What is this join stuff I’m talking about? Glad you asked. We’ll pick up with VENN Diagrams and joins in our next lesson.