FAQ - Database Testing ( 21 to 30 )

21. There is Eno and Gender in a table. Eno has primary key and Gender has a check constraints for the values 'M' and 'F'. While inserting the data into the table, M was misspelled as F and F as M. What is the update statement to replace F with M and M with F?

UPDATE SET GENDER= CASE WHERE GENDER='F' THEN 'M'WHERE GENDER='M' THEN 'F';

22. What is the use of the DROP option in the ALTER TABLE command?

It is used to drop constraints specified on the table.

23. What is the difference between TRUNCATE and DELETE command?

Both will result in deleting all the rows in the table. TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server TRUNCATE is much faster. Whereas DELETE call is an DML command and can be rolled back.

24. Consider the below statements,
TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?

Both will result in deleting all the rows in the table EMP.

25. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?

The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

26. Which date function is used to find the difference between two dates?

MONTHS_BETWEEN

27. Why you need indexing? Where that is stored and what you mean by schema object? For what purpose we are using view?

We can’t create an Index on Index. Index is stored in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view. Indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is, view needs to be getting refreshed for retrieving updated data.

28. How to find second maximum value from table?

SELECT MAX (FIELD1) FROM TNAME1 WHERE FIELD1=(SELECT MAX (FIELD1) FROM TNAME1 WHERE FIELD1<(SELECT MAX (FIELD1) FROM TNAME1);Field1- Salary fieldTname= Table name.

29. How can we backup the SQL files?

You can backup the SQL files through backup utilities or some backup command in SQL. SAP is ERP software for the organization to integrate the software.

30. How to find out the 10th highest salary in SQL query?

Table - Tbl_Test_Salary Column - int_salarySELECT MAX (INT_SALARY) FROM TBL_TEST_SALARY WHERE INT_SALARY IN(SELECT TOP 10 INT_SALARY FROM TBL_TEST_SALARY ORDER BY INT_SALARY)