UPDATE command in SQL

UPDATE command in SQL is used to update or make the changes to the existing data which is present in the table.

Syntax:

UPDATE <Table_name> set <columnlist>=< value>

Table name can be name of a base table or an updatable view.

WHERE clause is optional: if omitted, named columns are updated for all rows in table. If specified, only those rows that satisfy search condition are updated.

New value must be compatible with data type for corresponding column.

Example:

1. UPDATE Bank set Customername=”Johny” where Customerid=105
2. UPDATE Bank set Customername=”ABC”,Balance=7000 where Customerid=105

Query 2 update two columns Customername and Balance at a time. One can update single or multiple columns at a time.

SELECT command in SQL

SELECT command in SQL is used for retrieving the data either from a table or tables.

Syntax:

SELECT <columnlist> from <table name> [<condition>]

Example:

1.       SELECT * from Bank

Above query retrieves all the records (all rows and columns) from table Bank

2.       SELECT * from Bank where Custid=105

Above query retrieves the record of customer having customer id =105

3.       SELECT * from Bank where Cname= NULL

This is incorrect SQL statement as NULL is not comparable because NULL is infinite or unknown value.

4.       SELECT * from Bank where Cname is NULL

We can make use of NULL value in SQL as mentioned in above query.

Query 3 will not retrieve any data because NULL value cannot be compared with another NULL value. In terms of a database, NULL value is treated as infinite or unknown value as two infinite or unknown values cannot be compared with each other. Two NULL’s also cannot be compared with each other.

When you want to perform a comparison on NULL values using operators like =,! =, <,<=,>,=> is not possible. You can use only is ‘IS NULL’ or ‘IS NOT NULL’

INSERT command in SQL

INSERT command is used in SQL for inserting or adding the data into the table.

Syntax:

INSERT into <tname> [(<columnlist>)] values (<list of values>)

Table BANK:

Custid	Cname	Balance
1	John	5000
2	Rama	7000

Examples:

1.       INSERT into BANK values (101, ‘Ajay’,5000)

2.       INSERT into BANK (Custid,Cname,Balance) values(102,’Laura’,3500)

Note: String and Date values are enclosed into a single quote only

Query 2 is compiled and executed faster than Query 1.

If you want to INSERT some special values at special column then use query as below:

3.       INSERT into BANK (Custid, Balance) values (107,9000)

It is advised to use Query type 2 or Query type 3.

If the value is not supplied to a column while inserting, the default value is taken as NULL.

4.       INSERT into BANK values (111, NULL, 8700)

You can also explicitly insert NULL values into any column as you required as mentioned in Query 4.

CREATE command in SQL

CREATE command is used for creating any object on the database server.

Syntax:

CREATE Table<Table_name>(<columnlist1><datatype>[width],…<columnlistn><datatype>[width])

Rules for CREATE command:

1. Name of the table should always be unique under the database
2. A table name should not start with numeric’s or special characters
3. The maximum acceptable size for an object is 128 characters
4. Column name should be unique under the tables
5. A table can have maximum 1024 columns

Example:

CREATE table Bank (Custid int, Custname varchar (50), Bal Decimal (7, 2))

Note: SP_Help < Table_name> command is used to view the structure of a table under database

Example: SP_Help Bank

Types of View in SQL

A view is an object which is similar to a table. It is known as a "Logical table" because it will never occupy space in the memory for storing the data. It is a dependent object that can be created from an existing table. View allows you to perform query as well as DML operations.

There are four types of views in SQL.

Types of View

1. Updatable view: It is a view which allows manipulation on it.
2. Non-updatable view: It doesn’t allow manipulation on it.
3. Simple view: It is a view on single table that contains only the column or columns of the table in it.

By default simple views are updatable only insert operation will be restricted if at all not NULL column of the base table were not present in the view.

4. Complex view: It is a view based on more than one table. Complex views based on multiple tables were referred as non-updatable because here the DML operation can’t affect multiple base table but using a complex view you can perform DML operation referring to a single table. 

A complex view allows manipulation on it but with restriction.

A view based on single table can also be called as complex view if it contains any of the following in it.

• Group by clause
• Having clause
• Distinct
• Computed columns

What does a view stores in it?

The view only stores the select statement that is used for creating it. It never stores any data in it so view is also known as stored select statement or an imaginary table.

Whenever you query on a view, the view will internal execute the select statement what it has stored and retrieves data from base table which is presented to the end user.

Sometimes a view also allows you to perform changes on it.

