Database Knowledge for a Tester Part V

Hi Readers, this is the last series of DataBase Knowledge for a Tester. I hope you people must enjoyed the reading and gained database knowledge. Soon I will come with some more stuffs on Technology Knowledge for Tester.

1. GROUPING DATA FROM TABLES IN SQL

Apart from SELECT, WHERE, DISTINCT, ORDER BY etc, there are 2 more clauses which facilitate selective retrieval of row. These are the GROUP BY and HAVING clauses. These are same as ORDER BY and WHERE clauses, except that they act on recordsets, and not on individual records.

1.1 CONCEPT OF GROUPING

1.1.1 Group By clause

The GROUP BY clause creates a data set, containing several sets of records grouped together based on a condition.
Syntax:
SELECT (colname1),(colname2),(colnameN), AGGREGATE_FUNCTION(expression) from TABLE NAME where (condition) GROUP BY (colname1),(colname2),(colnameN);
For e.g.
Consider we want to find out how many employees are there in each department.
Select DEPT_NAME, COUNT(EMP_NO)”No. of Employees” From DEPT_MASTER GROUP BY DEPT_NAME;
Consider another example. In case if we have to find out how many employees are there in each Branch of a Bank.
Select BRANCH_NO, COUNT(EMP_NO)”No. of Employees” From EMP_MASTER GROUP BY BRANCH_NO;

1.1.2 HAVING Clause

HAVING imposes a condition on the GROUP BY clause, which further filters the groups created by the GROUP BY clause.
Each column specification specified in the HAVING clause must occur within a statistical function or must occur in the list of columns named in the GROUP BY clause.
For e.g.
Consider we want to find out all the customers which have more than one account in the Bank.
SELECT CUST_NO, COUNT(ACC_NO) “No. of Accounts” from ACC_DETAIL where ACC_NO like ‘CA%’ or ACC_NO like ‘SA%’ GROUP BY CUST_NO HAVING COUNT(ACC_NO)>1;

Consider another example, if we want to find the list of those customers having only 1 account.

SELECT CUST_NO, COUNT(ACC_NO) “No. of Accounts” from ACC_DETAIL GROUP BY CUST_NO HAVING COUNT(ACC_NO)=1;

1.1.3 Rules For GROUP BY and Having Clause

• Columns listed in the Select Statement have to be listed in the GROUP BY Clause
• Columns listed in the GROUP BY Clause need not be listed in the SELECT Statement.
• Only GROUP functions can be used in the HAVING clause
• The Group functions listed in the HAVING clause need not be listed in the SELECT statement.

1.2 SUBQUERIES

1.2.1 Subqueries

Subqueries are also called Nested query. A subquery is a form of an SQL statement that appears inside another SQL statement. The statement containing a subquery is called a parent statement.
The Parent statement uses the rows (i.e. the result set) returned by the subquery.
It can be used for:
• Inserting records in a target table.
• Creating tables and inserting records in the table created.
• Updating records in a target table.
• Creating Views.
• Providing values for conditions in WHERE, HAVING, IN etc and used with SELECT, UPDATE and DELETE statements.
For e.g.
Get the address of the Customer names Sachin Kumar.
SELECT CODE_NO “Cust No”, ADD1 ‘ ‘ ADD 2 ‘ ‘ ‘ ‘ CITY ‘ , ‘ STATE ‘ , ‘ PINCODE “address” FROM ADD_DETAIL WHERE CODE_NO IN
(SELECT CUST_NO FROM CUST_MASTER WHERE FNAME=’SACHIN’ AND LNAME=’KUMAR’);

1.2.2 Using Subquery in the FROM clause

The concept of using a subquery in the FROM clause is called an inline view. A subquery in the FROM clause of the SELECT statement defines a data source from that particular Select statement.
For e.g.
List accounts alongwith the current balance, the branch, and the avg balance of that branch, having a balance more than the avg. balance of the branch, to which the account belongs.
SELECT A.ACC_NO, A.CURBAL, A.BRANCH_NO, A.AVGBAL FROM ACCT_MSTR A, (SELECT BRANCH_NO, AVG(CURBAL) AVGBAL FROM ACCT_MSTR GROUP BY BRANCH_NO) B
WHERE A.BRANCH_NO=B.BRANCH_NO and A.CURBAL>B.AVGBAL;

