SQL SELECT Tutorial

| |

To view all the SQL tutorials in chronological order, visit the SQL page

Now that we understand Inserting, updating, and deleting records, and modifying the table structure, we’re ready to start retrieving information, or querying the database.

Documentation: Pattern Matching Regular Expressions SELECT Statement

In order to learn how to grab information out of the database, we need lots of data to play with, so lets get some data. We’ll start over with the pets table. Go ahead and delete the pets table (if you have one) and create a new one with the following sql:

CREATE TABLE pets (
pet_id INT NOT NULL AUTO_INCREMENT,
pet_name VARCHAR(20) NOT NULL,
class VARCHAR(30),
age TINYINT,
family VARCHAR(30),
owner VARCHAR(30),
owner_gender VARCHAR(30),
PRIMARY KEY (pet_id)
);

We’re going to fill the DB with about 30 rows so we have a little something to work with. Enter the following SQL!

INSERT INTO pets (pet_name, class, age, family, owner, owner_gender)
VALUES
("PJ", "Mammal", 15, "Dog", "John", "Male"),
("Buffy", "Mammal", 14, "Dog", "John", "Male"),
("Pixie", "Mammal", 8, "Cat", "Faith", "Female"),
("Charlie", "Mammal", 7, "Cat", "Judy", "Female"),
("Scooter", "Mammal", 10, "Squirrel", "Judy", "Female"),
("Smoke", "Bird", 4, "Parrot", "Alex", "none ya bizness"),
("Butch", "Reptile", 2, "Iguana", "Allison", "Female"),
("Rex", "Plant", 8, "Venus Flytrap", "Lucy", "Female"),
("Snowflake", "Amphibian", 23, "Salamander", "Faith", "Female"),
("Spot", "Arthropod", 4, "Black widow", "Mary", "Female"),
("Daisy", "Plant", 2, "Orchid", "Mary", "none ya bizness"),
("Squawk", "Bird", 9, "Hawk", "Giovanni", "Male"),
("Skeeter", "Reptile", 3, "Snake", "John", "Male"),
("Moonlight", "Mammal", 6, "Dog", "John", "Male"),
("Sunshine", "Fish", 1, "Piranha", "Mary", "Female"),
("Sandy", "Fish", 1, "Koi", "Allison", "Female"),
("Wags", "Mammal", 2, "Dog", "Alex", "Male"),
("Willy", "Amphibian", 9, "Frog", "Faith", "Female"),
("Tank", "Bird", 7, "Eagle", "Kate", "Female"),
("Ollie", "reptile", 14, "Dragon", "Judy", "Female"),
("Coco", "Bird", 11, "Parakeet", "Mary", "Female"),
("Luna", "Arthropod", 2, "Scorpion", "Giovanni", "Male"),
("Molly", "Mammal", 5, "Whale", "Mary", "Female"),
("Milly", "Arthropod", 4, "Grasshopper", "Edwina", "Female"),
("Simba", "Mammal", 8, "Cat", "Mary", "Female"),
("Jaws", "Fish", 13, "Barracuda", "Chris", "Male"),
("Yoshi", "Reptile", 4, "Dragon", "Zena", "Female"),
("Rango", "Amphibian", 7, "Frog", "Jason", "Male"),
("Pringle", "Mammal", 13, "Dolphin", "Caroline", "Female"),
("Lizzie", "Reptile", 3, "Lizard", "Curtis", "Male"),
("Norbert", "Bird", 7, "Magpie", "Owen", "Male"),
("Godzilla", "Reptile", 20, "Dragon", "Caroline", "Female"),
("Blue", "Mammal", 40, "Whale", "Thomas", "Male"),
("Blizzard", "Plant", 6, "Orchid", "Sreehari", "Male"),
("Spinner", "Mammal", 5, "Dolphin", "Sreehari", "Male"),
("Charmander", "Reptile", 2, "Lizard", "Jason", "Male")

Let’s use the SELECT query to get specific data from the database. First, let’s grab everything from the database.

SELECT *
FROM pets;

The * represents everything basically. So you can read it like this: “Select everything from the pets table”, and it will happily return everything in that table. Every column of every row. That would take FOREVER if there were millions of records though. So make sure you only grab what you want. Here’s what you should get:

