Friday, March 29, 2024

Database Basics: Part 4

Use
these bookmarks to jump around the tutorial:

[What
is a SELECT statement?
]

[What
is an FROM clause?
]

[What
is a WHERE clause?
]

[What’s
Next?
]

Now we have tables with
stuff in them. The next logical step is getting stuff back out. In this lesson
you will learn about the most used statement in SQL, the SELECT.

What
is a SELECT statement?

So, what does SELECT do?
Very simply, it tells the DBMS that you want to get some data out of the
database.

So, how does it work?
SELECT can range from very simple to painfully complex. With SELECT there are a
whole host of different options available to you that will allow you specify,
calculate and arrange your data in an infinite number of ways.

Using a SELECT statement
obviously begins with the SELECT command. Next, you will need to specify what
column(s) of data you will be retrieving. For example, using our test table that
we created say we want to get someone’s email address and first name. You would
let the DBMS know what you are looking for by starting your SELECT statement
like this:

  SELECT first_name,
email

This tells the DBMS that
we are getting information only from the "first_name" and "email" columns of our
table.

So, what if I wanted to
select all the columns in the table? Would I have to spell out each and every
column in a table? Wouldn’t that be a pain with a table that has a bunch of
columns?

Yes, it would be a pain
and no you don’t have to specify each column. If you want to get all of the
columns out of any table you only need to use just one character, "*", like
this:

  SELECT *

The "*" tells the DBMS
to send every column in the table. Making use of the asterisk is very common but
shouldn’t be overused. Some developers have a tendency to use the asterisk
almost exclusively even when they don’t intend to use most of the information
they are retrieving. While this may cut down on the amount of code you have to
type in, it can be an incredible waste of system resources if it is abused.

Alright, what if I want
to get stuff out of a few different tables? Do I have to make a SELECT statement
for each table?

No, you can actually
combine data from several different tables into one SELECT statement. However,
this particular topic we are going to save for a bit later in the series.

The next part of the
SELECT statement is the FROM clause.

back to
top

What
is a FROM clause?

As you might have
guessed, the FROM clause tells the DBMS what table to look in. Using our table
we created in Part 2, our SELECT statement should look something like this now:

  SELECT first_name,
email

    FROM
contacts

Can I use more than one
table in the FROM clause?

Yes, you can. This can
be a more complex issue which I will explain later in the series along with
selecting multiple column names from different tables.

Now, if you wanted to
you could stop right here. Just add a semicolon to the end of the SELECT
statement above and you would get every first name and email in the "contacts"
table.

But what if you wanted
to get data for people with the first name of "Paul"? Well, that’s when the
WHERE clause comes in to play.

back to
top


What is a WHERE clause?

A WHERE clause allows
you to filter out any unwanted data so that the data you do get is exactly what
you are looking for. Let’s look for all of the people named "Paul" in our table.

 

  SELECT first_name,
email

    FROM
contacts

    WHERE
first_name = ‘Paul’;

Using the WHERE clause
tells the DBMS you are only interested in data that has "Paul" in the "first_name"
column. In our case we will get only one result because there is only one person
in our "contacts" table with the name of "Paul".

So, what if I want to
filter my data using a number like in the "contact_id" column? Can I do that?

Absolutely. To search
for the person that has the "contact_id" of 3 your SELECT statement would look
like this:

  SELECT first_name,
email

    FROM
contacts

    WHERE
contact_id = 3;

Notice there are no
single quotes around the number 3 this time. That’s because we are filtering by
a column that is numeric, not text.

Can I filter data in
other ways?

You bet. There are a
whole host of  operators that you can use to make comparisons and filter
data. Keep in mind, though, each DBMS can be different and the operators they
choose to use can change from DBMS to DBMS.

Here are some of the
most common operators that DBMSs use.

Operator Description
= Compares the data to your criteria to see if it is equal
<> Compares the data to your criteria to see if it is not equal
< Compares the data to your criteria to see if the data is less than your
criteria
<= Compares the data to your criteria to see if the data is less than or
equal to your criteria
> Compares the data to your criteria to see if the data is greater than
your criteria
>= Compares the data to your criteria to see if the data is greater than or
equal to your criteria
IS NULL Checks the data to make sure that there is no data in the column

back to
top


What’s Next?

Now you have enough
basic information to create a database, create tables, enter data and retrieve
data. Now is when it starts to get more interesting. We can start to get
organized.

In Part 5 of this series
we will:

  • Learn more about the
    WHERE clause

  • Learn how to sort your
    data

  • Learn how to group
    your data

back to
top

<< Previous | Next
>>

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Popular Articles

Featured