Monday, November 30, 2015

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 IDCategory NameCategory AddedCategory Deleted
1Category1NULLNULL
2Category2NULLNULL
3Category3NULLNULL
4Category4NULLNULL
5Category5NULLNULL
Below is the sample image of how the table scan is happened without clustered index -
Table Index

Below is the sample image of how the table data is structured after clustered index -
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
clustered index iteration

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.

Sunday, November 29, 2015

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
Table Class
tStudent table structure
Table Student

Inner join

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
  • 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.

Thursday, November 26, 2015

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 IDCustomer NameCustomer DOBCustomer Age
1John10/10/9024
2Martin05/12/6549
3Darren07/26/8628
4Derek01/20/9025

Table Name - Order Table
Order IDCustomer IDOrder Date
100103/20/15
101103/22/15
102201/29/14
103306/22/14
104308/24/14
105301/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.

Wednesday, November 25, 2015

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_NameYearMonthSalary
John2015April30000
John2015May31000
John2015June32000
Alex2015April40000
Alex2015May41000
Alex2015June42000

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_NameSalary
John93000
Alex123000

Tuesday, November 24, 2015

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
ItemQuantityPriceReductionTotal
Item15400102000
Item26300101800
Item3315545
Item4230560
Item523005600
Item66400102400

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_IDEmployee_NameAge
1John28
2Alex24
3James35
4Roy22
5Kay44
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

Monday, November 23, 2015

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
ItemQuantityPriceReductionTotal
Item15400102000
Item26300101800
Item3315545
Item4230560
Item523005600
Item66400102400

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
ItemTotal
Item12000
Item21800
Item345
Item460
Item5600
Item62400

Sunday, November 22, 2015

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_IDEmployee_NameAge
1John28
2Alex24
3James35
4Roy22
5Kay44
Table Name : Salary
Employee_ID_RefYearMonthSalary
12015April30000
12015May31000
12015June32000
22015April40000
22015May41000
22015June42000

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_IDEmployee_NameAgeEmployee_ID_RefYearMonthSalary
1John2812015April30000
1John2812015May31000
1John2812015June32000
2Alex2422015April40000
2Alex2422015May41000
2Alex2422015June42000

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_IDEmployee_NameAgeEmployee_ID_RefYearMonthSalary
1John2812015April30000
1John2812015May31000
1John2812015June32000
2Alex2422015April40000
2Alex2422015May41000
2Alex2422015June42000
3James35
4Roy22
5Kay44

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