Welcome to DB Training Application

In this page you will get crash course on working with a MySQL database and You will learn the following

  1. Get set up with Tools to connect to databases
  2. How to connect to a MySQL server
  3. Observe the structure of the database (pay attention to names!)
  4. Practice the four most commonly used sql queries with a real database
  5. Practice the some of the most used keywords to organize (where, orderby, groupby)

Do not skip this page activity because other web pages of this application will show you a bunch of graphs and numbers and you will be asked to validate the numbers displayed. The only way you can validate the numbers is if you know how to perform SQL queries.


1. Approach

Our approach here is quite different from what you are used to. In most SQL tutorials, you will learn how to install a database, then you will create a database, then some tables and populate it with data. Well, all that knowledge is nearly useless for QA engineers. In most companies, you will get access to an already set up and functioning database that the application under test uses. So, to keep things realistic, in this ticket, we will limit your learning only to the things you will use most often. For the rest, we will rely on your ability to Google and learn new things when you hit new uses cases.


2. Setup

Install DBeaver and DBVisualiser

At your next job, you will likely be asked to connect to a database. The database will likely sit on a server somewhere. You will need to install a client to connect with the database. A client in this context is simply some software that lets you

  • Log in to the database
  • Examine the database structure
  • Ask the database for data you want (using a database language called SQL)

For this example, I am going to make you use phpMyAdmin for which you can simply use the browser. But I also want you to follow along with DBeaver and DBVisualiser too. So please install DBeaver and DbVisualizer. BTW, all three are clients and do pretty much the same.


3. Connect to the database

We have setup up a really simple database for you. It has just two tables and less than 50 rows in it! We did it so you can actually feel comfortable when starting. The drawback, of course, is that this is not very realistic. But we felt it is a good place for you to get started. So come along for the ride.

Database Details

You usually need 5 pieces of data to connect to any database. They are:

  1. Server: db4free.net
  2. Port: 3306
  3. Database: qtrtrainer
  4. Username: trainee
  5. Password: Qxf2Training
Connect with phpMyAdmin
  1. Visit phpMyAdmin
  2. Use the username and password to connect
  3. Once you connect, you will be logged in. There will be a left menu listing the databases available and you should notice qtrtrainer database listed over there
  4. Click on qtrtrainer
  5. Now click on the SQL tab (you see tabs like Structure, SQL, Search, Query, etc?)


Connecting with DbVisualizer and DBeaver

Figure it out! You will likely need at least 4 of the pieces of information we gave you above.

I logged in. Why is there so much on the UI??

Well, most features in the tool you are using is for database administrators. After all, these are highly specialized tools used by database admins. Most of the features will not used by QA. We will just need the basic features.


4. Observe the structure of the database

Expand the entities in the left menu and look at the structure of the database. I am hoping you realize it is a database about celebrities.

  • Can you identify the tables in the database?
  • Can you list out the columns present in each table?
  • Did you get annoyed by the way we named some column? (tip: even when you are presented with something new, you should not be turning off your QA brain that spots errors and inconsistencies)

A database has tables and views and indexes. For now, don't bother about views and indexes. Its good enough if you know tables. Tables are just a collection of rows and columns. You know, like a worksheet in Excel.


5. The four most used queries

A query is just a fancy way of saying we are asking the database for some data. The language we will use is called SQL (literally Structured Query Language). SQL's syntax is highly intuitive.
There are only four operations (or transactions) that a computer can do with a piece of data. It can:

  1. Create
  2. Read
  3. Update (tip: even when you are presented with something new, you should not be turning off your QA brain that spots errors and inconsistencies)
  4. Delete

The four operations together are called CRUD operations. You will hear this term in multiple contexts. In the context of databases, you can imagine that SQL will let us perform the four CRUD operations. In SQL land,

  • Create is done with an INSERT statement
  • Read is done with a SELECT statement
  • Update is done with a UPDATE statement
  • Delete is done with a DELETE statement

Let us try each of these. At this point, you should be in the SQL tab of your phpMyAdmin interface.
Tip 1: Don't be scared of shy about trying commands. We own the database and we can fix anything you break.

SELECT statement

Take 5-minutes (not more) to Google and figure out how to write a MySQL Select statement. Once you are done, try the following:

  • Get all the professions listed in the professions table (Hint: The SQL for this looks something like SELECT * FROM professions)
  • Get all the celebrities we have listed in the celebrities table
  • Did you notice anything wrong with the celebrity birthdays?
  • Did you notice 3 fake celebrities towards the end?