1.2.3 Using Correlated Sub-Queries

A sub-query becomes correlated when the subquery references a column from a table in the parent query. A correlated subquery is evaluated once for each row processed by the parent statement, which can be any of SELECT, UPDATE or DELETE.
It is used whenever a subquery must return a different result for each candidate row considered by the parent query.
For e.g.
List accounts alongwith the current balance, the branch, having a balance more than the avg. balance of the branch, to which the account belongs
SELECT ACC_NO, CURBAL, BRANC_NO FROM ACCT_MSTR A WHERE CURBAL>(SELECT AVG(CURBAL) FROM ACCT_MSTR WHERE BRANCH_NO=A.BRANCH_NO);

1.2.4 Using Multi Column Sub-Query

Each row of the outer query is compared to the values from the inner query (Multi Row and Multi Column).
For e.g.
Find out all the customers having the same name as the employees.
SELECT FNAME, LNAME FROM CUST_MSTR WHERE (FNAME, LNAME) IN (SELECT FNAME, LNAME FROM EMP_MSTR);

1.2.5 Using EXISTS/ NOT EXISTS Operator

The EXISTS operator is usually used with correlated subqueries. This operator enables to test whether a value retrieved by the outer query exists in the result set of the values retrieved by the inner query. If a match is found TRUE is returned else FALSE.
The EXISTS ensures that the search in the inner query terminates when at least one match is found.
Similarly, the NOT EXISTS enables to test whether a value retrieved by the outer query is not a part of the result set of the values retrieved by the inner query.
For e.g.
List employees who have verified at least one account.
SELECT EMP_NO, FNAME, LNAME FROM EMP_MSTR E WHERE EXISTS(SELECT ‘SCT’ FROM ACCT_MSTR WHERE VERI_EMP_NO = E.EMP_NO);

List those branches, which don’t have employees yet.
SELECT BRANC_NO, NAME FROM BRANCH_MSTR B WHERE NOT EXISTS(SELECT ‘SCT’ FROM EMP_MSTR WHERE BRANCH_NO=B.BRANCH_NO);

1.3 JOINS

Joins are used to manipulate data from multiple tables using a single SQL sentence. Tables are joined on columns having the same data type, and data width in the tables.
SYNTAX:
ANSI STYLE
SELECT COL1, COL2, COLN From Table1 INNER JOIN Table 2 on Table1.COL1=Table2.COL2 WHERE (CONDITION) ORDER BY COL1, COL2, COLN
THETA-STYLESELECT COL1, COL2, COLN From Table1, Table 2 WHERE Table1.COL1=Table2.COL2 AND (CONDITION) ORDER BY COL1, COL2, COLN
In the above syntax:
1. COL1 in Table1 is its Primary Key
2. COL2 in Table2 is its Foreign Key
3. COL1 and COL2 must have the same data type and for certain data types, the same size.

1.3.6 Inner or Equi Join

INNER JOIN returns all rows from both tables where there is a match.
For e.g.
List the employee details alongwith Branch Names to which they belong.
Select E.EMP_NAME, D.BRANCH_NAME FROM EMP_MSTR E, DEPT_MSTR D WHERE D.DEPT_NO = E.DEPT_NO;

1.3.7 Outer Join

