Clustered Index vs Non Clustered Index

What is Indexing

Index is one of the database object and indexing can be done in one or more columns of the database table. The purpose of doing indexing is simple i.e, to speed up the retrieval process of rows in the table. Indexing can be applied to tables or views to speed up the retrieval process. Index can be considered as “copy” of the data of table and will be sorted based on some way. For example if we have a table called “Category” as shown below -

Category Table

CategoryID INT - PK
CategoryName VARCHAR(50)
CategoryAdded DATETIME
CategoryDeleted DATETIME

As shown above in the table structure “Category ID” is a primary key and will default have the clustered index. So all primary keys will default have the clustered index associated with it. Now consider another scenario where in we search the records based on “Category Name” and say the table has more than 1 million records in that table it will surely give the performance issues while filtering records based on “Category Name” as it does “full table scan” internally.

So to avoid the performance issues we can set “Category Name” as Non clustered Index. So now it sorts the data based on “Category Name” and stores it in the disk of the machine and hugely increases the performance as well.

Clustered Index

Let’s add some data into the table –“Category” and let’s see how the table search happens through query and let’s see how we can minimize number of comparisons using clustered index –

Category ID	Category Name	Category Added	Category Deleted
1	Category1	NULL	NULL
2	Category2	NULL	NULL
3	Category3	NULL	NULL
4	Category4	NULL	NULL
5	Category5	NULL	NULL

Below is the sample image of how the table scan is happened without clustered index -

Below is the sample image of how the table data is structured after clustered index -

As shown in the first figure above, table – Category have primary key – “Category ID” which is by default have a clustered index. Primary key as we know does not allow null and does not allow duplicate values.

Below are the list of points to be kept in mind while creating a Clustered Index -

• There could be only one clustered index allowed in a table.
• It is good to add the clustered index to the column which is used mostly in “where” clause the most. Because it greatly affects the performance of the table.
• Whenever index is changed, reordering of data is happened in the table.
• When clustered index is created it forms a binary tree and binary tree info stored in the disc separately. Sample binary tree diagram shown above.
• During Update, Delete, Insert statements reordering will happen in the table.

Now let’s see how the data compared in binary tree in a query. Suppose I have a query to find the category having CategoryID = 5 –

Select * from Category where Category ID = 5

As shown in the above binary tree figure -

• 5 is matched with 3, 
• 5 is matched with 4
• 5 is matched with 5 so only 3 comparisons.

So here total comparisons are 3 which is less compared to comparisons without clustered index 5.

Difference between Inner join and Left join

SQL Join is used for combining the rows between two or more tables using common fields/columns between these tables in a relational database. The goal of joining table is to extract the meaningful list of data. Joins will be obtained from the term called predicate. “Join” is a term used to combine fields of two or more tables by using common values between the tables.

There are different types of joins in SQL and they are –

• INNER JOIN
• CROSS JOIN
• SELF JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Difference between Inner Join vs Left join

For demonstration purpose let’s create tables and let’s call it as – “tClass” and “tStudent”

tClass table structure

tStudent table structure

Inner join

Inner join matches the common records in two tables joined. Here in the above example we have “ClassID” in tclass table and “ClassID” in tstudent table. Below query is used to fetch the list of students which are in Class “10th”

SELECT s.StudentID, s.StudentName
FROM tClass c 

INNER JOIN tstudent s ON c.ClassID = s.ClassID

WHERE c.ClassID = 10

So above query is used to fetch the list of students which are studying in Class – “10th”. So here in “tstudent” table all students are stored irrespective of their classes and similarly in “tclass” table all classes are stored. So as the result of the above query, matched records between tstudent and tclass tables are searched and displayed. In the introduction part there was an introduction to the term called “predicate”, in the above query it is – “ON c.ClassID = s.ClassID”, which is one of the essential part of the join.

Note : There are multiple keys associated to each table like in tStudent table have “StudentID” as a primary key field and “ClassID” as a foreign key which inturn refer to the table – “tClass”.

• In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
• JOIN word can be used instead of INNER JOIN, both meant the same.
• INNER JOIN is the intersection of data between table A and table B.

The above query can be rewritten without using inner join like below but the performance will be impacted compared to inner join –

SELECT s.StudentID, s.StudentName
FROM tClass c , tstudent s

WHERE c.ClassID = s.ClassID AND c.ClassID = 10

Left Join

• Left join will return all the rows from the left table and matching rows from right table.
• In case of no match with right side table it will return NULL value.
• “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.

Difference between having and where clause