pet_idpet_nameclassagefamilyownerowner_gender
1PJMammal15DogJohnMale
2BuffyMammal14DogJohnMale
3PixieMammal8CatFaithFemale
4CharlieMammal7CatJudyFemale
5ScooterMammal10SquirrelJudyFemale
6SmokeBird4ParrotAlexnone ya bizness
7ButchReptile2IguanaAllisonFemale
8RexPlant8Venus FlytrapLucyFemale
9SnowflakeAmphibian23SalamanderFaithFemale
10SpotArthropod4Black widowMaryFemale
11DaisyPlant2OrchidMarynone ya bizness
12SquawkBird9HawkGiovanniMale
13SkeeterReptile3SnakeJohnMale
14MoonlightMammal6DogJohnMale
15SunshineFish1PiranhaMaryFemale
16SandyFish1KoiAllisonFemale
17WagsMammal2DogAlexMale
18WillyAmphibian9FrogFaithFemale
19TankBird7EagleKateFemale
20Olliereptile14DragonJudyFemale
21CocoBird11ParakeetMaryFemale
22LunaArthropod2ScorpionGiovanniMale
23MollyMammal5WhaleMaryFemale
24MillyArthropod4GrasshopperEdwinaFemale
25SimbaMammal8CatMaryFemale
26JawsFish13BarracudaChrisMale
27YoshiReptile4DragonZenaFemale
28RangoAmphibian7FrogJasonMale
29PringleMammal13DolphinCarolineFemale
30LizzieReptile3LizardCurtisMale
31NorbertBird7MagpieOwenMale
32GodzillaReptile20DragonCarolineFemale
33BlueMammal40WhaleThomasMale
34BlizzardPlant6OrchidSreehariMale
35SpinnerMammal5DolphinSreehariMale
36CharmanderReptile2LizardJasonMale

Let’s try getting just the names of the pets. You use “SELECT” to choose which columns you want to get from the table, and “FROM” will determine which table you’re getting the information from.

SELECT pet_name
FROM pets;

Easy right? Shows the name of every pet, without all the unnecessary info. Cool beans!

pet_name
PJ
Buffy
Pixie
Charlie
Scooter
Smoke
Butch
Rex
Snowflake
Spot
Daisy
Squawk
Skeeter
Moonlight
Sunshine
Sandy
Wags
Willy
Tank
Ollie
Coco
Luna
Molly
Milly
Simba
Jaws
Yoshi
Rango
Pringle
Lizzie
Norbert
Godzilla
Blue
Blizzard
Spinner
Charmander

How about getting the pet_name and its “family”? Again, SELECT which columns you want. In this case, we want ‘pet_name’ and ‘family’, FROM the ‘pets’ table, so the query looks like this:

SELECT pet_name, family
FROM pets;

See how easy that is?

pet_namefamily
PJDog
BuffyDog
PixieCat
CharlieCat
ScooterSquirrel
SmokeParrot
ButchIguana
RexVenus Flytrap
SnowflakeSalamander
SpotBlack widow
DaisyOrchid
SquawkHawk
SkeeterSnake
MoonlightDog
SunshinePiranha
SandyKoi
WagsDog
WillyFrog
TankEagle
OllieDragon
CocoParakeet
LunaScorpion
MollyWhale
MillyGrasshopper
SimbaCat
JawsBarracuda
YoshiDragon
RangoFrog
PringleDolphin
LizzieLizard
NorbertMagpie
GodzillaDragon
BlueWhale
BlizzardOrchid
SpinnerDolphin
CharmanderLizard

Now lets try getting all of the owners names who own a reptile. To get only a specific record or records that match a condition, you’ll use the ‘WHERE’ clause, like you did with DELETE in the last tutorial.

We’re just chaining things together. SELECT ‘columns you want’ FROM ‘the table’ WHERE ‘some condition is met’

To get the owners who own a reptile, it’d look like this:

SELECT owner
FROM pets
WHERE class = "Reptile";

If you’re working with the same database as me, you should get 7 people. Allison, John, Judy, Zena, Curtis, Caroline, and Jason!

owner
Allison
John
Judy
Zena
Curtis
Caroline
Jason

Let’s see how many of these reptile owners are Female. That means we’re even MORE specific. We need the owners who own a reptile AND are female. You can use the AND keyword inside the WHERE clause to make your conditions very specific:

SELECT owner
FROM pets
WHERE class = "Reptile" AND owner_gender = "Female";
owner
Allison
Judy
Zena
Caroline

What if you wanted to get all the Males owning a Mammal OR Females owning a Bird. You can use the “OR” keyword too. You’re able to “chain” conditions together as needed. Notice the parenthesis groups conditions together.

