Basics of SQL

image from sqlbot.co

Hello Everyone! Today we’re going to be learning the basics of SQL. So to get started, we’ll be installing an application called ‘DB Browser SQLite’. SQLite is just another form of SQL. Some other examples are ‘MySQL’, ‘postgresql’, and etc. Click on this link. Download the Standard MSI installer if you are on windows.

Once the download is complete, click on it. You will now get a setup wizard like so:

Click next, and you can read through the license agreement. I’ve already read it, so tick the box that says ‘I accept the terms in the License Agreement’ and click on next.

Now choose where you would like to have the application shown. I prefer to have it on the desktop and the Program Menu, so I’ll select those two boxes. We’ll ignore the SQLCipher for now.

Click on next, and then next again. Finally, click on install, and give it any administrative if required. After the installation, click on ‘Finish’. Now let’s get started by creating our first database. Open up the application by searching ‘SQL’ in the search box. A window like this should pop up:

Start by clicking on the ‘new database’ option on the top-left side of the screen below the ‘File’ menu. Call the file whatever you like and save it wherever you want. After doing this, a window like this should come up:

Click on the cancel button. Now go to the ‘Execute SQL’ tab. This is where we will store the tables that we will create. The image below is where what data is present.

A database is not one table, but it is somewhat like a folder that can hold multiple tables. Each row in a table is called a record, and each column is called a field. The title of each field is called the field name. Now let’s get started by creating a table called task manager. Note that SQL is sometimes case sensitive. It is also advised to add a semicolon after each complete statement if you want to run multiple statements in one go.

CREATE TABLE ‘Task manager’ (
);

This line of code is self-explanatory. It creates an empty table. You can’t run this code now, though, since we still need to provide the field names. If you try to run it, you’ll get an error like this:

We will first create a field that will be our serial number; therefore, it should be an integer. It should not be empty. It should be the primary key, meaning it identifies each record; it should automatically increase its number for each new record. It should be unique so that each value is different from the next one. The field name should be ‘id’.To create the field, let’s add these lines of code to our program, inside the brackets:

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE

Press F5 to run the program. And then go to the browse data tab. You should see something like this.

So we can see that the id table is generated. It is advised to plan and create all the beforehand. However, there is a way to add fields if you have not previously added them. In the ‘Execute SQL’ tab, type:

ALTER TABLE ‘Task Manager’ ADD To_do TEXT NOT NULL

This code basically says that we have to change the ‘Task Manager’ table by adding a new field called ‘To_do’, a ‘TEXT’ data type. The data inside of the ‘To_do’ field cannot be left empty. Now, if we run this script:

And there you go. Now let’s get started by adding some values to the table. Obviously, you can just do it like that:

But to get used to SQL, let’s use the hardcode method. Go to the ‘Execute SQL’ tab and type:

INSERT INTO ‘Task manager’(To_do) VALUE(‘←-Whatever you want to put in the ‘To_do’ Field — ->’)

This line basically says that: we have to insert a value that I’ll give in to the ‘To_do’ field of the Task manager table. So I’ll put in:

INSERT INTO ‘Task manager’(To_do) VALUES(‘Write this blog’);

The result:

You will also notice that the id field automatically got 1. This is because we have set it to autoincrement. Now I’ll add in some more values to demonstrate some other lines of code:

Now let’s get some data from the table. We can use this simple line of code:

SELECT To_do FROM ‘Task manager’;

To get the entire ‘To_do’ column and its values. I get:

I can add a ‘WHERE’ clause to get some specific values. For example:

SELECT To_do FROM ‘Task manager’ WHERE ‘Task manager’.id = 1;

This line of code says to ‘SELECT’ values from the ‘To_do’ column ‘FROM’ the ‘Task manager’ table ‘WHERE’ the value’s corresponding id is 1. I get:

Since my corresponding value for ‘Write this blog’ is 1. This can be helpful cases where you want to find out a value in a large database.

So, this is all for this blog. I hope this blog has given you the basics of SQL, and by now, you can understand that SQL is almost like English. If you have any doubts or find it useful, please leave a comment. I’ll try to answer all of them. You can also follow me to get updates on more such blogs.

Thank you for reading!