UPDATE statement

Take 5-minutes (not more) to Google and figure out how to write a MySQL Update statement. Once you are done, try the following:

  • Update any one celebrity's birthday to be real (Hint: The SQL for this looks something UPDATE celebrities SET birthday=Date('1969-12-11') WHERE NAME='Viswanathan Anand'; )
  • We purposely made the previous question hard! You had to set the date in the right 'datatype' for MySQL to accept it! So if you struggled with 5.b.1, its completely ok.
DELETE statement

Take 5-minutes (not more) to Google and figure out how to write a MySQL Delete statement. Once you are done, try the following:

  • Delete one of the fake celebrities (Hint: The SQL for this looks something like DELETE FROM celebrities WHERE NAME='dashing doe';)
INSERT statement

Take 5-minutes (not more) to Google and figure out how to write a MySQL Insert statement. Once you are done, try the following:

  • Insert a new fake celebrity so the next trainees have something to delete! (Hint: The SQL for this looks something like this: INSERT into celebrities (NAME) VALUES ('Elegant Elephant'))

SQL keywords can be written in either uppercase or lowercase. But table names and column names should match whatever you used when creating them.


6. Organizing the results

Databases return rows of whatever you want. The problem is, we usually need to re-organize this data into something meaningful that a computer or a human can understand. The most common operations you perform to organize the results are - filtering, ordering and grouping. In this section, we will let you practice these operations using SQL.

  • Get all celebrity details for the celebrity whose name is 'Viswanathan Anand' (Hint; Use the WHERE clause.So something like this:
    SELECT * FROM celebrities WHERE NAME='Viswanathan Anand'

  • Get all chess players (Hint: Notice the profession_id column is a cryptic number? Its actually the id of the profession of the celebrity. So first, figure out what the id for chess player is by doing
    SELECT * FROM professions WHERE profession_name='chess player'
    You will notice the id returned is 1. Use this information to query the celebrities table. SELECT * FROM celebrities WHERE profession_id=1. If you do this well, you will see the last three world champions - Carlsen, Anand and Kramnik listed)

  • Get the list of celebrities in alphabetical order. (Hint: Use the ORDER BY clause. So something like
    SELECT * from celebrities ORDER BY NAME ASC

  • Get a count of celebrities for each profession (Hint: Use the COUNT and GROUP BY clauses. So something like
    SELECT profession_id, COUNT from celebrities GROUP BY profession_id
    The count is an example of an 'aggregate function' that operates on several rows of data to return one aggregate value.

  • Hmmm ... the output of 6d was hard to read because it showed profession id and count. Wouldn't it be better to get profession name and count? Well, sure. Whenever you need data from two separate tables, you perform something called a JOIN. We will be doing something called an 'inner join'

Before you look at our solution, we encourage you to go to YouTube (not just Google) and spend about 60-minutes understanding joins. You don't need to be an expert. Just know enough to execute some commands.

SELECT t2.profession_name, COUNT(*) FROM celebrities t1 INNER JOIN professions t2 ON t1.profession_id = t2.profession_id GROUP BY t1.profession_id


Ha! If you are new to SQL, I bet you a bit flustered. Don't worry. We didn't go Rest of the fucking owl on you. With SQL, it is important to understand that any complex query is simply a chain of simpler queries. So here is the breakdown:

  • we need columns from two tables ... so we need a join. That will look like INNER JOIN professions t2 ON t1.profession_id = t2.profession_id
  • we need to aggregate by profession id. So that looks like GROUP BY t1.profession_id
  • we need a count. This one is hard, but since COUNT operates on rows, we don't bother specifying the columns to retrieve. Instead, we just ask the database to retrieve all columns and then count it.
  • we then added an (optional) alias to the table names to make them easier to refer to in code

7. Conclusion

Being able to interact with a database will give you QA super-powers ... especially when working with data-heavy applications. Believe it or not, by executing the statements we showed you in the hints, you have almost everything you will need to succeed. Complex SQL queries are nothing more than simple SQL queries chained together. So if you know how to execute these statements we showed, you are well on your way to writing complex SQL statements.

As a personal note, we have been using databases for over a decade. We still don't know the syntax. But we remember the concept of CRUD and what we want to do with the data. So every time We work with a database, we simply Google for how the CRUD statements syntax looks like and continue.

© Arunkumar Muralidharan 2018 -