SQL Interview Questions Part -4

Assume there is view with name as EMP_V so what are all the objects can be created with same Name.


Below are the objects which we can't create with existed object Name.

1.   create or replace synonym emp_v for emp  --Not allowed
2.  create table emp_v (id integer); --Not Allowed
3.  create materialized view emp_v  as select * from emp; --Not Allowed
4.   create sequence emp_v --Not Allowed
5. CREATE OR REPLACE PACKAGE EMP_V IS PROCEDURE P1; END;   --Not Allowed
6. CREATE OR REPLACE PROCEDURE EMP_V IS BEGIN   NULL; END;  -- Not Allowed

Below are the objects which we can create with existed object Name.

1. create or replace public synonym emp_v for emp  --allowed
2. create index emp_v on emp(mgr); --Allowed

ORA-02438: Column check constraint cannot reference other columns

ORA-02438: Column check constraint cannot reference other columns
Cause: attempted to define a column check constraint that references another column.
Action: define it as a table check constraint.

Lets check scenario:

Case 1: Missing Constraint Name

CREATE TABLE EMP_tab (
            EMPNO NUMBER (4,0) PRIMARY KEY,
            ENAME VARCHAR2 (20) NOT NULL,
            MGR NUMBER (4,0) REFERENCES EMP (EMPNO)
                  ON DELETE SET NULL,
            DOB DATE,
            HIREDATE DATE,
            EMAIL VARCHAR2 (50) UNIQUE,
            CONSTRAINT MY_Ch1 CHECK (HIREDATE >= DOB+365*16),
            CONSTRAINT  CHECK  ((INSTR  (EMAIL, '@') > 0 AND (INSTR(EMAIL,'.') > 0))
      ))

ORA-02438: Column check constraint cannot reference other columns


Here add explicit constraint name for last check constraint and issue will be fixed.

SQL> CREATE TABLE EMP_tab (
              EMPNO NUMBER (4,0) PRIMARY KEY,
              ENAME VARCHAR2 (20) NOT NULL,
              MGR NUMBER (4,0) REFERENCES EMP (EMPNO)
                    ON DELETE SET NULL,
              DOB DATE,
              HIREDATE DATE,
              EMAIL VARCHAR2 (50) UNIQUE,
              CONSTRAINT MY_Ch1 CHECK (HIREDATE >= DOB+365*16),
              CONSTRAINT  My_Ch2 CHECK  ((INSTR  (EMAIL, '@') > 0 AND (INSTR(EMAIL,'.') > 0))
        ));

Table created


Case 2: Specify Comma before adding constraints.

SQL> create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date check(txn_amount > 100));
create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date check(txn_amount > 100))

ORA-02438: Column check constraint cannot reference other columns

rewrite with comma as below

SQL> create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date, check(txn_amount > 100));

Table created

Multi Table Insert Statement

Multi Table Insert:

Its very important concept to know when we have scenario to insert data to multiple tables based on single query output.

say eg: Usually we use external table to read data from local files and then to load to target tables.

so here instead of writing complex program to load data from external tables to Target tables ,we can simply use Multi table insert concept.

Lets go through examples.

Assume our scenario is load data from EMP table (In SCOTT Schema) data to another 2 different target tables.

(Note: Here you can take your external table also instead of EMP table).

Creating 2 target tables as below:


create table dest_table1 as select * from emp where 1=2;



create table dest_table2 as select * from emp where 1=2;

Case 1: Unconditional Insert All

When we are using Unconditional Insert All, the results which are returned by query(Driving Query) will insert as new record in target tables which are specified in INTO clauses.




select * from emp where rownum<=4

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING        PRESIDENT           11/17/1981   5000.00               10
 7698 BLAKE      MANAGER    7839  5/1/1981      2850.00               30
 7782 CLARK      MANAGER    7839  6/9/1981      2450.00               10

 7566 JONES      MANAGER    7839  4/2/1981      2975.00               20


SQL> INSERT ALL
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
             INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


8 rows inserted

In  Above example ,the Driving query returns 4 records so each target table(which are in INTO clause) will get 4 new records) so total 8 records for 2 tables. 

So basically here we are not applying any condition using "INSERT ALL", it means whatever records are coming from driving query will get inserted to target tables.

So how can we achieve if we have scenario to insert records based on condition ,Now we have to go for case 2.

Case 2: Conditional Insert ALL

In this case, we can add conditions to the INTO clause.Its looks like a CASE statement but here each condition is always tested for current row from the Driving query.

SQL> INSERT ALL
     WHEN DEPTNO=10 THEN //This case will be evaluated 4 times as we 4 records in driving query even though for deptno 10 records are 2.
           INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN DEPTNO IN (20,30) THEN
 //This case will be evaluated 4 times as we 4 records in driving query even though for deptno 20,30 records are 2.
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


4 rows inserted

Hope you got clarity on how Conditional Insert All works.

In below case ,we added 1=1 at the end ,It means its always true so all records will be inserted into target table here.

SQL> INSERT ALL
   WHEN DEPTNO=10 THEN //here 2 records will be inserted
        INTO dest_table1  
VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN DEPTNO IN (20,30) THEN  //here 2 records will be inserted
              INTO dest_table2
VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   WHEN 1=1 THEN //here 4 records will be inserted
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


