Quick Contact

    Databases are the collection of files which are interconnected. Databases at different servers may not be of same type, i.e. databases may be heterogeneous. Basically, database testing is a layered process. Database systems usually consist of four layers: the user interface (UI) layer, the business layer, the data access layer and the database itself. Testing at these different layers is important for a consistent database system. Often, web software accesses the database from different backbends,
    i.e. they access heterogeneous databases.

    Database testing is one of the major testing which requires tester to expertise in checking tables, writing queries and procedures. Testing can be performed in web application or desktop and database can be used in the application like SQL or Oracle. There are many projects like banking, finance, health insurance which requires extensive database testing.

    6.1 Database Testing and Process

    The database is a software subsystem which provides an efficient way to store user data and allows requesting it via a structured query language known as SQL. Not only does it stores the critical business information but also functions as the backbone of an entire application. Hence, database testing is essential for software testers to ensure the system is working correctly.

    Database testing involves the retrieved values from the database by the web or desktop application. Data in the User Interface should be matched as per the records are stored in the database.

    Database testing is a means to validate the data stored in the database, objects controlling data, and the functionality wrapped around it. The databases use objects to manage data like tables for storage, views for representation, and functions/triggers for manipulation.

    Nowadays, the use of databases is wide-spread, especially in web applications. And they are getting more and more complex with new technologies and platforms. That’ss why checking the quality, security, and correctness of data make it eminent to learn database testing.

    Database Testing is checking the schema, tables, triggers, etc. of the database under test. It may involve creating complex queries to load/stress test the database and check its responsiveness. It Checks data integrity and consistency.

    Database Testing Validations

    The following verifications are carried out during database testing:

    • Checking the data Mapping.
    • ACID (Atomicity, Consistency, Isolation, Durability) properties validation.
    • Data Integrity
    • Business rule conformance
    Fundamental differences between user-interface and data testing

    The difference between user-interface testing and database testing is given below:

    User-Interface testing Database or Data testing
    This type of testing is also known as Graphical User Interface testing or Front-end Testing. This type of testing is also known as Back-end Testing or data testing.
    This type of testing chiefly deals with all the testable items that are open to the user for viewership and interaction like Forms, Presentation, Graphs, Menus, and Reports, etc. (created through VB, VB.net, VC++, Delphi- Frontend Tools ) This type of testing chiefly deals with all the testable items that are generally hidden from the user for viewership. These include internal process and storage like Assembly, DBMS like Oracle, SQL Server, MYSQL, etc.
    This type of testing includes validating the

    • Text boxes
    • Select dropdowns
    • Calendars and buttons
    • Navigation from one page to another
    • Display of images
    • Look and feel of the overall application.
    This type of testing involves validating

    • The schema
    • Database tables
    • Columns
    • Keys and indexes
    • Stored procedures
    • Triggers
    • Database server validations
    • Validating data duplication
    The tester must be thoroughly knowledgeable about the business requirements as well as the usage of the development tools and the usage of automation framework and tools. The tester in order to be able to perform back- end testing must have a strong background in the database server and Structured Query Language concepts.
    Debugging

    It is a systematic process of spotting and fixing the number of bugs, or defects, in a piece of software so that the software is behaving as expected. Debugging is harder for complex systems in particular when various subsystems are tightly coupled as changes in one system or interface may cause bugs to emerge in another.

    Debugging is a developer activity and effective debugging is very important before testing begins to increase the quality of the system. Debugging will not give confidence that the system meets its requirements completely, but testing gives confidence.

    When the application is under execution, the end user mainly utilizes the ‘CRUD’ operations facilitated by the DB Tool.

    • C: Create: When user ‘Save’ any new transaction, ‘Create’ operation is performed.
    • R: Retrieve: When user ‘Search’ or ‘View’ any saved transaction, ‘Retrieve’ operation is performed.
    • U: Update: When user ‘Edit’ or ‘Modify’ an existing record, the ‘Update’ operation of DB is performed.
    • D: Delete: When user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.

    It does not matter at all, which DB is used and how the operation is performed. The end user has no concern if any join or subquery, trigger or stored-procedure, query or function was used to do what he wanted. But, the interesting thing is that any DB operation performed by the user is always one of the above four.

    Things tested in Database testing
    Ensure data mapping

    Data mapping is one of the key aspects in the database and it should be tested rigorously by every software tester.

    Make sure that the mapping between different forms or screens of AUT and its DB is not only accurate but also per the design documents (SRS/BRS) or code. Basically, you need to validate the mapping between every front-end field with its corresponding backend database field.

    For all CRUD operations, verify that respective tables and records are updated when the user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.

    In short, following are the things you need to verify here :
    • Table mapping, column mapping, and data type mapping.
    • Lookup data mapping.
    • Correct CRUD operation is invoked for every user action at UI.
    • CRUD operation is successful.
    ACID properties validation

    Atomicity, Consistency, Isolation, and Durability. Every transaction a DB performs has to adhere to these four properties.

    • Atomicity means that a transaction either fails or passes. This means that even if a single part of the transaction fails- it means that the entire transaction has failed. Usually, this is called the “all-or-nothing” rule.
    • Consistency: A transaction will always result in a valid state of the DB
    • Isolation: If there are multiple transactions and they are executed all at once, the result/state of the DB should be the same as if they were executed one after the other.
    • Durability: Once a transaction is done and committed, no external factors like power loss or crash should be able to change it
    Data integrity

    This means that following any of the CRUD operations, the updated and most recent values/status of shared data should appear on all the forms and screens. A value should not be updated on one screen and display an older value on another one. So devise your DB test cases in a way to include checking the data in all the places it appears to see if it is consistently the same.

    Accuracy of implemented Business Rules

    Today, databases are not meant only to store the records. In fact, DBs have been evolved into extremely powerful tools that provide ample support to the developers to implement the business logic at the DB level. Some simple examples of powerful features of DBs are ‘Referential Integrity’, relational constraints, triggers and stored procedures. So, using these and many other features
    offered by DBs, developers implement the business logic at the DB level. The tester must ensure that the implemented business logic is correct and works accurately.

    6.2 Database Applications

    The database is one of the inevitable parts of a software application these days. It does not matter at all whether it is the web, desktop or mobile, client-server or peer to peer, enterprise or individual business; the database is working everywhere at the backend. Similarly, whether it is healthcare or finance, leasing or retail, mailing application or controlling a spaceship; a database is always in action behind the scene.

    Moreover, as the complexity of application increases, the need of stronger and secure database emerges. In the same way, for the applications with a high frequency of transactions (e.g. banking or finance application), the necessity of fully featured DB Tool is coupled. Nowadays, we have big data which is large and complex that the traditional databases can’t handle them.

    Currently, several database tools are available in the market e.g. MS-Access2016, MS SQL Server 2016, SQL server 2008 R2, Oracle 12c, Oracle Financial, MySQL, PostgreSQL, DB2, Toad, Admirer, etc. These tools vary in cost, robustness, features, and security. Each of these DBs possesses its own benefits and drawbacks. One thing is certain; a business application must be built using one of these or other DB Tools.

    Database Application

    A database application is a computer program whose primary purpose is entering and retrieving information from a computerized database.

    Many home and small business owners create simple databases such as customer contact and mailing lists with easy to use software such as Microsoft “Access” and “FileMaker Pro.” “Oracle,” “SQL Server,” and “FoxPro” are examples of advanced database applications with programming languages that can be used to build custom business solutions in networked environments.

    The applications of database are given below:

    • • Telecom: There is a database to keeps track of the information regarding calls made, network usage, customer details etc. Without the database systems it is hard to maintain that huge amount of data that keeps updating every millisecond.
    • • Industry: Where it is a manufacturing unit, warehouse or distribution center, each one needs a database to keep the records of ins and outs. For example, distribution center should keep a track of the product units that supplied into the center as well as the products that got delivered out from the distribution center on each day; this is where DBMS comes into picture.
    • • Banking System: For storing customer info, tracking day to day credit and debit transactions, generating bank statements etc. All this work has been done with the help of Database management systems.
    • • Education sector: Database systems are frequently used in schools and colleges to store and retrieve the data regarding student details, staff details, course details, exam details, payroll data, attendance details, fees details etc. There is a hell lot amount of inter-related data that needs to be stored and retrieved in an efficient manner.
    • • Online shopping: You must be aware of the online shopping websites such as Amazon, Flipkart etc. These sites store the product information, your addresses and preferences, credit details and provide you the relevant list of products based on your query. All this involves a Database management system.
    6.3 Database Architecture

    The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier architecture divides the whole system into related but independent n modules, which can be independently modified, altered, changed, or replaced.

    In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and uses it. Any changes done here will directly be done on the DBMS itself. It does not provide handy tools for end-users. Database designers and programmers normally prefer to use single-tier architecture.

    If the architecture of DBMS is 2-tier, then it must have an application through which the DBMS can be accessed. Programmers use 2-tier architecture where they access the DBMS by means of an application. Here the application tier is entirely independent of the database in terms of operation, design, and programming.

    Three-Level ANSI-SPARC Architecture

    An early proposal for a standard terminology and general architecture for database systems was produced in 1971 by the DBTG (Data Base Task Group) appointed by the Conference on Data Systems and Languages (CODASYL, 1971). The DBTG recognized the need for a two-level approach with a system view called the schema and user views called sub-schemas.

    Here is the figure showing the ANSI_SPARC Architecture of the database system:

    image

    A DBMS architecture is depending on its design and can be of the following types:

    • Centralized
    • Decentralized
    • Hierarchical

    DBMS architecture can be seen as either single tier or multi-tier. An architecture having n-tier splits the entire system into related but independent n modules that can be independently customized, changed, altered, or replaced.

    The architecture of a database system is very much influenced by the primary computer system on which the database system runs. Database systems can be centralized, or client-server, where one server machine executes work on behalf of multiple client machines. Database systems can also be designed to exploit parallel computer architectures. Distributed databases span multiple geographically separated machines.

    The 3-tier Architecture

    A 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. It is the most widely used architecture to design a DBMS.

    image

    • Database (Data) Tier: At this tier, the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.
    • Application (Middle) Tier: At this tier reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End-users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. Hence, the application layer sits in the middle and acts as a mediator between the end- user and the database.
    • User (Presentation) Tier: End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.

    Multiple-tier database architecture is highly modifiable, as almost all its components are independent and can be changed independently.

    6.4 Basics of SQL Queries

    SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. This tutorial will give you a quick start to SQL. It covers most of the topics required for a basic understanding of SQL and to get a feel of how it works.

    SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

    Also, they are using different dialects, such as:

    • MS SQL Server using T-SQL,
    • Oracle using PL/SQL,
    • MS Access version of SQL is called JET SQL (native format) etc.
    Benefits of using SQL

    SQL is widely popular because it offers the following advantages:

    • Allows users to access data in the relational database management systems.
    • Allows users to describe the data.
    • Allows users to define the data in a database and manipulate that data.
    • Allows to embed within other languages using SQL modules, libraries & pre-compilers.
    • Allows users to create and drop databases and tables.
    • Allows users to create view, stored procedure, functions in a database.
    • Allows users to set permissions on tables, procedures and views.
    SQL Process

    When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

    There are various components included in this process.

    These components are:

    • Query Dispatcher
    • Optimization Engines
    • Classic Query Engine
    • SQL Query Engine, etc.

    A classic query engine handles all the non-SQL queries, but a SQL query engine won’t handle logical files.

    SQL Commands

    The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature:

    DDL – Data Definition Language
    Sr.No. Command & Description
    1 CREATE

    Creates a new table, a view of a table, or other object in the database.

    2 ALTER

    Modifies an existing database object, such as a table.

    3 DROP

    Deletes an entire table, a view of a table or other objects in the database.

    DML – Data Manipulation Language

    Sr.No. Command & Description
    1 SELECT

    Retrieves certain records from one or more tables.

    2 INSERT

    Creates a record.

    3 UPDATE

    Modifies records.

    4 DELETE

    Deletes records.

    DCL – Data Control Language

    Sr.No. Command & Description
    1 GRANT

    Gives a privilege to user.

    2 REVOKE

    Takes back privileges granted from user.

    Basic SQL Queries

    The following are the various SQL queries:

    CREATE Database

    The SQL CREATE DATABASE statement is used by a developer to create a database.

    Let’s see the syntax of SQL CREATE DATABASE:

    CREATE DATABASE database_name;
    SQL DROP Database

    SQL DROP statement is used to delete or remove indexes from a table in the database.

    If you want to delete or drop an existing database in a SQL schema, you can use SQL DROP DATABASE

    Let’s see the syntax of SQL DROP DATABASE:

    DROP DATABASE database_name;
    SQL RENAME Database

    SQL RENAME DATABASE is used when you need to change the name of your database. Sometimes it is used because you think that the original name is not more relevant to the database or you want to give a temporary name to that database.

    This command is useful for SQL server 2005, 2008, 2008R2 and 2012.

    ALTER DATABASE old_name MODIFY NAME = new_name
    SQL SELECT Database

    In MySQL database, you need to select a database first before executing any query on table, view etc. To do so, we use following query:

    USE DATABASE database_name;
    SQL CREATE TABLE

    SQL CREATE TABLE statement is used to create table in a database.

    If you want to create a table, you should name the table and define its column and each column’s data type.

    Let’s see the simple syntax to create the table.

    create table "tablename" 
    	("column1" "data type", 
    	"column2" "data type", 
    	"column3" "data type",
    ...
    "columnN" "data type");
    

    The data type of the columns may vary from one database to another. For example, NUMBER is supported in Oracle database for integer value whereas INT is supported in MySQL.

    Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.

    SQL> CREATE TABLE STUDENTS (
    ID INT	
    NOT NULL,
    NAME VARCHAR (20) NOT NULL,
    AGE INT	NOT NULL,
    ADDRESS CHAR (25), 
    PRIMARY KEY (ID)
    );
    

    You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else you can use DESC command as follows:

    SQL> DESC STUDENTS;

    FIELD TYPE NULL KEY DEFAULT EXTRA
    ID Int(11) NO PRI
    NAME Varchar(20) NO
    AGE Int(11) NO
    ADDRESS Varchar(25) YES NULL
    4 rows in set (0.00 sec)
    SQL DROP TABLE

    A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

    This is very important to know that once a table is deleted all the information available in the table is lost forever, so we have to be very careful when using this command.

    Let’s see the syntax to drop the table from the database.

    DROP TABLE "table_name";
    SQL DELETE TABLE

    The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

    DELETE FROM table_name [WHERE condition];
    Difference between DELETE and TRUNCATE statements

    There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.

    But it does not free the space containing by the table.

    The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

    SQL RENAME TABLE

    SQL RENAME TABLE syntax is used to change the name of a table. Sometimes, we choose non- meaningful name for the table. So it is required to be changed.

    Let’s see the syntax to rename a table from the database.

    ALTER TABLE table_name 
    RENAME TO new_table_name;
    SQL ALTER TABLE

    The ALTER TABLE statement is used to add, modify or delete columns in an existing table. It is also used to rename a table.

    You can also use SQL ALTER TABLE command to add and drop various constraints on an existing table.

    SQL ALTER TABLE Add Column

    If you want to add columns in SQL table, the SQL alter table syntax is given below:

    ALTER TABLE table_name ADD column_name column-definition;
    If you want to add multiple columns in table, the SQL table will 
    be
    ALTER TABLE table_name
    ADD (column_1 column-definition,
    column_2 column-definition,
    .....
    column_n column-definition);
    
    SQL ALTER TABLE Modify Column

    If you want to modify an existing column in SQL table, syntax is given below:

    ALTER TABLE table_name MODIFY column_name column_type;
    If you want to modify multiple columns in table, the SQL table 
    will be
    ALTER TABLE table_name
     
    MODIFY (column_1 column_type,
    column_2 column_type,
    .....
    column_n column_type);
    
    SQL ALTER TABLE DROP Column

    The syntax of alter table drop column is given below:

    ALTER TABLE table_name DROP COLUMN column_name;
    SQL SELECT

    The most commonly used SQL command is SELECT statement. It is used to query the database and retrieve selected data that follow the conditions we want.

    In simple words, we can say that the select statement used to query or retrieve data from a table in the database.

    Let’s see the syntax of select statement.

    SELECT expressions FROM tables
    WHERE conditions;
    

    Here expression is the column that we want to retrieve.

    Tables indicate the tables, we want to retrieve records from.

    Optional clauses in SELECT statement

    There are some optional clauses in SELECT statement:

    [WHERE Clause] : It specifies which rows to retrieve.

    [GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be applied to each group.

    [HAVING Clause] : It selects among the groups defined by the GROUP BY clause.

    [ORDER BY Clause] : It specifies an order in which to return the rows.

    For example, let a database table: student_details;

    ID First_name Last_name Age Subject Hobby
    1 Amar Sharma 20 Maths Cricket
    2 Akbar Khan 22 Biology Football
    3 Anthony Milton 25 Commerce Gambling

    From the above example, select the first name of all the students. To do so, query should be like this:

    SELECT first_name FROM student_details;
    Amar
    Akbar
    Anthony
    SQL INSERT STATEMENT

    SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.

    There are two ways to insert data in a table:

    • By SQL insert into statement
      • By specifying column names
      • Without specifying column names
    • By SQL insert into select statement
    Inserting data directly into a table

    You can insert a row in the table by using SQL INSERT INTO command. But there are 2 ways to do this.

    You can specify or ignore the column names while using INSERT INTO statement.

    To insert partial column values, you must have to specify the column names. But if you want to insert all the column values, you can specify or ignore the column names.

    If you specify the column names, syntax of the insert into statement will be as follows:

    INSERT INTO TABLE_NAME
    [(col1, col2, col3,.... col N)]
    VALUES (value1, value2, value 3, .... Value N);
    

    Here col1, col2, col3, colN are the columns of the table in which you want to insert data.

    Inserting data through SELECT Statement

    SQL INSERT INTO SELECT Syntax

    INSERT INTO table_name
    [(column1, column2, column)]
    SELECT column1, column2, …. Column N FROM table_name [WHERE condition];

    SQL UPDATE

    The SQL commands (UPDATE and DELETE) are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause.

    SQL UPDATE statement is used to change the data of the records held by tables. Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause.

    The UPDATE statement can be written in following form:

    UPDATE table_name SET [column_name1= value1,... column_nameN = va lueN] [WHERE condition]

    Let’s see the Syntax:

    UPDATE table_name
    
    SET column_name = expression 
    WHERE conditions
    
    6.5 Joining Tables

    JOIN means to combine something. In case of SQL, JOIN means to combine two or more tables.

    The SQL JOIN clause takes records from two or more tables in a database and combines it together.

    ANSI standard SQL defines five types of JOIN :

    • inner join,
    • left outer join,
    • right outer join,
    • full outer join, and
    • cross join.

    In the process of joining, rows of both tables are combined in a single table.

    Need of SQL Join
    • If you want to access more than one table through a select statement.
    • If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement.
    • The joining of two or more tables is based on common field between them.
    • SQL INNER JOIN also known as simple join is the most common type of join.
    SQL OUTER JOIN

    In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.

    If you take an example of employee table

    Outer join of two types
    • Left outer join (also known as left join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.
    • Right outer join (also known as right join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.
    SQL LEFT JOIN

    The SQL left join returns all the values from the left table and it also includes matching values from right table, if there are no matching join value it returns NULL.

    BASIC SYNTAX FOR LEFT JOIN:
    SELECT table1.column1, table2.column2....
    FROM table1 LEFTJOIN table2
    ON table1.column_field = table2.column_field;
    

    let us take two tables in this example to elaborate all the things:

    CUSTOMER TABLE:
    ID NAME AGE SALARY
    1 ARYAN 51 56000
    2 AROHI 21 25000
    3 VINEET 24 31000
    4 AJEET 23 32000
    5 RAVI 23 42000

    This is second table

    ORDER TABLE:
    O_ID DATE CUSTOMER_ID AMOUNT
    001 20-01-2012 2 3000
    002 12-02-2012 2 2000
    003 22-03-2012 3 4000
    004 11-04-2012 4 5000
    join these two tables with LEFT JOIN:
    SQL SELECT ID, NAME, AMOUNT,DATE 
    	FROM CUSTOMER
    LEFT JOIN ORDER
    ON CUSTOMER.ID = ORDER.CUSTOMER_ID;
    

    This will produce the following result:

    ID NAME AMOUNT DATE
    1 ARYAN NULL NULL
    2 AROHI 3000 20-01-2012
    2 AROHI 2000 12-02-2012
    3 VINEET 4000 22-03-2012
    4 AJEET 5000 11-04-2012
    5 RAVI NULL NULL
    SQL RIGHT JOIN

    The SQL right join returns all the values from the rows of right table. It also includes the matched values from left table but if there is no matching in both tables, it returns NULL.

    Basic syntax for right join:

    SELECT table1.column1, table2.column2.....
    FROM table1
    RIGHT JOIN table2
    ON table1.column_field = table2.column_field;
    

    let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.

    CUSTOMER TABLE:
    ID NAME AGE SALARY
    1 ARYAN 51 56000
    2 AROHI 21 25000
    3 VINEET 24 31000
    4 AJEET 23 32000
    5 RAVI 23 42000

    and this is the second table:

    ORDER TABLE:
    DATE O_ID CUSTOMER_ID AMOUNT
    20-01-2012 001 2 3000
    12-02-2012 002 2 2000
    22-03-2012 003 3 4000
    11-04-2012 004 4 5000

    Here we will join these two tables with SQL RIGHT JOIN:

    SQL> SELECT ID,NAME,AMOUNT,DATE FROM CUSTOMER
    RIGHT JOIN ORDER
    ON CUSTOMER.ID = ORDER.CUSTOMER_ID;
    
    ID NAME AMOUNT DATE
    2 AROHI 3000 20-01-2012
    2 AROHI 2000 12-02-2012
    3 VINEET 4000 22-03-2012
    4 AJEET 5000 11-04-2012
    SQL FULL JOIN

    The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.

    SQL full outer join and SQL join are same.Generally it is known as SQL FULL JOIN.

    SQL full outer join

    SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don?t care its matched or unmatched) from the both participating tables.

    Syntax for full outer join:
    SELECT *
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name = table2.column_name;
    

    Note: here table1 and table2 are the name of the tables participating in joining and column_name is the column of the participating tables.

    Let us take two tables to demonstrate full outer join:

    table_A
    A M
    1 m
    2 n
    4 o
    table_B
    A N
    2 p
    3 q
    5 r
    Resulting table
    A M A N
    2 N 2 p
    1 M
    4 O
    4 O
    3 q
    5 r
    SQL Cross Join

    When each row of first table is combined with each row from the second table, known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table.

    We can specify a CROSS JOIN in two ways:
    • Using the JOIN syntax.
    • the table in the FROM clause without using a WHERE clause.
    SYNTAX of SQL Cross Join
    SELECT * FROM [TABLE1] CROSS JOIN [TABLE2] OR
     
    
    SELECT * FROM [ TABLE1] , [TABLE2]
    

    Let us take an example of two tables,

    Table1 – MatchScore
    Player Department_id Goals
    Franklin 1 2
    Alan 1 3
    Priyanka 2 2
    Rajesh 3 5
    Table2 – Departments
    Department_id Department_name
    1 IT
    2 HR
    3 Marketing
    SQL Statement:
    SELECT * FROM MatchScore CROSS JOIN Departments
    After executing this query , you will find the following result:
    Player Department_id Goals Depatment_id Department_name
    Franklin 1 2 1 IT
    Alan 1 3 1 IT
    Priyanka 2 2 1 IT
    Rajesh 3 5 1 IT
    Franklin 1 2 2 HR
    Alan 1 3 2 HR
    Priyanka 2 2 2 HR
    Rajesh 3 5 2 HR
    Franklin 1 2 3 Marketing
    Alan 1 3 3 Marketing
    Priyanka 2 2 3 Marketing
    Rajesh 3 5 3 Marketing
    6.6 Operators

    SQL statements generally contain some reserved words or characters that are used to perform operations such as comparison and arithmetical operations etc. These reserved words or characters are known as operators.

    Generally there are three types of operators in SQL:

    • SQL Arithmetic Operators
    • SQL Comparison Operators
    • SQL Logical Operators
    SQL Arithmetic Operators:

    Let’s assume two variables “a” and “b”. Here “a” is valued 50 and “b” valued 100.

    Example:
    Operators Descriptions Examples
    + It is used to add containing values of both operands a+b will give 150
    It subtracts right hand operand from left hand operand a-b will give -50
    * It multiply both operand?s values a*b will give 5000
    / It divides left hand operand by right hand operand b/a will give 2
    % It divides left hand operand by right hand operand and returns reminder b%a will give 0
    SQL Comparison Operators

    Let’s take two variables “a” and “b” that are valued 50 and 100.

    Operator Description Example
    = Examine both operands value that are equal or not,if yes condition become true. (a=b) is not true
    != This is used to check the value of both operands equal or not,if not condition become true. (a!=b) is true
    <> Examines the operand?s value equal or not, if values are not equal condition is true (a<>b) is true
    > Examine the left operand value is greater than right Operand, if yes condition becomes true (a>b) is not true
    < Examines the left operand value is less than right Operand, if yes condition becomes true (a<=””td=””>
    >= Examines that the value of left operand is greater than or equal to the value of right operand or not,if yes condition become true (a>=b) is not true
    <= Examines that the value of left operand is less than or equal to the value of right operand or not, if yes condition becomes true (a<=b) is true
    !< Examines that the left operand value is not less than the right operand value (a!<=””td=””>
    !> Examines that the value of left operand is not greater than the value of right operand (a!>b) is true
    SQL Logical Operators

    This is the list of logical operators used in SQL.

    Operator Description
    ALL this is used to compare a value to all values in another value set.
    AND this operator allows the existence of multiple conditions in an SQL statement.
    ANY this operator is used to compare the value in list according to the condition.
    BETWEEN this operator is used to search for values, that are within a set of values
    IN this operator is used to compare a value to that specified list value
    NOT the NOT operator reverse the meaning of any logical operator
    OR this operator is used to combine multiple conditions in SQL statements
    EXISTS the EXISTS operator is used to search for the presence of a row in a specified table
    LIKE this operator is used to compare a value to similar values using wildcard operator
    6.7 Constraints and Views
    Constraints

    Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

    Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.

    Following are some of the most commonly used constraints available in SQL. These constraints have already been discussed in SQL – RDBMS Concepts chapter, but it’s worth to revise them at this point.

    • NOT NULL Constraint: Ensures that a column cannot have NULL value.
    • DEFAULT Constraint: Provides a default value for a column when none is specified.
    • UNIQUE Constraint: Ensures that all values in a column are different.
    • PRIMARY Key: Uniquely identifies each row/record in a database table.
    • FOREIGN Key: Uniquely identifies a row/record in any of the given database table.
    • CHECK Constraint: The CHECK constraint ensures that all the values in a column satisfies certain conditions.
    • INDEX: Used to create and retrieve data from the database very quickly.

    Constraints can be specified when a table is created with the CREATE TABLE statement or you can use the ALTER TABLE statement to create constraints even after the table is created.

    Dropping Constraints

    Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

    For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command.

    ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

    Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command.

    ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

    Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.

    Integrity Constraints

    Integrity constraints are used to ensure accuracy and consistency of the data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.

    There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above.

    Views

    A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.

    A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

    Views, which are a type of virtual tables allow users to do the following −

    • Structure data in a way that users or classes of users find natural or intuitive.
    • Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
    • Summarize data from various tables which can be used to generate reports.
    Creating Views

    Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view.

    To create a view, a user must have the appropriate system privilege according to the specific implementation.

    The basic CREATE VIEW syntax is as follows:

    CREATE VIEW view_name AS SELECT column1, column2.....
     
    
    FROM table_name WHERE [condition];
    
    Summary

    The points below summarize the topics as discussed:

    • Database is a software subsystem which provides an efficient way to store user data and allows requesting it via a structured query language known as SQL.
    • Database testing is a means to validate the data stored in the database, objects controlling data, and the functionality wrapped around it.
    • A database application is a computer program whose primary purpose is entering and retrieving information from a computerized database.
    • The architecture of a Database can be seen as either single tier or multi-tier that can be centralized or decentralized.
    • JOIN means to combine two or more tables to get the results.
    • Constraints are the rules enforced on the data columns of a table.
    • A view is nothing more than a SQL statement that is stored in the database with an associated name.

    Enrolled Course — API TESTING

    Copyright 1999- Ducat Creative, All rights reserved.