SQL: VIEWS

---

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.

Creating a VIEW

The syntax for creating a VIEW is:

CREATE VIEW view_name AS

SELECT columns

FROM table

WHERE predicates;

For example:

CREATE VIEW sup_orders AS

SELECT suppliers.supplier_id, orders.quantity, orders.price

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id

and suppliers.supplier_name = 'IBM';

This would create a virtual table based on the result set of the select statement. You can now query the view as follows:

SELECT *

FROM sup_orders;

Updating a VIEW

You can update a VIEW without dropping it by using the following syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT columns

FROM table

WHERE predicates;

For example:

CREATE or REPLACE VIEW sup_orders AS

SELECT suppliers.supplier_id, orders.quantity, orders.price

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id

and suppliers.supplier_name = 'Microsoft';

Dropping a VIEW

The syntax for dropping a VIEW is:

DROP VIEW view_name;

For example:

DROP VIEW sup_orders;

SQL Joins Introduction

SQL: Joins

---

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

There are different kinds of joins. Let's take a look at a few examples.

Inner Join (simple join)

Chances are, you've already written an SQL statement that uses an inner join. It is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.

For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id;

This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how inner joins work:

We have a table called suppliers with two fields (supplier_id and supplier_ name).
It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date).

It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we run the SQL statement below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.

Outer Join

Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

For example,

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date

from suppliers, orders

where suppliers.supplier_id = orders.supplier_id(+);

This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

The above SQL statement could also be written as follows:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date

from suppliers, orders

where orders.supplier_id(+) = suppliers.supplier_id

Let's look at some data to explain how outer joins work:

We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date).

It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we run the SQL statement below:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date

from suppliers, orders

where suppliers.supplier_id = orders.supplier_id(+);

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft	<null>
10003	NVIDIA	<null>

The rows for Microsoft and NVIDIA would be included because an outer join was used. However, you will notice that the order_date field for those records contains a <null> value.

Difference between truncate and delete in mysql

Truncate and Delete are both SQL commands which result in removing the table records. So lets list the differences one by one :
Type of Command – Truncate is a DDL command and Delete is a Dml command
RollBack - As mentioned above Truncate is DDL command, so the changes made by it are committed automatically hence there is nothing called rollback when you use truncate, while Delete commands can be rolled back
Table Structure – When you use Truncate command, all the rows in the table are delete and the structure of the table is recreated and so does the indexes. On the contrary if you use Delete command only the desired rows or all the rows are deleted and the structure remains unchanged.
Syntax - The syntax for both the commands is :
Truncate table #command to truncate a table.
Delete from #command to delete all the records from table.
Practical example -
#creates a table with 2 columns, 1st column is auto incremented
Create table mysqlDemo (id integer not null auto_increment,name varchar(100),PRIMARY KEY(id));

#now insert two records in the table
insert into mysqlDemo(name)values ('sachin');
insert into mysqlDemo(name)values ('digimantra');

#check the records and note their auto_increment values
select * from mysqlDemo;

#Let us try delete it using Delete command
delete from mysqlDemo;

#Now the table is empty, lets insert values from the first row.
insert into mysqlDemo(name)values ('new_sachin');
insert into mysqlDemo(name)values ('new_digimantra');

#check the records and note their auto_increment values
select * from mysqlDemo; #the aut_increment values will continue from the last records, as the table structure is preserved.

#Now let us Truncate the table and re-insert the values.
Truncate table mysqlDemo;
insert into mysqlDemo(name)values ('sachin');
insert into mysqlDemo(name)values ('digimantra');

#check the records and note their auto_increment values
select * from mysqlDemo;

#this time the auto_increment value will start from one, as the table structure is recreated because we used Truncate instead of Delete.
So this is what the difference is, in short always remember Truncate command recreates the structure of the table and deletes all the records of the table. However Delete command does not recreates the structure and deletes the complete or partial records (as desired) from the table.

So this is what the difference is, in short always remember Truncate command recreates the structure of the table and deletes all the records of the table. However Delete command does not recreates the structure and deletes the complete or partial records (as desired) from the table.

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Lesson 4...Bug Life Cycle

Check out this SlideShare Presentation:

Lesson 4...Bug Life Cycle

View more documents from bhushan Nehete.

Bug Life Cycle

The standard Bug life cycle which i have collected from Bugzilla....is given below



Generally,what we use to follow...More simplified version of the above is ...

vb script to compare two excel sheets

This is a fantastic code to compare two excel sheets.Basically this can be treated as Lean project.:)


ExcelFilePath1 = InputBox("Please Enter the Path of first Excel File")

Set fso = CreateObject("Scripting.FileSystemObject")

If (fso.FileExists(ExcelFilePath1) = false )Then

msgbox ExcelFilePath1 & " doesn't exist."

wscript.quit

End If

ExcelFilePath2 = InputBox("Please Enter the Path of second Excel File")

If (fso.FileExists(ExcelFilePath2) = false )Then

msgbox ExcelFilePath2 & " doesn't exist."

wscript.quit

End If





Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = false





Set objWorkbook1 = objExcel.Workbooks.Open(ExcelFilePath1)

Set objWorkbook2 = objExcel.Workbooks.Open(ExcelFilePath2)



Set objWorksheet1= objWorkbook1.Worksheets(1)



Set objWorksheet2= objWorkbook2.Worksheets(1)



For Each cell In objWorksheet1.UsedRange

If cell.Value <> objWorksheet2.Range(cell.Address).Value Then





'Highlights in green color if any changes in cells (for the first file)

cell.Interior.ColorIndex = 4

'Highlights the same cell in the Second file

objWorksheet2.range(cell.Address).interior.colorIndex = 4



Else

cell.Interior.ColorIndex = 0

End If

Next



ObjExcel.displayAlerts = False



objExcel.Save

objExcel.quit



set objExcel=nothing



msgbox "It is Done dude"
 
 
Source : http://askqtp.blogspot.com

Automated Testing with Agile

Check out this SlideShare Presentation:

Automated Testing with Agile

View more presentations from Ken McCorkell.

Software Testing Basics

Software Testing Basics

View more presentations from Belal Raslan.