How to find duplicate values using SQL

Assume, Table sample contains 3 rows

select * from sample;
+----+------------+
| id | Name       |
+----+------------+
|  1 | xyz        |
|  2 | Rajesh     |
|  3 | Rajesh     |
+----+------------+

Select name,count(*) from sample group by name;

The above query will group all the similar name
value into a group,count(*) will list the number
of counts for the value in the group.

+------------+----------+
| Name       | count(*) |
+------------+----------+
|Rajesh      |        2 |
| ABC        |        1 |
+------------+----------+

Now we can able to identify the duplicate rows,
 but how to list the
duplicate rows alone.


Select Name,count(*) from sample group by
name having count(*) > 1;

+------------+----------+
| Name       | count(*) |
+------------+----------+
|Rajesh      |        2 |
+------------+----------+
As we know duplicate rows has the count greater
than 1. So we are using the Having clause to
check which group contains the counts greater than 1

State Bank ATM Machine

This time, I come with the defects of SBI ATM machine. Here I am trying to get my mini stmt. The Screen offered me two types of account, one is SB and another one is Current account. I was suppose to touch a command called SB account in the screen,(since ATM is Touch screen machine) but the touch screen is not working for SB Type. So I selected Current account in the screen by just brushing my finger over the current account command in the screen, ATM should not provide me mini stmt, since I am holding SB account. But the ATM gave me mini stmt and that too for the correct balance. Here it should throw a error message like "You does not hold current account"

Testing the MOVIES

Suddenly I got a thought, why can't I test the movies and look for logic loop holes in the movies, as tester I always used to find something which goes wrong. So I started to search for logic loop holes in the movies during the process, on Saturday, I came across a climax scene of Tamil movie called whistle. I will describe the climax scene and logical issue.

Scene as follows

Both Hero and Heroine are standing in front of the villain(Lady called sherin) and a inspector lying down with injuries. Villain(sherin) holding a gun and pointing towards the hero and heroine with a cunning smile on her face(trying to kill them). In the rescue process,Inspector throws something hard at the hand of villain, after hitting hard villain drops the gun and heroine collects the gun and now gun is pointed to villain.Both hero and inspector ask heroine to shoot the villain.But she refuses to shoot(Because villain was very good friend of heroine before). Mean time villain takes advantage and broke a bottle and try to kill heroine, immediately inspector takes a gun from his socks and shoot the villain and the Movie ends happily.

My question about the logic is

Why does inspector doesn't use his gun at the first attempt itself rather than throwing something hard at the villain hands, and later using his gun. Inspector got the chance to kill her but he made villain to drop the gun and ask the heroine to kill her when she refuse, inspector kill the villain. What a Drama and twist.

Stupid isn't.

Microsoft closes Encarta

It seems Microsoft closes it Encarta encyclopedia. By the end of June Encarta software removed from store and by the end of December, website will be removed. The reason might be Microsoft lost its battle with Wikipedia. Since most of the content is outdated and cannot survive the Battle with Wikipedia(Text based online encyclopedia whereas Encarta is audio and graphics based encyclopedia). Check the FAQ in the official site

For more information click here

How to Detect a memory leak with Load runner

I came across an excellent article for memory leak.
Check the URL

http://motevich.blogspot.com/2007/08/how-to-detect-memory-leaks-with.html

Xenu a broken link finder

I supposed to find broken links in one of my project, which I did that manually. It was huge task as links kept going on. I thought if I can automate that it would be easier for me. I started googling and found Xenu

Xenu is an open source tool, used to find broken links in the websites. It is quick and easy to install and it total occupying space is less than 3 MB.

But I never used that tool in my project, it went to release before I get used with the tool.

Google error

Some of the errors while blogging, as a tester engineer my first duty is to take a snap shot.

verifyForText function in selenium

verifyTextPresent function in selenium is used to verify whether the Text is present in the page or not.
If not, it returns false.

In my experience, I tried with the click function and followed by this function with wrong value, so that it should return false. But I couldn't see false is returned.

After scratching my head i tried with open and followed by the verifyTextPresent function(as usual with wrong value), it returned false for the invalid data.

Element not found error in Selenium IDE

I often face "element not found error" while executing Test cases in Selenium IDE. After some googling , I found error arises because, Selenium try to execute the command before the page gets loaded.

To over come, use AndWait command before the line where error is occurred

For example, If the flow like

Search button is clicked and a value is selected from the select box and proceed further.

If error occurs while selecting a value in the select box, use clickAndWait command for Search button click. So that Selenium Click the Search button and waits for the page to get load.

Selenium IDE

Selenium IDE is an Open source, record and Play back tool which runs on browser.
It comes as a plug in for Firefox.

