SQL for QA

There is a subset of SQL statements that we need to use, without going into a number of advanced uses of the language.


As a starter,
There are several statements and clauses that are used within SQL, but because I am new to QA, and new to SQL – then these five statements and clauses are the basics that I need to know up front.
 

• SELECT

The basic of all basic queries is the SELECT statement. Use a SELECT statement to select data from a table.
 
Syntax:
SELECT Name from Class _2011
 
Results:
Amila Sampth
Senuri Navodya
Kamak Yasapala
Ayomi Jayaweera

---

• WHERE

A WHERE clause provides a condition to a SELECT statement, so that specific selections may be made. WHERE clauses use specific operators to aid in the selection process. The operators are as follows:

Syntax:
SELECT * from Class _2011 WHERE Position = ‘Student’

Results:
Senuri Navodya

---

• INSERT

If you want to place an item into a table, you can use the INSERT statement. The INSERT statement requires values that will be placed into the table column.
 
Syntax:
INSERT INTO Class 2011
VALUES (‘Nimalka’, ‘Teacher’, ‘28’, ‘Trainee’)
 
Results:
Nimalka
Taecher
28
Trainee

Please note that the Number field value is not an Integer, so it is placed in quotes. I will use this again when I apply the UPDATE statement below.

---

• UPDATE

If you have values in a table that need to be changed, then you can use the UPDATE statement to modify the field.

Syntax:
UPDATE Class _2011 SET Position = ‘Trainee _Teacher’
WHERE Number = ‘28’

Results:
Trainee Teacher will be updated for the number of 28

---

• DELETE

When you have to remove field values from a table, you can use the DELETE statement.

Syntax:
DELETE * FROM Class _2011 WHERE Name = ‘Amal’

Results:
All results of Amal will be deleted

---

In addition…
As we grow in our QA career, and as more database related tasks are placed on our plate, we may need to know a little more than just the working knowledge of typical statements and clauses. Here are a few things that I’ve encountered on particular projects.

Teachers Table

 

• SQL JOIN

Suppose that we have two table and we need to grab data from both of them. We can use a SQL JOIN clause to accomplish this. Here’s a sample of the syntax below.

Syntax:
SELECT Class_2011.Name, Teachers.Name
FROM Class_2011, Teachers
WHERE Class_2011.Subject_Number = Teachers.Subject_Number

Results:
Science, Amal Suraweera
Maths, Amila Sampath