SQL Questions

What are the wildcards used for pattern matching.?

%

Can you update the data in a view?

A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view. So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables

What is a view?

A view is nothing but a window of an existing table by which one can view and can also change the values in tables. The main point aspect of view is views have no storage space.

What is BREAK?

BREAK command clarifies reports by suppressing repeated values, skipping lines & allowing for controlled break points.

What command is used to create a table by copying the structure of another table?

CREATE TABLE .. AS SELECT command Explanation: To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2; If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

What is Character Functions?

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.

What is Correlated Sub-query?

Correlated Sub-query is a sub-query that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

What is correlated sub-query?

Correlated sub-query is a sub-query, which has reference to the main query.

What is DECODE function used for?

DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

What is the use of DESC in SQL?

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order. Explanation: The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?

Data Definition Language (DDL).

What is Date Functions?

Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.

How to test data loading in Data base testing?

Following steps are necessary for Data Load testing: - You need to know about source data (table(s), columns, data types and Constraints) - You need to know about Target data (table(s), columns, data types and Constraints) - You need to check the compatibility of Source and Target. - You need to Open corresponding DTS package in SQL Enterprise Manager and run the DTS package (If you are using SQL Server). - Then you need to compare the column's data of Source and the Target. - You have to check the number to rows of Source and Target. - Then you need to update the data in Source and see the change is reflecting in Target or not. - You have to check about junk character and NULLs.

With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

INSERT INTO Persons (LastName) VALUES ('Olsen')

What operator tests column for the absence of data?

IS NULL operator.

What we normally check for in the Database Testing?

In Database Testing we need to check: - The field size validation - Check constraints. - Indexes are done or not (for performance related issues) - Stored procedures - The field size defined in the application is matching with that in the db.

What is Index?

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of less than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed.

What is Intersect

Intersect is the product of two tables listing only the matching rows.

What operator performs pattern matching?

LIKE operator.

What is NVL?

Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.

What command is used to get back the privileges offered by the GRANT command?

REVOKE.

Which command displays the SQL command in the SQL buffer, and then executes it?

RUN.

Which SQL statement is used to extract data from a database?

SELECT

With SQL, how do you select all the columns from a table named "Persons"?

SELECT * FROM Persons

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?

SELECT * FROM Persons WHERE FirstName LIKE 'a%'

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

SELECT * FROM Persons WHERE FirstName='Peter'

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

With SQL, how do you select a column named "FirstName"?

SELECT FirstName FROM Persons

What does the following query do?

SELECT SAL + NVL(COMM,0) FROM EMP;? This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.

What is SPOOL?

SPOOL command creates a print file of the report.

What is Savepoint?

Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

Where the integrity constraints are stored in data dictionary?

The integrity constraints are stored in USER_CONSTRAINTS.

What are the advantages of VIEW?

To protect some of the columns of a table from other users To hide complexity of a query To hide complexity of calculations

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true.

True

What is the use of truncate command?

Truncate command will delete all records and the most important thing to make note of truncate command is since truncate command cannot be rolled back one must make sure to take proper backup of table.

Which SQL statement is used to update data in a database?

UPDATE

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

What is Union?

Union is the product of two or more tables.

Does the view exist if the table is dropped from the database?

Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors. If you recreate the table (that you had dropped), the view will again be fine.

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

DELETE FROM Persons WHERE FirstName = 'Peter'

What is the sub-query?

Sub-query is a query whose return values are used in filtering conditions of the main query.

With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

SELECT * FROM Persons ORDER BY FirstName DESC

How to test a SQL Query in QTP? Without using database checkpoints.

