PLEASE NOTE: This lesson and the previous lesson 1.Introduction to Beginners SQL in one day are a taste of the first two sections of a book that I have published on Amazon. These lessons are FREE, however, if you find them interesting and would like to read more, then why not purchase the complete book Learn SQL in One Day: A must have tutorial for every Software Test Analyst and SQL Beginner
This brief lesson is to show you how to use sqliteonline.com (https://sqliteonline.com/)…an excellent FREE online SQL editor that you will be using in order to execute (run) the SQL code you will be learning.
Table of contents
- sqliteonline.com – the (FREE) Application
- How to use sqliteonline.com
- sqliteonline.com…how it works
- Downloading the example code
- Running your first bit of SQL code 🙂 !
- Summary
1. sqliteonline.com – the (FREE) Application
Yes, this application is free and the kind people who have developed it have allowed people like you and I to make use of it, allowing us to practice our SQL.
I chose the application because it’s very simple to use, however, the best thing with it is that you do NOT need to learn all about installing and running a Database Management System (DBMS) on your PC!
As with almost all the other SQL Tutorial books you’ll find on Amazon, I could have had you download and install a full DBMS to practice things on, but that would be very involved and would defeat the object of this tutorial…a simple tutorial to just show you the basics and hopefully build your confidence for you to move on to bigger and better things!
2. How to use sqliteonline.com
This lesson will introduce you (gently) to running some SQL, by doing something that you would not normally be asked to do as a Test Analyst or user of data…you’re going to create a simple Database Table. However, once you’ve created the table, you will be able to run your first SELECT statement so as to retrieve the data you’ve created…exciting!
To do this, you will be:
- Visiting a FREE website where you can run and practice your SQL
- Cutting and pasting some SQL for you to create a table and some data
- Retrieving (by executing a SELECT Statement) data from the table to see the data you created
Again, this very brief lesson is to show you how to use sqliteonline.com…an online application. So let’s have a real quick play with it by running some SQL code.
I don’t want you to try to understand the code you are running! You are simply getting used to using sqliteonline.com.
3. sqliteonline.com…how it works
This app is very simple to use:
There are 3 main areas (as below) on the web page:
- The area marked 1 shows the Databases we can use to run our commands
- Area 2, the SQL code tab, is where you will write the SQL commands you will be running
- Area 3 shows the results of running the SQL commands
As a Test Analyst, you are not expected to manage a Database System…like creating tables! In all the big corporate companies, there is a specialist team (Database Administrators aka DBAs) who set up the database, create the structure and the tables and generally maintain it to keep it running smoothly.
Important things to note about using sqliteonline.com:
- The Tables you will be creating will not be permanent. They are deleted once you close the page OR you change the table layout (which is known as a Schema)
- Is this a problem? No…not at all. You are, after all, just attempting to practise your SQL, which you do in the Right Hand Pane of SqlFiddle.com
- If you experience any problems, just click on “Clear” and cut and paste the Schema again in the Left Hand Pane and click Build Schema
- By all means, once you’re up and running…have a play with the application. See what the other buttons do…you honestly cannot break anything!
4. Downloading the example code
You can download all the example code (SQL and creation of tables) that is
presented in this book by simply clicking here, which I strongly suggest you do. Once you have clicked here,
a file (LearnSqlInOneDay.csv) with the code in it will be downloaded automatically onto your PC in
your “download” area as a csv file. The file can be viewed in any text reader
application, such as NotePad or NotePad++.
If you experience any issues with the download, please message me via: maketestingeasy100@gmail.com
However, in order to help yourself learn better, it is
preferable that you attempt to type in as much of the example SQL code as
you can, I really do believe that by typing it will help you remember
and understand so much more! EXCEPT TO SAY…this book is
all about learning SQL, so where I ask you to “Create Table(s) and Data”, then by all means just cut and paste this code.
If you decide to type most of the SQL code and find you don’t get the result you expect or the book expects, then just compare your code with the downloaded one to see where you perhaps have gone wrong…and if all else fails, just cut and paste the downloaded code and run that!
5. Running your first bit of SQL code 🙂 !
Open the sqliteonline.com application, click here https://sqliteonline.com/ .
Create your first Table and some data:
- This book has been created to use the MariaDB Database (DB), so you can ignore the other DBs…SQLite, PostgreSQL and MS SQL. Please ensure you use the MariaDB :
- Either copy the following code or cut and paste it from the file you downloaded in the previous section:
CREATE TABLE account (
name VARCHAR (30) PRIMARY KEY,
description VARCHAR (100) NOT NULL,
start_dt DATE NOT NULL,
end_dt DATE
)
;
INSERT INTO account
VALUES
('VISA', 'Visa Acct', '2022-01-01', NULL)
,('Current', 'Current Acct', '2022-01-01', NULL)
,('Savings', 'Savings Acct', '2022-01-01', NULL)
;
- Type or paste the code into the SQL tab as below. Click the green Run button at the top of the application. You should now see that the word account has appeared below Table. This indicates that your code has run successfully and that the table “account” has been created. Go ahead, click on the arrow next to account and you will then be shown the columns that exist in the table (name, description, start_dt etc):
Now…let’s run our first SQL Statement:
- First, you need to clear the SQL tab of the code you used to create the table account. Do that by just highlighting the code (one way is by clicking on the SQL tab and then then pressing the keys Ctrl and A together on your keyboard) and then pressing the delete key
- Type or copy the following Try it! code and paste it into the SQL tab
- Once you click the green Run button, then you should see the results as in Expected Result , below
SELECT *
FROM account
;
Woo hoo…your first execution of SQL!
For this first Expected Result, I have shown it as if it were displayed in sqliteonline.com, however, for the rest of the book, you will see them as a table, which I think is clearer to read.
Also…when you retrieve data, it may not always come back in the same order as displayed in this tutorial, don’t worry, that’s because it may be stored on the DB differently. However, there is a command to “order” data, which we will see later.
6. Summary
As mentioned in lesson 1, section 1.1, I am not after you knowing too much about databases. At this stage I want you to know just enough so as to be able to learn SQL…how to retrieve data from a database.
That is the beauty of using sqliteonline.com, it allows you to concentrate on running SQL statements and not creating and managing a Database.
So, now, not only have you worked out how to use the Application that will run your practice SQL, but you have also created some SQL and executed it to get some data back!
In this section, you will have learned:
- You do not need to understand the workings of a DBMS to learn SQL
- How to use an application that runs just SQL
Prev lesson ⇒ 1. Intro to Beginners SQL
Next lesson ⇒ 3. Database Basics