DATABASE TESTING INTERVIEW PART-1

Please go through the most frequently asked database testing interview topics.

Database

A database is the place of storage of the data in the form of tables. Data means information that is very useful. A database is also a collection of 1 or more tables.

Table – a table is a collection of rows and columns.

A cell is an intersection of a row and a column. A column is also called as a field/attribute

A record is also called a row/tuple.

A table is also called as an entity/relation.

Note :-

If we install any of the database related software(s) – we can create our own database, we can create our own tables and we can store the data inside it.
When we install any database software(s) – a part of the hard disk will be designated/reserved to perform database-related activities.
A database can also contain other database objects like views, indexes, stored procedures, functions, triggers etc, apart from tables.

Some of the database software(s) we have are,
Oracle, SQL Server, DB2, Sybase, Informix, MySQL, MS – Access, Foxbase, FoxPro

Among the above database software – some of them are DBMS and some of them are RDBMS

The s/w which is widely used today is Oracle. The different versions of Oracle starting from the earliest to the latest are – Oracle 2, Oracle 3, Oracle 4, Oracle 5, Oracle 6, Oracle 7, Oracle 8i, Oracle 9i, Oracle 10g, and the latest to hit the market is Oracle 11g. here ‘i’ stands for Internet and ‘g’ stands for Grid / Grid computing.

RELATIONSHIPS

A relationship is an association between any two tables which preserves data integrity.

The relationship helps to prevent incorrect data in the child tables.

Once the relationship is created, one table becomes master (or parent) and the other one becomes the child ( or detail ).
Whatever we insert into the child should be present in the master, else the record will be rejected from the child.

The master table contains the master data which will not change frequently. The child table contains the transactional data which will change quite often.

DBMS & RDBMS

DBMS – stands for Database Management System

DBMS is a database software which allows us to store the data in the form of tables.

RDBMS – stands for Relational DBMS
RDBMS is also a database software which has the facility to handle more data volume, good performance, enhanced security features etc when compared against DBMS.
Any DBMS to qualify as an RDBMS should support the Codd rules / Codd laws

Ex for DBMS – FoxPro, FoxBase, Dbase
Ex for RDBMS – Oracle, Sybase, DB2, Teradata, SQL Server, MySQL

CONSTRAINTS

A constraint is a condition which restricts the invalid data in the table. A constraint can be provided for a column of a table.

Types of Constraints

NOT NULL
UNIQUE
Primary Key
Foreign Key
Check

NOT NULL

NOT NULL will ensure at least some value should be present in a column.

UNIQUE

It will not allow any duplicates in a column
UNIQUE column can take multiple NULL (s)

Primary Key

It is the combination of NOT NULL and UNIQUE
Only one PK is allowed in a table
It identifies a record uniquely in a table
Creation of Primary Key is not mandatory, but it is highly recommended to create

Foreign Key

Foreign Key creates a relationship between any two tables

Foreign Key is also called as referential integrity constraints

Foreign Key is created on the child table

Foreign Key can take both NULL and duplicate values

To create Foreign Key, the master table should have Primary Key defined on the common column of the master table

We can have more than 1 Primary Key in a given table

CHECK

It is used to provide additional validations as per the customer requirements.

Ex –

sal > 0
empnum should start with 1
commission should be between 1000 & 5000

SQL

SQL – Structured Query Language

SQL – it is a language to talk to the database / to access the database

SQL – it is a language, whereas SQL server is a database. To work on SQL , a DB software (RDBMS) is required. SQL is not case sensitive.

SELECT * FROM TAB; -> This query gives the list of tables.

SELECT * FROM DEPARTMENT; -> This query gives the description of the table “department”

SELECT * FROM EMP;

The above query gives a description of the “employee” table.

We use the following command to see the data in a more orderly fashion.

SET LINESIZE 120;

SELECT * FROM EMP;

The “set linesize” command helps in increasing the line size, thus the data is arranged in an orderly fashion.

SET PAGESIZE 20;

SELECT * FROM EMP;

The above command “set page size 20” increases the page size, thus accommodating more number of rows in a single page.

SELECT ENAME, JOB, SAL FROM EMP;

The above query gives the value of only these 3 columns from the table “employee”.

SELECT * FROM EMP WHERE SAL=3000;

