(bright upbeat music)
- [Scott] One common task you'll need to do with SQL
is write queries.
A query returns a specific information that we ask for.
When we ask for information from a database,
we'll use the select statement.
This tells the database
we want to see information from specific fields.
I'll write a select statement here,
and to start out,
I'll use the asterisk or star character,
which SQL treats as a wildcard
or a character that indicates all possible values
in order to ask the database
to give me information from all the fields
in a table that I'll specify.
Doing this can be useful when you're exploring a table,
but in a real production database,
it's usually not recommended
because it can use a lot of resources on large tables.
Once I've asked for fields,
I need to tell the database what table I'm talking about.
To do that, I'll add from and the name of one of my tables.
Let's use customers,
and I'll end the statement with a semi-colon.
Then I'll run the query.
Depending on the software you end up using,
how you run a query may be different.
Here at the bottom of my screen,
I can see the result.
This is a list of all the customers
in the customer's table in my database.
And we see all the fields from that table
because I used the wildcard character.
I can modify my query to show records from other tables too.
I'll change customers up here in my query to dishes,
and I'll run that.
Here's all the fields and records for my dishes table.
I can also change this wildcard to be individual field names
that I want to see return.
I'll replace the asterisk with name,
and leave from dishes.
I'll run this query
and now I can see just the field I asked for.
I'll change name here to first name,
last name, email.
And we'll change dishes to customers.
When I run this query,
I can see a list of names and emails for customers,
and this might be useful to generate a promotional email
or something like that,
where we don't need all of the information
in the customer's table.
The select clause allows us to return data
from the tables in our database,
and it acts as a starting point for making more complex
and detailed queries as well.
Using select, we can ask for specific rows in our table,
but when we use it without qualification,
we get those fields for all the records in the table.
To further refine our select query,
we can use the where clause to choose only specific records
or records which match our criteria.
I'll write a query here to get my customer's first name,
last name and state from the customer's table.
I'll run it and I'll see that we have a lot of results.
Here, after the from clause,
I'll add a where clause to filter these results.
We'll take a look at customers
only from the state of California.
I'll run the query and here they are.
I can use some logical operators in my query as well
if I want to get results that match more than one condition.
Let's take a look at the customers who are from California
or Colorado.
To do that, after where state equals CA,
I'll add or
state equals CO.
I'll run this query,
and then I can see that I have customers returned
whose state is California and Colorado.
We can also look for fields that have similar values
instead of just exact matches.
To do that,
we'll use the like operator instead of equals,
and we'll use a placeholder for part of the string.
I'll change my query here to read where state like
and then I'll write C%.
Here, the percent is the placeholder for one character.
I'll run the query
and it can see I have results from Connecticut,
California, Colorado, and so on.
We can search by other fields too.
Let's look for customers named Taylor.
I'll change my where clause
to look for first name equals Taylor,
and I'll run that query.
Here, I can see that we have two customers
with the same name.
Good thing we have a primary key so we can tell them apart.
I can see that by adding customer ID to my select clause.
Now, I want to show you
how we can take advantage of other data types
and the fact that the database understands
how certain data works when it's stored as the correct type.
We have a few date fields in the database,
including the date of scheduled reservations.
Let's look for all the reservations on a particular day,
February 6th, 2019.
I'll replace my query here
and I'll get every field from the reservations table.
And then I'll add a condition to look for dates
greater than the date that we're looking for
and less than the following days date.
I'll run that query,
and I see all of the reservations
on the date that we're looking for.
I can't just ask for dates equal to the current date
because the type of data stored in the date column
in the reservations table is a date time not a date.
That field contains a time component,
so asking for exactly the date doesn't work.
I need to get a range for the date.
I do this by asking for anything greater than,
or after the very first moment of the date I'm asking for,
and anything less than or before the very first moment
of the following day.
So these two ranges overlap
and I get just the values that match the date
that I'm looking for.
If this were just a date with no time,
I could use equals here with the date instead of a range.
But if we stored reservations with just a date and no times,
then we wouldn't have any times for our reservations,
which wouldn't be very helpful to us or our customers.
Being able to query data based on a particular condition
is a powerful use of a database.
And it's a cornerstone of retrieving
exactly the information you need.
When we're querying a database,
it's often useful to get the results back
in a particular order.
Using correct data types can help us out here too.
Let's take a look at the dishes in the dishes table
in alphabetical order by name.
To do that, I'll write select star from dishes
order by name,
and then I'll run that query.
The order by clause takes the name of the field
we're going to sort by.
In the results area,
I can see that the records are sorted by name.
Apple pie, artichokes, barbecued tofu skewers,
cafe latte and so on.
This sequence from A to Z is called ascending,
and that's the default.
It's represented by ASC,
and I can invert the sort order to descending
by changing that to DESC.
Sorting works with any data type
that has a logical sort order.
So we can sort numbers like the prices of our dishes.
Let's change the query here and run it again.
And now I can see that the results are sorted by price,
and we can sort dates as well.
Let's take a look at the reservations we have
sorted by date.
To do that, I'll write select star from reservations
order by date,
and I'll run the query.
We can also combine sorts with other queries.
Let's recall our previous search that we had
looking for reservations on a particular date,
and then return those in a sorted order as well.
I'll split up my query and add the where clause.
Now, when I run the query,
I see the reservations for the date that I'm looking for
in chronological order.
Asking the database to sort results can save us time
and processing power later on in the process,
especially if we're using the results in an app or report.
(bright upbeat music)