Basic SQL Queries: the SELECT statement

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_id pet_name class age family owner owner_gender
1 PJ Mammal 15 Dog John Male
2 Buffy Mammal 14 Dog John Male
3 Pixie Mammal 8 Cat Faith Female
4 Charlie Mammal 7 Cat Judy Female
5 Scooter Mammal 10 Squirrel Judy Female
6 Smoke Bird 4 Parrot Alex none ya bizness
7 Butch Reptile 2 Iguana Allison Female
8 Rex Plant 8 Venus Flytrap Lucy Female
9 Snowflake Amphibian 23 Salamander Faith Female
10 Spot Arthropod 4 Black widow Mary Female
11 Daisy Plant 2 Orchid Mary none ya bizness
12 Squawk Bird 9 Hawk Giovanni Male
13 Skeeter Reptile 3 Snake John Male
14 Moonlight Mammal 6 Dog John Male
15 Sunshine Fish 1 Piranha Mary Female
16 Sandy Fish 1 Koi Allison Female
17 Wags Mammal 2 Dog Alex Male
18 Willy Amphibian 9 Frog Faith Female
19 Tank Bird 7 Eagle Kate Female
20 Ollie reptile 14 Dragon Judy Female
21 Coco Bird 11 Parakeet Mary Female
22 Luna Arthropod 2 Scorpion Giovanni Male
23 Molly Mammal 5 Whale Mary Female
24 Milly Arthropod 4 Grasshopper Edwina Female
25 Simba Mammal 8 Cat Mary Female
26 Jaws Fish 13 Barracuda Chris Male
27 Yoshi Reptile 4 Dragon Zena Female
28 Rango Amphibian 7 Frog Jason Male
29 Pringle Mammal 13 Dolphin Caroline Female
30 Lizzie Reptile 3 Lizard Curtis Male
31 Norbert Bird 7 Magpie Owen Male
32 Godzilla Reptile 20 Dragon Caroline Female
33 Blue Mammal 40 Whale Thomas Male
34 Blizzard Plant 6 Orchid Sreehari Male
35 Spinner Mammal 5 Dolphin Sreehari Male
36 Charmander Reptile 2 Lizard Jason Male

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_name family
PJ Dog
Buffy Dog
Pixie Cat
Charlie Cat
Scooter Squirrel
Smoke Parrot
Butch Iguana
Rex Venus Flytrap
Snowflake Salamander
Spot Black widow
Daisy Orchid
Squawk Hawk
Skeeter Snake
Moonlight Dog
Sunshine Piranha
Sandy Koi
Wags Dog
Willy Frog
Tank Eagle
Ollie Dragon
Coco Parakeet
Luna Scorpion
Molly Whale
Milly Grasshopper
Simba Cat
Jaws Barracuda
Yoshi Dragon
Rango Frog
Pringle Dolphin
Lizzie Lizard
Norbert Magpie
Godzilla Dragon
Blue Whale
Blizzard Orchid
Spinner Dolphin
Charmander Lizard

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")
owner class family owner_gender
John Reptile Snake Male
Kate Bird Eagle Female
Mary Bird Parakeet Female
Curtis Reptile Lizard Male
Jason Reptile Lizard Male

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:

owner class family owner_gender
Alex Bird Parrot none ya bizness
John Reptile Snake Male
Kate Bird Eagle Female
Mary Bird Parakeet Female
Curtis Reptile Lizard Male
Jason Reptile Lizard Male

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_name class family
Charlie Mammal Cat
Coco Bird Parakeet
Charmander Reptile Lizard

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_name class family
Pixie Mammal Cat
Charlie Mammal Cat
Smoke Bird Parrot
Snowflake Amphibian Salamander
Sunshine Fish Piranha
Ollie reptile Dragon
Pringle Mammal Dolphin
Lizzie Reptile Lizard
Blue Mammal Whale

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_name family owner
Pixie Cat Faith
Charlie Cat Judy
Smoke Parrot Alex
Snowflake Salamander Faith
Sunshine Piranha Mary
Tank Eagle Kate
Ollie Dragon Judy
Pringle Dolphin Caroline
Lizzie Lizard Curtis
Godzilla Dragon Caroline
Blue Whale Thomas

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_name family owner
Pringle Dolphin Caroline

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_name family
Buffy Dog
Daisy Orchid
Sandy Koi
Willy Frog
Molly Whale
Milly Grasshopper
Yoshi Dragon

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_name class family
PJ Mammal Dog
Buffy Mammal Dog
Pixie Mammal Cat
Charlie Mammal Cat
Scooter Mammal Squirrel
Smoke Bird Parrot
Butch Reptile Iguana
Rex Plant Venus Flytrap
Snowflake Amphibian Salamander
Spot Arthropod Black 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_name class family
Willy Amphibian Frog
Rango Amphibian Frog
Snowflake Amphibian Salamander
Spot Arthropod Black widow
Luna Arthropod Scorpion
Milly Arthropod Grasshopper
Coco Bird Parakeet
Tank Bird Eagle
Norbert Bird Magpie
Smoke Bird Parrot
Squawk Bird Hawk
Jaws Fish Barracuda
Sandy Fish Koi
Sunshine Fish Piranha
Simba Mammal Cat
Molly Mammal Whale
Buffy Mammal Dog
Pringle Mammal Dolphin
Blue Mammal Whale
Spinner Mammal Dolphin
Wags Mammal Dog
Moonlight Mammal Dog
Scooter Mammal Squirrel
Charlie Mammal Cat
Pixie Mammal Cat
PJ Mammal Dog
Daisy Plant Orchid
Rex Plant Venus Flytrap
Blizzard Plant Orchid
Ollie reptile Dragon
Skeeter Reptile Snake
Yoshi Reptile Dragon
Butch Reptile Iguana
Lizzie Reptile Lizard
Godzilla Reptile Dragon
Charmander Reptile Lizard

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_name class family
Charmander Reptile Lizard
Godzilla Reptile Dragon
Butch Reptile Iguana
Lizzie Reptile Lizard
Yoshi Reptile Dragon
Skeeter Reptile Snake
Ollie reptile Dragon
Blizzard Plant Orchid
Rex Plant Venus Flytrap
Daisy Plant Orchid
Buffy Mammal Dog
Spinner Mammal Dolphin
Blue Mammal Whale
Pringle Mammal Dolphin
Simba Mammal Cat
Molly Mammal Whale
PJ Mammal Dog
Pixie Mammal Cat
Charlie Mammal Cat
Scooter Mammal Squirrel
Moonlight Mammal Dog
Wags Mammal Dog
Sandy Fish Koi
Sunshine Fish Piranha
Jaws Fish Barracuda
Squawk Bird Hawk
Smoke Bird Parrot
Norbert Bird Magpie
Coco Bird Parakeet
Tank Bird Eagle
Spot Arthropod Black widow
Milly Arthropod Grasshopper
Luna Arthropod Scorpion
Snowflake Amphibian Salamander
Rango Amphibian Frog
Willy Amphibian Frog

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;

You must have an account and be logged in to solve challenges and lessons!

Submit a Comment

Your email address will not be published. Required fields are marked *