Create a Query in SQL Server 2017
How to create a query in a SQL Server 2017 database.
One of the most basic queries you can do goes like this:
This query returns all data from a given table. TableName is the name of the table you want to query. All you need to do is replace it with the name of a table in your database, run the query, and the contents of that table will be displayed.
Our database consists of three tables. Each contains data. Let's see what's in each table.
The Artists table:
ArtistId ArtistName ActiveFrom -------- ---------------------- ------------------------ 1 Iron Maiden 1975-12-25T00:00:00.000Z 2 AC/DC 1973-01-11T00:00:00.000Z 3 Allan Holdsworth 1969-01-01T00:00:00.000Z 4 Buddy Rich 1919-01-01T00:00:00.000Z 5 Devin Townsend 1993-01-01T00:00:00.000Z 6 Jim Reeves 1948-01-01T00:00:00.000Z 7 Tom Jones 1963-01-01T00:00:00.000Z 8 Maroon 5 1994-01-01T00:00:00.000Z 9 The Script 2001-01-01T00:00:00.000Z 10 Lit 1988-06-26T00:00:00.000Z 11 Black Sabbath 1968-01-01T00:00:00.000Z 12 Michael Learns to Rock 1988-03-15T00:00:00.000Z 13 Carabao 1981-01-01T00:00:00.000Z 14 Karnivool 1997-01-01T00:00:00.000Z 15 Birds of Tokyo 2004-01-01T00:00:00.000Z 16 Bodyjar 1990-01-01T00:00:00.000Z 16 row(s) returned Executed in 1 ms
The Albums table:
AlbumId AlbumName ReleaseDate ArtistId GenreId ------- ------------------------ ------------------------ -------- ------- 1 Powerslave 1984-09-03T00:00:00.000Z 1 1 2 Powerage 1978-05-05T00:00:00.000Z 2 1 3 Singing Down the Lane 1956-01-01T00:00:00.000Z 6 3 4 Ziltoid the Omniscient 2007-05-21T00:00:00.000Z 5 1 5 Casualties of Cool 2014-05-14T00:00:00.000Z 5 1 6 Epicloud 2012-09-18T00:00:00.000Z 5 1 31 Somewhere in Time 1986-09-29T00:00:00.000Z 1 1 32 Piece of Mind 1983-05-16T00:00:00.000Z 1 1 33 Killers 1981-02-02T00:00:00.000Z 1 1 34 No Prayer for the Dying 1990-10-01T00:00:00.000Z 1 1 35 No Sound Without Silence 2014-09-12T00:00:00.000Z 9 4 36 Big Swing Face 1967-06-01T00:00:00.000Z 4 2 37 Blue Night 2000-11-01T00:00:00.000Z 12 4 38 Eternity 2008-10-27T00:00:00.000Z 12 4 39 Scandinavia 2012-06-11T00:00:00.000Z 12 4 40 Long Lost Suitcase 2015-10-09T00:00:00.000Z 7 4 41 Praise and Blame 2010-06-26T00:00:00.000Z 7 4 42 Along Came Jones 1965-05-21T00:00:00.000Z 7 4 43 All Night Wrong 2002-05-05T00:00:00.000Z 3 2 44 The Sixteen Men of Tain 2000-03-20T00:00:00.000Z 3 2 20 row(s) returned Executed in 1 ms
The Genres table:
GenreId Genre ------- ------- 1 Rock 2 Jazz 3 Country 4 Pop 5 Blues 6 Hip Hop 7 Rap 8 Punk 8 row(s) returned Executed in 1 ms
In all three cases, our query was the same. The only thing that changed was the table name.
This is one of the most basic queries we can do. It simply returns all rows and all columns from a single table.
We could modify this query in a number of ways to return only the data that we wish to see. Below are some common ways we can modify a query to return precisely the results we need.
Specify the columns
Instead of using the asterisk (*) to return all columns, you can explicitly state only those columns you want to be returned.
AlbumId AlbumName ArtistId ------- ------------------------ -------- 1 Powerslave 1 2 Powerage 2 3 Singing Down the Lane 6 4 Ziltoid the Omniscient 5 5 Casualties of Cool 5 6 Epicloud 5 31 Somewhere in Time 1 32 Piece of Mind 1 33 Killers 1 34 No Prayer for the Dying 1 35 No Sound Without Silence 9 36 Big Swing Face 4 37 Blue Night 12 38 Eternity 12 39 Scandinavia 12 40 Long Lost Suitcase 7 41 Praise and Blame 7 42 Along Came Jones 7 43 All Night Wrong 3 44 The Sixteen Men of Tain 3 20 row(s) returned Executed in 1 ms
Narrow the Criteria
You can add a WHERE clause to return only those rows that match a criteria provided by you.
AlbumId AlbumName ArtistId ------- ----------------------- -------- 1 Powerslave 1 31 Somewhere in Time 1 32 Piece of Mind 1 33 Killers 1 34 No Prayer for the Dying 1 5 row(s) returned Executed in 1 ms
Join Another Table
You can use a join to return results from multiple tables that share data. This is what relationships are all about. In particular, a join is typically used where the foreign key of one table matches the primary key of another.
AlbumId AlbumName ArtistName ------- --------------------- ---------- 2 Powerage AC/DC 3 Singing Down the Lane Jim Reeves 36 Big Swing Face Buddy Rich 42 Along Came Jones Tom Jones 4 row(s) returned Executed in 1 ms
You'll see that the WHERE clause still works on columns that aren't actually included in the output. In this case, it was applied to the ReleaseDate column, even though we don't include that column in the results.
You'll also see that we qualify the two ArtistId columns with the name of the table (i.e. Albums.ArtistId and Artists.ArtistId). We must do this in order for SQL Server to know which table we're referring to when referencing that column. Some database developers consider it good practice to qualify all column names in all SQL queries, however this is more a case of personal preference or project-specific coding convention.
Add an Alias
You can also add table aliases to your queries to make the code more concise. For example, you could could give Artists an alias of ar and Albums an alias of al (or any other string you like).
You can use these aliases to qualify the column names. Here's the same query as above, but with all column names qualified with table aliases:
AlbumId AlbumName ArtistName ------- --------------------- ---------- 2 Powerage AC/DC 3 Singing Down the Lane Jim Reeves 36 Big Swing Face Buddy Rich 42 Along Came Jones Tom Jones 4 row(s) returned Executed in 1 ms
You can also assign aliases to columns (not just tables). We do this in the next example.
Format the Date
There are many different ways that dates and times can be treated in databases. In SQL Server there are different data types for storing dates (such as date, time, datetime, smalldatetime, etc) and there are many different functions for dealing with dates (for example SYSDATETIME(), GETDATE( ), CURRENT_TIMESTAMP, etc).
In this example, we'll use the YEAR() function to return just the year part of the date.
AlbumName Year ------------------------ ---- Powerslave 1984 Powerage 1978 Singing Down the Lane 1956 Ziltoid the Omniscient 2007 Casualties of Cool 2014 Epicloud 2012 Somewhere in Time 1986 Piece of Mind 1983 Killers 1981 No Prayer for the Dying 1990 No Sound Without Silence 2014 Big Swing Face 1967 Blue Night 2000 Eternity 2008 Scandinavia 2012 Long Lost Suitcase 2015 Praise and Blame 2010 Along Came Jones 1965 All Night Wrong 2002 The Sixteen Men of Tain 2000 20 row(s) returned Executed in 1 ms
In this example we also assign an alias to a column. More specifically, we assign an alias to the result of the YEAR() function, of which we pass the ReleaseDate column as an argument.
Another point about this example is that we used the AS keyword when assigning the alias. This is optional, and we could also have used the AS keyword when assigning an alias to a table in the previous example.
About SQL and Transact-SQL
The above queries (and the other queries in this tutorial) are written in Structured Query Language (SQL). More specifically, SQL Server uses Transact-SQL (sometimes shortened to T-SQL), which is Microsoft's and Sybase's proprietary extension to SQL.
SQL is the standard query language used on most relational database management systems. It's a standard of the American National Standards Institute (ANSI), and of the International Organization for Standardization (ISO).
While most basic queries will work across most relational databases, some queries may need to be slightly modified when porting between one database system and another. For example, you might have a script that runs in SQL Server. You could also use that script in MySQL, however, you might find that you need to change a few things before it will run successfully.
The SQL scripts in this tutorial demonstrate just a small sample of things you can do with SQL. For more information check out my SQL Tutorial or go to the Microsoft Transact-SQL Reference.