Where clause can be applied to a single row, where as Having clause is applied to the whole group. But, it does not mean that we cannot have both Where and Having clause in a single query. If both where and having clauses are used together in single a query, where clause will apply filter condition to the list of records and it will filter the group of records from the table and Having clause will be applied to this resultant group and groups which meets the condition given in having clause will be filtered in the resultant table.

Having clause is used, when we want to use select query to filter data from a table based on a aggregate condition. Aggregate functions used along with the having clause are Sum, Count, Max and Min. So, Having clause should always be used along with group by clause in SQL. If it is not used with Group By clause it throws an error saying – “Column 'Your column' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BYclause“.

Example for Where Clause and Having Clause

Having clause is been added in SQL because WHERE clause could not be used like an aggregate function. Let’s create a multiple tables for demonstrating this with an example –

Table Name - Customer Table

Customer ID	Customer Name	Customer DOB	Customer Age
1	John	10/10/90	24
2	Martin	05/12/65	49
3	Darren	07/26/86	28
4	Derek	01/20/90	25

Table Name - Order Table

Order ID	Customer ID	Order Date
100	1	03/20/15
101	1	03/22/15
102	2	01/29/14
103	3	06/22/14
104	3	08/24/14
105	3	01/19/15

Now let’s filter the list of orders of the customer – “John” done –

SELECT o. Order ID, o. Order Date FROM Customer as c INNER JOIN Order as o ON c.Customer ID = o.Customer ID WHERE c.Customer Name = ‘John’

Above is the straight forward query to filter all the orders done by the customer – “John” and displaying the order date and order ID of the filtered records. So here in the above query we can use Group by clause and having clause also along with the existing where clause as shown below –

So below query is used to filter the orders of all the customers and group all the orders of the customers and filter the customers who has more than one order using having clause.

SELECT o.CustomerID FROM Customer as c INNER JOIN Order as o ON c.Custome rID = o.Customer ID GROUP BY o.Customer ID HAVING COUNT(o.Customer ID) > 1

So as we can see in the above query I am trying to check number of occurrence of Customer ID records in Order table, which means the number of orders done by the customers and then I am trying to fetch the Customer ID which is matching the filter criteria in having clause. So in case you want to get the name of the customer instead of customer ID then below query can be used –

SELECT c1.Customer Name

FROM Customer as c1 INNER JOIN
(

 SELECT o.Customer ID as Customer ID
FROM Customer as c

 INNER JOIN Order as o ON c.Customer ID = o.Customer ID
GROUP BY o.Customer ID
 HAVING COUNT(o.Customer ID) > 1
)cinner

ON cinner.Customer ID = c1.Customer ID

Looks straight forward query. Now my first query has been moved to inner query and now new join has been introduced to match the Customer ID from the filtered data and Customer table’s Customer ID. So the selected customer ID‘s Customer Name has been fetched. This is not only the way to fetch the Customer Name but this is one way of the way. 

We can use Temporary table as well for the same purpose. Group by clause allows to us to use functions like max(), min(), sum() etc. which are very useful where as “where” clause does not work with these functions. So having clause can be used with aggregate functions whereas where clause will not work with aggregate functions.

SQL Group By and Having Command

Learning SQL won't be complete without knowing the usage of "Group By" and "Having" clause. These commands are one of the most commonly used one is SQL queries and knowing when and how to use them make your life easy.

SQL Group By Clause

SQL Group by Clause is used to find count, sum, min and max values belonging to a particular category. For example, to get total salary of employees from the Salary table given below, following SQL query is used.

Employee_Name	Year	Month	Salary
John	2015	April	30000
John	2015	May	31000
John	2015	June	32000
Alex	2015	April	40000
Alex	2015	May	41000
Alex	2015	June	42000

select employee_name,Sum(salary) Salary from salary group by employee_name

The result of the above query is given below. The group by function helps us to add up the salaries with respect to the column specified after "Group by" function. In this case, it is employee name.

Employee_Name	Salary
John	93000
Alex	123000

SQL Mathematical Functions, Concat and Trim Commands

SQL Average

The "Average" keyword is used to find average of specified column in a table or the number of rows that match a specific criterion. To find the average prices from sales table given below

Item	Quantity	Price	Reduction	Total
Item1	5	400	10	2000
Item2	6	300	10	1800
Item3	3	15	5	45
Item4	2	30	5	60
Item5	2	300	5	600
Item6	6	400	10	2400

select avg(price) Average-Price from sales

The result of the above mentioned query is as shown below

Average-Price
240.833

SQL Count

