Author: Shameer
•11:35 PM
Query 1

   SELECT MAX(salary) FROM employee
                                       WHERE salary NOT IN
 ( SELECT MAX(salary) FROM employee)


Query 2 

     SELECT MAX(salary) FROM employee
                                         WHERE salary <
   ( SELECT MAX(salary) FROM employee)

Query 3 (for SQL Server)


    SELECT MIN (salary) FROM employee
                                       WHERE salary in
(SELECT TOP 2 (salary) FROM employee
                                 ORDER BY salary DESC)



Author: Shameer
•8:54 PM
Open Microsoft SQL Server -> Enterprise Manager

Click here for larger image


Click here for larger image

Right Click and Select New SQL Server Registration

Click here for larger image

Click Next.
Click here for larger image

Enter the Servername and click Add button

Click here for larger image


Then Click Next button

Click here for larger image


Again Click Next

Click here for larger image

Click Next


Click here for larger image


Click Finish

Click here for larger image


Once the server is connect click Close

Click here for larger image


Click here for larger image

Now the server is connected and it will list the database. Select the corresponding database.

Click here for larger image



Click here for larger image

Click Stored Procedure from the Tree

Click here for larger image


Select all the procedure in the right side grid using Ctrl + A

Click here for larger image

Now right click select All Tasks -> Generate SQL Script and click Ok


Click here for larger image
Give a name for the script and select the path to Save

Click here for larger image

Progressing

Click here for larger image

Now the script is saved successfully

Click here for larger image

Now we can view the script from the My Documents folder where we saved initially
Author: Shameer
•9:46 PM

How to change security authentication mode

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
  4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login

  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  2. On the General page, you might have to create and confirm a password for the login.
  3. On the Status page, in the Login section, click Enabled, and then click OK.

Using Transact - SQL

To enable the sa login
  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute. The following example enables the sa login and sets a new password.
    ALTER LOGIN sa ENABLE ;
    GO
    ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
    GO
    
Author: Shameer
•3:12 AM


Click here for larger image





Click here for larger image

Click here for larger image


Click here for larger image


Click here for larger image


Click here for larger image


Click here for larger image


Click here for larger image


Click here for larger image


Click here for larger image


Click here for larger image


Author: Shameer
•2:17 AM

Input Data



Query to display the duplicate values from the table
---------------------------------------------------


SELECT a.* FROM dbo.dept As [a]
 INNER JOIN
  (
      SELECT dept_name, mgr_name FROM   dbo.dept
    GROUP BY dept_name, mgr_name
      HAVING Count(*)  >1
  ) As [b]
    ON a.dept_name = b.dept_name
   AND a.mgr_name  = b.mgr_name


Output
---------



Query to delete the duplicate values from the table
--------------------------------------------------------

 DELETE FROM dbo.dept
   WHERE dbo.dept.dept_id IN

-- List 1 - all rows that have duplicates
 (SELECT T1.dept_id
    FROM dbo.dept AS T1
   WHERE Exists (SELECT dept_name, mgr_name, Count(dept_id)
    FROM dbo.dept
   WHERE dbo.dept.dept_name = T1.dept_name
     AND dbo.dept.mgr_name = T1.mgr_name
GROUP BY dbo.dept.dept_name, dbo.dept.mgr_name
  HAVING Count(dbo.dept.dept_id) > 1))
     AND dbo.dept.dept_id NOT IN

-- List 2 - one row from each set of duplicate
 (SELECT Min(dept_id)
    FROM dbo.dept AS T1
   WHERE Exists (SELECT dept_name, mgr_name, Count(dept_id)
    FROM dbo.dept 
   WHERE dbo.dept.dept_name = T1.dept_name
     AND dbo.dept.mgr_name = T1.mgr_name
GROUP BY dbo.dept.dept_name, dbo.dept.mgr_name
  HAVING Count(dbo.dept.dept_id) > 1)
GROUP BY dept_name, mgr_name)


Output
----------




Author: Shameer
•5:00 AM


 1) What is Database testing ?
          Testing the backend databases like comparing the actual results with expected results.
 2). What is database testing and what we test in database testing
      Data bas testing basically include the following.
1) Data validity testing.
2) Data Integrity testing
3) Performance related to database.
4) Testing of Procedure, triggers and functions.
       For doing data validity testing you should be good in SQL queries
For data integrity testing you should know about referential integrity and different constraint.
For performance related things you should have idea about the table structure and design.
For testing Procedure triggers and functions you should be able to understand the same.

 3). What we normally check for in the Database Testing?
      Database testing involves some in-depth knowledge of the given application and requires more defined plan of approach to test the data. Key issues include:
1) data Integrity
2) data validity
3) data manipulation and updates.

Tester must be aware of the database design concepts and implementation rules
 4). How to Test database in Manually? Explain with an example
       Observing that operations, which are operated on front-end is effected on back-end or not.
The approach is as follows:
While adding a record thru' front-end check back-end that addition of record is effected or not.
So same for delete, update...

Ex: Enter employee record in database thru' front-end and check if the record is added or not to the back-end (manually).
5). How to test a SQL Query in WinRunner? With out using Database Checkpoints?

     By writing scripting procedure in the TCL we can connect to the database and we can test database and queries.

6) .How does you test whether a database in updated when information is entered in the front end?

         With database check point only in WinRunner, but in manual we will go to front end using some information. Will get some session names using that session names we search in backend. If that information is correct then we will see query results.