By writing scripting procedure we can connect to the database and can test the database and queries. - Connect to the database: db_connect("query1",DRIVER={drivername};SERVER=server_name; UID=uidname;PWD=password;DBQ=database_name "); - Execute the query: db_excecute_query("query1","write query u want to execute"); Condition to be mentioned- - Disconnect the connection: db_disconnect("query");

What is the usage of SAVEPOINTS?

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

What is SQL?

SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.

Which command executes the contents of a specified file?

START or @.

What are the differences between stored procedures and triggers?

The basic difference between stored procedure and trigger is stored procedure can call explicitly, but trigger cannot call explicitly. Generally triggers are used for Audit and trail. What should be the action perform while or before or after insert, update.

What is Transaction?

Transaction is defined as all changes made to the database between successive commits.

State true or false. EXISTS, SOME, ANY are operators in SQL?

True.

Which SQL statement is used to delete data from a database?

DELETE

While doing database testing, how do we know that a trigger is fired or not?

It can be verified by querying the common audit log where we are able to see if the trigger is fired or not.

How can we do manual testing of database? Explain with an example.

It involves observing operations that are operated on front-end are effected on the back-end or not. The approach can be explained like: While adding a record through the front-end, check back-end that addition of record is effected or not; it applies to delete, update etc. For Example: Enter the employee record in database through the front-end and check manually if the record is added or not to the back-end.

What is Minus?

Minus is the product of two tables listing only the non-matching rows.

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

What is SET?

SET command changes the system variables affecting the report environment.

With SQL, how can you return the number of records in the "Persons" table?

SELECT COUNT(*) FROM Persons

Which SQL statement is used to return only different values?

SELECT DISTINCT

What is Rollback?

Rollback causes work in the current transaction to be undone.

What SQL statements have you used in Database Testing?

The most important statement for database testing is the SELECT statement, which returns data rows from one or multiple tables that satisfies a given set of criteria. We can use other DML (Data Manipulation Language) statements like INSERT, UPDATE and DELTE to manage your test data. We can use DDL (Data Definition Language) statements like CREATE TABLE, ALTER TABLE, and DROP TABLE to manage your test tables. We may need some other commands to view table structures, column definitions, indexes, constraints and store procedures.

What is Database testing?

Database testing includes the following. - Data validity testing: For doing data validity testing you should be good in SQL queries. - Data Integrity testing: For data integrity testing you should know referential integrity and different constraints. - Performance related to database: For performance related things you should have idea about the table structure and design. - Testing of Procedure triggers and functions: For this a clear understanding of testing procedure triggers and functions is required. - Database testing is all about testing joins, views, imports and exports, testing the procedures, checking locks, indexing etc. Database testing does not refer to testing of the data in the database. - Usually Database administrators perform database testing

What steps does a tester take in testing Stored Procedure?

First the tester should to go through the requirement, as to why the particular stored procedure is written for. Then check whether all the required indexes, joins, updates, deletions are correct comparing with the tables mentioned in the Stored Procedure. And also she/he has to ensure whether the Stored Procedure follows the standard format like comments, updated by, etc. Then check the procedure calling name, calling parameters, and expected Reponses for different sets of input parameters. Then run the procedure yourself with database client programs like TOAD, or mysql, or Query Analyzer. Rerun the procedure with different parameters, and check results against expected values. Finally, automate the tests with QTP.

What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL?

1200.

What is Synonyms?

A synonym is the alias name for table, views, sequences & procedures and is created for reasons of Security and Convenience. Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

In a Client/Server context, what does API (Application Programming Interface) refer to?

An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.

How do we use SQL queries in QTP?

By 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 results.

What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR the maximum length is 255 and 2000 for VARCHAR2

What are the data types allowed in a table?

CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

What is COLUMN ?

COLUMN command defines column headings & format data values.

What is the use of COUNT with DISTINCT option?

COUNT is a group function. That is which gives a result by summarizing multiple rows. All group value function will have the usage of DISTINCT or ALL option and so is the COUNT which uses the DISTINCT

What's Commit?

Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity checks are uniqueness, consistency and db restrictions.

What's Consistency?

Consistency assures users that the data they are changing or viewing is not changed until they are thro' with it.

Why does the following command give a compilation error?

DROP TABLE &TABLE_NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.

Difference between Function and Store-Procedure in PL/SQL

Functions are compiled and executed at run time. Stored procedures are stored in parsed and compiled format in the database. Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations on the database. Stored Procedures can affect the state of the database by using insert, delete, update and create operations. Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output. Stored procedures are basically used to process the task. Function cannot change server environment and our operating system environment. Stored procedures can change server environment and our operating system environment. Functions cannot be invoked from SQL Statements. Execute. SELECT operating system can be invoked from SQL Statements. Execute. SELECT Functions can run an executable file from SQL SELECT or an action query operating system use Execute to run.

Which SQL statement is used to insert new data in a database?

INSERT INTO

With SQL, how can you insert a new record into the "Persons" table?

INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

What is an integrity constraint?

Integrity constraint is a rule that restricts values to a column in a table.

How do you test whether a database in updated when information is entered in the front end?

It depends upon the application interface. - If your application provides view functionality for the entered data, then you can verify that from front end only. This way Black- box test engineers verify the functionality most of the times. - If your application has only data entry from front end and there is no view from the front end, then you have to go to Database and run relevant SQL query. - You can also use database checkpoint function in QTP.

If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?

It won't, Because SYSDATE format contains time attached with it.

What is JOIN?

JOIN is the form of SELECT command that combines info from two or more tables.

What's Locking?

Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

What is referential integrity constraint?

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

Is an "A fast database retrieval rate" a testable requirement?

No. Since the requirement seems to be ambiguous. The SRS should clearly mention the performance or transaction requirements i.e. It should specify clearly like - Database retrieval rate of 5 microseconds.

Which SQL keyword is used to sort the result-set?

ORDER BY