The changes that are performed on view will be internally performed by the view on the table on which it was created.

Top 7 differences between Table and View in SQL

In order to undertand the database it is essential that one should know the difference between table and view. Below listed are few differences between table and view.

Table contains rows and columns, columns representing fields and rows containing the records whereas View is an imaginary or virtual table which contains rows and columns, just like a real table and is created from one or more than one table by joins, with selected columns.

Tables always occupy space in the memory database whereas Views never occupies space in the memory for storing data.

Table can have limited number of columns and unlimited number of rows whereas View is an extract from a database. For example, we have tables like Department, Technology, Sales, Personal Details, Academic details etc which has multiple columns and rows. But, we usually requires certain number of columns from each table to create a report in such cases rather than writing complex SQL query to fetch specific records  or columns from these tables, we create a View which saves time as requires less time to fetch data from View.

Tables are the actual database objects that hold rows whereas Views are "imaginary tables" or "Logical tables" that are created based on the actual tables.

Views are created to provide security mechanism. For example, we have a table which hold customers important personal details and various teams are working on this table. In such cases, we create View with selected number of records or fields so team can access the data for their operations. Only the users having the access to tables and modify the data, users having access to View can view data but cannot update it.

Views executes fasters than the Tables as Views are extract from tables and requires less time to execute or perform action.

Tables can be updated, deleted and modified whereas Views may be updated, modified and updated depending upon the permissions provided to user.

What is a need for View:

Views are used in two different scenarios.

1. Whenever you required to give permissions on some information in a table to other users without providing access directly to the table we create views and give access on the view which provides security. E.g. Team A, Team B and Team C is working on the same project. But, due to some security constraint we don’t want to give permission to Team B and C to see the Salary, DOB of an EMPLOYEE.
2. Sometimes we may be using complex queries for retrieving data from tables which may be complicated for us to write the query each time in such cases you can create a view using a query and then start querying on the view directly.

       E.g.  Create table EMPLOYEE

       (Emp_Idinteger not NULL, FName Varchar (25),LName  Varchar (25), Salary decimal (10, 2),Dept       Varchar (50), DOB date,Address Varchar (150))

        Create view EMPLOYEE_VIEW as

       (Select Emp_Id, FName, LName,Dept, Addressfrom EMPLOYEE)

SEQUEL

SEQUEL is a structured English query language used for interacting with a Relational database. SEQUEL is also known as SQL .

SEQUEL has five sub-languages in it like-

1. DDL (Data Definition Language):

This Language is used to define the structure of objects under the database with 4 commands in it.

Command	Description
Create	Crates a new table, view, any object in database
Alter	Modifies the table, view, existing database object
Drop	Destroy or delete the entire table
Truncate	Deletes all the data present under the table

2. DML(Data Manipulation Language):

This deals with the data under database with 3 commands in it.

Command	Description
Insert	Insert data in to table
Update	Modify or update tab
Delete	Destroy or delete row or rows from a table

3. DQL(Data Query Language):

this deals with data also but for retrieving of the data with 1 command in it.

Command	Description
Select	Retrieve the data either from table or tables

4. DCL(Data Control Language):

This is used for managing the security of objects using which one can Grant or Revoke permissions with 3 commands in it.

Command	Description
Grant	Gives a privilege to user
Revoke	Take back granted privileges from user
Deny	Reject or refuse the privilege to user

5. TCL(Transaction Control Language):

This is used for transaction management with 3 commands in it.

Command	Description
Commit	Update the changes in database
Rollback	Revert the changes database
Save	Save the changes database

Transaction is a set of actions which tells all the actions should be performed successfully or none of the action should be performed.

Regression testing and Regression testing techniques

Software maintenance is the most crucial phase in the software development life cycle in which development team is supposed to maintain software. Software maintenance results for the reasons like enhancements in the software, error corrections, deletion of capabilities and optimization. Regression testing is testing performed on the changed or modified software. Regression testing includes testing of both the modified code and other parts of the software that may be adversely affected by the changes introduced in the software or a part of it.

Regression testing is a part of software maintenance phase and is an expensive process. Regression testing is important from software testing perspective as it focuses on variations encounter in the SDLC, and the resulting poor quality of the software. Regression testing is used to monitor the changes in the software and timely feedback from the consequences of changes. In regression testing an existing tested functionality is tested once again, in order to check if the functionality is the bug free and to validate if existing functionality is not affected whenever new change is added or defect is fixed.

In simple terms it is testing to check the cross-impact of defect fix.