7). What are the different stages involved in Database Testing
In DB testing we need to check for,
1. The field size validation
2. Check constraints.
3. Indexes are done or not (for performance related issues)
4. Stored procedures.
5. The field size defined in the application is matching with that in the db.

8). What SQL statements have you used in Database Testing?

  DDL
DDL is Data Definition Language statements. Some examples: · CREATE · ALTER - · DROP -· TRUNCATE -· COMMENT - · RENAME –
DML

DML is Data Manipulation Language statements. Some examples: · SELECT - · INSERT - · UPDATE - · DELETE - · MERGE - UPSERT -· CALL - · EXPLAIN PLAN - · LOCK TABLE –

DCL
DCL is Data Control Language statements. Some examples: · GRANT - · REVOKE - · COMMIT - · SAVEPOINT - · ROLLBACK - COMMIT -· SET TRANSACTION - This are the Database testing commands.


9). How to use SQL queries in WinRunner/QTP
     
In QTP Using output database check point and database check point,Select SQL manual queries option And enter the "select" queries to retrieve data in the database and compare  the expected and actual.
10). What steps does a tester take in testing Stored Procedures?
             In my view, the tester has to go through the requirement, as to why the particular stored procedure is written for? And check whether all the required indexes, joins, updates, deletions are correct comparing with the tables mentions in the Stored Procedure.  And also he has to ensure whether the Stored Procedure follows the standard format like comments, updated by, etc.

11). How to check a trigger is fired or not, while doing Database testing?

             It can be verified by querying the common audit log where we can able to see the triggers fired.

12). Is an "A fast database retrieval rate" a testable requirement?
          Since the requirement seems to be ambiguous. The SRS should clearly mention the performance or transaction requirements i.e. It should say like 'A DB retrieval rate of 5 micro sec'.

13). How to test a DTS package created for data Insert, update and delete? What should be considered in the above case while testing it? What conditions are to be checked if the data is inserted, updated or deleted using a text files?
         Data Integrity checks should be performed.  IF the database schema is 3rd normal form, then that should be maintained.  Check to see if any of the constraints have thrown an error.  The most important command will have to be the DELETE command.  That is where things can go really wrong.
Most of all, maintain a backup of the previous database.


14). How to test data loading in Data base testing
         
Using with Query analyzer.

You have to do the following things while you are involving in Data Load testing.
1. You have to know about source data (table(s), columns, data types and Constraints)
2. You have to know about Target data (table(s), columns, data types and Constraints)
3. You have to check the compatibility of Source and Target.
4. You have to Open corresponding DTS package in SQL Enterprise Manager and run the DTS package (If you are using SQL Server).
5. Then you should compare the column's data of Source and Target.
6. You have to check the number to rows of Source and Target.
7. Then you have to update the data in Source and see the change is reflecting in Target or not.
8. You have to check about junk character and Nulls.



15). What is way of writing test cases for database testing?
         
You have to do the following for writing the database test cases.
1. First of all you have to understand the functional requirement of the application thoroughly.
2. Then you have to find out the back end tables used, joined used between the tables, cursors used (if any), triggers used (if any), stored procedures used (if any), input parameter used and output parameters used for developing that requirement.
3. After knowing all these things you have to write the test cases with different input values for checking all the paths of SP.
One thing writing test cases for backend testing not like functional testing. You have to use white box testing techniques.
Author: Shameer
•8:02 AM
SQL - View

             A SQL View is a virtual table, which is based on SQL SELECT query. A view consists of rows and columns just like a table,except that the real tables store data, while the views won’t store data. The difference between a view and a table is that views are definitions built on top of a single table or multiple tables (or on top of another view), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables.

Views ensure the security of data by restricting access to the following data:

• Specific rows of the tables.
• Specific columns of the tables.
• Specific rows and columns of the tables.
• Rows fetched by using joins.
• Statistical summary of data in a given tables.
• Subsets of another view or a subset of views and tables.

Some common examples of views are:
• A subset of rows or columns of a base table.
• A union of two or more tables.
• A join of two or more tables.
• A statistical summary of base tables.
• A subset of another view, or some combination of views and base table.

CREATING VIEWS

A view can be created by using the CREATE VIEW statement

SYNTAX

CREATE VIEW view_name [column_name (s)] AS
SELECT column_name(s)
FROM table_name
WHERE condition [WITH CHECK OPTION]

column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent.

A view can be referenced and used from another view, from a SQL query, and from stored procedure. You can reference a view as you would reference any real SQL database table:

SELECT * FROM view_name

Example

Customer_Info


Product_Info


     CREATE VIEW V_PRODUCT_SALES AS
      SELECT A1.Customer_Name Customer_Info, SUM (A2.Sales) Product_Info
        FROM Customer_Info A1, Product_Info A2
      WHERE A1.Product_Name = A2.Product_Name
GROUP BY A1.Customer_Name

To find the contents of the VIEW

SELECT * FROM V_PRODUCT_SALES



UPDATING A VIEW

SYNTAX


CREATE OR REPLACE VIEW view_name AS
 SELECT column_name(s)
   FROM table_name
 WHERE condition


DROPPING A VIEW

SYNTAX

DROP VIEW view_name

Advantages:

1. A view hides the complexity of the database tables from end users.
2. Views take very little space to store as it is not storing the actual data.
3. A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying table.
4. A view can also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be retrieved using simple queries.

Restrictions imposed on views:

• A view can be created only in the current database.
• The name of a view must follow the rules for identifiers and must not be the same as that of the base table.
• A view can be created only if there is a SELECT permission on its base table.
• A SELECT INTO statement cannot be used in view declaration statement.
• A trigger or an index cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.