So, in our previous articles, we introduced you to what is SQL, for what it is used, how it stores data in databases and tables, and how it works through commands (SQL query), and how the commands are different based on what SQL type you use.

Now that we have the overview, we are going to dive into writing our first SQL command, which in technical terms is called a query, as you might already know.

Types of an SQL query

As we wrote in our previous article, there are multiple types of commands in SQL (DDL, DML, DCL, DQL).

We will go over all of these types of queries, what their syntax is, and how/when to use them. But before starting to write queries, we will need an environment where to run them. So, before we start please download and install SQL Server Management Studio (Express edition is free).

Also, there’s quite a good video online that will walk you through installing and opening SQL Server Management Studio. After you finished installing and opened the program just click the New Query button so a white window will appear where we will write our queries.

DDL (Data Definition Language)

These queries are used to define and create elements in the database or the database itself.

• CREATE SQL query

  The first command we are going to go over is the CREATEquery.

  From this point on we are going to try and make a short tutorial on how to use these queries in a real-life situation, in logical order so you can get an idea about the big picture they are used in.

  Now, we want to create a container where to store our data. We will first have to create our database, and we will use the following command to create it:

  CREATE DATABASE myFirstDatabase

  After you wrote this command either press F5 or click the Execute button and this is the message that you should see.
  

  And if you look to the left side of the screenshot, the database should appear in the list (otherwise, right-click in on the Databases folder and select Refresh).

  After you create the database, you need to specify that we need to use it for our later steps, that is why the next command we will use is:

  use myFirstDatabase

  

  Notice that the myFirstDatabase appears in the top left corner of the image, in the dropdown box, next to the Execute button. This means that this database is selected for this query window.

  Now that we created our container, we will need to create a table where the information will be stored. For this, the SQL query that we will use will create the table with information about cars that we used in this article.

  CREATE TABLE CARS(
      BRAND NVARCHAR(30),
      HORSEPOWER INTEGER,
      COLOR NVARCHAR(30))

  

• ALTER SQL query

  The next command we are going to review is the ALTER SQL query. Let’s say you forgot that you wanted to add another column to your CARS table and you already created it.

  Well, we can now add it with the ALTER command. Let’s say we want to add a column that stores the year the car was built, we are going to use the next query:

  ALTER TABLE CARS
  ADD YEAR INTEGER

  

• DROP SQL query

  This is the last query that is part of the DDL type queries and it is used to remove all elements from either a database or remove the database itself.

  We do not recommend using this query instead you know what you are doing, and we do not take any responsibility for when and how you use it. But just to display how this is used, we are going to remove the CARS table from the database, which will require you to create it again afterward, because we will use it in our next examples, in our next posts.

  DROP TABLE CARS

  
  There are multiple types of objects in a database server apart from a database and a table, thus you can use the CREATE, ALTER, and DROPqueries to make, modify or remove other objects as well. We will try to take over all of those others in the next articles.

Now that we have built all of the elements that store our data, we will continue with what query you have to use to get the data back from the system.

DQL (Data Query Language)

There is only one query that is part of this category is the SELECT statement which is used to retrieve data from the database and its tables.

If you haven’t recreated the CARS table, please do it now so we can see what the SELECT statement does.

There are two ways of running a SELECT query.

The first one is to run it like this:

SELECT * FROM CARS

 

Now, to explain what it does and what its elements are:

• SELECT– the name of the command
• * – specifies that we want to get back ALL of the columns of the table
• FROM – used to specify the table name, from where you want the data
• CARS – the name of the table, can be any table in your system

The second one is to run it like this, where we explicitly specify the columns we want to be returned:

SELECT
    BRAND,
    HORSEPOWER,
    COLOR,
    YEAR
FROM CARS

Because this second method is explicit, we can choose what columns to return. We are not forced to SELECT all of the columns.

Now, we know how to build a container for our data, a database, a table to store our data in a structured manner, and how to request data from the databases’ tables. The next logical step is to ADD data into our system, which we will cover in the next article.