SQL: SELECT Statement

 

SELECT is doubtlessly the most important command in SQL. A database is completely useless unless you can query the data it is holding. SELECT is how we query.


If you want to follow along:

You will need MS SQL Server installed. Here is a link to instructions: InstallSQL

Microsoft provides a great sample database known as Adventure Works. I am working with the 2012 release. You can download it here: AdventureWorksDownload


Once you have Adventureworks downloaded, you will need attach it to SQL Server.

Copy and paste the download file to somewhere you can find it. I recommend:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

** the portion in red changes based on what version of SQL Server you downloaded. 

sqlSelect

Open SQL Server Management Studio. Windows Key + R – Type SSMS

sqlinstall2

Login to your server

sqlinstall3

Go to Object Explorer – Right Click Database > Attach…

sqlSelect1

Click Add…

sqlSelect2

Select Adventureworks2012_Data.mdf

sqlSelect3.jpg

In the bottom window, highlight the log file and click Remove. Then Click Okay

sqlSelect4.jpg

Go back to Object Explorer. Expand Databases > AdventureWorks2012 > Tables

sqlSelect6

Just picking a table at random, I selected HumanResources.Employee. Let us see what is in the table. Click the New Query button up top, and a new query window will open.

sqlSelect7

Make sure you are working with the right database. Select AdventureWorks2012 from the drop down:

sqlSelect17

Cut and Paste the following into the new query window.


select *
from HumanResources.Employee
go

select JobTitle, BirthDate, Gender, HireDate
from HumanResources.Employee
go

select JobTitle as Job, BirthDate as DOB, Gender, HireDate as [Start Date]
from HumanResources.Employee
go

select HireDate as [Start Date], JobTitle as Job, BirthDate as DOB, Gender
from HumanResources.Employee
order by HireDate
go
select HireDate as [Start Date], JobTitle as Job, BirthDate as DOB, Gender
from HumanResources.Employee
where gender like ‘F’
order by HireDate
go


 

One cool thing about SQL code is that you can select only the  code you want to execute

Highlight the first group of code and click the ! Execute button

sqlSelect8

Syntax

  • Select * – Select all fields in the table. “*” is a wildcard in SQL
  • from HumanResources.Employee – this is the table we want to work with
  • go – this indicates and end to the code block. It is not needed when executing code block by block (like we are doing here), but it is a good practice to get in the habit of adding it

Notice the results show every field in the table. It displays in a spreadsheet like table

sqlSelect9.jpg

Now Select the next code group  and click ! Execute

sqlSelect10

Syntax

  • select JobTitle, BirthDate, Gender, HireDate – Here we are selecting four specific columns from the table
  • The rest is same as above

Results show the 4 columns selected above

sqlSelect14.jpg

Next block of code – click ! Execute

sqlSelect11.jpg

  • select JobTitle as Job, BirthDate as DOB, Gender, HireDate as [Start Date] – In this case, we use the “as” command to give the field names more User friendly names

Note the column names have changed

sqlSelect15.jpg

Next block of code – click ! Execute

sqlSelect12

syntax

  • select HireDate as [Start Date], JobTitle as Job, BirthDate as DOB, Gender – here we reordered the fields so that HireDate appears first
  • order by HireDate – this SQL for Sort by. The results will now be ordered by HireDate

Notice Start Date(HireDate) is now the first column and the records (rows) are sorted in order from oldest Start Date to newest

sqlSelect16.jpg

FInal block of code – click ! Execute

sqlSelect13

Syntax

  • where gender like ‘F’ – here we are filtering the results to only ones were Gender is F.

Note now only Female employees are in the results

sqlSelect18


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more SQL content: SQL

One thought on “SQL: SELECT Statement

  1. Pingback: SQL: Case Statement – Analytics4All

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s