Getting Started with SQL Server (Pt.3 Basic Intro into SQL)

Table of Contents
    Add a header to begin generating the table of contents

    Introduction

    This is the Third tutorial in a series of three tutorials aiming to give students a basic working knowledge of Microsoft SQL Server (relating to working with football data). This third tutorial builds upon the first and second tutorials so you must have completed both prior to starting this one!

    In this tutorial we will be looking at querying our newly built database within SQL Server itself, we will do this with help from a language called SQL (Structured Query Language). SQL is a powerful and industry standard language with application far beyond simply retrieving basic sets of data, our focus in this tutorial will be to introduce this strength and depth of features and then start building some basic queries.

    At the end of this tutorial you will have not only a basic, football stats database in SQL Server available but also the skills to retrieve basic sets of data. This new skill can then be built upon to start answering more complex questions of your football data.

    ** An Important note about this tutorial **

    Learning SQL is a very useful skill, one which can potentially stretch far beyond working with football data and also be applied in many other data related fields. As such it is also necessary to point out that SQL is not necessarily an easy skill to pick up, even though the basics might be fairly easy, mastering SQL will take time, patience and regular practice by solving data related problems.

    I would advise any readers of this tutorial to be as patient as possible and attempt the tutorial at least twice, reviewing the code carefully as you go. If you do get really stuck or need some extra help with a specific part of the tutorial please contact me via the Pro-Data support form, I will try and provide some extra explanation wherever I can.

    ** One of the biggest problems many people encounter whilst learning any kind of programming language are “typos” – with this in mind be extra careful when copying code from the tutorial, this will save you a lot of wasted time! **

    Prerequisites

    This tutorial requires that the first and second tutorials in this series be completed first. It is inadvisable to attempt this tutorial without first fully completing this first and second tutorials!

    Investigating possible data sources

    Before starting to learn SQL we must have a database pre-populated with some football data, this was covered in part 2 of this tutorial series where a database was built using the data files available from the Pro-Data service.

    Introduction to SQL

    In this chapter we will start to learn the basics of SQL (structured query language) and in turn learn how to start working with the data contained within our football stats database.

    SQL is a programming language used for managing and retrieving data stored within a database management system! A database, in its simplest form, is simply a collection of tables, each of which contains a set of data. A relational database management system (RDBMS) is simply a piece of software (such as SQL Server) which is used for managing a database.

    In addition to basic managing and retrieval of data, modern database systems such as SQL Server implement their own extensions to the SQL language allowing for advanced data analysis and managing of the database server itself all via SQL code. This means SQL Server can help us immensely with the process of retrieving, managing and analysing our football data further.

    The main use of SQL in this tutorial will be for selecting data from our football database and then performing some basic analysis on said data. You can do a lot with SQL but these two functions will be our main focus in this tutorial.

    Once you are happy with this tutorial there will be more advanced SQL tutorials available on the site which you can then move onto, check out the main Tutorials page for more info!

    Why Learn SQL?

    Despite not having the “popularity” of other languages such as Python, Java and C++ to name just a few, SQL is actually in use much more than you might think. For example, almost all (bar the very simplest) websites in the world will be driven by some form of database and SQL behind the scenes as well as a lot of other mainstream commercial software also making use of the language in some way.

    Cross over to the IT world and SQL is very much one of the most important skills you can learn. From writing business reports through to investigating why software won’t work, SQL is an invaluable skill for any IT professional! For the prior reasons (and more) if you ever plan (or dream) on developing your skills to eventually start an IT career then having a working knowledge of SQL will be a massive advantage!

    Even though SQL is very desirable on anyone’s CV and useful in the IT world, the real reason for learning and becoming proficient with SQL in a statistics context is as follows:

    Once you know SQL, you can ask any questions of data that you want!

    Gone are the days of simply consuming whatever data is given to you! – This ability to answer any question about your data which you might think of is the real killer application of learning SQL. Ignore what anyone else might tell you, this ability to ask and answer almost any question of your data is the real skill which will help propel you football stats forwards!

    Not only this but the process of learning to ask questions is in itself a skill and like with most skills will get better through practice. This means if you keep using SQL and asking questions of your data then over time the questions you ask will become much more thought provoking and in turn help you to develop much deeper insights. In fact, I’m so confident this is the case I can assure you that you will almost certainly be amazed at the types of analysis you will start performing if you manage to stay the course and get proficient with SQL!

    Contrast this approach with how most people use football stats where they simply head over to a football stats website and “consume” the pre-digested information already on offer (and available to everyone else I might add!).

    So, with all of this in mind, let’s get started learning some SQL!

    Selecting Data

    One of the most common functions of SQL is that of selecting data from a database. If you remember back to when we imported our CSV files into SQL server we ran a quick query whereby we selected all data from a table to ensure the import had worked successfully:

    ssms select top 1000 rows

    In the above image the menu item we selected would run a SELECT statement against our database table and retrieve the top 1000 rows of data like so:

    ssms select top 1000 rows editor

    This is the easiest way to quickly get some results from a table but lets build up a SELECT statement piece by piece from scratch and learn how it works.

    The SELECT Clause

    The SELECT clause is one of the most commonly used clauses in the SQL language and is used to retrieve data from a database.

    Note: You may have noticed I capitalise SELECT as I do all of my SQL clauses! This is not strictly necessary as SQL will run just fine if you type it in lowercase but it is good practice to keep any SQL commands in uppercase (this can help differentiate SQL from any data or other programming code in a file).

    To start using a SELECT clause head over to the SQL Server Management Studio and navigate to the “football-stats-db” database which we have been building up:

    ssms new query button

    As can be seen in the image above we should click on the database and then on the “New Query” button on the toolbar to begin writing a SQL query from scratch. Once you have done this you will get a new code tab open up like so:

    ssms new editor tab

    In the new code window the first thing we should get into the habit of doing is writing a comment about what the new script will do. This is not strictly required but is useful when looking back at old SQL scripts and being quickly able to see what functions they perform.

    To write a comment use the /* command to start and the */ command to finish, everything in between these commands will be ignored by the SQL Server (it is only for our own reference). Below is an example:

    ssms sql comment

    Next, the first actual statement we should write is a “USE” clause, this tells SQL Server which database we want to query. Type in the use statement like below:

    ssms execute basic sql statement

    Notice in the image above I have typed the “USE” clause followed by the name of the database I want to use in square brackets (“football-stats-db”). The “USE” statement should always be at the top of any SQL scripts you write as it will help reduce the number of errors you come across in the course of writing SQL (such as trying to access the wrong database).

    Note: You might have also noticed above I use a semicolon (;) after specifying the database name, this simply tells SQL Server that it has reached the end of the current statement.

    Another point to bring up before we go on any further is that of intellisense. Intellisense is an auto complete tool integrated into the management studio code editor, you might have noticed a drop-down box appear after you typed the “USE” command above like so:

    ssms intellisense example

    In the above example as we start typing “football-stats-db” a drop-down box appears with the name of our database in it, this is the management studio trying to help us out by suggesting the name we might be wanting to type out. You can of course ignore this suggestion but if you were to click on the completed entry (or use your cursor and down arrow for speed) SQL Server will automatically fill out the rest of the name for you (including the square brackets!).

    Now of course you don’t have to use Intellisense (you can even turn it off if you wish) but once you get used to it you will find it makes writing SQL much faster in addition to helping reduce errors from typos and the like.

    Next, we will start using the SELECT clause itself, in the image below I have written a simple statement to select all columns and all rows from the English Premier League 2018 / 2019 table (notice we use the “Execute” button highlighted below to run the script):

    ssms execute basic sql

    In this statement above the asterix (*) symbol tells SQL server to select all columns in the table and the “FROM” clause specifies that this must be done using the “england-premier-league-2018-2019” table. This gives us the following result in the results window (which will appear when you first run the code):

    ssms simple sql results

    This command is really useful if we want to see a full table, but suppose we only want to see the date column along with the home and away teams playing on that date? Below we build such a query, notice how instead of using an asterix we instead specify the list of columns we want to return:

    ssms simple sql columns

    In the image above the result of this query is as we wanted showing only the date, the home team and the away team columns, great!

    In the next section we will look at how we can further extend our SELECT statement but first we should save our work so far. To save the code click on the file menu and then select “save SQLQuery1.sql”, when asked for the folder navigate to our “football-stats-db” folder, create a new folder called “sql-scripts” and give the file a name of “sql-select-examples” like so:

    ssms save sql script

    Basic Filtering with the WHERE Clause

    In the previous section we have seen how we can open up a new query editor tab in SQL Server Management Studio and run some basic statements against our database, these statements however retrieve all rows back from the table we were using. Suppose that we want to filter down our results further and, for example, only see home games played by

    Manchester City in the Premier League 2019 / 2020 season so far:

    ssms man city filter

    The image above achieves this aim of only showing Man City home games by using the “WHERE” clause! This clause is designed to work on a specified column (in this case “Home_Team”) and return only results which match the filter (in our case the filter is ‘Manchester City’).

    Note: Also notice above how I have used the comment markers (/* */) to comment out the previous statement. This prevents it from being run so the only results we see will be for the current (latest) statement! This is a useful way of building scripts up as the previous statements are still visible for reference if needed!

    There are a few important things to note about the above statement:

    • Firstly the “WHERE” clause only works on one column at a time (we will cover working with multiple columns later on).

    • Secondly the equal operator ( = ) is usually used in maths but in SQL we can use it for matching text as well as numbers.

    • Thirdly, since we are looking to match text we must put this text within single quotes to tell SQL Server that we are working with text (e.g. ‘Manchester City’).

    • And finally, the search text is case sensitive, so make sure it reads ‘Manchester City’ as any other variation will not work (‘manchester city’ for example wont work).

    Note: I appreciate this might seem like a lot to take in right now but there are more examples to come, keep practising and the filtering of results should become clearer!

    Great, we now have a set of data showing all Man City home games, but suppose we want to see all Man City games regardless of whether they are at home or away? This would mean filtering on both the “Home_Team” and “Away_Team” columns like so:

    ssms home and away games filter

    Above we have almost the same query as before albeit with the addition of the “OR” clause on the final line. This “OR” clause works in much the same way as the “WHERE” clause and works on matching a single column to a search condition (although we must always have a WHERE clause in use first before using an OR clause!).

    To summarise, with this statement we are asking SQL Server to retrieve all games where the home team is Man City or the away team is Man City (this will return all Man City games from this season).

    In addition to searching on names we can also filter on other parameters, for example, let’s assume we want to see all games which finished up 1-1:

    ssms sql 1-1 filter

    To do this (as shown above) we need to filter all games which have exactly 1x home goal scored (“FTHG”) and exactly 1x away goal scored as well (“FTAG”). Since we want both

    columns to match 1x goal we must make use of the “AND” clause this time (so in other words both home AND away must have both scored exactly 1x goal).

    You might also have noticed we have enclosed the 1 in single quotes, this is because all of our data is currently stored as text meaning we ourselves might see it as a number 1 but SQL Server sees it as a string of text containing the character ‘1’. If you remember back to importing the data we actually imported everything as text in order to keep the process as simple as possible. This means we must now convert certain data (such as goals scored) to a numeric value whenever it is needed for calculations!

    Since I want to keep these sections fairly short I will address the string / numeric issue in the next section!

    More Advanced Filtering with the WHERE Clause

    Following on from the last section one of the issues we were left with is that we have all of the numbers in our table stored as text, this would mean, for example returning games with 3 or more goals (Over 2.5) becomes difficult in the table’s current state.

    So, to achieve such a result we must convert the number of goals (FTHG and FTAG) to numbers (integers) within our query like so:

    sql 3 or more goals

    This query (above) now retrieves all games with at least 3 goals scored, since we are adding both home and away goals together these 3 goals could be from just one team or between the two. The main line we need to look at from the above code is that containing the WHERE clause as per below:

    sql 3 or more goals zoomed

    Let’s go through what is happening above:

    • First, we are using the “CAST” function on both the FTHG and FTAG columns, we also specify “AS INT” in each case which means we are telling SQL Server to treat the returned column value as an integer value (a full numeric value with no decimal place).

    • In Between the two “CAST” functions we see a plus ( +) operator, since we are dealing with numeric values (after they have been cast) the plus ( + ) operator will tell SQL Server to add together both numeric values.

    • Finally, towards the end of the line is the condition to be met, in this case we are using “>= 3” which means equal to or greater than 3 goals (i.e. over 2.5 goals).

    So, the above query will retrieve all games with 3 or more goals scored, but what if we want to see all the games with 3 or more goals and both teams having scored at least 1 goal:

    sql 3 or more goals and btts

    The above query is identical as the one prior except for the last two lines, these are added to ensure each team has scored at least 1 goal (remember since we’re using the AND operator both conditions must be true!).

    Just like in the “WHERE” clause both score columns (FTHG & FTAG) must be converted to integers using the CAST function first and each line must then have a numeric value of 1 or higher (i.e. both teams have scored).

    Filtering with Search Conditions

    In addition to filtering our queries on fixed values such a set number of goals or a specific team name we might also want to do more advanced “searches”. For example let’s assume we want to see all the games where either Manchester United or Manchester City have played

    sql for all manchester games

    Since both Manchester United and Manchester City begin with “Manchester” we can use the “LIKE” operator, this operator effectively allows us to “search” all the values in a column and return only those which match our search term (of “Manchester”).

    In addition to being enclosed in single quotes (its a string value) you might also notice we have added a % symbol to the end of the search text. The % symbol is a wild card meaning that as long as everything before it is a perfect match anything after the % symbol won’t matter. In other words, as long as the name begins “Manchester” it doesn’t matter about the next bit meaning if it’s followed by either “United” or “City” won’t matter and both will be picked up!

    In addition to this we have other wildcard characters which work in slightly different ways, the table below shows what these are and how they can be used:

    ssms sql symbols table

    As a further example, let’s say we want to see only games played in January, we could use the like operator on the “date” column like so:

    sql wildcard date text example

    Here we use the % wildcard operator twice, the first time to get any combination of characters before the first hyphen (any day value) and then again to get any value after the last hyphen (any year). This has the effect of retrieving any game played on any day in January.

    Another example of using wildcards might be to retrieve all games where a team beginning with a, b or c is playing, we could achieve this like so:

    sql wildcard teams a to c example

    In the SQL above you can see we are using the range wildcard operator [A-C] (note it is enclosed in single quotes to denote it is a string). This means any characters within the range of A to C (A, B and C) will match this wildcard. After the square brackets we include a % wildcard which means we will accept any combination of characters after the square brackets – in other words any team beginning with A, B or C will be returned (since the [] wildcard is the first thing in the single quotes this means it must be at the beginning of the name).

    Ordering and Limiting Data

    At this point we are starting to get some useful sets of data back from our database, for example, we can find all games where a specific team has played or even games where said team has won, lost or drawn! Moving on from this our next step is to learn about a few clauses which help us organise and limit our data – these are the “ORDER BY” clause, the “DISTINCT” clause and the “TOP” clause.

    Let’s say we want to see a list of all teams within the English Premier League 2019 / 2020 season, we can start by bringing back all games and select only the “Home_Team” column like so:

    sql top 3 teams

    Note: BE CAREFUL when looking for certain things in your data! For example, in the above example the football season was well underway when I ran this query meaning every team will have played at least 1 home game at the time of writing (in other words every team is represented in the data set). Had I, for example, run this query in the first few days of a new season not all teams might have yet played and I wouldn’t have received a full set of results!

    Obviously this is an extreme example but it shows you need to think carefully about what question you are asking and if it can be answered accurately with the data you have available!

    In the above code we specify we want to retrieve all games from the “[england-premier-league-2018-2019]” table and show only the “Home_Team” column. Everything looks good until we look a bit further down the list to see that we have duplicate team names:

    sql duplicate team names example

    Fortunately SQL has a built in way of handling duplicates like this which is the “DISTINCT” clause, by including this in our statement SQL Server will automatically remove any duplicates for us like so:

    sql for all distinct teams in season

    Now we will have a list containing all teams participating in the 2018 / 2019 season:

    sql distinct list of epl 19-20 teams

    At this point you might also have noticed that by including the “DISTINCT” clause SQL Server has automatically sorted the list into alphabetical order for us! This is great but what if we had instead wanted this list in descending alphabetical order (z to a)? This is where we start looking at the “ORDER BY” clause.

    Next, let’s modify the above statement to show our list of teams in descending alphabetical order instead:

    sql for all distinct teams in season desc ordering

    In the code above the “ORDER BY” clause is added to the end of the statement (it is always added after the “FROM” and “WHERE” clauses!) and the column to sort on is specified afterwards (in this case it is the “Home_Team” column). The second thing to notice is we have added the “DESC” parameter to the end of this line, this tells the “ORDER BY” clause we want to sort in descending order, had we added “ASC” instead we would have received the list in ascending order!

    Note: In the example above had I not added either “ASC” nor “DESC” then “ORDER BY” will default to ascending order!

    Next, let’s look at using the “TOP” clause, this will limit the number of rows that are returned from a query. For example, let’s assume we only want to see the top 3 results from this data set:

    sql distinct top 3 teams

    As can be seen above we can include the “TOP” clause in the top line of the statement along with the number of rows to retrieve (NB – when applicable always insert the “TOP” clause after “DISTINCT”).

    Aggregate Functions and Grouping

    As we near the end of this subchapter we have one last important area to look at which is aggregate functions. These functions are used for performing mathematical functions on our data, for example, summing up all values in a column or selecting the highest or lowest value in a column.

    The full list of aggregate functions in SQL is below:

    sql agregate symbols table

    In order to see the power of these functions let’s have a look at a couple of examples!

    Let’s say we want to see the total number of home goals scored in the English Premier League 2017 / 2018 season:

    sql total home goals per season

    In the code above notice how the first thing we do is use the “CAST” function to convert the FTHG column (full time home goals) to a number, this allows the “SUM” function to work.

    The SUM function itself encloses both the “CAST” function and the name of the column meaning it is acting on the result of that operation (i.e acting on the numeric value of the number of goals).

    Next, let’s look at retrieving the total number for goals for the season (both home and away), this will involve using the addition (+) operator which we looked at earlier on in the chapter. We will also address that the returned column is currently called “(No column name)”, this might not matter sometimes but let’s give it a relevant name as well in the below code:

    sql total home and away goals

    In the above code we have first added (using the + operator) the sum of all home goals to the sum of all away goals, as before note that both columns must be to be converted to an integer value first!

    After the addition operation we have added the “AS” clause, this allows us to specify the name of the new, calculated column we have just created (this must be a single word, or multiple words enclosed in square brackets, also it must begin with a letter and not a number or symbol!).

    Following on from the above example let’s assume we now want to see the total number of games played in a season, for this we can make use of the “COUNT” function like so:

    sql count total games

    “COUNT” is a fairly simple function to use and like in the previous example we have used “AS” to give this count column a nice name. Note that we have used the asterix (*) in brackets after the “COUNT” keyword, this simply tells SQL server to count all rows!

    Next, let’s look for the game with the highest number of goals scored during the 2017 / 2018 season, to do this we must use the “MAX” function on the result of the home goals and away goals added together (i.e. total goals):

    sql for highest scoring game

    Notice that the above code is along the same lines as before, we convert both the “FTHG” and “FTAG” columns to integers and then add them together, we then pass this into the

    “MAX” function which will return the highest available value (the highest number of goals scored in any given game). If we wanted to see the lowest number of goals in any game we could replace “MAX” with “MIN” (although this will almost certainly be 0 for any football season so has little value here!).

    Next, let’s work out the average number of goals scored per team whilst playing at home in this same season, this is a good next step as we can introduce the “GROUP BY” clause like so:

    sql avg goals per team

    In the above example there is quite a lot going on at first glance, along with this a couple of new things that we haven’t looked at so far are introduced! Let’s break down what is happening here:

    • On the first line of the select statement we have specified we want to return the “Home_Team” and “Average_Goals” columns.

    • The “Average_Goals” column is calculated using the “AVG” (average) function which gives us an average for all values in the “FTHG” (home goals) column. NB – since we now have included the “Home_Team” column in the statement SQL will now split up (or GROUP) these averages on a per team basis (if we removed Home_Team we would get the average across all games).

    • Since we are dealing with averages we should use decimal places, this means we are now converting our “FTHG” column into a decimal now instead of an integer (a decimal will show the decimal place whereas an INT will just round it up which is of no use here).

    • On line 3 of the statement we have added a “GROUP BY” clause and specified to group by the “Home_Team” column. All (non-aggregate) columns included in the first line of the select statement must be also included in the “GROUP BY” clause (or else the SQL query will fail). The “GROUP BY” clause basically tells SQL to split up the averages and do the calculations individually for each team present in the “Home_Team” column as opposed to averaging it across all games.

    • Finally, we are using the “ORDER BY” clause again at the end of the statement, notice how we can pass in the name of the column we created in the first part of the statement! This is allowed due to the “execution order” of SQL. Unfortunately this is too complex to go into in-depth right now but this does work as SQL first performs the calculations and then orders the resulting table afterwards (meaning this column is available when it comes to ordering).

    • Also, note how we order the average number of goals in descending order, this means we get the highest values first (teams with the highest number of goals on average).

    At this point things are starting to get a little complex for the “introduction to SQL” tutorial so this is as far as I will go for now (Other tutorial will provide more in-depth SQL queries and how to make use of them). Before finishing I want to go over one other important use of SQL, creating tables and inserting data into them

    Building Tables and Inserting Data

    Being able to build tables using SQL has many advantages, this can be the building of permanent tables used for keeping data long-term or the building of temporary structures used to help build more advanced SQL statements (as we will see demonstrated later on).

    To start with, let’s build a new table like so:

    sql create table example

    The above SQL shows use of the “CREATE TABLE” statement followed by the name of the new table being created (“tbl_football_matches”) and then finally in the brackets the columns this new table will contain. Once this statement is run you will see “commands completed successfully” in the results window (telling us the table has been created).

    Once this command has been run if you go to the object explorer in the Management Studio and refresh the tables branch under the “football-stats-db” database you should see the new table showing up:

    ssms new table in object explorer

    You might have noticed above that after each column name specified we have also added some more text afterwards, this is the “data type” of the new column. Every column in a

    database has a data-type which basically tells SQL Server what type of data is stored in that column (this could be text, an integer or even a date value). Below is a list of the most commonly used data types in SQL Server:

    sql data types table

    Note: There are many more data types available but I have only listed the most commonly used ones above. If you ever find yourself in need of something more specific you can easily look up the full list although this smaller list should be more than enough for working through this tutorial!

    Next, we will manually insert some data into our new table using an “INSERT” statement as is shown below:

    sql insert data and check worked

    Let’s break down what is happening in the above “INSERT” statement:

    • First, we use “INSERT INTO” followed by the name of the table we wish to insert data into.

    • Next, in brackets, we specify the columns we wish to insert values into (we don’t necessarily have to always insert values into all available columns!).

    • Next, we use the “VALUES” clause followed by a value for each specified column in brackets.

    • Note that these values must be inserted in the same order as the columns that are specified (in brackets) on the line above.

    • Also, note that since we are inserting a date value this date value must be in a valid date format (YYYY-MM-DD is the standard). Dates must also be enclosed within single quotes!

    • Again, note that the two team names are strings (VARCHAR) and must also be enclosed within single quotes.

    The next job is to execute the statement – notice how we get a confirmation that 1 row is affected when we run the statement, this means one row has been inserted into the table. We can confirm this by running a quick “SELECT” statement on the new table like so:

    sql check insert worked ok

    If everything has worked correctly we should have one row returned as per the above example.

    Next, we will look at a couple of other useful features when creating and inserting data into tables, these are defining numeric columns, NULLs, required columns and automatically inserting the current date and time!

    To start with, let’s create a new table like the one below:

    second sql create table example

    There are a few new concepts introduced above so let’s go through them all individually:

    • In line 1 we use the “CREATE TABLE” clause followed by the name for our new table.

    • Lines 2 – 5 are where we define the columns for the new table, I have done them line-by-line instead of in one long row for better readability.

    • On line 2 we define a column called “Date”, notice we use the “DEFAULT” keyword after defining the data-type of “DATETIME”. This default setting tells SQL Server that if no specific value is supplied during an insert operation then it should automatically fill the column with this value.

    • The default value we want it to use in this case is a SQL Function called “GETDATE()”, this function will return the current date and time for when the insert operation is run.

    • We can of course still manually specify a date value and in doing so the “DEFAULT” clause will become redundant.

    • On line 3 we define the “season” column, note how we end this column definition with a “NULL” cause. This basically tells SQL Server we will accept a blank value (a NULL value) for the field (“NULL” basically means no value).

    • More importantly on line 4 where we set “team_name” we have a “NOT NULL” clause, this means a valid value must be supplied for this column when writing data to this table else the entire insert will fail!

    Also, following on from the above all fields in a table are set to allow NULLs by default unless specifically told not to allow them or are of a special data type. I have just added the “NULL” clauses above to highlight that they exist and how they can be utilised.

    Next, we can run this command (above) and then look at the object explorer again to check that this new table has been created:

    sql second new table example

    Next, let’s look at adding some data to our new table, remember we have a default value added for the date column whenever no date value is specified:

    sql insert into second table

    So, in the code above we can see that we want to insert data into is “tbl_total_goals_season” and we specify only the “season, team_name” and “no_goals” columns as those we want to insert data into.

    Note: Remember that we set the “team_name” column to “NOT NULL” meaning if we don’t add a value to this column we can’t add any of the other values either, the entire insert operation will fail!

    Updating and Deleting Data

    Updating and deleting data from tables are both core skills which you must grasp whilst learning SQL!

    Whilst neither of these look too complicated it needs to be stated that you should be very careful when using either as they both have the potential to cause unknown damage to your data and cause plenty of headaches in the process!

    Updating Data

    The code to update a table looks very similar to that used to insert data into it. As with an insert statement we must specify the table we want to work on and the columns we want to modify. In the below example I have updated the number of goals for Manchester United in the “tbl_total_goals_season” table we created a little earlier:sql update example

    Once you have run this statement for yourself let’s go through what is happening above:

    • On the first line we specify the “UPDATE” clause followed by the name of the table we wish to update. As expected this tells SQL Server we wish to update the specified table.

    • On the second line we specify the column (or multiple columns) we want to change, in the above example I have selected only the “no_goals” column and after the equals ( = ) operator I have set the new value for the column.

    • On the third line I have filtered out the rows to which his update will apply (when the team is ‘Manchester United’ only).

    • IMPORTANT – if you do not set a filter SQL Server will update all rows with this new value so be very careful here!

    Note: It is often a good idea to select the data you wish to update first! This process helps ensure your filter is working as it should and brings back the correct data before then altering your statement into an UPDATE statement!

    Deleting Data

    In this section we will look at deleting data from a table. It is important to reiterate you need to be very careful when doing this as it can be quite easy to accidentally delete an entire table worth of data should you not get your statement correct!

    To demonstrate this let’s go back to the “tbl_total_goals_season” table we created a little earlier and then delete the one and only row like so:sql delete example

    Notice that we must use a filter (the “WHERE” clause) to ensure we only delete the correct rows. Also notice that this statement is very similar to a standard select statement, if we change “DELETE” to “SELECT *” then we will select all of the data instead of deleting it (again this is a good idea to do this to ensure you don’t make a mistake and are deleting what you are intending to delete).

    If we check the table we should see it is now empty confirming that the delete operation has worked:

    sql check delete statement has worked

    There isn’t really much more to say about deleting data at this point, obviously take care when deleting stuff as once it is gone, its gone!

    Note: You could set up transaction logging on your SQL Server database which will allow for a deletion to be undone, this is however beyond the scope of this tutorial!

    And with the final section on deleting completes this tutorial.

    Where to Next

    As was mentioned earlier this is the first in a three part series of tutorials for getting started using SQL Server. After completing this tutorials you will be ready to move onto other, more advanced tutorials on the Tutorials page.

    If you have experienced any problems whilst following this tutorial please get in touch via the Pro-Data support form.

    Scroll to Top