SQL

[is0] Write a query to show all employee who all are in office.

select * from employee_inout e where status = 'IN' and not exists (select * from employee_inout  where employee_id=e.employee_id and status='OUT' and timing>e.timing)

[is1] Write a count for below scenario using join: t1(1,1,2,2) t2(1,2)

select * from t1 natural join t2; --4

select * from t1  cross join t2 ; --8

select * from t1  join t2 using (column1); --4

select * from t1  join t2 on (t1.column1=t2.column1);--4

select * from t1  left outer join t2 on (t1.column1=t2.column1);--4

select * from t1  right outer join t2 on (t1.column1=t2.column1);--4

select * from t1  full outer join t2 on (t1.column1=t2.column1);--4

[cb0] Write a query to show data ordered based on country_name, but India need to come first and rest need to be ordered as usually.

SELECT country_id, country_name FROM countries ORDER BY CASE WHEN country_name = 'India' THEN 1 ELSE 2 END, country_name ASC;

[cb1] Write a query to print a list of comma separate values in new rows, input row is 'Rajasthan,Maharastra,Tamilnadu' 

SELECT TRIM(REGEXP_SUBSTR('Rajasthan,Maharastra,Tamilnadu', '[^,]+', 1, LEVEL)) AS state FROM DUAL CONNECT BY REGEXP_SUBSTR('Rajasthan,Maharastra,Tamilnadu', '[^,]+', 1, LEVEL) IS NOT NULL;

[cb2] Write a query to print last forward slash value 'India/Norway/England/Dubai'?

SELECT SUBSTR('India/Norway/England/Dubai', INSTR('India/Norway/England/Dubai', '/', -1) + 1) AS last_value FROM dual;

[cb3] How to update tab1 col1 using tab2  col1, using below table structure: 

tab1 (id,    col1,   col2)

tab2(id,   col1,   col2)

Option-01 

UPDATE tab1 t1 SET t1.col1 = (  SELECT t2.col1  FROM tab2 t2  WHERE t1.id = t2.id)

WHERE EXISTS (  SELECT 1  FROM tab2 t2  WHERE t1.id = t2.id);

Option-02 

MERGE INTO tab1 t1 USING tab2 t2 ON (t1.id = t2.id) WHEN MATCHED THEN UPDATE SET t1.col1 = t2.col1;

[cb4] How to reset sequence from 500 to 0?

To reset a sequence in Oracle from 500 to 0 and then start from 1 to n, you can use the following SQL commands:

ALTER SEQUENCE your_sequence_name

INCREMENT BY -500

MINVALUE 0;

 ALTER SEQUENCE my_sequence_name INCREMENT BY 1;

[cb5] how to add 10 second in current system date time?

You can add 10 seconds to the current date and time in Oracle using the SYSDATE function along with an interval. Here's an example:

SELECT SYSDATE + INTERVAL '10' SECOND FROM DUAL;

[cb6] How to get hh:mi difference from col1 which is varchar and containing hh:mi values 

and another col2 is timestamp and having full date and time?

SELECT col1, col2,

       EXTRACT(hour FROM (TO_TIMESTAMP(col1, 'HH24:MI') - col2)) || ':' ||

       EXTRACT(minute FROM (TO_TIMESTAMP(col1, 'HH24:MI') - col2)) AS diff_hh_mi

FROM your_table;

[cb7] Can we create table as dual?

No, we cannot create a table as dual in Oracle. The dual table is a special one-row, one-column table that is provided by Oracle for performing calculations or calling functions that do not require any table input. It is not a regular table that can be created or modified by the user. Instead, it is automatically created by Oracle when the database is installed, and it can be accessed by any user without any special privileges.

[cb8] How many are regular expression function?

Oracle provides a set of regular expression functions that can be used in SQL and PL/SQL. The following is a list of the regular expression functions available in Oracle:

• REGEXP_LIKE
• REGEXP_SUBSTR
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_COUNT

These functions allow you to search, extract, and manipulate text based on patterns defined using regular expressions.

[cb9] Write a query based on below scenario: 

There is an emp table and having budget table to allocate fund 60% to superior and 40% junior based on rating.

CREATE TABLE budget(emp_id NUMBER, 

fund_allocation NUMBER, total_fund NUMBER);

 

CREATE TABLE emp(emp_id NUMBER, rating VARCHAR(30));

 

MERGE INTO budget b USING emp e ON (e.emp_id = b.emp_id)

WHEN MATCHED THEN                                

UPDATE SET b.fund_allocation = 

    CASE  WHEN e.rating = 'Superior' THEN b.total_fund * 0.6

                 WHEN e.rating = 'Junior' THEN b.total_fund * 0.4 ELSE 0 END;

Write a query to show data without special character from table column?  

SELECT REGEXP_REPLACE(column_name, '[^[:alnum:]]') AS cleaned_column

FROM table_name;

What is the use of Aggregate functions in Oracle?

Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. These are: AVG | MIN | MAX | COUNT |SUM

What are the set operators UNION, UNION ALL, MINUS & INTERSECT meant to do?

The set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are the same in the source tables. 

