Index in SQL with examples

Data Storage:

1. The data in SQL Server is stored in the form of data pages.  
2. The size of each data page is 8kb.
3. The data page contains the page header which is 96 bytes.
4. The header will have previous pointer (PP), object id, next pointer (NP).
5. The previous pointer pointing to the previous page, this value is NULL for first page.
6. The next pointer pointing to the next page, it’s value is NULL for the last page.

Data Access:

In SQL Server Data will be accessed in two ways.

Table Scan : It starts from beginning of the table and scan each and every page for required data, it extracts the rows to meet the query criteria.

Using Index: When index is created for a table, it travels through index tree structure to determine the required rows and extracts the rows which will meets the query criteria.

User Defined Functions in SQL Server

The functions created by the user are called user defined functions.

 Types of user defined functions:

 1. Scalar Valued Functions
2. Table Valued Functions

1. Scalar Valued Functions:

The functions which return scalar (single value) to the calling environment are called scalar valued functions.

 Syntax:

create function (function_name)
(@para1 datatype(size), ……@para n datatype (size))
retrun datatype
as
begin
function body
return ( variable)
end

For executing the function

Declare @ var (datatype)
Set @var= owner.funciton_name ( para1, para2..)
print@var

Example: Write a function to find the sum of the two numbers

Create function add_sum ( @a int, @b int)
Return int
As
Begin
Declare @c int,
Set @c = @a+@b;
return(@c);
end

Executing the function

Declare @z int
Set @z= dbo.add_sum(3,5)
print@z

Aggregate and Group Functions in SQL and Oracle with examples

• Aggregate functions will acts group of values at a time and returns a single value. It is also called Group functions.
• Aggregate functions will ignore NULL values while calculating.
• Here is the sample emp table using for solving examples.

Emp Sample Table

1. Sum:
Sum is used to find the sum of values in a column.

Example:Write a query for find the total salary of emp table.

select sum(sal) from emp

2. Avg: Avg is used to find average values for given values.

Example:Write a query for find the average salary of emp table.

select avg(sal) from emp

Database (DB)Testing - Strategy, Concepts, Checklist with sample test cases

In software testing there will be so many types of testings we will be carried out to deliver the product as per the client needs or as per the requirements. In which database testing or db testing is the one which requires thorough knowledge on sql commands, stored procedures, functions and triggers. There will be so many database software available in the market like SQL Server, Oracle, MySQL, PostgreSQL, Hadoop (Hadoop distributed file system) etc., What ever may be the database software we are using there will be slight difference in SQL query syntax, however most of the things will be same in all software. Database testing or DB testing is a technique of validating the database in which data is getting inserted/updated/deleted in various tables on various databases. Database testing is will differ based on the type of the data you are going to verify. I mean based on the OLTP or OLAP (Online Analytical Processing) the method of database testing will differ. For OLTP (Online Transaction Processing) we are verifying the stored procedures and validating the data is inserted/deleted/modified properly or not. And for OLAP lot of scripts or DW jobs will comes into picture. Having extensive knowledge on database testing I can say database testing should be carried out manually, why because I didn't find any automation testing tool which fulfills the db tsting requirements. Whenever the project started and ready for testing initially we will get the packages, stored procedures, functions etc for QA. In the sense the Database testing concept is divided into two phases.

1. Backend Database Testing
2. Frontend Database Testing

Functions in SQL - Scalar - Mathematical, String, Date and Time functions

A function is a predefined programming segment specially used for specific and well defined task.

Functions in SQL Server:

     1. Scalar functions
     2. Aggregate or group functions

1. Scalar functions:

These scalar functions will take single value as input and returns a single value.

• Mathematical Functions
• String functions
• Date time functions
• System functions
• Security functions
• Confirmation functions
• Cursor functions
• System statistic functions
• Text and image functions

We will concentrate on first three functions now.

Transactions in SQL

Transaction is a logical unit of work in a database.

Types of transactions:

1. Auto commit transactions
2. Explicit transactions
3. Implicit transactions.

1. Auto Commit Transactions:

Every individual DML statement is an auto commit transaction.
Ex:

insert into dept values (50,'export','hyd')

Constraints in Database

• Constraint in a database is a mechanism used by Oracle (or) SQL Server to control the unwanted or invalid data flow into a table. (or)
• Enforcing the rules to maintain the data integrity.
• This mechanism automatically activates when user performance a DML operation.

Types of Constraints:

1. NOT NULL: It will not allows the NULL values, but allow the duplicate values.
2. UNIQUE: It will now allows the duplicate values, but allows NULL values.
3. Primary Key:
1. It is a combination of unique, not null and clustered index.
2. It will not allow NULL values and duplicated values.

     4. Default: It is used to insert default values into the column when user skips the column from insert statement.

     5. Check: It is used to impose a condition rule.

     6. Foreign Key:

1. It is used to establish parent/child or Master/Detailed relationships between the tables.
2. It always linked to primary key column or unique key column of master table.
3. It will not allow NULL values.

Joins in SQL and Oracle

Joins:
Join is a query that combines the rows from two or more queries. 

Types of joins: 

 1. Cross Join 
 2. Inner join or equi join or simple join
 3. Outer join 
 4. Self join 

1. Cross join: 

• In cross join every row in the first table joins with the every row in the second table. 
• The output of cross join is called Cartesian product.

Ex:      select ename,dname 
         from emp, dept
                   or
         select ename,dname 
         from emp cross join dept

Data Integrity in DBMS

The Reliability and accuracy of data is called the data integrity.

Types of Data Integrity in DBMS:

1. Entity integrity
2. Domain integrity
3. Referential integrity
4. User defined integrity

1. Entity Integrity:

The basic idea of entity integrity is that each should have acolumn that uniquely identifies the remaining columns in the table.

