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.
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;
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;
grid. The query will return the first 5 rows, ordered by
-- 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;
-- 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;