SELECT owner, class, family, owner_gender
FROM pets
WHERE
(class = "Reptile" AND owner_gender = "Male") OR (class = "Bird" AND owner_gender = "Female")
ownerclassfamilyowner_gender
JohnReptileSnakeMale
KateBirdEagleFemale
MaryBirdParakeetFemale
CurtisReptileLizardMale
JasonReptileLizardMale

If someone identifies their gender as “none ya bizness” and they own a bird or reptile, lets include them as well. There’s several ways to do this. You could include the “none ya bizness” in both the Male section and female section, or you could just create a separate “OR” clause for “none ya bizness”.

It can get confusing pretty quickly when you’re chaining ANDS and or’s, so make sure you use parenthesis, indentation, and newlines to help your mind process the information. Also, it’s not always best to cram everything into one query. Simple is usually better.

SELECT owner, class, family, owner_gender
FROM pets
WHERE
(class = "Reptile" AND owner_gender = "Male")
OR
(class = "Bird" AND owner_gender = "Female")
OR
( (class = "Bird" OR class = "Reptile")
AND owner_gender = "none ya bizness" )

What we have above in plain english is this: “Get the owner, class, family, and owner_gender columns from the pets table, ONLY where these conditions are met. The person is a Male who owns a reptile. Also Get all bird owners who are Female. Get all “none ya bizness” genders who own either birds or reptiles.”

This is the results of the query:

ownerclassfamilyowner_gender
AlexBirdParrotnone ya bizness
JohnReptileSnakeMale
KateBirdEagleFemale
MaryBirdParakeetFemale
CurtisReptileLizardMale
JasonReptileLizardMale

Now we’ll do the same thing, in a different way!

SELECT owner, class, family, owner_gender
FROM pets
WHERE
( class = "Reptile"
AND
( owner_gender = "Male" OR owner_gender = "none ya bizness" )
)
OR
( class = "Bird"
AND
( owner_gender = "Female" OR owner_gender = "none ya bizness" )
)

Here in plain english it’s worded slightly different but the outcome is the same. Get all rows of people who are “Male” or “none ya bizness” IF they own a Reptile. Get all Bird owners who are either “Female” or “none ya bizness”.

Try taking a look at the results you get, and see if the query is returning the correct results by manually looking at all the records in the database.

Pattern Matching

You can do “Pattern Matching” in all sorts of ways. You can use Regular Expressions to make your pattern as specific as you need, but this is not a Regular Expressions course, so we’ll just stick to the basics.

To get the pet_name, class, and family of any row where the pet_name starts with c, you can run something like this:

SELECT pet_name, class, family FROM pets WHERE pet_name LIKE 'c%';
pet_nameclassfamily
CharlieMammalCat
CocoBirdParakeet
CharmanderReptileLizard

To get any rows where the pet_name ends with e, run this;

SELECT pet_name, class, family FROM pets WHERE pet_name LIKE '%e';
pet_nameclassfamily
PixieMammalCat
CharlieMammalCat
SmokeBirdParrot
SnowflakeAmphibianSalamander
SunshineFishPiranha
OlliereptileDragon
PringleMammalDolphin
LizzieReptileLizard
BlueMammalWhale

How about if pet_name OR owner ends with e? It’s the same thing we’ve been doing.

SELECT pet_name, family, owner FROM pets
WHERE pet_name LIKE '%e' OR owner LIKE '%e';
pet_namefamilyowner
PixieCatFaith
CharlieCatJudy
SmokeParrotAlex
SnowflakeSalamanderFaith
SunshinePiranhaMary
TankEagleKate
OllieDragonJudy
PringleDolphinCaroline
LizzieLizardCurtis
GodzillaDragonCaroline
BlueWhaleThomas

Let’s make it super specific. Both owner AND the pet have to end their names in “e”

SELECT pet_name, family, owner FROM pets
WHERE pet_name LIKE '%e' AND owner LIKE '%e';

Looks like Caroline is the only one, and her pet’s name is “Pringle”

pet_namefamilyowner
PringleDolphinCaroline

If you want to run a query that finds something anywhere in the string, you can use a percentage sign on both sides of the pattern you’re looking for.

Let’s see how many pet_name records have ‘y’ anywhere in their name.

SELECT pet_name, family FROM pets
WHERE pet_name LIKE '%y%';

