Type your first SQL statement, CREATE TABLE, and review the syntax conventions in the documentation.
- [Instructor] To learn the structured query language SQL notation, we need something to query from, so our first step will be to create a table from which we can query. This will look and feel like an Excel sheet, but it has many more features that we will leverage. To create a table, we will type the text for creating a table, the instructions. To learn those instructions, we will need to refer to the documentation very regularly. In this example, we will create a new query and then use the built-in help feature to find the information we need so that we can learn how to create a correct statement for SQL Server to execute.
Right-click on Queries at the top right. So within your solution, under the project Learning, there's a folder for the Queries. I'm going to say right-click and New Query. By adding a new query, multiple things will happen. You'll notice the text editor opens in the middle of the screen, but also, on the right-hand side, you have the new file created as well as a new connection. It's important to pay attention to the connection. If you have any other configuration, it might be different from what you expect. So take a look at the Connections folder.
In my case, it says learningazuresqlquerying, which is the correct database. Notice at the far right-hand corner, if you hover your cursor over the connection, you'll see your username at the far right. This is important to make sure you have all the permissions, all the privileges that we're going to be using to create a new table. So let's rename the file, make it easy to keep organized. In this case, we'll start with 02_01 and CREATE TABLE. Now, how did I know it was supposed to say CREATE TABLE? We're going to find out.
So, to learn structured query language is to learn the syntax as well as all of the rules for what is possible in terms of the options we have. Type the words CREATE TABLE. Select all of the text. Use your cursor or your keyboard. Make sure it is selected. Then, press F1. The F1 key, the help key, will take us to the documentation for that statement. So the first thing we're going to learn is how to make it possible to put data in the database, and for that, we need a container of some kind.
So we create a table, looks and feels like an Excel sheet, into which we can add rows and columns of data. In this case, we see the CREATE TABLE documentation, which has a section called Syntax. Scrolling down to Syntax, we can see that there is a definition, a description, of what the CREATE TABLE statement might look like. If you are new to documentation for programmers, this is not readable yet, so what we need to do is understand how the documentation describes these new things we want to learn.
So to do that, there is a document about documentation. The document you need to look at is called the Transact-SQL Syntax Conventions. The Syntax Conventions is a description of how the documentation works, and we will refer to this several times through this course. Notice at the top of the screen, this topic applies to Azure SQL databases, and then we have a table of conventions. So let's start with the most important three.
The first one says there is a convention for uppercase, which is used for Transact-SQL keywords. That's the first thing we just learned. There is keywords in Transact-SQL for creating a table. There's literally CREATE TABLE, and it was provided in uppercase. Let's go back to the documentation page. Notice, under Syntax, the first thing it tells us is CREATE TABLE, and because it is in uppercase, it is what is defined in the structured query language.
So, defined by the language means it's a keyword, and we do not get to change it. We have to type it exactly as provided. It is not case sensitive when you type it, but in the documentation, they will make keywords in uppercase, capital letters, so that they are easy to identify. The other two things we need to pay attention to is the vertical bar and the kinds of brackets available. So, the vertical bar, a few rows down in the table, says it separates the syntax items within brackets, and it means you can choose one of those options.
It's like an or, a choice. You choose something on the left or on the right of the vertical bar. It's a separator. Just below the vertical bar, you see there are two kinds of brackets. Square brackets, or just brackets for short, and curly braces, curly brackets, which is the second row there. When it is square brackets, it means the item being described is optional. You get to choose it. When it is in curly brackets or braces, it is a required item and you have to have that item.
So let's see what the CREATE TABLE statement looks like, given what we just learned. It says CREATE TABLE, and then it opens with a square bracket, which means this part is optional. It opens another square bracket, which means the second part is optional. Then, there is the vertical bar. It says, well, you can also specify that third item. Then, the square bracket closes. This first part is entirely optional, but notice the square bracket closes before table_name. So the syntax here is CREATE TABLE, and then you have to have a table name, and then you have to have an open parenthesis.
The round brackets are not part of the syntax notation. It is something you have to type. So the statement will read CREATE TABLE. We will choose not to use the database name, we will choose not to use the schema name, and we will be forced to provide a table name. Then, we will provide an opening parenthesis, there's a description we don't know enough about, and a closing parenthesis. In your editor, SQL Server Management Studio, go find the CREATE TABLE statement you typed earlier. Go to the end of the line and provide a table name.
In this case, the first table we will create is called Countries. Then, you know we are required to provide an opening and closing parenthesis. The document says they must be there. The keyword's CREATE TABLE. We must provide a table name and then parentheses. Now, we need to learn more about what goes inside the parentheses. Let's go back to the documentation. In the syntax, it says CREATE TABLE, say what are optional items, and then table name is required, and then inside the parentheses, the column definition is required.
How do I know it's required? Notice the curly braces. Now that you've noticed the column definition, we need to find what is allowed within the parentheses, and that is described further down. The first section of syntax was essentially a summary, so you can quickly notice CREATE TABLE, table name, and then a column definition. So let's scroll down and find the definition of what could be a column definition. You'll find this much further down the page. There's a section called column_definition and it says column_definition::=, meaning, quite literally, that in place of column_definition we have to type a column name and a data type.
We will keep this very simple for now. I will provide a data type, and we will discuss the data types in more detail later on. Let's go back to your CREATE TABLE statement. So we have CREATE TABLE Countries, and we know we need to provide a column name and the data type. For now, we will use CountryName as the column name, and we will ask for it to be of data type varchar(max), and we will talk about that in just a moment. To say CREATE TABLE Countries(CountryName varchar(max)) produces in the database itself a structure where we can list country names, and we call that list, sort of like an Excel sheet, we call that list a list of countries.
The naming convention is up to you. You can say Country for the table name if you prefer the singular, and similarly, for the country name, you might make up all sorts of varieties of names based on your naming conventions. In this case, I used Countries and CountryName, and you'll see us refer back to that as we have our tour through the structured query language.
- Creating a new Azure SQL database
- Using basic SQL statements such as SELECT and INSERT
- Using SQL statements to update, modify, and delete data
- Adding functions to your SELECT statement to modify how numbers are displayed
- Filtering and formatting results
- Saving and sharing your work by creating views
- Sharing access to your database with other users