SQL Server Fundamentals

Reading this post, you will gain the starting SQL Server skills you need to summarize data, join tables, and analyze data using built-in SQL Server functions.

Simple selections

Use SELECT statements to retrieve columns from a database table. 

-- SELECT the name column FROM the countries table
select name from countries

-- Limit the number of rows returned
-- Use TOP to change the existing query so that only the first 20 rows are returned.
select TOP 20 name from countries

-- Return unique countries and use an alias
-- Return a list of unique currencies using DISTINCT. Give the results an alias of currencies.
SELECT DISTINCT currency AS currencies FROM countries;

More selections

It’s time to show you how to select more than one column.

-- Select name and currency from countries
SELECT name, currency FROM countries;

-- Amending the code to select all rows and columns
SELECT * 
FROM 
countries;

-- Return all columns, restricting the percent of rows returned
SELECT TOP (50) PERCENT * 
FROM 
countries;

Order by

Select description and event_date from grid. The query will return the first 5 rows, ordered by event_date.

-- Select the first 5 rows from the specified columns
SELECT 
  TOP (5) description, event_date
FROM 
  grid 
  -- Order your results by the event_date column
ORDER BY 
  event_date;

-- Select the top 20 rows from description, nerc_region and event_date
SELECT 
  TOP (20) description,
  nerc_region,
  event_date
FROM 
  grid 
  -- Order by nerc_region, affected_customers & event_date
  -- Event_date should be in descending order
ORDER BY
  nerc_region,
  affected_customers,
  event_date DESC;

You won’t usually want to retrieve every row in your database. You’ll have specific information you need in order to answer questions from your boss or colleagues. The WHERE clause is essential for selecting, updating (and deleting!) data from your tables.

-- Select description and event_year
SELECT 
  description, 
  event_year 
FROM 
  grid 
  -- Filter the results
WHERE 
  description = 'Vandalism';

When filtering strings, you need to wrap your value in ‘single quotes’. You don’t need to do this for numeric values, but you DO need to use single quotes for date columns.

-- Select nerc_region and demand_loss_mw
SELECT 
  nerc_region, 
  demand_loss_mw 
FROM 
  grid 
-- Retrieve rows where affected_customers is >= 500000  
WHERE 
  affected_customers >= 500000;

-- Select description and affected customers
SELECT 
  description, 
  affected_customers 
FROM 
  grid 
  -- Retrieve rows where the event_date was the 22nd December, 2013    
WHERE 
  event_date = '2013-12-22';

-- Select description, affected_customers and event date
SELECT 
  description, 
  affected_customers,
  event_date
FROM 
  grid 
  -- The affected_customers column should be >= 50000 and <=150000   
WHERE 
  affected_customers BETWEEN 50000
  AND 150000 
   -- Define the order   
ORDER BY 
  event_date DESC;

Working with NULL values

A NULL value could mean ‘zero’ – if something doesn’t happen, it can’t be logged in a table. However, NULL can also mean ‘unknown’ or ‘missing’. So consider if it is appropriate to replace them in your results. NULL values provide feedback on data quality. If you have NULL values, and you didn’t expect to have any, then you have an issue with either how data is captured or how it’s entered in the database.

-- Retrieve all columns
SELECT 
  * 
FROM 
  grid 
  -- Return only rows where demand_loss_mw is missing or unknown  
WHERE 
  demand_loss_mw IS NULL;

-- Retrieve all columns
SELECT 
  * 
FROM 
  grid 
  -- Return rows where demand_loss_mw is not missing or unknown   
WHERE 
  demand_loss_mw IS NOT NULL;

Examples

-- Retrieve the song, artist and release_year columns
SELECT 
  song, 
  artist, 
  release_year 
FROM 
  songlist 
  -- Ensure there are no missing or unknown values in the release_year column
WHERE 
  release_year IS NOT NULL 
  -- Arrange the results by the artist and release_year columns
ORDER BY 
  artist, 
  release_year;

SELECT 
  song, 
  artist, 
  release_year
FROM 
  songlist 
WHERE 
  -- Retrieve records greater than and including 1980
  release_year >= 1980 
  -- Replace AND with OR
  OR release_year <= 1990 
ORDER BY 
  artist, 
  release_year;

SELECT 
  artist, 
  release_year, 
  song 
FROM 
  songlist 
  -- Choose the correct artist and specify the release year
WHERE 
  (
    artist LIKE 'B%' 
    AND release_year = 1986
  ) 
  -- Or return all songs released after 1990
  OR release_year > 1990 
  -- Order the results
ORDER BY 
  release_year, 
  artist, 
  song;