Getting Started with SQL: Setting Up Your Database

A Step-by-Step Guide to Setting Up Your Relational Database and Writing Your First SQL Query

Structured Query Language, or SQL, is an essential tool for developers and data engineers working with databases. Almost every application we use today interacts with databases, making proficiency in SQL a critical skill for these professions. This language allows for effective management and analysis of data, which is crucial in data engineering. In this article, we will provide a step-by-step guide to setting up a database, creating tables, and inserting data using SQL.

Prerequisites

To get started, you will need to install the required software, including SQL Server Management Studio (SSMS) and SQL Server.

• SQL Server Management Studio (SSMS): https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

• SQL Server: https://www.microsoft.com/en-us/sql-server/sql-server-downloads. The Express version will suffice for the purposes of this guide.

After the successful installation of these programs, you can launch SSMS and connect to the SQL server.

Creating Your Database

The first step in setting up your SQL environment is to create a database. To do this, right-click on 'databases' in the left pane of the SSMS interface and create a new database. You can name this database according to your preference. For reference, see the image below:

Once created, you can refresh the left pane, and the newly created database should appear. Expanding the database will reveal a 'Tables' section, which will be empty for now (there might be some folders there but those are more system built and doesn’t concern us right now). Currently, there are no tables created. You can either right click on the Tables and create new table, which will give you an interactive table, where you can set column names and their data types. Or we can do it using SQL, which is what we are going to do.

Writing Your First Query

To begin creating tables within your database, click on the 'New Query' button. This will open a text file where you will write your SQL code. Ensure that you select the name of the database you created from the dropdown menu below the 'New Query' button.

The basic syntax for creating a new table in SQL is as follows:

CREATE TABLE table_name (
	first_field int,
	second_field varchar(32)
	.....
)

Let's break down this syntax:

• CREATE TABLE is the command that tells SQL we want to create a new table.

• table_name is where you would place the name of the table you are creating.

• The parentheses enclose the definitions for the columns of your table, with each line representing a new column.

• Each column definition is separated by a comma, and the entire command is concluded with a closing parenthesis and a semicolon.

Creating Tables

With the basics of the CREATE TABLE statement understood, we can now create two tables: YoutubeChannel and ChannelStats. The selection of these tables as examples aims to mimic a real-world application, as such tables are often found in similar systems.

CREATE TABLE YoutubeChannel (
    ChannelID int,
    ChannelName varchar(255),
    DateCreated date
);

CREATE TABLE ChannelStats (
    ChannelID int,
    SubscribersCount int,
    TotalViews int,
    Date date
);

The YoutubeChannel table has three columns: ChannelID, ChannelName, and DateCreated, which hold the unique identifier for each YouTube channel, the name of the channel, and the date when the channel was created, respectively.

The ChannelStats table also has four columns: ChannelID, SubscribersCount, TotalViews, and Date, which hold the unique identifier for each YouTube channel, the number of subscribers for a channel, the total number of views across all videos for a channel, and the date when the statistics were recorded, respectively.

You can execute these queries by selecting each CREATE TABLE command one by one or simply executing the entire file. After refreshing the Tables section in the left pane, you should see the two new tables.

Checking the Created Tables

By right-clicking on one of these tables and selecting “Select top 1000 rows”, a new query tab will open and show something like below:

It is basically empty at the moment (ignore the select statement, we will dive deep into it in a later article)

Conclusion

In this introductory guide to SQL, we have accomplished several key tasks. We have successfully installed the necessary software, SQL Server Management Studio (SSMS) and SQL Server. We then created a database and constructed two tables within it, YoutubeChannel and ChannelStats.

Moving forward, we will be delving deeper into the world of SQL. Our upcoming articles will explore how to populate these tables with data and leverage SQL queries to analyze the data. Stay tuned for more!