Interview Questions & Answers

SQL Interview questions for Manual testing | 1 – 20

Now-a-days SQL knowledge has become a must-have for Software Testers. Before diving into SQL, one of the common interview questions is ‘How would you rate yourself in SQL on a scale of 5 or 10?’ Depending on your answer, it can either make or break your opportunity. I have worked on several different applications and some require strong SQL verification skills, some of them required medium skills, and for some, no SQL knowledge. There is a horde of Manual testers out there, i.e. to stand out, AT LEAST you need to be good at SQL. In this article, we will get you started with SQL basics which will act as a pre-requisite to our future articles!

SQL Interview questions for Manual Testing

Question-1. What is SQL?

SQL stands for Structured Query Language, used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, update, insertion and deletion of data from a database.

Question-2. What is the difference between SQL and PL/SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90’s. It adds procedural features of programming languages in SQL.

Question-3. What is a constraint in SQL?

Constraint, as in limitation or check, can be used to specify the limit on the data type of any table column. It can be specified while creating or altering the table. Common constraints – NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY.

Question-4. What is a Primary key?

It’s like your Identity proof – PAN, Aadhaar card number or License number. The idea is to uniquely identify each row in a table using this ‘Primary key’. Usually it’s a column (or columns) that has completely unique data throughout the table, i.e. each row in a table can be uniquely identified by a Primary Key. E.g. Customer_ID.

Question-5. What is a Foreign key?

As the name suggests – foreign means outside the current table. Foreign key in a database is a key from another table that refers to the primary key, in the table being used. A primary key can be targeted by multiple foreign keys from other tables. But a primary key does not necessarily have to be the target of any foreign keys.

  • Table 1: Employee | Columns – Emp_ID (Primary key), Emp_Name
  • Table 2: Manager | Column – Manager_ID (Primary key), Manager_Name, Emp_ID (Foreign key referring to Primary key of Employee table)

Question-6. What is UNIQUE key constraint?

Same as primary key in the sense – Unique key constraint uniquely identifies each record in the database table. A column defined with UNIQUE constraint cannot have duplicate values. The difference: UNIQUE constraint allows NULL values + there can be many unique constraints defined per table, but only one Primary key constraint.

Question-7. What is difference between UNIQUE and PRIMARY KEY constraints?

  • A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.
  • The columns defined as Primary Key cannot contain NULL value (defined NOT NULL), whereas a column defined as a UNIQUE KEY can also have NULL values.
  • By default primary key creates a clustered index on the column, whereas unique creates a non-clustered index.

Question-8. What is a Procedure?

A set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

Question-9. What is Database Trigger?

In English – Trigger is to Start, Initiate or Activate. Putting it in database perspective, a Database Trigger is a procedure (set of SQL and PL/SQL statements) that is automatically executed (activated/initiated) in response to any depending database action such as insert, update or delete from a table. Mainly, trigger helps to maintain the integrity of the database.

Question-10. What is an Index in SQL?

An index is a performance tuning method, i.e. faster retrieval of records from the table. How? For each record in a table, an Index is created using one or more columns of the table. Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.

Question-11. Explain View in SQL

A ‘virtual’ table based on one or more actual tables (a subset of data contained in a table). Often used to restrict data access, to make complex queries easy, provide data independence or to present different views of the same data. Please note that Views do not contain or store data. View can have data of one or more tables combined, depending on the relationship between tables.

Question-12. What is JOIN?

JOIN keyword is used to query data from more than one table based on the relationship between the fields (columns) of the tables. Keys play a major role when JOINs are used.

Question-13. What is the proper order for SQL SELECT statement?

The order is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Only the SELECT and FROM clause are mandatory.

Question-14. What is the usage of DISTINCT keyword? Or How to avoid duplicate records in a SELECT query?

The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value.

Question-15. What is difference between DELETE, TRUNCATE and DROP?

  • DELETE: delete selected rows from the table, WHERE clause can be used for conditional parameters. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
  • TRUNCATE: delete ALL rows from the table >> auto-commits, i.e. cannot be rolled back. Database triggers do not fire on TRUNCATE
  • DROP: delete a table from the database and operation cannot be rolled back.

Question-16. What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table.

  • UNION will omit duplicate records whereas UNION ALL will include duplicate records.
  • The performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates.

Question-17. What is the difference between BETWEEN and IN condition operators?

  • BETWEEN: used to display rows based on a range of values
  • IN: used to check for values contained in a specific set of values.

Question-18. What is the difference between the WHERE and HAVING clauses?

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BY is used:

  • The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
  • The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

Question-19. What is the difference among NULL value, zero and blank space?

  • NULL: a value which is ‘unavailable, unassigned, unknown or not applicable’
  • ZERO: a number
  • BLANK SPACE: a character

In fact two NULLS are also not equal, i.e. one NULL value is NOT equal to another NULL value.

Question-20. What is the difference between the RANK() and DENSE_RANK() functions?

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, consider the set {25, 25, 50, 75, 75, 100}

  • RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie). RANK() will return {1, 1, 3, 4, 4, 6} — note that the values 2 and 5 are skipped.
  • DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie). DENSE_RANK() will return {1,1,2,3,3,4}.

Now that you are good with SQL basics, we will look at SQL Joins and practical SQL queries in our future articles!

Fast Forward: SQL Interview Questions and Answers for Manual Testing | 21 – 33

One thought on “SQL Interview questions for Manual testing | 1 – 20

Leave a Reply

Your email address will not be published. Required fields are marked *