‘where’ clause is used to restrict the number of records displayed. It gives only the records of the specified condition.

SELECT * FROM EMP WHERE JOB=’Manager’;

Any string data should be enclosed within single quotes ( ‘ ‘ ) and the same becomes case sensitive.

Try the following:

1) List the employees in dept 20

SELECT * FROM EMP WHERE DEPT NO=20;

2) List the employees earning more than Rs 2500.

SELECT * FROM EMP WHERE SAL > 2500;

3) Display all salesmen

SELECT * FROM EMP WHERE JOB='salesmen';

Operators are classified into,

Arithmetic Operators ( +, – , * , / )
RelationalOperators(>,<,>=,<=,=,<>or!=-not equals to)
Logical Operators ( NOT, AND, OR )
Special Operators ( IN, LIKE, BETWEEN, IS )

SPECIAL OPERATORS

1) IN – it is used for evaluating multiple values.

List the employees in dept 10 & 20.

SELECT * FROM EMP WHERE DEPTNO IN (10, 20);

List all the clerks and analysts

SELECT * FROM EMP WHERE JOB IN ('CLERK' 'ANALYST');

2) LIKE – used for pattern matching

% (percentage) – matches 0 or ‘n’ characters_ (underscore) – matches exactly one character

List all the employees whose name starts with ‘S’

SELECT * FROM EMP WHERE ENAME LIKE 'S%'

Whenever we use % or _, always ensure that it is preceded by the word ‘like’

List the employees whose name is having letter ‘L’ as 2 character

SELECT * FROM EMP WHERE ENAME LIKE '_L%';

Try the following:

List the employees whose name is having at least 2 L’s

SELECT * FROM EMP WHERE ENAME LIKE '%_LL_%';

List the employees whose name is having letter ‘E’ as the last but one character

SELECT * FROM EMP WHERE ENAME LIKE '%_E_';

List all the employees whose name is having letter ‘R’ in the 3 position

SELECT * FROM EMP WHERE ENAME LIKE '_R%';

In the above query, we give 2 underscores before R%.

List all the employees who are having exactly 5 characters in their jobs

SELECT  ENAME, JOB FROM EMP WHERE JOBE LIKE '_____' ;

Here, in single quotes – we give 5 underscores.

List the employees whose name is having at least 5 characters

SELECT ENAME FROM EMP WHERE ENAME LIKE '_____';

Here, also in single quotes – we give 5 underscores ( _____ )

3) BETWEEN operator – used for searching based on a range of values.

List the employees whose salary is between 2000 and 3000

SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000

4) IS operator – it is used to compare nulls

List all the employees whose commission is null

SELECT * FROM EMP WHERE COMM IS NULL;

LOGICAL OPERATORS

List all the salesmen in dept 30

SELECT * FROM EMP WHERE JOB= 'SALESMAN' AND DEPTNO= 30;

List all the salesmen in dept number 30 and having a salary greater than 1500

SELECT * FROM EMP WHERE JOB= 'SALESMAN' AND DEPTNO= 30 AND SAL > 1500;

List all the employees whose name starts with ‘s’ or ‘a’

SELECT * FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'A%';

List all the employees except those who are working in dept 10 & 20.

SELECT * FROM EMP WHERE DEPTNO NOT IN (10,20);

List the employees whose name does not start with ‘S’

SELECT * FROM EMP WHERE ENAME NOT LIKE 'S%';

List all the employees who are having reporting managers in dept 10

SELECT * FROM EMP WHERE MGR IS NOT NULL AND DEPTNO=10;

Try the following:

List the employees who are not working as managers and clerks in dept 10 and 20 with a salary in the range of 1000 to 3000.

SELECT * FROM EMP WHERE JOB NOT IN ('MANAGER', 'CLERK') AND DEPTNO IN (10,20) AND SAL BETWEEN 1000 AND 3000;

List the employees whose salary not in the range of 1000 to 2000 in dept 10,20,30 except all salesmen

SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000 AND DEPTNO IN (10,20,30) AND JOB <> 'SALESMAN';

List the department names which are having letter ‘O’ in their locations as well as their department names

SELECT * FROM DEPT WHERE LOC LIKE '%_0_%' AND DNAME LIKE '%_0_%';

