FAQ - Database Testing ( 51 to 60 )

51. What is difference between Oracle & MS Access? What are the disadvantages in Oracle and MS Access? What are features in Oracle and MS Access?

Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.

52. What will be the output of the following query? SELECT DECODE (TRANSLATE (‘A’,’1234567890’,’1111111111’), '1','YES', ‘NO’);?

No.
Explanation:
The query checks whether a given string is a numerical digit.

53. Given an employee and manager table, write a SQL syntax that could be used to find out an employee's manager's manager, assuming all managers are in the employee table?

It is assumed that u have created a single table for populating data of Employee and Manager (.a Manager is also an Employee so s/he will be residing in the same table).The sample data would be like thisEmployeeID ----- EmployeeName ------- ManagerID1 Akhtar 02 Bilal 13 Faheem 2** This query will return "Akhtar" for given EmployeeID = 3.SELECT m.EmployeeName FROM #EmpTemp m WHERE m.EmloyeeID = (SELECT e.ManagerID FROM #EmpTemp e WHERE e.EmloyeeID = (Select t.ManagerID FROM #EmpTemp t WHERE t.EmloyeeID = 3))** This is solution is workable in case of 3-levels only not even to 2-levels of hierarchy. An n-level solution can be achieved by writing a Recursive stored procedure and that will also be given soon.
The best query is to create the dummy table and then make the relations,SELECT EMP.EMPLOYEEID, EMP.EMPLOYEENAME, EMP.MANAGERID,EMPTEMP.EMPLOYEENAME MANAGERFROM EMPLOYEES EMP ,EMPLOYEES EMPTEMPWHERE EMP.MANAGERID=EMPTEMP.EMPLOYEEID

54. When using COUNT (DISTINCT) is it better to use a self-join or temp table to find redundant data, and provide an example?

Instead of this we can use GROUP BY Clause with HAVING condition.For ex,SELECT COUNT(*),LASTNAME FROM TBLUSERS GROUP BY LASTNAME HAVING COUNT(*)>1This query return the duplicated lastnames values in the lastname column from tblUsers table.

55. What are the advantages and disadvantages of primary key and foreign key in SQL?

Primary key
Advantages
1) It is a unique key on which all the other candidate keys are functionally dependent
Disadvantage
1) There can be more than one keys on which all the other attributes are dependent on.

Foreign Key
Advantage
1) It allows referencing another table using the primary key for the other table.

56. What is difference between Co-related sub query and nested sub query?

Correlated sub-query runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested sub-query runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.For example, Correlated Sub-query: SELECT E1.EMPNAME, E1.BASICSAL, E1.DEPTNO FROM EMP E1 WHERE E1.BASICSAL = (SELECT MAX (BASICSAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO)Nested Sub-query: SELECT EMPNAME, BASICSAL, DEPTNO FROM EMP WHERE (DEPTNO, BASICSAL) IN (SELECT DEPTNO, MAX(BASICSAL) FROM EMP GROUP BY DEPTNO)

57. What is the back end processes when we type "SELECT * FROM TABLE"?

First it will look into the System Global Area (SGA) weather the query is been executed earlier. If it exists, it would retrieve the same output present in memory. If not the query we typed is complied and the resulting parse tree and execution plan is been stored in SGA. Then query gets executed and output is given to the application.

58. What is a Cursor?

To retrieve data with SQL one row at a time you need to use cursor processing. Not all relational databases support this, but many do. Here I show this in Oracle with PL/SQL, which is Procedural Language SQL. Cursor processing is done in several steps:1. Define the rows you want to retrieve. This is called declaring the cursor.2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.3. Fetch the data into variables.4. Close the cursor.

59. What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g. sp_helpdb, sp_renamedb, sp_depends etc.

60. How do I write a cron, which will run a SQL query and mail the results to a group?

Use DBMS_JOB for scheduling a cron job and DBMS_MAIL to send the results through email.