Selenium IDE can be found at http://seleniumhq.org/projects/ide for download.

Once Selenium IDE is installed as plug in. Make sure to restart the Firefox. Selenium IDE can found at Tools menu of Firefox and Once Selenium IDE is clicked, it opens on a new window.

Transaction Isolation Level

Transaction isolation Level is a setting decides, how data of the current transaction is made visible to another transaction

More info click here

Be bugging

Be bugging is the process, where known defect are intentionally added to the system or components to monitor the detection and removal and to estimate the remaining defects. It is also know as Fault seeding.

Test Case

Test case is a document, with set of conditions(inputs) and expected output. Test case is used to determine the application or software meets its requirement.

Collection of Test Case can be called as Test Suite

Restore a database in SQL using Query

To restore a database,

restore Database [databasename]
From Disk ='c:\databasefilename.bak'

BackUp a database using query in SQL 2005

Query Syntax

Backup database [database name] to disk=’path to store the file’

Example

Backup database xyz to disk ='c:\data_backup'

While taking backup you can also view the progress status

Query

Backup database [database name] to disk=’path to store the file’ with stats=30

here 30 will show you

30 percent processed
60 percent processed

GMail now in Offline(BETA)

GMail is now available at Offline mode. This feature can be enabled in labs and with the help of Gears, Google download GMail cache. As long as the connection is available, cache is synchronized with Google Server. Once the connection is down, Google automatically switches to Offline mode and uses local cache from the hard disk. The mail which is sent when it is Offline are placed in Outbox and once connection established, the mails are sent automatically.

just follow these steps to get started:

1. Click Settings and click the Labs tab.
2. Select Enable next to Offline Gmail.
3. Click Save Changes.
4. After your browser reloads, you'll see a new "Offline0.1" link in the upper righthand corner of your account, next to your username. Click this link to start the offline set up process and download Gears if you don't already have it.

SaaS

SaaS stands for Software as a Service. It is an application hosted on remote server and can be accessed through Internet. In general, the application is not loaded on the client machine and no need to have storage area. SaaS pricing based on the monthly fee and which is more economically than general software licensing.

Example

Gmail, Yahoo Mail etc...

More info Saas

Google Blogger Usability Issue

Again I am finding some problem with Blogger. This, time it is usability issue.Whenever I go to new posting I can able to use FONT, TEXT SIZE Options from the toolbar.I kept idle without blogging for three to four days. One fine day I decided to blog, suddenly I found there is no Options for FONT, TEXT SIZE in the toolbar. I was shocked and I don't know how to get back those Options. I made some R & D in SETTING but still I couldn't find out.

At last I decided to do some Googling and I got the answer that those Options are available only at Compose Tab.

Fine Ok no problem that might be a requirement, I am blogging for the past one month, whenever I go to Posting TAB and create a new post, I can find those options without changing the TAB from Edit Html to Compose i.e If you go to new post the default tab will be Compose TAB.

So how it is possible, suddenly default TAB got changed.?

CRM

CRM stands for Customer Relationship Model, it is a process where a company follows to track and organize their customers, to develop a strong relationship with them in turn company can increase their revenue. CRM tool supports these process.

Wiki defines

Customer relationship management (CRM) consists of the processes a company uses to track and organize its contacts with its current and prospective customers. CRM software is used to support these processes; the software system can be accessed, and information about customers and customer interactions can be entered, stored and accessed by employees in different company departments. Typical CRM goals are to improve services provided to customers, and to use customer contact information for targeted marketing.

Merits

• Provide better customer service
• Increase customer revenues
• Discover new customers
• Cross sell/Up Sell products more effectively
• Help sales staff close deals faster
• Make call centers more efficient
• Simplify marketing and sales processes

Some types of CRM

• Sales and purchase data
• Account information
• Web registration data
• Service and support records

Some Animal Test

Some of the ANImal Test

here, are they

• Duck Test
• Elephant Test
• Monkey Test
• Guerrilla Test

Duck Test

Wiki says "The duck test is a humorous term for a form of inductive reasoning. It can be explained this way:

If it looks like a duck, swims like a duck and quacks like a duck, then it probably is a duck.

The test implies that a person can figure out the true nature of an unknown subject by observing this subject's readily identifiable traits. It is sometimes used to counter abstruse arguments that something is not what it appears to be.

  Whether this is Duck?

Figure out the Image through Duck Test.

Elephant Test

 Something very hard to describe, but it can be instantly recognizable when spotted.

Moneky Test(ing)

It is a random testing  for unit test without any intention, performed by automation tools.

Example "A monkey test can enter random input to the inputs box to ensure handling of all the valid inputs by the users"

It comes from the famous quotes  ‘a thousand monkeys at a thousand typewriters will eventually type out the entire works of Shakespeare’

