Now…let’s get down to it!
This lesson is the fun part 🙂 ! You will be learning how to retrieve (SELECT) data from a database by actually running SQL statements, which you can cut and paste and run right there and then!
Table of contents
- Running your SQL
- Try it Yourself
- Some SQL rules to be aware of
- Retrieving your data
- Filtering your data with WHERE
- Summary
- Test Yourself
1. Running your SQL
SQL Fiddle Application
In lesson 2. Where to run your SQL, you learned how to create a Table and Data and how to run SQL statements to query the table, using SqlFiddle.com. That is the application we will be using in this lesson.
To make it easier to read this tutorial and run the SQL at the same time, it is a very good idea to have 2 windows side by side, one with this tutorial and one with SqlFiddle.com running. If you don’t know how to, you can actually drag windows (window tabs) and snap them into left or right position…as per this YouTube video https://www.youtube.com/watch?v=_LWFmLe3JS4
You should now be looking at something like the following…great eh 🙂 !
2. Try it! exercises
All the way through this lesson I will be teaching you some basic SQL and once you have digested the content…just go ahead and run the Try it! exercises in SqlFiddle.com! I strongly suggest you complete these, as this will certainly aid your retention of the lesson.
3. Some SQL rules to be aware of
Sorry, I just quickly need to show you some boring stuff 🙁 that you are better off knowing before proceeding…
The syntax of SQL is quite simple, as is the language itself, however, there are a few simple rules that you will need to know:
SQL is NOT case sensitive
You will find that the SQL I have provided for you to run has some UPPERCASE words and some lowercase ones, this is just to make it easier to read. The uppercase ones are keywords (a reserved word used by the SQL language…more on this later). However, you can write your SQL in all uppercase, lowercase or a mixture of both, it really doesn’t matter…your preference!
White spaces
Extra white spaces in SQL statements are ignored when executing the statements. The statements themselves can be written on one long line or written over many lines, as you will see in the examples
Terminating statements
When executing SQL, multiple statements must be separated by a semicolon ; . Single statements do not need them, however, it is a good idea to get used to putting a semicolon in at the end of a statement even if you do not have multiple ones 🙂 . Again…examples will self explain
Comments
When writing many lines of code (which can turn into hundreds and even thousands in production systems), it is a very good idea to apply “comments” to the code to explain what is going on. This is done by using either 2 hyphens (- -) together at the start of a line or using /* and */ at the beginning and end of consecutive lines, e.g. :
-- this is a comment
/*
This is a
series of lines
commented out
*/
/* this is another commented out line */
Keywords
Keywords are reserved words that are used to form part of the SQL language. In the examples used in this lesson, you will see them in uppercase. A list of Keywords can be found here https://www.w3schools.com/sql/sql_ref_keywords.asp
Handling errors
When you run your SQL, you will undoubtedly get errors..we all do! I’m afraid that database / SQL applications (including SqlFiddle) are not the best at telling you what you have done wrong! Don’t panic! Just look over your code and see where you think you may have done something wrong. The code from this lesson works, so if you have copied (cut and pasted) it, then just ensure that it compares correctly to this website 🙂 .
Different DBMS’s SQL
Different Database Management Systems (ORACLE, DB2, MySql, PostgreSQL etc) will have very slight differences to their SQL statements. I have written statements in this lesson as a MySql standard…that work in SqlFiddle.
However, once you start using other particular DBMS, you may notice that statements I have coded do not work! If this is the case…just simply look up the correct statement that you need for your DBMS in Google. Or better still…a brilliant website for Programmers / Developers and Technical Test Analysts to find answers to their technical questions is https://stackoverflow.com/ .
4. Retrieving your data
So…let’s get down to learning some basic SQL.
4.1 Setting up your Table(s) and Data to run SQL statements against
For all the examples I will be showing you in this lesson, you will be creating a temporary table(s) and data to run against. To do this, everytime you see the following box, cut and paste the code that accompanies it and run it from SqlFiddle.com’s Left Hand Pane. This will set you up for the SQL examples that follow:
4.2 Create your first table…
Using what you learned in Lesson 2. Where to run your SQL, cut and paste the following SQL statements into the Left Hand Pane of SqlFiddle.com and click Build Schema
CREATE TABLE account (
account_name VARCHAR (30) PRIMARY KEY,
account_desc VARCHAR (100) NOT NULL,
account_start_date DATE NOT NULL,
account_end_date DATE
)
;
INSERT INTO account
VALUES
('VISA', 'Visa ACCT', '2022-02-01', NULL)
,('CurrentAccount', 'Current Account', '2022-03-01', NULL)
,('SavingsAccount', 'Savings Account', '2022-05-01', '2022-05-27')
;
Now that you have a table and some data set up, let’s run some basic SQL commands.
4.3 Basic SELECT Statement
To retrieve data from a table, you will be using the most common SQL Statement: SELECT.
To select all columns from a Table:
Cut and paste the following code into SqlFiddle’s Right Hand Pane and click Run Sql:
SELECT *
FROM account
;
- You start with the SQL statement SELECT so as to select the data you want
- The wildcard character * (asterisk) is used to select all the columns from a table
- The FROM clause tells the DBMS the table you are selecting data from, in this case the account table
- The semicolon ; is used to terminate the SQL (SELECT) statement
- As there is no WHERE clause, ALL the account table’s rows will be returned
- Once you click Run Sql, you should have the same results as the Expected Results above
Bravo…you’ve run an SQL statement to retrieve data 🙂 !
4.4 To SELECT specific Columns:
Maybe you don’t want to retrieve all the columns from a table, only specific ones.
SELECT account_name, account_desc
FROM account
;
- The SELECT statement is the same as before, except we have removed the asterisk * and replaced it with the columns we want returning.
- Columns account_name and account_desc are displayed in the Results
- The other columns, account_start_date and account_end_date, are no longer displayed
4.5 Ordering your data
When you retrieve data from a table, it will be in no specific order! It will be stored on the table as it was inserted onto it, so when you retrieve it, it will probably be in that order.
To sort your data in a particular order, you need to use the ORDER BY clause, specifying the column you wish to sort on. Let’s have a look at 2 examples, one to sort in Ascending order and another to sort in Descending order.
ASC (ascending)
SELECT *
FROM account
ORDER BY account_name ASC
;
- The SELECT statement now includes an ORDER BY clause
- The clause is followed by the name of the column you wish to sort on. In this instance account_name
- The ASC means that the data will be sorted in Ascending order, e.g. from A to Z or from 1 to 999…
- So, when you retrieve the data, it will be in ascending account_name order: CurrentAccount, SavingsAccount, VISA
DESC (descending)
SELECT *
FROM account
ORDER BY account_name DESC
;
- The SELECT statement is the same as the previous one, EXCEPT now, instead of ASC, DESC is at the end of the ORDER BY clause
- The DESC means that the data will be sorted in Descending order, e.g. from Z to A or from 999… to 1
- So, when you retrieve the data, it will be in descending account_name order: VISA, SavingsAccount, CurrentAccount
Right…I hope this is all making sense! If so, you’re doing GREAT 🙂 !
We have come a long way in just this section of the lesson alone. You have learned how to retrieve data from a table and how to retrieve it in an ordered manner. I hope you can already see the power that you will possess in knowing SQL!
4.6 DISTINCT
If you do not filter the data (something I will be coming onto in this lesson)…the SELECT statement on its own returns all rows in a table. But say you want to return a certain column and remove duplicates…so as to just see the unique values for that column. Well, this is where DISTINCT comes in…
Create a new table for this example (remember…this code goes in the Left Hand Pane of SqlFiddle.com and you need to click Build Schema):
CREATE TABLE account_transaction (
trans_id INT (7) PRIMARY KEY,
account_name VARCHAR (30) NOT NULL,
trans_date DATE NOT NULL,
payee VARCHAR (30) NOT NULL,
category VARCHAR (40) NOT NULL,
description VARCHAR (100) NOT NULL,
income DECIMAL (7,2) NOT NULL,
expense DECIMAL (7,2) NOT NULL,
reconciled VARCHAR (1) NOT NULL
)
;
INSERT INTO account_transaction
(
trans_id
,account_name
,trans_date
,payee
,category
,description
,income
,expense
,reconciled
)
VALUES
(1, 'VISA', '2022-01-01', 'OpeningBalance', 'Opening Balance', ' ', 2.00, 0.00, 'N')
,(2, 'VISA', '2022-01-01', 'The Gas Company', 'Utilities:Gas', ' ', 0.00, 37.50, 'N')
,(3, 'VISA', '2022-01-02', 'The Big Supermarket', 'Weekly Shop', 'our shopping', 0.00, 52.39, 'N')
,(4, 'VISA', '2022-02-12', 'The Big Supermarket', 'Weekly Shop', 'mom shopping', 0.00, 29.99, 'N')
,(5, 'VISA', '2022-02-17', 'The Football Club', 'Misc Expenses', 'our shopping', 0.00, 30.00, 'N')
,(6, 'VISA', '2022-02-22', 'The Corner Shop', 'Weekly Shop', 'holiday shop', 0.00, 10.46, 'N')
;
Now select all the data from the Table…
SELECT *
FROM account_transaction
;
- For now…you have only done a SELECT which has returned all the data from the table
- You will see that the category column has returned the values: Opening Balance, Utilities:Gas, Weekly Shop and Misc Expenses. With Weekly Shop repeated 3 times
- BUT what if we just want to identify what unique categories there are?
SELECT DISTINCT(category)
FROM account_transaction
;
- We included the DISTINCT word in the SELECT Statement, specifying what column we wanted the unique values of (effectively…duplicates removed)
- Now, you can see that the display shows only the 4 unique categories
4.7 Running multiple statements
You can run more than one SQL Statement at a time. To do that, you just need to place a semicolon ; in between each statement.
select *
from account_transaction
;
SELECT DISTINCT(category)
FROM account_transaction
;
- By placing a semicolon in between the SQL statements, this allows you to run as many Statements all at once
- If you scroll down in SqlFiddle.com, you will now see both Statement’s Results
Phew…Basic SELECT now done! That wasn’t so bad was it? Let’s proceed with looking at how to retrieve only the data you want or need…
5. Filtering your data with WHERE
Example data for this section
For the whole of this section, we will be using the same table and data, as follows. So, to get started, let’s create things and do a SELECT to show the data that you will be working with.
CREATE TABLE account_transaction (
trans_id INT (7) PRIMARY KEY,
account_name VARCHAR (30) NOT NULL,
trans_date DATE NOT NULL,
payee VARCHAR (30) NOT NULL,
category VARCHAR (40) NOT NULL,
description VARCHAR (100) NOT NULL,
income DECIMAL (7,2) NOT NULL,
expense DECIMAL (7,2) NOT NULL,
reconciled VARCHAR (1)
)
;
INSERT INTO account_transaction
(
trans_id
,account_name
,trans_date
,payee
,category
,description
,income
,expense
,reconciled
)
VALUES
(1, 'VISA', '2022-01-01', 'OpeningBalance', 'Opening Balance', ' ', 2.00, 0.00, 'Y')
,(2, 'VISA', '2022-01-01', 'The Gas Company', 'Utilities:Gas', ' ', 0.00, 37.50, 'Y')
,(3, 'VISA', '2022-01-02', 'The Big Supermarket', 'Weekly Shop', 'our shopping', 0.00, 52.39, 'Y')
,(4, 'VISA', '2022-02-12', 'The Big Supermarket', 'Weekly Shop', 'mom shopping', 0.00, 29.99, NULL)
,(5, 'VISA', '2022-02-17', 'The Football Club', 'Misc Expenses', ' ', 0.00, 30.00, NULL)
,(6, 'VISA', '2022-02-22', 'The Big Bank', 'Refund', ' ', 39.00, 0.00, NULL)
,(7, 'VISA', '2022-02-28', 'The Corner Shop', 'Weekly Shop', 'holiday shop', 0.00, 10.46, NULL)
,(8, 'VISA', '2022-03-17', 'The Football Club', 'Misc Expenses', ' ', 0.00, 30.00, NULL)
;
select *
from account_transaction
;
- Right…now we have data for the rest of this section 🙂 !
You will see from the above Expected Result, that you have 8 rows of Account Transaction data to play with. To help you refer back to this Result’s Table, you can view it in a new browser tab by clicking on here.
5.1 SELECT only specific rows: using the WHERE clause
Up to now, when we have done a SELECT we have been bringing back all the data on a table (except for a SELECT DISTINCT). However, what if you just wanted to retrieve some specific data. Well, that’s when we use the WHERE clause.
For the tables we used in our previous examples, we only have a very small amount of data (done purposefully so that you can see what is happening easier 🙂 )! However, in the real world, most Databases hold very large amounts of data, especially the Banking Industry’s databases. Bank Account Transaction Tables will hold multiple millions of Rows.
In order to use something like a Bank Transaction table, we really wouldn’t want to retrieve all the data…we would be looking to retrieve subsets of data. Introducing the WHERE clause.
As part of a SELECT Statement, the WHERE clause is placed after the FROM clause and specifies the criteria of what data will be retrieved. Let’s take a look…
As noted, the WHERE clause comes after the FROM clause and it is also before ORDER BY, HAVING and GROUP BY if they are used.
SELECT *
FROM account_transaction
WHERE expense = 30
;
- If we look at the complete list of rows in Table account_transaction, as per figure 4.5.1 – complete list of account_transaction data, there are 8 rows
- The above WHERE clause is specifying that it only wishes to retrieve rows where the expense = 30, therefore, when the statement is executed 2 rows that match that criteria are retrieved
5.2 WHERE clause operators
In the SELECT statement, above, we used an = (equal) sign in the WHERE clause to retrieve any rows WHERE expense equals 30. This = sign is called an operator, we could, however, have used other operators to help us search for data.
The following are other types of operators:
Operator | Description |
= | Equal |
<> | Not equal. Note: In some versions of SQL this operator may be written as != |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
BETWEEN | Between a certain range |
LIKE | Search for a pattern |
IN | To specify multiple possible values for a column |
In the sections following this one and in lesson 5. More Data Retrieval, we will be exploring some of these in more detail. However, for now, let’s have a play with some of them:
SELECT *
FROM account_transaction
WHERE expense > 30
;
SELECT *
FROM account_transaction
WHERE income <> 0
;
SELECT *
FROM account_transaction
WHERE expense <= 11
;
- Using the code in Try It! we ran multiple statements at the same time, retrieving 3 sets of Expected Results
- The 1st statement only retrieved the data (2 rows) where the expense was greater than 30
- Hopefully, the other 2 statements are self explanatory, where the 2nd statement is using a not equal to operator and the 3rd statement is using a less than or equal to operator
As mentioned before, this tutorial is being taught using the MySql DBMS conventions. I am purposely teaching you the SQL used by this DBMS…which is a great start. I could have tried to teach you all the differences between the different databases, but that would be far too complicated for this simple tutorial
5.3 SELECT WHERE INT / VARCHAR (String, Text) etc
If we want to search on a VARCHAR (Text / String) column, we must put the value we are searching on in single quotes (‘ ‘), not double quotes.
If we are searching on a numeric (INT or DECIMAL) column, then no quotes are needed, as seen previously.
SELECT *
FROM account_transaction
WHERE payee = 'The Big Supermarket'
;
SELECT *
FROM account_transaction
WHERE expense = 29.99
;
- You will see that searching on expense (a DECIMAL) that no quotes are needed for the value
- However, searching on a string column payee (VARCHAR) DOES need quotes
5.4 AND operator
Now, what if you want to search on two columns. Say that you want to search on the category “Weekly Shop”, but only wish to retrieve “Weekly Shop” rows where the cost of your shopping (expense) is greater than £20.00?
SELECT *
FROM account_transaction
WHERE category = 'Weekly Shop'
AND expense > 20.00
;
- By adding the AND operator, you can now filter your searches even more
- The Result shows that even though there are 3 “Weekly Shop” rows on the table, there are only 2 rows where these have an expense value > £20.00
5.5 OR operator
What if you wanted to retrieve data where category is equal to “Weekly Shop” OR “Refund”? Whereby either condition is met so as to retrieve rows.
SELECT *
FROM account_transaction
WHERE category = 'Weekly Shop'
OR category = 'Refund'
;
SELECT *
FROM account_transaction
WHERE category = 'Weekly Shop'
OR payee = 'The Gas Company'
;
- The 1st statement will return rows where the category matches ‘Weekly Shop’ OR ‘Refund’. We are excluding rows that do not match these 2 criteria
- For the 2nd statement we are now looking for rows that match on different columns. We are only returning rows with either category = ‘Weekly Shop’ OR payee = ‘The Gas Company’. NOTE: That no category = ‘Refund’ are now returned
5.6 Let’s have a play with a combination of AND and OR
You can combine the AND and OR operators, but be very careful!
You have seen quite an array of choices you can make to retrieve certain data. Please do not think that there is a restriction on using different operators at the same time. However, great care must be taken when mixing the AND and OR operators.
If, for example, you want to retrieve rows that have:
- a category = ‘Weekly Shop’ OR a payee = ‘The Gas Company’
- AND the expense is greater than £15.00
you would probably think to code something like this:
SELECT *
FROM account_transaction
WHERE category = 'Weekly Shop'
OR payee = 'The Gas Company'
AND expense > 15.00
;
- You might have thought that you would see that all rows retrieved would have an expense > £15.00, but how come there is a row with expense = £10.46 (trans_id = 7) ?
- Well, the problem is with the way the statement has been coded
- With SQL, it executes the AND statements before the OR statements
- So, in this instance, the way the SQL has been written:
- the statement payee = ‘The Gas Company’ AND expense > 15.00 will be executed first, bringing back “The Gas Company” row
- Then, the statement category = ‘Weekly Shop’ will be executed, bringing back all the “Weekly Shop” rows…even if they are less than £15.00
- Hmmm…so how do we get around this? Well, the answer is to use parentheses to explicitly group statements, as follows:
SELECT *
FROM account_transaction
WHERE (category = 'Weekly Shop' OR payee = 'The Gas Company')
AND expense > 15.00
;
- Right then…now we have brought back the rows we were expecting
- We have used parentheses ( ), so as to separate the AND and OR statements
- Now, when things are executed, the rows retrieved must meet both conditions:
- First the condition where category = ‘Weekly Shop’ OR payee = ‘The Gas Company’ which would bring back 4 rows
- And where these 4 rows have an expense > 15.00, and this is only the 3 rows displayed above
This may take a little thinking about, but don’t worry…just have a little play around with a few more statements using both AND and OR together, ensuring you use parentheses, and all will make sense!
5.7 NULL / NOT NULL values
You may have noticed some rows have a (null) value in the reconciled column. This means that there is nothing in this column’s cell…it is empty.
So, how do we retrieve data with a value of null?
SELECT *
FROM account_transaction
WHERE reconciled IS NULL
;
- Yep…you’ve guessed it, all the rows where reconciled are equal to null (IS NULL) are returned
And to retrieve rows where the reconciled is not equal to null:
SELECT *
FROM account_transaction
WHERE reconciled IS NOT NULL
;
- And now…we bring back any rows where reconciled has a value in it 🙂
5.8 LIKE Operator
What if you want to retrieve rows where a column’s text (VARCHAR) data starts with “The Big”? We can use the LIKE operator:
SELECT *
FROM account_transaction
WHERE payee LIKE 'the big%'
;
- By using the LIKE operator with a percent wildcard search %, we have now retrieved only rows that start with The Big
- Using the percent sign we can create a search pattern, e.g. :
If the percent sign is at the end of the pattern, then all rows will be returned starting with “The Big”:
WHERE payee LIKE ‘the big%’
If the percent sign is at the beginning, then all rows ending with “The Big” will be returned:
WHERE payee LIKE ‘%the big’
With a percent sign at the beginning and at the end of a value, then columns containing “The Big” in any part of the cell will be returned:
WHERE payee LIKE ‘%the big%’
SELECT *
FROM account_transaction
WHERE payee LIKE 'the big%'
;
SELECT *
FROM account_transaction
WHERE payee LIKE '%club'
;
SELECT *
FROM account_transaction
WHERE payee LIKE '%super%'
;
- The first statement (where the % sign is at the end of the search value) has brought back rows where payee cells start with “The Big”. Note: as stated before, SQL is not case sensitive. So any value, even if it’s stored with uppercase letters will be retrieved
- The second statement (where the % sign is at the start of the search value), retrieves only rows where the payee ends with the letters club
- Now…the third statement is retrieving any rows which has the phrase super anywhere in the payee column
6. Summary
So…you have learned the basics! Well that’s what I was after, you…learning just the basics at this point in time!
I didn’t want to overload you with advanced SQL, you can do that at a later date. However, you really have come a long way within this short lesson! I hope your head is not spinning too much! Let’s just take stock of where we are…
You will have learned…
- How to use the SqlFiddle.com application to run your practice SQL
- Some SQL rules to be aware of
- The very basics of creating a TABLE and INSERTing data
- The basics of how to retrieve data using SELECT
- How to use ORDER BY to sort retrieved data
- How to run multiple statements
- How to use WHERE so as to filter data to retrieve just the information you want
- How to use different Operators with a WHERE clause
And…some of the common SQL Keywords:
- SELECT
- FROM
- ORDER BY…DESC or ASC
- DISTINCT
- WHERE
- INT
- VARCHAR
- AND
- OR
- NULL / NOT NULL
- LIKE (%)
Have I shown you all that can be learnt using SELECT, ORDER BY, DISTINCT etc…NO! ORDER BY and DISTINCT, for example, can be performed on multiple columns…I didn’t show you that! Why, because I didn’t want to overwhelm you, I wanted to give you a flavour of what can be learned and what I believe is a great start to improving your skills as a Test Analyst.
Hopefully this has given you the confidence to go on 🙂 !
7. Test Yourself
For this test, please do not be put off by the number of questions (27), as this section is a little more involved than the previous one!
Important to do well…
As with the first test, once complete check your answers, if you do not score that well (let’s say not getting at least 22 correct), please either go back over the tutorial’s section again, perhaps making more notes as you go, or re-do the test to ensure you have a good understanding.
It is always important that you understand the section that you have read / learned before you move on 🙂 !
Click here to Test Yourself.
Prev lesson ⇒ 3. Database Basics
Next lesson ⇒ 5. More Data Retrieval