FAQ - Database Testing ( 41 to 50 )

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

REVOKE

42. What are the privileges that can be granted on a table by a user to others?

INSERT, UPDATE, DELETE, SELECT, REFERENCES, INDEX, EXECUTE, ALTER, ALL

43. Which is the subset of the SQL commands used to manipulate Oracle database structures?

Data Definition Language (DDL)

44. What is Materialized View?

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely or used to create summary tables based on aggregation of a tables data. Materialized views, which store data based on the remote tables are also, know as snapshots.

45. How to write a SQL statement to find the first occurrence of a Non-Zero value?

There is a slight chance the column "a" has a value of 0 which is not null. In that case, you'll loose the information. There is another way of searching the first not null value of a column:SELECT COLUMN_NAME FROM TABLE_NAME WHERE COLUMN_NAME IS NOT NULL AND ROWNUM<2;>

46. Is there any query which is use to find the case sensitivity in each records in database through visual basic?

For case sensitive string comparison in SQL one has to use substring() and ascii() functions in the following way.Get first character of both strings using substring function as substring(str1, 1, 1)Find ascii value of both characters and compare.Put statements 1 and 2 in loop to advance to next characterFor example if (ascii(substring(str1, @pos, 1)) = ascii(substring(str2, @pos, 1)) then @pos = @pos + 1.

47. Which system table contains information on constraints on all the tables created?

USER_CONSTRAINTS

48. What operator tests column for the absence of data?

IS NULL operator.

49. If DELETE ANY TABLE in back-end then what are the triggers will fire automatically (Those triggers are back-end triggers only)?

Oracle has Schema triggers (CREATE OR REPLACE TRIGGER ... ON SCHEMA ... that will
file on DDL commands. You can do things like CREATE OR REPLACE TRIGGER SAVE_OUR_DBBEFORE DROP OR TRUNCATE ON SCHEMAto stop/log attempts to drop a table.

50. How to write a SQL statement to query the result set and display row as columns and columns as row?

TRANSFORM COUNT(ROLL_NO) AS COUNTOFROLL_NO SELECT ACADEMIC_STATUSFROM TBL_ENR_STATUS GROUP BY ACADEMIC_STATUS PIVOT CURNT_STATUS;