• UNION operator returns all the rows from both the tables except the duplicate rows.
• UNION ALL returns all the rows from both the tables along with the duplicate rows.
• MINUS returns rows from the first table, which does not exist in the second table.
• INTERSECT returns only the common rows in both the tables.

--Important SQL-- | --13-Aug-2022-- | --analytical function--

select employee_id, salary, department_id,  avg(salary)  from employees;

--ORA-00937: not a single-group group function

--fix as below but not showing correct result as avg over department

select employee_id, salary, department_id, avg(salary) 

from employees

group by employee_id, salary, department_id;

--accurate fix as below:

select employee_id, salary, department_id, 

       avg(salary) over(partition by department_id) avgsal

from employees;

--job_id

select employee_id, salary, department_id, job_id,

       avg(salary) over(partition by job_id) avgsal

from employees

order by department_id, job_id;

---

select employee_id, salary, department_id, job_id,

       avg(salary) over() avgsal

from employees

order by department_id, job_id;

-- 

select  

       avg(salary) 

from employees

--

select employee_id, salary, department_id, job_id,

       avg(salary) over(partition by job_id order by salary) avgsal

from employees

--rank

select employee_id, rank() over (order by employee_id) from employees;

select salary, rank() over (order by salary) from employees;

select salary, dense_rank() over (order by salary) from employees;

--order by department_id, job_id;

--matrix queries

select * from employees;

select job_id, hire_Date "2003" from employees;

select job_id, 

       sum(case when hire_Date like '%03' then 1 else 0 end )"2003",

       sum(case when hire_Date like '%04' then 1 else 0 end )"2004",

       sum(case when hire_Date like '%05' then 1 else 0 end )"2005",

       sum(case when hire_Date like '%06' then 1 else 0 end )"2006"

from employees

group by job_id;

select job_id, 

       sum(case when hire_Date like '%03' then salary else 0 end )"2003",

       sum(case when hire_Date like '%04' then salary else 0 end )"2004",

       sum(case when hire_Date like '%05' then salary else 0 end )"2005",

       sum(case when hire_Date like '%06' then salary else 0 end )"2006"

from employees

group by job_id;

--using decode--

select job_id, 

       sum(decode(to_char(hire_Date,'yyyy'), '2003', salary , 0 ) )"2003",

       sum(decode(to_char(hire_Date,'yyyy'),  '2004', salary, 0 ) )"2004",

       sum(decode(to_char(hire_Date,'yyyy'), '2005', salary, 0 ) )"2005",

       sum(decode(to_char(hire_Date,'yyyy'),  '2006', salary, 0 ) )"2006"

from employees

group by job_id;

select 1 as mynum, cast(1 as char) as mychar, 100 mynum from dual;

--character appears in left aligned

--number appears in right aligned.

--how to replace * instead of salary

select salary, lpad(salary, 10,'*'), lpad('*',salary/1000,'*') from employees;

--06-Aug-2022--

--What is difference between case vs decode() by using sql statement?

--case expression | decode - scope of work for [if then else logic]

select

      case

      when 1=1 then 'Hello Students'  --hs

      when 2=2 then 'Hey Guys'       --hg

      when 3=3 then 'Hey Jobseekers' --hj

      else 'bye everyone'            --be

      end as myresult

from dual; 

select

      case

      when 1=10 then 'Hello Students'  --hs

      when 2=2 then 'Hey Guys'       --hg

      when 3=3 then 'Hey Jobseekers' --hj

      end as myresult

from dual;

select

      case

      when 1=10 then 'Hello Students'  --hs

      when 2=22 then 'Hey Guys'       --hg

      when 3=3 then 'Hey Jobseekers' --hj

      else 'bye everyone'            --be

      end as myresult

from dual;

--simple way to evaluate if then else in case expression

select

      case 22

      when 10 then 'Hello Students'  --hs

      when 22 then 'Hey Guys'       --hg

      when 30 then 'Hey Jobseekers' --hj

      else 'bye everyone'            --be

      end as myresult

from dual;

--conditional format 

select

      case

      when 1=10 then 'Hello Students'  --hs

      when 2=22 then 'Hey Guys'       --hg

      when 3=30 then 'Hey Jobseekers' --hj

      else 'bye everyone'            --be

      end as myresult

from dual;

select 100 from dual where 1=1;   

select 100 from dual where 1=0;

select job_id,department_id last_name,salary,

       case 

        when job_id='AC_ACCOUNT'  then salary*1.10

        when job_id='AC_MGR'  then salary*1.15

        when job_id='AD_ASST'  then salary*1.20

        else salary end as "new revised salary" 

from employees; 

--in case we want to see default msg 'no increment'

select job_id,department_id last_name,salary,

       case 

        when job_id='AC_ACCOUNT'  then salary*1.10

        when job_id='AC_MGR'  then salary*1.15

        when job_id='AD_ASST'  then salary*1.20

        else 'no increment' end as "new revised salary" 

from employees; 

--ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

--fix of error

