In this page you will get crash course on working with a MySQL database and You will learn the following
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.
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.
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
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.
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.
You usually need 5 pieces of data to connect to any database. They are:
Figure it out! You will likely need at least 4 of the pieces of information we gave you above.
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.
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.
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.
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:
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,
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.
Take 5-minutes (not more) to Google and figure out how to write a MySQL Select statement. Once you are done, try the following:
Take 5-minutes (not more) to Google and figure out how to write a MySQL Update statement. Once you are done, try the following:
Take 5-minutes (not more) to Google and figure out how to write a MySQL Delete statement. Once you are done, try the following:
Take 5-minutes (not more) to Google and figure out how to write a MySQL Insert statement. Once you are done, try the following:
SQL keywords can be written in either uppercase or lowercase. But table names and column names should match whatever you used when creating them.
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.
SELECT * FROM celebrities WHERE NAME='Viswanathan Anand'
SELECT * FROM professions WHERE profession_name='chess player'
SELECT * from celebrities ORDER BY NAME ASC
SELECT profession_id, COUNT from celebrities GROUP BY profession_id
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:
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 -