Regression testing is also useful method that will help in determining whether the software is complying with quality and standard. An added useful feature of regression testing is to validate whether the software is able to produce the expected output without any errors. With regression testing, software testers can uncover if the changes introduced are affecting the program in a negative way.

At the time of execution if tester comes across any defect, the tester reports it to the development team and when development team has fixed the defect tester needs to perform the testing on fixed defect. While the defect fix developer might have changed the other functionality. To ensure these changes does not affect other functionality regression testing is done.

Need for Regression testing:

• Bug or defect fixes often break other parts or pieces of software the developer isn’t concentrating on
• Sometimes defect fixes don’t fix the defect
• Discovering fault localisation
• To validate software is working as expected even after undergoing the changes in the software code, design or architecture
• Errors in build process

Types of Regression testing:
There are four regression testing techniques.

1. Retest all: It is one of the most conventional testing technique in which all the test cases in the existing test suite are re-executed. It is expensive technique compared to other regression techniques as it requires more time, resources and cost to execute full test suites.
2. Regression Test Selection (RST): In this testing technique specific test cases are selected from existing test suites instead of rerunning the all test suites again. It is less expensive as compared to Retest all technique as it includes specific number of test cases for execution. In addition to existing selected test suites few new test cases are designed that may test software for areas which may not be covered by existing test cases.
3. Test Case Prioritisation: In this testing technique test cases are prioritised depending upon the business impact, critical and frequently used functionality so as to increase a test suites rate of fault detection.
4. Hybrid approach: It is the combination of both Regression Test Selection and Test Case Prioritisation.

Now a day, most of the companies are using automation tools for regression testing like Unified Functional Testing (UFT),Test Complete, Selenium, Cucumber, Rational Functional Test (RFT) etc.

Advantages:

• Helps to ensure that software is working as expected even after undergoing the changes and modifications
• Provide precision in testing to facilitate maximum coverage through minimal number of test cases
• Detect errors present in application
• Identify the unforeseen errors
• Improves the efficiency of quality assurance applications
• Reduce time to market significantly

 Disadvantages:

• Automation regression scripts needs skilled testers
• Need to update the scripts on timely basis

Bottom Up Integration testing

 Bottom Up Integration testing approach is the opposite to top down where lower level components are tested first and proceed upwards to the higher level components. In this approach when highest level components are developed they are integrated with the lower level components. It is known as bottom-up or lowers to highest approach. There is possibility of missing highest level components which affects the integration process. The main advantage of the Bottom Up approach is that defects are more easily found.

In this situation to make the integration possible developer develop the temporary dummy program that is utilised in place of missing module known as ‘DRIVER’.

Bottom Up Integration testing

What is Sanity testing and how to do a Sanity Testing?

Sanity testing is the testing to check major functionalities of the application to validate whether the application is ready for testing or not. It is a rapid test to validate whether a particular application or software produces the desired results or not.  It is not in-depth level testing. Before undergoing the Sanity testing software has to pass the other kinds of testing. Sanity testing is more depth than Smoke testing.

Sanity testing usually includes a suite of core test cases of basic GUI functionality to demonstrate connectivity to application server, database, printers etc.

When a new build is obtained after fixing the some minor issues then instead of doing complete regression testing sanity is performed on that build. In general words Sanity testing is a subset of regression testing. Sanity testing is performed after thorough regression testing is completed, to ensure that any defect fixes does not break the core functionality of the product.

It is performed towards the end of the product release phase such as before Alpha or Beta testing. Sanity testing is performed with an objective to verify that requirements are met on not. Sanity test is normally unscripted. Sanity testing is a subset of Acceptance testing.

 Sanity testing is the term which is correlated to Smoke testing but they are diverse. One similarity between these two is that both are used as criteria for accepting or rejecting the new build.

                       Sanity Testing

If sanity test cases fail then deployed build is rejected because if the deployed build is not having the required changes then there is no point of doing regression testing on the deployed build. Smoke is being part of regression testing which validates the crucial functionality whereas sanity testing is part of acceptance testing which validates whether the newly added functionality is working or not. Usually smoke is performed on relatively unstable build or product while sanity testing is done on the relatively stable build or product. Moreover generally only one of them is performed but if required both can be performed. When both need to perform then Smoke testing is performed first following Sanity testing.

Examples of Sanity testing:

1. Database connectivity among other modules of the application or software
2. Identification of missing objects
3. Check for missing errors from previous build
4. Testing on the application servers
5. Slow function issues
6. Database crash issues
7. System termination

