SQL #2: Constraints

@grchab

#sql #learnsql #learntocode #learninpublic

♬ original sound – Curiosity Corner 🙇🏽‍♂️

So in the last post we learned how to specify columns.

Now in any table, all the rows have an ID number, like 1, 2, 3, 4…

In real-world data, there’s gonna be hundreds, if not thousands of rows. Not only will I be looking for instances, I’ll be looking for sets of instances with different factors applied to them, aka constraints. When it comes to this, scrolling and looking is not an option, at least not an efficient one. With this, we use the ‘WHERE’ command.

We’re gonna keep using my imaginary basketball team.

So let’s say I wanted to find a player by his ID number (not his jersey number, his place on the table). If his ID is 6 I would do:

SELECT id, name FROM bball
WHERE id = 6;

The name is necessary for this command because if you just left it at ‘id’, it would ONLY show that ID. You have to be very specific with these computers. There’s no room for assumptions. Any column you want to see, you have to tell it you want to see it.

Enough with the IDs now. Let’s say I want to see all my players who are between 6’3 and 6’7. I’m glad this language is keeping it straightforward so far because I would use the ‘BETWEEN’ command:

SELECT player, height FROM bball
WHERE height BETWEEN 6'3 AND 6'7

The top line selects the columns I want to work with. The bottom line adds a constraint to those columns, which was the range of heights in this case.

Now let’s flip it. If I want to see all the players who are under 6’3 or over 6’7, anybody who wouldn’t have been in that last table, I’d use the greater than and less than signs:

SELECT player, height FROM bball
WHERE height < 6'3 OR height > 6'7

This is the opposite of ‘BETWEEN’.

Lastly, say I want to see all the players 6’6 and under.

SELECT player, height FROM bball
WHERE height <= 6'6;