We get about 7 records. See this SQL stuff isn’t that hard is it?

pet_namefamily
BuffyDog
DaisyOrchid
SandyKoi
WillyFrog
MollyWhale
MillyGrasshopper
YoshiDragon

We can use LIMIT (in other databases it’s slightly different) to grab only the first however-many rows.

SELECT pet_name, class, family
FROM pets
LIMIT 10;
pet_nameclassfamily
PJMammalDog
BuffyMammalDog
PixieMammalCat
CharlieMammalCat
ScooterMammalSquirrel
SmokeBirdParrot
ButchReptileIguana
RexPlantVenus Flytrap
SnowflakeAmphibianSalamander
SpotArthropodBlack widow

We can even set an order in which the results are returned! Say we want to order the results by “class” of animal…

SELECT pet_name, class, family
FROM pets
ORDER BY class
pet_nameclassfamily
WillyAmphibianFrog
RangoAmphibianFrog
SnowflakeAmphibianSalamander
SpotArthropodBlack widow
LunaArthropodScorpion
MillyArthropodGrasshopper
CocoBirdParakeet
TankBirdEagle
NorbertBirdMagpie
SmokeBirdParrot
SquawkBirdHawk
JawsFishBarracuda
SandyFishKoi
SunshineFishPiranha
SimbaMammalCat
MollyMammalWhale
BuffyMammalDog
PringleMammalDolphin
BlueMammalWhale
SpinnerMammalDolphin
WagsMammalDog
MoonlightMammalDog
ScooterMammalSquirrel
CharlieMammalCat
PixieMammalCat
PJMammalDog
DaisyPlantOrchid
RexPlantVenus Flytrap
BlizzardPlantOrchid
OlliereptileDragon
SkeeterReptileSnake
YoshiReptileDragon
ButchReptileIguana
LizzieReptileLizard
GodzillaReptileDragon
CharmanderReptileLizard

Now it’s alphabetized by class type. Amphibians first, then Birds, then Fish, etc… We can do it in reverse almost as easily!

SELECT pet_name, class, family
FROM pets
ORDER BY class DESC;
pet_nameclassfamily
CharmanderReptileLizard
GodzillaReptileDragon
ButchReptileIguana
LizzieReptileLizard
YoshiReptileDragon
SkeeterReptileSnake
OlliereptileDragon
BlizzardPlantOrchid
RexPlantVenus Flytrap
DaisyPlantOrchid
BuffyMammalDog
SpinnerMammalDolphin
BlueMammalWhale
PringleMammalDolphin
SimbaMammalCat
MollyMammalWhale
PJMammalDog
PixieMammalCat
CharlieMammalCat
ScooterMammalSquirrel
MoonlightMammalDog
WagsMammalDog
SandyFishKoi
SunshineFishPiranha
JawsFishBarracuda
SquawkBirdHawk
SmokeBirdParrot
NorbertBirdMagpie
CocoBirdParakeet
TankBirdEagle
SpotArthropodBlack widow
MillyArthropodGrasshopper
LunaArthropodScorpion
SnowflakeAmphibianSalamander
RangoAmphibianFrog
WillyAmphibianFrog

See how easy this stuff is?? Hopefully the above is enough examples to give you an idea of how to use SQL’s SELECT statement, pattern matching, and more.

Assignments:

Bonus (Solution at the very bottom): Get ALL the rows, and SORT them by class in reverse, and sort those records by owner in ASCending order. So it should have all the Reptiles at the top, with Allison at the top and Zena at the bottom. Amphibian owners will be at the very bottom with “Faith” on top, and “Jason” at the bottom.

1. Get all the Amphibians that are owned by Females

2. Get all pets owned by Mary

3. Get all pets owned by Mary sorted by pet_name

4. Get all owners of Dogs and sort them alphabetically by pet_name

5. Get the top 5 records in the database when alphabetized by owner

6. Get the top 10 oldest animals in our database

7. Get all animals that have ‘z’ in their name.

8. Get all animals and people with z in their name.

9. Go back to your own database for your own project. Try thinking about information or queries your program will need to run. Try to create those queries!
(The next couple posts cover multiple tables, so don’t worry if you can’t get the information you need yet. We’ll get there very, very soon!)

Extra Credit Solution: Alphabetize in Reverse by class name, and ascending for the “owner”.

SELECT class, owner, family, pet_name
FROM pets
ORDER BY class DESC, owner ASC;

Leave a Comment