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