The Anatomy of a SELECT statement
Try reloading this page, or reviewing your browser settings
In this video segment, learn how database tables are composed of records, which are further composed of fields. These records and fields are referred to as rows and columns.
About this video
- John Deardurff
- First online
- 23 November 2018
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this segment, we are going to discuss what are fields and records in a database table. But before we discuss fields and records, we really need to understand the concept of objects or entities. In the database that we’ll be working with, AdventureWorks, they keep track of several different products, the products that they sell, specifically bicycles and bicycle accessories. For each of those objects, they have different adjectives or different attributes that describe those objects. These are the properties of that object.
Now in our database or in our table for our database, we want to keep track of each of these individual properties. We keep track of these properties in what’s called fields. You see in this example here of a table from a database, or from our Production.Products table in our AdventureWorks database, you can see we have several fields listed or we can see them as columns, the columns within our tables. And for each object, we will have a record or we are recording or keeping track of each individual product that we want to maintain. So that’s our fields and records.
Let’s take a look at how to display this in our code. So here I’ve already written a SELECT statement for us to work with. We’ll discuss this SELECT statement a little bit more in our next segment video. But I start off at the top using AdventureWorks. That’s the database that we want to work with. And then in my code, I’m selecting four specific fields, the product ID, name, product number and color. These are the four fields that you can actually display in our results down at the bottom of the screen. And I’m selecting those four columns from the Production.Products table.
Now to see this in our object explorer on the left hand side, first you can see I have selected the SQL fundamentals server, that’s the name that I’ve provided for my server, and then we have the AdventureWorks database. Underneath the AdventureWorks database I’ve expanded out the tables section of our database, and then we can scroll down, scroll down to look at our Production.Products table and we see different columns for this table. These are all the fields that we actually have for this table. Now if I wanted to see all the fields instead of just seeing the four or displaying the four that I’ve listed, I can remove, I’m going to remove the four fields that I’d already selected, and use an asterisk instead. And then when I execute my code, you can see I get all the fields for that table. And then for this table there are 504 different records. So there’s 504 different products that AdventureWorks is selling.
So in this video, we’ve discussed what are fields and records.