FAQ - Database Testing ( 71 to 80 )

71. What is the difference between ORACLE, SQL, and SQL SERVER?

Oracle is based on RDBMS.SQL is Structured Query Language.SQL Server is another tool for RDBMS provided by Microsoft.

72. How to retrieving the data from 11th column to nth column in a table?

SELECT * FROM EMP WHERE ROWID IN ( SELECT ROWID FROM EMP WHERE ROWNUM <=&UPTOMINUSSELECT ROWID FROM EMP WHERE ROWNUM <&STARTFROM)from this you can select between any range.

73. Difference between decode and case. In which case we are using case and in which case we are using decode? Explain with an example?

First I will give one example using 'decode'SQL>SELECT ENAME, SAL, DECODE (DEPTNO, 10, 'ACCOUNTING', 20,'RESEARCH', 30,'SALES', 40,'OPERATIONS','OTHERS') "DEPARTMENTS" FROM EMP;
I have used the decode function on 'deptno' column. It will give the user-friendly output. instead of using 'accounting', ‘research’. We can use anything we want to get the friendly outputs.

I have to check-out the 'case' function after that I will give an example using CASE expression we can use all comparative operators (<, >, ==, etc), where as using DECODE we should always use = condition.

74. What is the main difference between the IN and EXISTS clause in sub-queries?

The main difference between the IN and EXISTS predicate in sub-query is the way in which the query gets executed.IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once.EXISTS -- The first row from the outer query is selected, then the inner query is executed and , the outer query output uses this result for checking. This process of inner query execution repeats as many no. of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no. of times.

75. Difference between an equi-join and union?

Indeed both equi join and the Union are very different. Equi join is used to establish a condition between two tables to select data from them.. eg SELECT A.EMPLOYEEID, A.EMPLOYEENAME, B.DEPT_NAME FROM EMPLOYEEMASTER A , DEPARTMENTMASTER BWHERE A.EMPLOYEEID = B.EMPLOYEEID;This is the example of equijoin whereas with a Union allows you to select the similar data based on different conditions egSELECT A.EMPLOYEEID, A.EMPLOYEENAME FROM EMPLOYEEMASTER A WHERE A.EMPLOYEEID >100 B.EMPLOYEEIDUNION
SELECT A.EMPLOYEEID, A.EMPLOYEENAME FROM EMPLOYEEMASTER A WHERE A.EMPLOYEENAME LIKE 'B%'the above is the example of Union where in we select employee name and Id for two different conditions into the same record set and is used thereafter.

76. How to find out the database name from SQL*PLUS command prompt?

SELECT * FROM GLOBAL_NAME;This will give the database name which you are currently connected to.

77. What is the difference between Single row sub-Query and Scalar sub-Query?

Single row sub-query returns a value that is used by where clause, whereas scalar sub-query is a select statement used in column list can be thought of as an inline function in select column list.


78. What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

79. What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

80. What are the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.