select job_id,department_id last_name,salary,

       case 

        when job_id='AC_ACCOUNT'  then to_char(salary*1.10)

        when job_id='AC_MGR'  then to_char(salary*1.15)

        when job_id='AD_ASST'  then to_char(salary*1.20)

        else 'no increment' end as "new revised salary" 

from employees; 

--case with double condition

select job_id,department_id last_name,salary,

       case 

        when job_id='AC_ACCOUNT' and department_id=110 then to_char(salary*1.10)

        when job_id='AC_MGR' and department_id=110 then to_char(salary*1.15)

        when job_id='AD_ASST' and department_id=110 then to_char(salary*1.20)

        else 'no increment' end as "new revised salary" 

from employees;

--decode

select

      decode(22

      , 10 , 'Hello Stdents'  --hs

      , 22 , 'Hey Guys'       --hg

      , 30 , 'Hey Jobseekers' --hj

      , 'bye everyone'            --be

      ) as myresult

from dual;

select job_id,department_id last_name,salary,

       decode (job_id

        , 'AC_ACCOUNT'  , salary*1.10

        , 'AC_MGR'      , salary*1.15

        , 'AD_ASST'     , salary*1.20

        , salary ) as "new revised salary" 

from employees;

select job_id,department_id last_name,salary,

       decode (job_id

        , 'AC_ACCOUNT'  , salary*1.10

        , 'AC_MGR'      , salary*1.15

        , 'AD_ASST'     , salary*1.20

        , 'no increment' ) as "new revised salary" 

from employees;

--ORA-01722: invalid number

--fix as below

select job_id,department_id last_name,salary,

       decode (job_id

        , 'AC_ACCOUNT'  , salary*1.10

        , 'AC_MGR'      , salary*1.15

        , 'AD_ASST'     , salary*1.20

        , 0 ) as "new revised salary" 

from employees;

--diff btw case vs decode--

--[1]CASE expects data type consistency, DECODE does not

SELECT DECODE (2,1,1,'2','2','3') T FROM DUAL; 

--Ans - 2

SELECT CASE 2 WHEN 1 THEN '1' 

       WHEN '2' THEN '2'  

       ELSE '3' END FROM DUAL;

----Ans - Error - Inconsistent Data type  

--ORA-00932: inconsistent datatypes: expected NUMBER got CHAR  

--fix is below:

SELECT CASE 2 WHEN 1 THEN '1' 

       WHEN 2 THEN '2'  

       ELSE '3' END FROM DUAL;          

SELECT CASE 2 WHEN 1 THEN '1' 

       WHEN to_number('2') THEN '2'  

       ELSE '3' END FROM DUAL;

--[2] Case handles null differently.

SELECT DECODE (NULL, 

               NULL, 'NULL', 

               'NOT NULL') NULL_TEST 

FROM DUAL;    

-- NULL 

SELECT CASE NULL 

       WHEN NULL THEN 'NULL' 

       ELSE 'NOT NULL' END NULL_TEST 

FROM DUAL;

-- NOT NULL;

--how null handled in case

SELECT CASE  

       WHEN NULL IS NULL THEN 'NULL' 

       ELSE 'NOT NULL' END NULL_TEST 

FROM DUAL;

--DECODE () works as expression

--Case is part plsql so works for statement as well expression too.

select decode(2,1,0,2),e.* from employees e where decode(1,1,0)=0;

select decode(1,1,0),e.* from employees e where decode(2,1,0,2,2)=2;

--31-Jul-2022--

--How to know no of record in table?

select count(*) from employees;--107

--how to know no of records without using count?

select max(rownum) from employees;--107

select sum(1) from employees;--107

--------------------------------------

select count(*), count(department_id), count(1) from employees;

--note - group() never support null data so where data are missing ignore in count 

--to know missing value, we use nvl ()

select count(*), count(nvl(department_id,0)), count(1)

from employees;

--------------------------------------

select last_name from employees;

--find the data where getting repeat small a in their name more than 1 time--

select last_name,

       length(last_name),

       length(replace(last_name,'a')),

       length(last_name)-

       length(replace(last_name,'a'))

from employees

where length(last_name)-

       length(replace(last_name,'a'))>1;

-------------------------------------------

--Null | IS NULL | NULL=NULL  

SELECT CASE 

       WHEN NULL=NULL THEN 'Yes'

       else 'No' end "My Result-01",

       case

       when null is null then 'Yes'

       else 'No' end "My Result-02"   

from dual; 

--what is rownum?

--it is an imaginary no which is starting from 1 to N

select employee_id, rownum from employees;

select employee_id, rownum from employees where department_id in (30,20,10);

select * from employees where rownum=10;

select * from employees where rownum<=10;

select min(Salary),max(salary) from (

select * from employees where rownum<=10);

--how to figure out to know each of salaries in order and then eliminate duplicate

select distinct salary from employees order by salary desc;

--how to know 7th rank

select * from (

select distinct salary from employees order by salary desc)

where rownum<=7;

--

select min(salary) from (

select distinct salary from employees order by salary desc)

where rownum<=7;

--

select min(salary) from (

select distinct salary from employees order by salary desc)

where rownum<=&nthsalary;

Difference between DELETE & TRUNCATE?
DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.