What are SQL Interview Questions for Testers ? | SQL Queries for Tester

In my previous articles I explained about different interview questions related to SQL.I found so many comments from different users about SQL Interview Questions for Testers. The tester requires a SQL knowledge to test application in a proper way. This article basically gives you SQL Interview Questions for Testers so that tester will not face any difficulty while answering in the interview. The manual tester as well as automation tester requires SQL knowledge to perform the testing properly. I will try to explain the SQL Interview Questions for Testers in this article:

1.What is SQL?[100 % asked SQL Interview Questions for Testers]

Answer: 

SQL Stands for Structured Query Language which is specially designed to communicate with databases. SQL pronounced as Sequel is very widely used language in most of the database management systems like Oracle,MySQL,PostgreSQL etc. SQL provides us  a simple and efficient way of reading,writing,executing the data from the system. This is one of the SQL Interview Question ever asked in interviews

2.How to represent comments in oracle?

Answer:

There are following 2 ways for commenting in oracle:

1.Single Line comment: Two dashes (–) before beginning of the line

2. Multi-line comment/Block comment: WhenWhen user wants to comment multiple line /* */ operators are used.

3.Which are different statements in Data Definition Language in SQL?[100 % asked SQL Interview Questions for Testers ]

Answer:

There are following different statements in Data Definition Language:

1.1.Create:  Create Command is used to create new table,new view or any database objects from the table.

1.2.Alter: Alter Statements are used to modify the existing database object such as add new column in the table,remove column from the table,enable disable constraints from the table.

1.3.Drop:Drop statement deletes the entire table,view and other database objects.

4.Which are different statements in Data Manipulation Language in SQL?[100 % asked SQL Interview Questions for Testers ]

Answer:

There are following statements in Data Manipulation Language:

2.1 Update:Update statement of SQL are used to update the records from the table

2.2 Insert: Insert statement of SQL are used to insert the records in the table.

2.3 Delete : Delete statement in SQL are used to delete the records from the table.

5.Which are different Data Control Statements in SQL?[100 % asked SQL Interview Questions for Testers]

Answer:
There are following data control statements in SQL:

3.1 Grant:Grant command gives the privilege to the user.

3.2 Revoke: Revoke command takes back the privileges from the user.

6.How to create a table in SQL? Explain with examples.[100 % asked SQL Interview Questions for Testers ]

Answer:

Following syntax is used to create a table in SQL :

1) Syntax:

CREATE TABLE <Table_Name>

(Column_Name1 Data_Type (Size),

Column_Name2 Data_Type (Size),

…Column_NameN Data_Type (Size)

);

Example:

CREATE TABLE Student

(RollNo Number (10),

FName Varchar2 (15),

LName Varchar2 (15),

Class Number (10),

DOB Date,

Gender Varchar2 (15));

Download PPT for SQL interview questions for testers

7.What is mean by database testing?[100 % asked SQL Interview Questions for Testers]

Answer:

The database testing is nothing but checking the database with its integrity and its performance.Following aspects are considered in database testing:

• Testing of Data Integrity
• Testing of Data Validity
• Data base related performance
• Testing of functions, procedure and triggers

8.Explain Distinct in SQL with example.

Answer:

DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.

Syntax:

SELECT DISTINCT column_name(s)
FROM table_name;

Example:

Select distinct emp_no from Employee;

The above statement will select the distinct employees from table named Employee.

9.What are different steps in database testing?[100 % asked SQL Interview Questions for Testers ]

Answer:

There are following different steps in database testing:

• Constraint Check
• Validation of a Field size
• Stored procedure
• Matching application field size to database
• Indexes for performance based issues

10.What is Union Operator?[100 % asked SQL Interview Questions for Testers ]

Answer:

Union Operator combines the result of 2 or more tables and fetches the results of two select statements.Union operator eliminates the duplicates from the table and fetches the result.For each duplicate row in table only one row is displayed in the result.By considering the performance of SQL using union is not preferable option but if there is situation where user wants to remove the duplicate data from two or more table the use of Union is preferable.

Example:

Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;

Union

Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;

11.What is mean by SQL Constraints? Whcih constraints are used in SQL?[100 % asked SQL Interview Questions for Testers ]

Answer:

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.

There are 5 major constraints are used in SQL :

• NOT NULL: That indicates that the column must have some value and cannot be left null
• UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
• PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.
• FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
• CHECK: It is used to ensure whether the value in columns fulfills the specified condition

12.How to add SQL not null constraint in SQL?[100 % asked SQL Interview Questions for Testers ]

Answer:

Following is the process to add SQL Not Null Constraints with real life example.The Sql not null constraint is created at the time of creation of the table.

   Real life Example:

The following SQL enforces the “RollNo” Column to NOT accept NULL values:

CREATE TABLE Student

(

RollNo Number (10) NOT NULL,

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (20)

);

13.What is mean by data driven test?[100 % asked SQL Interview Questions for Testers ]

Answer:

In a data-table, to test the multi numbers of data, data-driven test is used. By using this it can easily replace the parameters at the same time from different locations.

14.What are transactions and controls in SQL?

Answer:

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

• COMMIT: It is used to save all changes made through the transaction
• ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before
• SET TRANSACTION: Set the name of transaction
• SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