Guerrilla Test(ing)

Testing one particular model or functionality heavily without knowing the steps to do it.

How to Set Isolation level and View

To set Isolation level, Say Read Uncommitted.

mysql>SET GLOBAL TRANSACTION ISOLATION ;

LEVEL READ UNCOMMITTED;

The default Isolation level is Repeatable Read.

To view current Isolation Level

mysql>select @@tx_isolation;

Sanity VS Smoke

Smoke Test:

When a build is received, a smoke test is run to ascertain if the build is stable and it can be considered for further testing.

Smoke testing can be done for testing the stability of any interim build.

Smoke testing can be executed for platform qualification tests.

Sanity testing:

Once a new build is obtained with minor revisions, instead of doing a through regression, a sanity is performed so as to ascertain the build has indeed rectified the issues and no further issue has been introduced by the fixes. Its generally a subset of regression testing and a group of test cases are executed that are related with the changes made to the app.

Generally, when multiple cycles of testing are executed, sanity testing may be done during the later cycles after through regression cycles.

Some Difference between them.

1) Smoke testing originated in the hardware testing practice of turning on a new piece of hardware for the first time and considering it a success if it does not catch fire and smoke. In software industry, smoke testing is a shallow and wide approach whereby all areas of the application without getting into too deep, is tested

Sanity test is a narrow regression test that focuses on one or a few areas of functionality. Sanity testing is usually narrow and deep.

2)Smoke test is scripted--either using a written set of tests or an automated test

Sanity test is usually unscripted.

3)Smoke test is designed to touch every part of the application in a cursory way. It's is shallow and wide.

Sanity test is used to determine a small section of the application is still working after minor change.

4)Smoke testing will be conducted to ensure whether the most crucial functions of a program work, but not bothering with finer details. (Such as build verification).

Sanity testing is a cursory testing; it is performed whenever a cursory testing is sufficient  to prove the application is functioning according to specifications. This level of testing is a  subset of regression testing.

5)Smoke testing is normal health check up to a build of an application before taking it to testing in depth.

Sanity testing is to verify whether requirements are met or not, checking all

features breadth- first

Difference between Repeatable Read and Read Committed

Main Difference between them

Repeatable Read: Changes are visible only, when all the

Transaction commit. i.e

example if Connection 1 starts a transaction, lets

Begin;

insert query to insert value;

select query to visible the records;

Remember, the above transaction is not committed using commit stmt.

But the newly inserted value can be viewed even without using

commit within the transaction.

Lets Connection 2 do the same thing

Begin;

insert query to insert value;

select query to visible the records;

No commit stmt used

Now Go to connection 1

Use commit stmt and view the records using select query.

This is the most important part, Connection 1 used committed stmt

and can view the newly inserted records which is used in Connection 1,

but still it cannot view newly inserted records done through connection 2.

Even connection 2 cannot view the newly inserted records done

through connection 1 until commit stmt is used. So to visible the

changes both Connection 1 and Connection 2 has to be committed.

Simply, Connection 1 cannot view the changes of Connection 2 until

Connection 2 commits and Connection 2 cannot view the changes made by

Connection 1 until Connection 1 commits

Read Committed: Changes are visible when anyone of the Transaction

commit them

If both Connection 1 and Connection 2 starts a transaction, changes

are visible when anyone of the Connection commit them.

For more information refer

http://rajesh-mydairy.blogspot.com/2009/01/
transaction-isolation-level.html

Transaction Isolation Level

Reference: http://www.databasejournal.com/features/

mysql/article.php/3393161/

MySQL-Transactions-Part-II---

Transaction-Isolation-Levels.htm

Transaction Isolation Levels

A transaction isolation level sets the default transactional behaviour. Our examples last month all took the default setting. This month, we see how changing the transaction isolation level leads to different results. As the name suggests, the setting determines how isolated each transation is, or what kind of locks are associated with queries inside a transaction. The four levels, in ascending order of strictness, are:

• READ UNCOMMITTED: Barely transactional, this setting allows for so-called 'dirty reads', where queries inside one transaction are affected by uncommitted changes in another transaction.
• READ COMMITTED: Committed updates are visible within another transaction. This means identical queries within a transaction can return differing results. This is the default in some DBMS's.
• REPEATABLE READ: The default isolation level for InnoDB tables. Within a transaction, all reads are consistent.
• SERIALIZABLE: Updates are not permitted in other transactions if a transaction has run an ordinary SELECT query, i.e. queries are treated as if they had a LOCK IN SHARE MODE, which we saw in action last month.

InnoDB tables support all four SQL standard transaction isolation levels. Be careful when converting code from other DBMS's, as they do not all support all four levels, nor do they all default to the same level.

