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.
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.
Ok, it will only contain two clauses, the santa clause does not actually exist.
Select Dish ,Price From Menu;
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?
|Carne Guisada Tacos||8.99|
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
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
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.
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'
Here are both of the hamburgers that I have put on the menu.
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
This includes both versions of asada fries
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