15.How to remove duplicate rows from table?[100 % asked  Interview SQL Questions ]

Answer:

First Step: Selecting Duplicate rows from table

Tip: Use concept of max (rowid) of table. Click here to get concept of rowid.

Select rollno FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

Step 2:  Delete duplicate rows

Delete FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

16.What is difference between unique and distinct?(90% asked in Interview SQL Questions )

Answer :

There is no difference between unique and distinct keywords apart from one difference.unique is applied before insertion and retrieval. It consists  of non duplicate values.if unique constraint is given it does not take duplicate values.distinct is used in retrieval it gives the suppressed row(ex if two rows are same it will show single row and non duplicate row) therefore distinct is the combination of suppressed duplicate and non duplicate rows.Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.

So there is no functional difference between Unique and distinct both have same functionalities.

17.What are views in SQL?Explain types of Views in SQL?[100 % asked SQL Interview Questions for Testers ]

Answer:

Views:

Views are nothing but the logical structure of the table where we can fetch the data from different tables or same table.

There are 2 types of views in Oracle:

1.Simple View:Simple view has been created on only a single table.

2.Complex view:Views which are created using more than 1 table which has joins clauses are known as complex views.

18.What is purpose of Normalization?[100 % asked SQL Interview Questions for Testers ]

Answer :

Normalization is used for following purpose:

• To Eliminate the redundant or useless data
• To Reduce the complexity of the data
• To Ensure the relationship between tables as well as data in the tables
• To Ensure data dependencies and data is logically stored.

19.What are Explicit Indexes?

Answer:

The indexes which is created by user are called as explicit indexes.You can say the indexes which are created by ‘Create Index’ statement are called as Explicit indexes.

Syntax:

create index indexname on tablename(columnname);

Example:

Create index IND_Employee_ID on Employee(Employee_ID);

20.What is difference between Truncate ,Drop and DELETE?

Answer:

1.Drop:

1.Drop command is DDL command which is used to delete the object from the database.

2.We can not use the “ROLLBACK” after using drop command.

3.Drop command free’s the space of database object.

4.Drop table table_name;

2.Truncate:

1.Truncate command is DDL command which is used to truncate the data from the database table.

2.We can not use the “ROLLBACK” after using Truncate command.

3.It free’s the space of database object but the structure remains same and memory of structure also remains same.

4.Truncate table table_name;

3.Delete:

1.Delete command is DML command which is used to delete the records from table.

2.We can use Rollback to Rollback the records from the table.

3.Delete command not free’s the memory space.

4.Delete table table_name where condition;

21.Explain the data loading steps in database testing?

Answer:

Following steps need to follow to test data loading

• Source data should be known
• Target data should be known
• Compatibility of source and target should be checked
• In SQL Enterprise manager, run the DTS package after opening the corresponding DTS package
• You have to compare the columns of target and data source
• Number of rows of target and source should be checked
• After updating data in the source, check whether the changes appears in the target or not.
• Check NULL and junk characters

22.What is Rownum in Oracle?

Answer:

1. ROWNUM is magical column in Oracle which assigns the sequence number to the rows retreives in the table.
2. To limit the values in the table you can use rownum pseudocolumn
3. ROWNUM is nothing but logical sequence number given to the rows fetched from the table.
4. ROWNUM is logical number assigned temporarily to  the physical location of the row.
5. You can limit the values in the table using rownum
6. ROWNUM is also unique temporary sequence number assigned to that row.

23.How to write test-cases in database testing?

Answer:

Writing a test-cases is like functional testing. First you have to know the functional requirement of the application. Then you have to decide the parameters for writing test-cases like

• Objective: Write the objective that you would like to test
• Input method: Write the method of action or input you want to execute
• Expected: how it should appear in the database

24.What are properties of the transaction?

Answer:

      Properties of transaction are known as ACID properties, such as

• Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
• Consistency: Ensures that all changes made through successful transaction are reflected properly on database
• Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
• Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure

25.How to test database manually?

Answer:

Testing the database manually involves checking the data at the back end and to see whether the addition of data in front end is affecting the back end or not, and same for delete, update, insert etc.

26.What are different database Environments used in any project?(90% asked in Interview SQL Questions )

Answer:

The Project to project database environment varies.But the following is basic environment structure used for projects.

1.Development Environment:

In Development Environment all developer works and development work is been done on development environment.

2.Test Environment:

Developers does not have access of test environment.After development is done the code is migrated to Test Environment.Testing team is working on Test environment and execute black box as well as white box test cases on this Environment.Sometimes System Integration Testing (SIT) is also done on this Environment.

3.UAT Environment:

UAT stands for User Acceptance Testing.On this Environment the Customer side testers tests the software and executes User Acceptance Test Cases.

4.Performance Testing Environment:

On this environment the performance tester tests all performance related issues on this environment. This environment contains very huge data and performance tester will try to break the system using that big data.

5.Production Environment:

On this Environment actual user works and uses the software..

I have given the 25 most important SQL Interview Questions for Testers so that testers will get the idea about the SQL used in testing. Hope you like this article on SQL Interview Questions for Testers.If you like this article or if you have any suggestions or concerns with the SQL Interview Questions for Testers article please comment in comment section.