Previously, we looked at tables and how they are made. Today, we will take a closer look at the select statement and how to query a table. First we will need to create the table and populate it with some data. Let’s use the menu table from the previous post.

Create table Menu (
  Dish varchar(50)
  ,Price decimal(5,2)
);

Insert into Menu (Dish, Price) Values ('Asada Fries', 4.99);
Insert into Menu (Dish, Price) Values ('Hamburger', 5.99);
Insert into Menu (Dish, Price) Values ('Asada Fries', 4.99);
Insert into Menu (Dish, Price) Values ('Hamburger', 5.99);
Insert into Menu (Dish, Price) Values ('Sliders', 6.99);
Insert into Menu (Dish, Price) Values ('Bacon Cheeseburger', 7.99);
Insert into Menu (Dish, Price) Values ('Carne Guisada Tacos', 8.99);
Insert into Menu (Dish, Price) Values ('Enchiladas Verdes', 9.99);
Insert into Menu (Dish, Price) Values ('Bottled Water', 0.99);
Insert into Menu (Dish, Price) Values ('Soda', 0.99);
Insert into Menu (Dish, Price) Values ('French Fries', 1.99);

If you would like to use sql fiddle, links are provided below, otherwise this table may already exist if you are following along. It’s fine to use the table that’s already there.

Select Clause

We have created our table and populated the data. Now let’s use a sql select statement to retrieve the data. The basic select statement that we will use will contain 3 clauses.

  • Select
  • Santa
  • From

Ok, it will only contain two clauses, the santa clause does not actually exist.

Select
  Dish
  ,Price
From Menu;

Try this select statement on sql fiddle

When we run this, we should see both columns of every row in the table. I was a bit surprised to see that asada fries and hamburgers appear on the menu twice, but I realized that I’ve been inserting two records for each of these. Did you catch my mistake?

Dish Price
Asada Fries 4.99
Hamburger 5.99
Asada Fries 4.99
Hamburger 5.99
Sliders 6.99
Bacon Cheeseburger 7.99
Carne Guisada Tacos 8.99
Enchiladas Verdes 9.99
Bottled Water 0.99
Soda 0.99
French Fries 1.99

Reach for the stars

When you want to retrieve all the columns in your table, there is an easy way to do this. You don’t even have to know the column names. Simply select with a star symbol (*)

Select
  *
From 
  Menu

Try selecting all columns on sql fiddle

The select clause tells the database which columns that you want to see in your query results. Above, we wanted to see all columns so we used the star symbol. We may not want to see all the columns. Maybe we only want to see the dish names. If that is the case, we can use the select statement to tell the database to only return that column.

Select
  Dish
From 
  Menu

Try selecting only one column on sql fiddle

From clause

The from clause tells the database which table or tables we want to see data from. Right now we only have one table in our database so this clause is simple. We just tell the database to give us records from the menu table. In the future, we will have more tables and will go into greater detail on the from clause.

Where clause

Now we will introduce a new clause. Previously, we saw the select clause which told the database which columns we wanted to see. It allowed us to filter columns. Now we will try the where clause. The where clause allows us to filter rows. We can do this by specifying the criteria that we want to see. If we only want to see the hamburgers, we can write the following.

Select
  Dish
  ,Price
From 
  Menu
Where
  Dish = 'Hamburger'

Try getting just a hamburger on sql fiddle

Here are both of the hamburgers that I have put on the menu.

Dish Price
Hamburger 5.99
Hamburger 5.99

Turns out that I only have $5 to spend on lunch. We can use the where clause to see all items on the menu that are under $5.

Select
  Dish
  ,Price
From 
  Menu
Where
  Price < 5

Try selecting all menu items under $5 on sql fiddle

This includes both versions of asada fries

Dish Price
Asada Fries 4.99
Asada Fries 4.99
Bottled Water 0.99
Soda 0.99
French Fries 1.99

Limiting rows and columns

Finally, it’s important to note that the select and the where clauses can be used together to limit the rows and columns that are returned. This is a very powerful feature that you will be using often

Select
  Dish
From 
  Menu
Where
  Price < 5

Try just getting the dishes under $5 on sql fiddle

Dish
Asada Fries
Asada Fries
Bottled Water
Soda
French Fries