This type of Join can be used in situations where it is desired, to select all rows from the table on the left (or right or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing.
LEFT OUTER JOIN
For e.g.
List the employee details alongwith the contact details (if any) Using Left Outer Join
ANSI STYLE SELECT A.NAME, A.DEPT, B.CONTACT from EMP_MSTR A LEFT JOIN CNTC_DTLS B ON A.EMP_NO=B.CODE_NO;
THETA STYLESELECT A.NAME, A.DEPT, B.CONTACT from EMP_MSTR A, CNTC_DTLS B WHERE A.EMP_NO=B.CODE_NO (+);

USING RIGHT OUTER JOIN- The same query.
ANSI STYLE
SELECT A.NAME, A.DEPT, B.CONTACT from CNTC_DTLS B RIGHT JOIN EMP_MSTR A ON B.CODE_NO=A.EMP_NO;
THETA STYLESELECT A.NAME, A.DEPT, B.CONTACT from CNTC_DTLS B , EMP_MSTR A WHERE B.CODE_NO (+)=A.EMP_NO;
In both the query, all the employee details need to be listed alongwith their contact details if any.

1.3.8 Cross Join

A Cross Join returns what’s known as a Cartesian product. The Join combines every row from the left table with every row in the right table.

1.3.9 Self Join

Joining a table to itself is referred to as self-join. In a self join, two rows from the same table combine to form a result row.
For e.g.
Retrieve the name of the employees and the names of their respective managers from the employee table.
SELECT EMP.FNAME “Employee”, MNGR.FNAME “Manager” FROM EMP_MSTR EMP, EMP_MSTR MNGR WHERE EMP.MNGR_NO = MNGR.EMP_NO;

1.4 Guidelines for Creating Joins

• Precede the column name with the table name when writing a select statement that joins tables.(For Clarity)
• If the same column name appears in more than one table, the column name must be prefixed with the table name.
• The WEHERE clause is the most critical clause in a join select statement. Always make sure to include the WHERE clause.

1.5 Miscellaneous Clause- UNION, INTERSECT AND MINUS

1.5.1 Union Clause

The output of multiple queries can be combined using UNION clause.

Output of the UNION ClauseFor e.g.
Retrieve the names of all the employee working in India and US
SELECT EMP_NAME FROM EMPLOYEE WHERE LOCATION=’INDIA’
UNION
SELECT EMP_NAME FROM EMPLOYEE WHERE LOCATION=’US’

The UNION clause picks up the common records as well as the individual records in both queries.

Guideline for using Union Clause
• The number of columns and data types of the columns being selected must be identical in all the SELECT statement used in the query. The Column name need not be identical.
• UNION operates over all of the columns being selected.
• NULL values are not ignored during duplicate checking
• The IN operator has a higher precedence than the UNION operator
• By default the output is sorted in ascending order of the first column of the select statement.
• UNION cannot be used in Subqueries
• UNION cannot be used with Aggregate functions.

1.5.2 Intersect Clause

The INTERSECT clause outputs only rows produced by both the queries intersected i.e. the output in an Intersect clause will include only those rows that are retrieved common to both the queries.


Output of the INTERSECT ClauseFor e.g.
Retrieve the customer holding accounts as well as fixed deposits in a bank.
SELECT DISTINCT CUST_NO FROM ACC_FD_TBL WHERE ACC_FD_NO LIKE ‘CA%’ OR ACC_FD_NO LIKE ‘SB%’
INTERSECT
SELECT DISTINCT CUST_NO FROM ACC_FD_TBL WHERE ACC_FD_NO LIKE ‘FS%’

The INTERSECT clause picks up the record that are common in both the queries.
Guideline for using Union Clause• The number of columns and data types of the columns being selected must be identical in all the SELECT statement used in the query. The Column name need not be identical.
• INTERSECT does not ignore NULL values.
• Reversing the order of the intersected tables does not alter the result.

1.5.3 Minus Clause

Multiple queries can be put together and their output combined using the minus clause. The MINUS clause outputs the rows produced by the first query, after filtering the rows retrieved by the second query.

For e.g.
Retrieve the customer holding accounts but not holding any fixed deposits in a bank.
SELECT DISTINCT CUST_NO FROM ACC_FD_TBL WHERE ACC_FD_NO LIKE ‘CA%’ OR ACC_FD_NO LIKE ‘SB%’
MINUS
SELECT DISTINCT CUST_NO FROM ACC_FD_TBL WHERE ACC_FD_NO LIKE ‘FS%’

The MINUS clause picks records in the first query after filtering the records retrieved by the second query.
Guideline for using Union Clause• The number of columns and data types of the columns being selected must be identical in all the SELECT statement used in the query. The Column name need not be identical.
• All the columns in the where clause must be in the SELECT clause for the MINUS operator to work.

2 REFERENCES

• SQL, PL/SQL Third Edition by Ivan Bayross.

3. Download

Click here to download a pdf copy of this article.