• SQL SERVER - READ COMMITTED
• Oracle - READ COMMITTED (supports only READ COMMITTED, SERIALIZABLE and the non-standard READ ONLY)
• DB2 - REPEATABLE READ (supports REPEATABLE READ, UNCOMMITTED READ and 2 non-standard levels)
• PostgreSQL - REPEATABLE READ (only supports REPEATABLE READ and SERIALIZABLE)

For those who have skipped Part 1, we are using the following table to test with:

mysql> DESC t;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| f | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.05 sec)

You can create and populate it as follows:

mysql> CREATE TABLE t (f INT) TYPE = InnoDB;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t values (1),(2),(3),(4),(55);

Query OK, 5 rows affected (0.00 sec)

If you have not made a specific change to the transaction isolation level, it will be a repeatable read. You can check this as follows:

mysql> SELECT @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)

Repeatable Reads

In this exercise, we begin a transaction, and see if a committed insert from another transaction is visible in the midst of the transaction.

Connection 1

mysql> BEGIN;

Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM t;

+------+

| f |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 55 |

+------+

5 rows in set (0.00 sec)

Connection 2

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES(6);

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;

+------+

| f |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 55 |

| 6 |

+------+

6 rows in set (0.00 sec)

Remember that it does not matter to the second connection that the SELECT statement was run after the COMMIT. Within the transaction, the new record is immediately visible.

Connection 1:

mysql> SELECT * FROM t;

+------+

| f |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 55 |

+------+

5 rows in set (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;

+------+

| f |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 55 |

| 6 |

+------+

6 rows in set (0.00 sec)

This is the essence of the repeatable read. The SELECT query returns a consistent result within a transaction, and new records added from another window during the transaction are not immediately visible. For a result to be visible, both the updating transaction, and any transactions that are already open, needs to be committed.

Uncommitted Reads

Let's see what happens with the READ UNCOMMITTED transaction isolation level. To change this, you will need to have the SUPER privilege.

mysql> SET GLOBAL TRANSACTION ISOLATION 

  LEVEL READ UNCOMMITTED;

Query OK, 0 rows affected (0.00 sec)

Use two new connections for the exercise below, as the new transaction isolation level takes effect only for new connections made after the command is run.

Connection 1:

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

+------+

6 rows in set (0.00 sec)

Connection 2:

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (7),(8);

Query OK, 1 row affected (0.06 sec)

Connection 1:

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

+------+

6 rows in set (0.00 sec)

This is known as a dirty read - the new records have not even been committed by the second transaction, yet they are still visible to the first transaction.

Connection 2:

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

Connection 1:

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

+------+

6 rows in set (0.00 sec)

There are dangers with this level of isolation and it bends the rules of transactions. You would only want to use this where you really do not care about the consistency of your results, but do care about potential locks impacting performance.

Committed Reads

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

Connection 1:

mysql>BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

+------+

6 rows in set (0.00 sec)

Connection 2:

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (7),(8);

Query OK, 1 row affected (0.05 sec)

Connection 1:

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

+------+

6 rows in set (0.00 sec)

Connection 2:

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

Connection 1:

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

|    7 |

|    8 |

+------+

8 rows in set (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

The important difference is that an uncommitted INSERT did not have any impact on the transaction in Connection 1. Once the second connection's transaction was committed, the result was visible in the first transaction. It is also important to distinguish the difference between this and the default repeatable read transaction isolation level we saw at the beginning. With READ COMMITTED, changes are visible when other transactions commit them. With REPEATABLE READ, changes are only visible when both other transactions commit them, and only in a new transaction. Understanding this important point brings you to the essence of the difference between the two states.

Serializable

Serializable takes locking a step further than even REPEATABLE READ. In this state, all plain SELECT queries are treated as if they had a LOCK IN SHARE MODE appended.

Connection 1:

mysql> BEGIN;

Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

|    7 |

|    8 |

+------+

8 rows in set (0.06 sec)

Connection 2:

mysql> BEGIN;

Query OK, 0 rows affected (0.06 sec)

mysql> UPDATE t SET f=88 WHERE f=8; 

Because of the SELECT statement from the first connection, the UPDATE is locked, just as with an ordinary LOCK IN SHARE MODE. Only once the first transaction is committed does the UPDATE take place.

Connection 1:

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

Connection 2:

Query OK, 1 rows affected (4.23 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;

+------+

| f    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|   55 |

|    6 |

|    7 |

|   88 |

+------+

8 rows in set (0.00 sec)

Conclusion

Repeatable reads makes sense as a default transaction isolation level, and in most cases, you are unlikely to want to change this. Locking issues can lead to endless hours of fun if you are not careful and do not take note of the subtleties.