Explain CTE(Common table expression) in oracle sql plsql interview question and answer.
CTE(Common table expression):
It is the result set of the query which exists temporarily. It is used within the context of large query. Result or output will not be stored permanently and will exit only for the duration of the query. It is much like the derived query. It is just like the database views and derived tables. It aids the query creators or builders to write and maintain the complex queries. It helps to increase the readability and simplification. Complexity can be reduced by splitting the complex queries into the smaller sub-blocks for reuse and rewriting the query for fast performance.
It is the temporary result set which is returned by the single statement. It is used further with the same statement. It is not stored anywhere and does not consume disk space. It is the kind of virtual table which contain the columns with physical records. It is created with the result of the query execution. It is used by another query which is deleted after the main query execution. It became popular with the introduction of SQL server 2005.Database specialists are using the complex queries to easy to read and maintain. By using this concept, it is easier for the reader to read and understand. It is an important tool for the data administrator. It acts as the building block for the recursive operations. It is used in debugging the code.
It is used for querying the hierarchical data. It is not compatible with the WHERE clause using IN and EXISTS. This is found in various or several SQL versions. It is used to maintain and readability of SQL query in various versions of database. It is like the subquery which is used to generate result that contain rows and columns of the data.CTE is used inside the queries like select,insert,update and delete. It is like the query which can be defined into another SQL query.
Another name is SQL with clause or subquery refactoring. The term refactoring is used to improve the design of the code. It's feature is available for the following databases are like:
Oracle
SQL Server
MySQL
PostgreSQL
SQLite
MariaDB
With clause query is used to process hierarchical data are called recursive CTE.It is little bit complicated. With clause is available since oracle 9 version and was launched in 2019.It is having feature to create the named query which is used in the rest of the query. In SQL server, it is used in the update statement. There are difference with temp tables. Temp tables
select deptno,avg(sal)
FROM emp
GROUP BY deptno
order by deptno;
with cte
as
(select e.empno,e.ename as Employee_Name,d.dname as Department_Name from emp e
join dept d on e.deptno=d.deptno) select empno,Employee_Name,Department_Name from cte;
create table employee_temp(emp_id number(4),first_name varchar2(10),last_name varchar2(10),dept_id number(4),manager_id number(4),office_id number(4));
create table department_temp(dept_id number(4),dept_name varchar2(10));
INSERT ALL
INTO employee_temp(emp_id,first_name,last_name,dept_id,manager_id,office_id) VALUES('1','Rahul','Kumar','2','4','5')
INTO employee_temp(emp_id,first_name,last_name,dept_id,manager_id,office_id) VALUES('2','Rishav','Mahto','3','5','6')
INTO employee_temp(emp_id,first_name,last_name,dept_id,manager_id,office_id) VALUES('3','Ram','Mohan','4','6','7')
INTO employee_temp(emp_id,first_name,last_name,dept_id,manager_id,office_id) VALUES('4','Sohan','Kumar','5',NULL,'8')
INTO employee_temp(emp_id,first_name,last_name,dept_id,manager_id,office_id) VALUES('5','Shyam','Sharma','6','8','9')
SELECT * FROM DUAL;
select * from employee_temp;
INSERT ALL
INTO department_temp(dept_id,dept_name) values('2','CS')
INTO department_temp(dept_id,dept_name) values('3','IT')
INTO department_temp(dept_id,dept_name) values('4','ECE')
INTO department_temp(dept_id,dept_name) values('5','CIVIL')
INTO department_temp(dept_id,dept_name) values('6','ENGINEER')
SELECT * FROM DUAL;
select e.first_name,e.last_name,d.dept_count FROM employee_temp e
INNER JOIN
(
select dept_id,count(*) AS dept_count
FROM employee_temp
GROUP BY dept_id)d
ON e.dept_id=d.dept_id;
In the result of an inline view is called as d.
with d_count AS(
SELECT dept_id,count(*) AS dept_count
FROM employee_temp
GROUP BY dept_id
)
SELECT e.first_name,
e.last_name,d.dept_count
FROM employee_temp e
INNER JOIN d_count d ON e.dept_id=d.dept_id;
It starts with keyword and the query inside the brackets is called as the d_count.Due to its name and columns,therfore it is treated as like the view. Multiple CTEs are used in the single query.
WITH firstquery AS(
select columns from table),
secondquery AS(
select columns from second_table
)
select main_columns from tables;
select e.emp_id,e.first_name,e.dept_id,
m.emp_id,m.first_name,m.dept_id,m.manager_id from employee_temp e
LEFT JOIN employee_temp m ON e.manager_id=m.emp_id;
with cteEmployee(emp_id,first_name,manager_id,emp_level) AS(
select emp_id,first_name,manager_id,1
FROM employee_temp
where manager_id is null
union all
select e.emp_id,e.first_name,e.manager_id,r.emp_level+1
FROM employee_temp e INNER JOIN cteEmployee r ON e.manager_id=r.emp_id
)
select emp_id,first_name,manager_id,emp_level FROM cteEmployee ORDER BY emp_level;
Inside the brackets,the name given for the columns are used in the main query.
select emp_id,first_name,manager_id,1
FROM employee_temp
where manager_id is null
First part of the recursive is known as the anchor. It will select those records where manager id is null. We have selected the value of 1 in the last column which maps to the emplevel column.
union all
select e.emp_id,e.first_name,e.manager_id,r.emp_level+1
FROM employee_temp e INNER JOIN cteEmployee r ON e.manager_id=r.emp_id
It is the recursion part.
FEATURES:
It cannot have indexes.
It cannot have constraints.
When the query is run, it can exist.
It can be referenced inside the query.
It can be recursive and donot have the recursive stats.
It improves the readability of complex and recursive queries.
Comments
Post a Comment