2. Domain Integrity:

It refers to the range of values that are allowed for the column.

3. Referential Integrity:

It refers to the relationships between the columns of a table.

4. User Defined Integrity:

It allows the user or administrator to enforce the new business rules which does not falls under above integrity.

                                                                                Back to SQL Tutorial Index

Sub Queries and Correlated SubQueries in SQL and Oracle

Sub Query: It is an select statement in another select statement.

Syntax:

select <column_list> from <table_name> where column operator in (select <column_list> from <table_name>)

Based on the result of sub query or inner query outer query is executed. i.e., outer query depends on inner query.

Q. Write a query to display all employees in king department .

A.select * 
       from emp 
       where deptno=(select deptno 
                     from emp 
                     where ename='KING')

Datatypes in SQL Server

There are two types of data types in SQL Server

1. Simple data types
2. User defined data types

1. Simple data types:

• The data types provided by software are called simple data types.

a. Character Data types:

1. Char(n):

• It is a fixed length data type used to store the character data.
• Default size is 1 byte
• Memory wastage if not used
• Maxi size is allowed for n is  800 bytes.

Dependencies In DBMS

Before discussing about the dependencies let us know what is meant by Entity and Attribute.

Entity: It is a person, place, event or thing about which the information is mentioned. For example employee, customer, student are called entities.

Attribute:The characteristic property that describes an entity is called attribute. For example emp number of employee or customer id of customer entity.

Three type of dependencies in DBMS

1. Functional Dependency
2. Partial Functional Dependency
3. Transitive Dependency.

Database Models in DBMS

DBMS: Database Management System

DBMS

• It is a collection of interrelated data and set of programs to access the data from database.
• It is a suit of software programs for creating maintaining the data in the database.
• Users are connected with DBMS not with the data.
• It allows the organisation to store data in one location from which multiple users can access the data.
• user can insert, update, delete the data by using tools provided by DBMS.

INTERSECT clause in Oracle, SQL Server and MySQL

INTERSECT clause is used in oracle and SQL Server to retrieve the data which are exists in both the tables.i.e we can say common records present in both the tables.

Syntax:

select column1, column2,....
from table 1 [where clause]

intersect

select column1, column2 ... 
from table2 [where clause]

For example  Table1 and table2 as below

MINUS clause in Oracle, SQL Server and MySQL

Minus clause in oracle used to query the records which are exists in table1 but not in table2.

select * from table1
minus
select * from table 2

or

select column1, column2... from table1
minus
select column1, column2... from table2

Make sure the data types for the columns should be compatible.

Normalization in Database

Normalization is the process of decomposing the relations (Tables) into smaller and well structured relations, to avoid the data redundancy. (or)

Normalization is the process of deciding which attributes should be grouped in a table to avoid the data redundancy in database.

There are some rules in the normalization each rule is called a normal form.

1. First Normal form (1NF)
2. Second Normal form (2NF)

3. Third Normal form (3NF)
4. Fourth Normal form (4NF)
5. Fifth Normal form (5NF).

Data Modelling in Dataware house

In Data warehouse dimension modelling is used. Dimension model is designed using fact and dimensions.

There are 3 types of schemas used in dimension modelling.

1. Star schema
2. Snowflake schema
3. Galaxy schema

1. Star schema:

In star schema center of the star is fact table and corners are dimension tables. In star schema dimension tables don't have parent tables. In star schema dimension tables are denormalized. It is efficient in query processing.

Star Schema

2. Snowflake schema:

In snowflake schema dimension tables have one or more parent tables. Snowflake schema efficient in transaction processing.

Snoflake Schema

3. Galaxy schema:

Multiple facts tables connected with multiple dimension tables. Also referred as multi star schema.

Galaxy Schema

Data Warehouse Concepts

Types of tables in Data warehouse:

1. Dimensions

2. Facts

Dimensions: These are attributes about facts.  Dimensions stores textural description about the business.

Facts:It stores business measurements. Facts are actual  transactions or values being analyzed.  They contain composite primary key where each attribute of primary key is a foreign key to the dimension table.

Fact table consists of keys and measures.

Performance Testing using JMETER

Performance testing is a process of checking if your applications can withstand the desired load. It is a process to ensure the code changes do not break or reduce performance of the existing application. In principle it is a meticulous process to ensure that applications response times are within the limits under the needed load conditions.

Peak Load:

It is the number of transactions or requests that the application is expected to get during the peak hour of Website/Application.

Database Migration Testing

Database Migration is moving applications, cron jobs, all components and data transfer from legacy database software system to another new database software system. Here database software system in the sense Data warehouse or Hadoop or any other system which can handle the existing functionality and handle the data without any performance issues. A plenty of database testing will be involved especially in data migration projects. Here are the varieties of data migration case studies like Migration of DW to Hadoop, Migration of ODS (Operational Data Store)to Pentaho or any other ETL tool etc. Any of the data migration or replacing ETL Tool with another ETL tool we should have the parallel system in the process, so that the data comparison should be pretty some easy going process.

ETL Testing

As we know ETL stands for Extract, Transform and Load. Generally ETL things will comes into picture with respect the data i.e., OLAP data. So many paid and free ware tools available in the market like Informatica, Datastage, Oracle data Integrator and Pentaho etc. In a simple manner what we are testing here is data from source modified based on some conditions and will be inserted into target tables.

Source: The source may be any format like flat files, tables, xml files etc.,These source data is generated from other jobs or other applications, we don't bother how the data is coming or from where the data is coming, here our concentration should be the source data should be inserted into one or two tables and one or more records. If you take in informatica the jobs are called workflows, it might be hourly workflows which will run hourly, or daily workflows which runs once in a day based on the design.