SORTING

It arranges the data either in ascending/descending order Ascending – ASC / Descending – DESC

We can sort the data using ORDER BY

By default, the data is always arranged in ASC order.

Arrange all the employees by their salary

SELECT * FROM EMP ORDER BY SAL;

Arrange all the employees by their salary in the descending order.

SELECT * FROM EMP ORDER BY SAL DESC;

Arrange ename, sal, job, empno and sort by descending order of salary

SELECT ENAME, SAL, JOB, EMPNO, FROM EMP ORDER BY 2 DESC;

In the above query we have – ORDER BY 2 – thus it arranges only the 2 columns ‘salary’ in the descending

order.
Thus to arrange the specific columns in order – we must have to specify the column number.

NOTE:- ORDER BY should be used always as the last statement in the SQL query.

Selecting DISTINCT VALUES

SELECT DISTINCT  DEPTNO FROM EMP;

The above query arranges all the distinct values of the department number.

GROUP

We have 5 GROUP functions,

Sum
Max
Min
Avg
Count

Sum – returns total value

Max – returns maximum value

Min – returns the minimum value

Avg – returns the average value

Count – returns number of records

Display the maximum salary, minimum salary and total salary from employee.

SELECT MAX(SAL), MIN(SAL), SUM(SAL) FROM EMP;

The below query gives the total number of employees

SELECT COUNT(*), COUNT(EMPNO) FROM EMP;

The below query gives the number of employees who have commission.

SELECT COUNT(*), COUNT(COMM) FROM EMP;

List the number of employees in department 30

SELECT COUNT(*) FROM EMP WHERE DEPTNO = 30;

Try the following:

Display the total salary in department 30

SELECT SUM(SAL) "TOTAL" FROM EMP WHERE DEPTNO= 30;

List the number of clerks in department 20

SELECT COUNT (*) FROM EMP WHERE DEPT NO =20 AND JOB= 'CLERK';

List the highest and lowest salary earned by salesmen

SELECT MAX(SAL),  MIN(SAL) FROM EMP WHERE JOB= 'SALESMAN';

GROUPING

It is the process of computing the aggregates by segregating based on one or more columns. Grouping is done by using ‘group by’ clause.

Display the total salary of all departments

SELECT DEPTNO, SUM(SAL) FROM EMP GROUPBY DEPTNO;

Display the maximum of each job

SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;

HAVING

Having’ is used to filter the grouped data. ‘Where’ is used to filter the non grouped data.

‘Having’ should be used after group by clause ‘Where’ should be used before group by clause.

Display job-wise highest salary only if the highest salary is more than Rs1500

SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB HAVING MAX (SAL) > 1500;

Display job-wise highest salary only if the highest salary is more than 1500 excluding department 30. Sort the data based on the highest salary in the ascending order.

SELECT JOB, MAX(SAL) FROM EMP WHERE DEPTNO <>30 GROUP BY JOB HAVING MAX(SAL) >1500 ORDER BY 2;

RESTRICTIONS ON GROUPING

We can select only the columns that are part of ‘group by’ statement If we try selecting other columns, we will get an error as shown below,

The above query is an error because ‘job’ is there in the select query but not in the group by query.
If it is enclosed in any of the group functions like sum(sal) etc – then it is not an error. But whatever table is included in the select query must also be included in the group by query.

The above problem can be overcome with the following query as shown below,

The below query is also correct to rectify the above error,

SELECT DEPTNO, SUM(SAL), SUM(COMM), FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO;

Whatever is there in the select statement must be there in the group by statement. But, whatever is there in the group by statement need not be present in the select statement. This is shown in the above two corrected queries.

Try the following:

Display the department numbers along with the number of employees in it

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

Display the department numbers which are having more than 4 employees in them

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >4 ORDER BY DEPTNO;

Display the maximum salary for each of the job excluding all the employees whose name ends with ‘S’

SELECT ENAME, JOB, MIN(SAL) FROM EMP WHERE ENAME NOT LIKE  '%S' GROUP BY ENAME, JOB ORDER BY 3;

Display the department numbers which are having more than 9000 as their departmental total salary

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) > 9000 ORDER BY 1;

CHECK DATABASE TESTING INTERVIEW PART-2

Wikipedia SQL