8 rows inserted

Case 3: Insert First

In this case conditions are tested until First Match is found and no further conditions are tested.

Lets take EMP table itself to understand clearly.

SQL> select * from emp
  where rownum<=4;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING        PRESIDENT          11/17/1981    5000.00               10
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00                 30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00                 10

 7566 JONES      MANAGER    7839 4/2/1981      2975.00                 20

SQL> INSERT ALL
     WHEN SAL>4000 THEN
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN SAL>2500 THEN
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   SELECT * FROM emp
    where rownum<=4;


4 rows inserted

I have taken INSERT ALL case here first and then will show INSERT FIRST.

As Per INSERT ALL,each condition is always tested for current row. so "dest_table1 " table will have only one record i.e with Salary of 5000/- but another table "dest_table2" will have 3 records i.e. with salary of 5000,2850,2975. Here if you observe 5000 salary record is getting inserted in 2nd table also even though it got inserted in first table so to avoid this we have INSERT FIRST case.

SQL> INSERT FIRST
    WHEN SAL>4000 THEN //here only one record
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN SAL>2500 THEN //here only 2 records
            INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;
  

3 rows inserted


The restrictions on multi table inserts are as follows.

• Multi table inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multi table insert via a DB link.
• You cannot perform multi table inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the multi table insert statement. It is considered a single statement, so only one sequence value will be generated and used for all rows.
• Multi table inserts can't be used with plan stability.
• If the PARALLEL hint is used for any target tables, the whole statement will be parallelized. If not, the statement will only be parallelized if the tables have PARALLEL defined.
• Multi table statements will not be parallelized if any of the tables are index-organized, or have bitmap indexes defined on them.

Analytical Functions in Oracle

We use analytical functions instead writing sub queries and joins where requirement comes along with aggregation.

Analytical functions extends the power of SQL, which means we can write queries in less lines of code with analytical function when compared with Native SQL.

Lets start with Aggregate functions (MIN, MAX, AVG,COUNT,SUM) to explore analytical  functions.

Note: We are going to use EMP table which is present in SCOTT schema for examples.


SQL> select sum(sal),deptno from emp
  2  group by deptno;
  SUM(SAL) DEPTNO
---------- ------
      9400     30
      8750     10
     10875     20


Now we will write above query using Analytical function.


SQL> select deptno, sum(sal)over(partition by deptno ) as grp from emp;
DEPTNO        GRP
------ ----------
    10       8750
    10       8750
    10       8750
    20      10875
    20      10875
    20      10875
    20      10875
    20      10875
    30       9400
    30       9400
    30       9400
    30       9400
    30       9400
    30       9400
14 rows selected


as we observed both queries output, Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they didn't reduce the number of rows returned by the query.


Syntax:

 Lets understand syntax here

analytic_function([ arguments ]) OVER (analytic_clause)

 Here  "analytic_function" can be any aggregate function or any other analytic function(eg: lead,lag,dense_rank etc)

 "Arguments" --> Based on requirement we pass arguments to analytic function. eg: in above query we passed "sal".


The analytic_clause breaks down into the following optional elements.

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]


query_partition_clause

The query_partition_clause divides the result set into partitions, or groups, of data. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a GROUP BY clause affects the action of an aggregate function. If the query_partition_clause is omitted, the whole result set is treated as a single partition. The following query uses an empty OVER clause, so the sum presented is based on all the rows of the result set.

 SQL> select deptno, sum(sal)over() as grp from emp;


DEPTNO        GRP
------ ----------
    10      29025
    30      29025
    10      29025
    20      29025
    20      29025
    20      29025
    20      29025
    30      29025
    30      29025
    30      29025
    30      29025
    20      29025
    30      29025
    10      29025
14 rows selected

order_by_clause:
Its optional, we can use whenever want to sort records.

Windowing_clause

 We have seen previously the query_partition_clause controls the window, or group of rows, the analytic operates on. The windowing_clause gives some analytic functions a further degree of control over this window within the current partition, or whole result set if no partitioning clause is used. The windowing_clause is an extension of the order_by_clause and as such, it can only be used if an order_by_clause is present. The windowing_clause has two basic forms.


RANGE BETWEEN start_point AND end_point
ROWS BETWEEN start_point AND end_point
When using ROWS BETWEEN, you are indicating a specific number of rows relative to the current row, either directly, or via an expression. Assuming you don't cross a partition boundary, that number of rows is fixed. In contrast, when you use RANGE BETWEEN you are referring to a range of values in a specific column relative to the value in the current row. As a result, Oracle doesn't know how many rows are included in the range until the ordered set is created.

Possible values for "start_point" and "end_point" are:

UNBOUNDED PRECEDING : The window starts at the first row of the partition, or the whole result set if no partitioning clause is used. Only available for start points.
UNBOUNDED FOLLOWING : The window ends at the last row of the partition, or the whole result set if no partitioning clause is used. Only available for end points.
CURRENT ROW : The window starts or ends at the current row. Can be used as start or end point.
value_expr PRECEDING : A physical or logical offset before the current row using a constant or expression that evaluates to a positive numerical value. When used with RANGE, it can also be an interval literal if the order_by_clause uses a DATE column.
value_expr FOLLOWING : As above, but an offset after the current row.