Table of Contents
26. What is SGA?
SGA is known as System Global Area. As soon as DB instance is created by database, it allocates certain amount of memory for the instance to read / write user request. This is the part of memory called SGA. Once the instance is closed / shutdown, these memories are deallocated from instance.
This is the most important memory structure of DB Instance. It contains shared memory structure to store cached data and control files that are necessary for the DB that it is using. That means, it is the portion of memory in the main memory, which contains the control file information about the DB it is connected to. It is required for an instance because it should know to which database it is connected to. It also contains the cached data from the disk, i.e.; when we query the database for the first time, the processes in the DB will first check in these memory areas for the data. If not found, then it will search in the data files which are in the secondary memory / disk of the DB or the physical structures of a DB. These files retrieved from the physical location of the DB are then kept in SGA for the future use. This makes the data retrieval faster (query main memory is faster than secondary memory). This will be kept in this main memory as long as it is queries. When the use / query on these data become least frequent, then it will be removed from SGA and space is freed for other retrieved records.
SGA mainly consists of Shared Pool, Large Pool, Buffer cache, Redo logs and other pools as shown in below diagram.
27. What is the difference between Data Mining and Data Warehouse ?
These two are interdependent concepts in a database. In simple words, data mining is a process of analyzing the data from the database. A database is composed of tables and their data. All these data’s are related to some real world process. When all these related data are analyzed, the user will get some useful information from it to promote their business. Such analysis using the existing data in the database is called data mining.
For example, consider the supermarket database. It will have all the records about the items in its store, number of users purchasing the items on each day and the peak hours in each day etc. All these data will be scattered in different tables in the database. A data mining analyst will pick up all these data from the DB, and analyze them to get some report. This report may include; which item has more demand on which day of the week? what is the trend of people purchasing particular category of items? What are the common items purchased by almost all the buyers etc.? Depending on this analysis, store keeper can increase the item numbers on particular day to meet the requirement of buyers, keep the most commonly purchased items together so that buyers need not go around searching for them, he can provide or not to provide any offer on items that are highly purchased on particular day of the week etc. All this analysis done on the data will help to improve the business of the store keeper. This process of analyzing the database data is called data mining.
A data warehousing is the process of accumulating all the tables and relationships in the database in such a way that data mining can be done very easily. In other words, data warehouse needs technical experts to build the database and data mining activity requires only analysts who has hardly have any technical knowledge.
In our example above, the supermarket database is designed and developed by a technical expert. This designing and developing process of database is called data warehouse. It distributes all related data among various related table, so that any data mining experts who are analyzing the data can easily get all required details.
In short, for data mining activity, a perfect data warehouse has to be set. A perfect data warehouse can set up by technical experts and data mining can be done by any users or individuals or any data analysts who does not have any technical knowledge.
28. What is the difference between the RANK () and DENSE_RANK () functions in Oracle? Also provide examples.
RANK is a function which assigns the numerical numbers to the column values based on the order of their values – either ascending or descending. In other words, list of values will be sorted first and then their order of appearance will be assigned using RANK function.
Consider the EMPLOYEES table where we need to find the RANK for the SALARY in ascending order. Then we can write query as below.
SELECT EMPLOYEE_ID, SALARY, RANK () OVER (ORDER BY SALARY) AS RANK_SAL FROM EMPLOYEES;
This example tells a lot about RANK function. In order to apply ranking, we need to have that particular column in sorted order. Otherwise, ranking the column does not have any sense. Since we have sorted the SALARY column in ascending order, the smallest SALARY value has got rank one, next smallest value has got rank 2 and so on. If two employees have same SALARY, then both of their salary will be assigned same rank. It indicates, both the salaries appear at the same order. In our example above, second and third employees have same salary and they have been given 2nd rank based on their salary. But the rank function will allot 4th rank to the forth employee even though third employee is at 2nd rank. That means, it will skip the number of ranks equal to number of same values that appears.
DENSE_RANK () is also similar to RANK () function, which assigns numerical order to the values in ascending or descending order. The only difference between RANK () and DENSE_RANK () is that, it assigns contiguous rank for the records, even if there are same values for more than once.
SELECT EMPLOYEE_ID, SALARY, DENSE_RANK () OVER (ORDER BY SALARY) AS RANK_SAL FROM EMPLOYEES;
29. What is an Inline View in SQL? Provide an example?
Inline views are derived tables. These inline views are the sub queries written in the FROM clause of another query. Since inline view queries are in another query, they will be acting like a view. In other words, since these queries are in FROM clause, they do not have any database schema and exists only till main query ends. Once it is done, it disappears. That is why these types of queries are called as inline view. They are also known as derived tables, as the query is in FROM clause and they are not any existing permanent table in the database.
The normal CASE statement will not allow any conditional comparisons. It will always check if the value to be compared is equal to the one listed in the CASE Statement.
SELECT emp.EMPLOYEE_ID, emp.FIRST_NAME, emp.LAST_NAME, emp.SALARY, CASE SALARY WHEN 13000 THEN 'Thirteen Thousand' WHEN 14000 THEN 'Fourteen Thousand' WHEN 17000 THEN 'Seventeen Thousand' ELSE 'Please enter valid Salary' END AS SAL_DESC FROM EMPLOYEES emp;
Consider the query below. Here EMPLOYEES table is joined with jbs inline view/ derived table. This inline view is derived from JOBS table in the database.
SELECT emp.* FROM EMPLOYEES emp, (SELECT JOB_ID FROM JOBS WHERE JOB_TITLE LIKE '%Account%') jbs WHERE emp.JOB_ID = jbs.JOB_ID;
30. What is the SQL CASE Statement? Provide an example.
CASE statement is another type conditional check available in SQL. CASE statement contains a condition which needs to be checked against various values. If any one of the listed value matches, then its block of code will be executed, else it will have default block which will be executed. If it does not have default block, then NULL will be returned.
Consider below query to print the salary range of each employee. Here we are comparing the salary of each employee with the threshold values. It checks sequentially from first comparison. If it matches while comparing first value itself, then it will print that value and proceed to close the pending task from the query. If the second value matches the comparison condition, then second message will be printed. Suppose there is no match lists in below query. In such case, it will not even execute any of these comparison conditions.
SELECT emp.EMPLOYEE_ID, emp.FIRST_NAME, emp.LAST_NAME, emp.SALARY, CASE WHEN SALARY < 10000 THEN 'Below Average' WHEN SALARY <20000 THEN 'Average' WHEN SALARY < 30000 THEN 'Above Average' WHEN SALARY >=30000 THEN 'HIGH Salary' ELSE 'Please enter valid Salary' END AS SAL_DESC FROM EMPLOYEES emp;
These type of CASE statement is known as Searched CASE statement. It gives more flexibility to compare the values by using comparison operators like <, >, <=, >= etc