SQL Mathematical Function, Count keyword is used to find the no of rows in a table or the number of rows that match a specific criterion. To find the no of rows in sales table following SQL query is used.

select count(*) Sales-Count from sales

Sales-Count
6

SQL MAX

SQL Max keyword is used to find maximum value of specified column in a table or the number of rows that match a specific criterion. To find the maximum prices from sales table following SQL query is used.

select max(price) Max-Price from sales

Max-Price
400

SQL Min

SQL Min keyword is used to find minimum value of specified column in a table or the number of rows that match a specific criterion. To find the maximum prices from sales table following SQL query is used

select min(price) Min-Price from sales

Min-Price
15

SQL Sum

SQL Sum keyword is used to find total value specified column in a table or the number of rows that match a specific criterion. To find the total prices from sales table following SQL query is used

select sum(price) Total from sales

Max-Price
1445

SQL Concat

SQL concat function is used to append columns or append string to a columns. For example, to append employee name & Age of the table given below, following query is used.

Employee_ID	Employee_Name	Age
1	John	28
2	Alex	24
3	James	35
4	Roy	22
5	Kay	44

For oracle

select employee_name || '_' || age from employee

For SQL Server

select employee_name + '_' + age from employee

IT2032 SOFTWARE TESTING - Important Questions

1.   various software testing principles

2.   various origins and concepts  of defects, defect classes , Coin prob example

3.   concepts of equivalence class partitioning and boundary value analysis, cause and effect graph, evaluating test adequacy criteria.

4.   white box test design approaches

5.   Types of system Testing

6.   unit testing(Planning, implementation , recording results)

7.   Testing Groups

8.   integration testing(Planning, implementation , recording results),

9.   components of test plan

10.  Testing Maturity Model

11.  skills needed by a test specialist

12.  Test metrics and measurements

13.   SCM

14.  . State and explain different levels of testing

15.Review and  its types

SQL Mathematical Operators

SQL Calculation fields can be used to perform mathematical ( + , - , * , /) operation on retrieved data. SQL Query with different mathematical operations performed on the table sales is given below

Table Name : Sales

Item	Quantity	Price	Reduction	Total
Item1	5	400	10	2000
Item2	6	300	10	1800
Item3	3	15	5	45
Item4	2	30	5	60
Item5	2	300	5	600
Item6	6	400	10	2400

For multiplication , following SQL Query is used . For example, Query to find the total of each based on the quantity and base price

select items, quantity* price Total from sales

Item	Total
Item1	2000
Item2	1800
Item3	45
Item4	60
Item5	600
Item6	2400

SQL Join Command - Inner, Outer and Left Join

In order to avoid data duplication, data is stored in related tables. Join commands like inner join, outer join and left join is used to fetch data from related tables.

Different type of joins are

• Join or inner join
• Outer Join
• Left Join
• Full Join

SQL Join query with examples on Employee and Salary table given below are

Table Name : Employee

Employee_ID	Employee_Name	Age
1	John	28
2	Alex	24
3	James	35
4	Roy	22
5	Kay	44

Table Name : Salary

Employee_ID_Ref	Year	Month	Salary
1	2015	April	30000
1	2015	May	31000
1	2015	June	32000
2	2015	April	40000
2	2015	May	41000
2	2015	June	42000

Inner JOIN

Inner join returns rows when there is at least one match in both tables. SQL query to join above tables is as given below.

select * from employee a join salary b on a.employee_id = b.employee_id_ref

The result of the above given query is as shown below.

Employee_ID	Employee_Name	Age	Employee_ID_Ref	Year	Month	Salary
1	John	28	1	2015	April	30000
1	John	28	1	2015	May	31000
1	John	28	1	2015	June	32000
2	Alex	24	2	2015	April	40000
2	Alex	24	2	2015	May	41000
2	Alex	24	2	2015	June	42000

Left JOIN

Return all rows from the left table, even if there are no matches in the right table. SQL query to left join above tables is as given below.

select * from employee a left join salary b on a.employee_id = b.employee_id_ref

Employee_ID	Employee_Name	Age	Employee_ID_Ref	Year	Month	Salary
1	John	28	1	2015	April	30000
1	John	28	1	2015	May	31000
1	John	28	1	2015	June	32000
2	Alex	24	2	2015	April	40000
2	Alex	24	2	2015	May	41000
2	Alex	24	2	2015	June	42000
3	James	35
4	Roy	22
5	Kay	44

Here, even though there is no matching records for employees "James,Roy and Kay", there names are shown in the results.