SQL Interview Preparation : QA Engineer Position

Question

I have interview with enterprise company for QA Engineer(New Grad-Mid level experience) position. I was told i would expect some questions on SQL. The company is eCommerce shopping portal. So what kind of questions do i expect for SQL coding ? . DO i need to learn how to code complex queries? Any inputs would be appreciated. Please provide links which you think can be helpful.

Yes i found similar question on StackOverflow, but i wanted to know important SQL topics from QA Engineer Perspective

Thanks

Answer 1

Here is my list of basic SQL that I would expect anyone who writes SQL for me to be able to do.

1.First a straight up select with no joins (and no select *)

SELECT field1, field2 
FROM table1

2.You should know how to combine two or more tables and get records that are in all the tables

SELECT a.field1, b.field2
FROM table1 a
JOIN table2 b ON a.id = b.id

3.You should know how to combine two or more tables and get records that are in all the tables but return only one record from the table with the many side of the one-to-many relationship

SELECT a.field1, b.field2
FROM table1 a
LEFT JOIN   (
                SELECT id, MIN(Field2) as Field2 FROM table2 
                GROUP BY id
            )b 
                ON a.id = b.id

4.You should be able to get the records in one table but not in an associated table

SELECT a.id, a.field1
FROM Table1 a
WHERE NOT EXISTS    
    (SELECT *
    FROM TABLE2 b
    WHERE a.ID = b.ID)

5.You should be able to Aggregate data for a report

SELECT a.field1, b.field2, Sum(Field3) 
FROM table1 a
JOIN table2 b ON a.id = b.id
GROUP BY a.field1, b.field2

6.You should be able to insert one record to a table

INSERT TABLE1 (Field1, field2)
VALUES ('test', 1)

7.You should be able to update one record in a table

UPDATE table1
SET Field1 = 'mytest'
WHERE ID = 10

8.You should be able to delete one record in a table

DELETE table1
WHERE ID = 10

9.You should be able to insert a group of records to a table without a cursor

INSERT table1 (field1) 
SELECT field1 FROM table2

Or

INSERT table1(field1) 
SELECT 1 
UNION ALL 
SELECT 2

10.You should be able to update a group of records in a table without a cursor

UPDATE t1
SET field1 = t2.field2
FROM table1 t1
JOIN table2 t2 on t1.id = t2.id
WHERE t2.field3 = 'CA'

11.You should be able to delete a group of records in a table without a cursor

DELETE table1
WHERE State = 'VA'

12.You should be able to perform multiple actions in one transaction and handle error trapping

BEGIN TRAN
BEGIN TRY

    INSERT table1 (field1)
    VALUES ('a')

    INSERT table2 (field2)
    VALUES ('b')

    COMMIT TRAN
END TRY

BEGIN CATCH
    ROLLBACK TRAN
    PRINT 'oops'
END CATCH

13.You should be able to create union of records and know when to use UNION vice UNION ALL (Union when you will have records that are duplicated in the two parts of the union and you want to filter them out, UNION ALL when the recordsets would be mutually exclusive or you want to see the dups. UNION ALL is faster so should be used if at all possible.)

SELECT Field1 from table1 
UNION  
SELECT Field1 from Table2

SELECT Field1 from table1 
UNION ALL 
SELECT Field2 from Table2

14.You should be able to vary the data for one field based on some criteria (using CASE)

SELECT id,  CASE    WHEN field1 = 'a' THEN 'Good'
                    WHEN field1 = 'b' THEN 'BAD'
            END as Status
FROM table1 

15.You should be able to write an IF Statement. (this assumes it is in a stored proc and the variables are sent in as input variables)

IF @test = 1
    BEGIN
    SELECT field1 FROM table1
    END
ELSE
    BEGIN
    SELECT field3 FROM table2
    END

Answer 2

I would expect this to vary depending on the company. One company may expect basic knowledge and eagerness to learn for a position they are hiring a new graduate for. Other companies may expect you to have much more advanced knowledge walking in the door.

As a QA Engineer, you will likely be devising and running tests that test for bugs and performance problems. The complexity of the queries you will need to write will depend on the complexity of the database that you will be QAing. If they have a very complex structure, the queries you will be writing to test scenarios will by necessity be complex.

Knowing more complex SQL queries and the difference between them (inner and outer joins, unions, functions that are specific to database platforms and not ANSI compliant, etc.), limitations of different database platforms would all come in useful in an interview. Whether that knowledge is required for this particular position is impossible to say.

Answer 3

As mentioned, it depends to some extent on how senior the position is and exactly what the company expects from you. IMO, you should know how to discuss (but not write actual SQL for):

1. What an index is
2. What a normalized database is, and how to normalize a DB
3. What a transaction is
4. How to backup and restore a SQL DB
5. A general idea of what profiling tools, like SQL Server Profiler, do
6. Stored procedures (sprocs)
7. Database security and permissions basics
8. Data types in SQL
9. What SQL injection is and how to test for it

Bad indexes and bad (de)normalization can cause performance issues, and you might find some interesting bugs if someone fails to use a transaction when they really should have used one. Being able to back-up and restore a DB is because QA often wants to use specific sets of data (e.g., run the program against a back-up of the production DB to test performance when the server is full). Profiling the DB can be very handy when debugging; it's helped me locate race conditions in the past. You should know how to tell if the wrong people can access and update the DB (and on the job might need to be able to give yourself SQL server permissions to do your job), and you should know all the data types that can be used (and should be used, and why). SQL injection is basic security info.

Normalized DBs in particular have come up quite a few times during interviews. You can really impress someone by pointing out that a DB in a SQL example ought to be normalized and what the performance, space, and maintainability benefits will be, and then sketching out a diagram of a normalized set of tables that does the same thing before continuing on with the problem.

You should know how to write this SQL well:

1. Basic SQL selects / updates / deletes
2. Selects where you left-hand JOIN several tables together (and should be able to discuss the other kinds of joins - unlikely that you will use them)
3. TOP, COUNT, ORDER BY (ASC / DESC), GROUP BY, and UNION.
4. Create and drop a table

During an interview, it's unlikely to get more complicated than joining a few tables together or testing for specific key words.

I think it's pretty unlikely that you'll get a question outside of this list for a QA position, and probably this is more than you will actually need for the interview (but pretty representative of what you may need on the job). I'm marking "community wiki", however, in case I missed anything.

Answer 4

Just as a couple of general ideas:

SQL injection would be a topic I'd review and possibly expect a question or two about how you'd check that this is handled properly.

You may be asked for how should tables look for various normalized forms, when can indexes be a good idea and when are they bad, and basic query knowledge like what are the different kinds of joins that exist and when do they make sense to use each of them.

Answer 5

Give an example of using a cross join in the real-world.

Answer 6

You should expect something basic:

SELECT INSERT UPDATE DELETE

TABLE CREATE DROP TRUNCATE

COUNT and symilars...

You can study it from: http://www.w3schools.com/sql/sql_intro.asp