Sanity testing factors:

1. Environment issues 

       E.g. Application closing, application getting hang, unable to launch URL

2. Exceptional errors

      E.g. java.io.exception (some source code will be displayed)

3. Urgent severity defects

When to perform Sanity Testing:

After receiving Software build with the minor fixes in code or functionality and there is no enough time for in-depth testing, Sanity testing is performed to check whether the defects reported in previously build are fixed and it is not impacting any previously working functionality. The objective of Sanity testing to validate the planned functionality is working as expected.

Advantages:

• Provides faster results
• Saves time
• Requires less preparation time as they are unscripted

Disadvantages:

• Defect reproducibility is difficult as these are unscripted
• Does not cover testing of entire application in-depth

Ad-hoc testing

Ad-hoc testing is unscripted random software testing method or testing types. Ad-hoc testing is also known as Expert testing. Ad-hoc testing is sometimes mixed up with other testing types such as exploratory testing, monkey testing and negative testing. Ideally it is performed only once unless there are some defects in application or system. Ad-hoc testing is effective testing technique and is done without any formal test plan, test cases, procedures or documentation.In structured testing, while testing any application or software, testers have to follow a certain scenario for executing test cases. But in Ad-hoc testing, there are not any specific scenarios or test cases for execution. Tester is allowed to execute any scenario or test case to explore the system or application.

Ad-hoc testing can be accomplished with the testing technique called "Error Guessing". Error guessing can be done by the tester having abundant experience on the system to "guess" the most likely source of errors.Ad-hoc testing is performed based on knowledge and experience of tester about the system being tested. Experienced testers can find many defects through ad-hoc testing as they know the common scenarios which cause the defects in application or software. Testers are allowed to improvise the test to find additional defects. The objective of ad-hoc testing is to break the system’s functionality and find defects in the application.

When to perform Ad-hoc testing?

Ad-hoc testing is least formal testing method with the goal of finding defects with any means possible.Ad-hoc testing can be performed anytime in the beginning, middle or towards the end of STLC. It is performed when there is limited time to do elaborates testing. It is usually performed after the formal test execution.

Ad-hoc testing examples:

1. Navigating through the work flow and using browser "Back" button to ensure user is correctly being navigated to the correct page that the user previously visited.
2. Saving a form twice for the same action.  For example, if user was to register a user on a website after filling the required fields, click on the Register button multiple times and then check the database to see if multiple records or single record get created.
3. Cross-browser testing. For more testing coverage, we have to perform testing on variety of browsers/OS, the more chances of finding defects.
4. Trying to enter and save data that is outside the provided range or boundary values.
5. Concurrent transactions or actions that do the exact same steps of a given functionality by using 2 different sessions on different machines.  For example, if an application requires tester to create a table name that is unique; how will it handle if you were to try to create a table with the same name on two distinct sessions? Validating to eliminate redundancy or duplicate data caused by concurrent transaction.
6. How will the system will perform when Java Script option is disabled? System should allow user to successfully operate the site using both Java Script enabled and disabled in Internet explorer, Chrome,Firebox,Safari.
7. Copying the current browser session URL and pasting it in another browser or editing the values in the URL to gain access to certain events that user normally would not be able to do it via User Interface. An URL which is being used by an unauthenticated or unauthorized user should be handled elegantly without exposing or violating security.

Types of Ad-hoc testing:

1. Buddy Testing:Two buddies, each one from development and testing team respectively work mutually on application or software to find the defects in same piece of module. Buddy testing helps the development team to make design level changes early and helps testing team to develop better suite of test cases. It is usually performed after successful completion of unit testing.
2. Pair Testing:It works on four eyes principle (two-man rule or the two-person rule).The word pair stands for twosome or couple. Two testers are assigned to perform testing on same piece of module to find defects. They share the ideas and work. They can divide the work such as one can perform the testing whereas other can make a note of findings or defects.Its effectiveness depends upon the ability, integrity and persistence of the individuals involved.
3. Monkey Testing:Testing is performed in a random fashion without any test cases with an objective to break the application or system.

Advantages:

1. Not bounded to specific test scenarios or test cases
2. Effective when there is a time limitation for testing the system or application under test
3. It Provides quick results
4. Testers can find more defects than formal testing techniques
5. Helps to increase code coverage benefits

Disadvantages:

1. Lack of documentation
2. Unstructured or unorganized testing
3. No reference documents to guide to testers
4. Possibility of not covering of major functionality
5. Need skilled testers to perform testing