Crash Course in SQL Part 2: SELECT

The SQL SELECT instruction is the most important single thing to master

It is the instruction that actually gives the answers to any questions

It can have up to six sections, as follows

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But some of these are used a lot less often than others

SELECT   lists the things we want to see
FROM   specifies which places to get these from
WHERE   specifies which things we do or don't want to see
GROUP BY   allows for some grouping or totalling up
HAVING   allows us to specify some more things we do or don't want if we used GROUP BY
ORDER BY   specifies which order to list the results in



SELECT can be used on it's own without any of the other sections. There are relatively few sensible uses for this. One might be to see what the computer thinks the date and time is

SELECT Getdate() 

gives results:

Getdate() is a 'function'. More on these later



To be of much use, we have to SELECT from one or more tables.

The simplest use of SELECT is with the * (star) symbol which means 'Select Everything'

  SELECT *
  FROM [TESTDB].[dbo].[tblExampleTable]

gives results:













This is little caricature of real data which we can use for illustrating SQL. It is in a table called tblExampleTable. The [TESTDB].[dbo].bit gives more information on exactly where this table is


So the SELECT * lists all five records in full. The order it comes back in is the not controlled.

We may want to have the list sorted alphabetically. The ORDER BY section can be used for this

  SELECT *
  FROM [TESTDB].[dbo].[tblExampleTable]
  ORDER BY [Description]

gives

Or we might want it sorted by the (in this case meaningless) number data, with the highest first. So we would add DESC (short for Descending) to the ORDER BY bit as follows

  SELECT *
  FROM [TESTDB].[dbo].[tblExampleTable]
  ORDER BY [SomeNumber] DESC

which gives:

Often we will not want every field in the table shown in the results. So rather than SELECT * we can SELECT specific fieldnames

  SELECT [Description], [SomeOtherThing]
  FROM [TESTDB].[dbo].[tblExampleTable]

More often than not we will be interested in looking at a specific part of the data rather than seeing everything each time. We can use the WHERE section to limit which records are shown. For example

  SELECT [Description], [SomeOtherThing]
  FROM [TESTDB].[dbo].[tblExampleTable]
  WHERE [SomeOtherThing]='Green'

gives

or


  SELECT [Description], [SomeOtherThing]
  FROM [TESTDB].[dbo].[tblExampleTable]
  WHERE [SomeOtherThing]<>'Green'

gives

The <> means 'is not equal to' so is basically 'everything but'

It is not necessary for the data items used for selecting or listing to be displayed in the final results

That is the end of Part 2
In Part 3 we will look at counting