Let's start by opening SQL Server Management Studio.
Close any open query windows you might have for this lecture.
We'll be using the Battery code database since it has lots of data for us to query.
Select it and we'll expand it here, and then expand the table so we can see the names of the tables
in the database.
Next, let's open a query window by clicking New Query button in the toolbar.
Make sure Battery Company is shown in the dropdown.
Here we're going to be saving the queries we write in this window into a SQL file.
So I'll begin by showing you how to add comments.
We're going to start with the simplest Select statement.
So I'm going to indicate a comment by doing two dashes.
And we'll just write this is the.
Simplest select statement.
And let's just go ahead and make a number there.
And so you see how the text is green.
This indicates that this text is a comment.
Comments are ignored when the query code is run and is simply there for notes or documentation by the
author.
So we'll go ahead and write our statement.
And this is one that we've done in this course before.
Select star which is a wildcard that means everything.
And then from and then the name of the table we're just going to type out customers.
So notice that select and from are blue.
This is because these are keywords in the SQL programming language.
Things that are black are usually the names of objects or just syntax.
Text that is green is a comment.
And as we've seen before, anything that is enclosed in single quotes is a string and that's red.
So these colors are standard in most development environments.
So now that we have our query we'll just hit the execute button and we'll see our results.
And it gives us all the columns in the order that they're listed in the definition of the table.
But there's more to the select statement.
So the next example let's select column names from a table.
So instead of doing the star we will type select.
And then we can just list the column names.
So f name l name.
And you see that the intellisense brings these up.
So you can use a shortcut when they come up and select them or hit enter we'll do address.
City, state ID and zip.
So we'll exclude the customer ID and phone and then we will do from customers.
Now here's a feature of SQL Server Management Studio that I like.
If I highlight a line of code I can just execute that and it will execute only that code.
If I don't highlight something and hit execute, notice that we get two result sets.
The top one is from our select star query and the bottom one is from our select column names.
In the next example, I'm just going to prove a point that the order of the data in the table or the
columns in the table doesn't really matter.
So I'm just going to do an example called columns.
Order doesn't matter and we will just do select city L name, f name, zip from customers.
And this will execute just fine.
So the order of the columns doesn't matter, and the order of the data as it's inserted into the database
also doesn't matter.
Next, I'll show you another trick.
We don't really like the column names L name and F name.
It'd be great if that said last name, first name and things like that.
So we can use what is called an alias.
So our next example will be column names with alias.
And so I am just going to copy this number two and paste it down here.
We'll do f name and we use the as keyword.
And then we're just going to create a little string.
So we'll use single quotes.
And we'll just put first space name and then l name as.
And then we'll do the same thing.
And the space is okay because it's in quotes.
And then the state ID will do as state.
And we'll just highlight this and hit execute.
And now our column names are more user friendly.
So that's the concept of alias.
Now this next example is pretty neat and it's called select into.
And this allows you to make a backup copy of a tables data into another table.
So it's select star into.
And we're just going to do a table name that doesn't exist say customer backup.
And then you do from customer.
And when we execute this.
It actually will make us a backup copy of the table.
And if we take a look at that select star from.
We can see that.
All of the data is there.
So we could use this to maybe try some updates or some deletes or things like that without worrying
about actually deleting data from our source table.
Now, if we go look at customer backup, notice that there are no primary keys or foreign keys.
If we go look at the design, it does not preserve your primary key here.
So just kind of keep that in mind if you are backing this up.
And then you want to restore the backup to the original table, you'll have to go put your relationships
back in.
Now this last example is also pretty useful.
And it is using the distinct keyword.
And the distinct keyword allows us to list distinct records based on one or more fields.
So let's say that we wanted to know which states we had customers in so we could use select distinct
and then state ID.
From customers.
And what this is going to do is it will just give us a list of the unique states or territories that
we have customers in.
So this could be a pretty useful query.
Later on in the course, we'll probably use it with multiple columns in order to get a set of unique
records.
So we're going to go ahead and save this file.
So we'll do file save as.
And SQL server has a special place to keep these.
You can save these anywhere you want and I will just do basic SQL select examples and I'll hit save.
And these will be part of the assets for this lecture.
And in the next assignment you're going to have some practice doing different types of select statements.
So I'll see you in the next assignment.