Some imporant oracle interview queries

1.Query to find Second Highest Salary of Employee?
Select distinct sal from emp e1 where 2=(Select count(distinct sal) from emp e2 where e1.sal<=e2.sal);
or
select min(sal) from (Select distinct(Sal) from Emp order by sal desc) where rownum<=2;
or
select * from (Select Dense_Rank() over (order by  sal desc) as Rnk,E.* from Emp E) where Rnk=2;

2.Query to find duplicate rows in table?
select *from emp where rowid not in(select min(rowid) from emp group by sal);
or
Select * from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

3.How to fetch  monthly Salary of Employee if annual salary is given?
Select ename,Sal/12 as "MonthlySalary" from emp;

4.What is the Query to fetch first record from Employee table?
Select * from Emp where Rownum =1;

5.What is the Query to fetch last record from the table?
Select * from Emp where Rowid =(select max(Rowid) from Emp);

6.What is Query to display first 5 Records from Employee table?
Select * from emp where Rownum <= 5;

7.What is Query to display last 5 Records from Employee table?
select * from (Select * from emp order by rowid desc) where rownum <=5;

8.What is Query to display Nth Record from Employee table?
Select * from emp where rownum = &5;

9.How to get 3 Highest salaries records from Employee table?
select distinct sal from emp a where 3 >= (select count(sal) from emp b where a.sal <= b.sal) order by a.sal desc;

10.How to Display Odd rows in Employee table?
Select * from(Select rownum as rno,E.* from Emp E) where Mod(rno,2)=1;

11.How to Display Even rows in Employee table?
Select * from(Select rownum as rno,E.* from Emp E) where Mod(rno,2)=0;

12.How Can i create table with same structure of Employee table?
Create table employee2 as Select * from emp where 1!=2;

13.How Can i create table with same structure with data of Employee table?
Create table Employee3 as select * from Employee1 where 1=2;

14.How do i fetch only common records between 2 tables.
Select * from Emp
intersect
Select * from Employee1

15.Find Query to get information of Employee where Employee is not assigned to the department.
Select * from Employee1 where Deptno Not in(Select Deptno from Employee1);

16.How to get distinct records from the table without using distinct keyword.
select * from Employee1 a where  rowid = (select max(rowid) from Employee1 b where  a.empno=b.Empno);

17.Select all records from Employee1 table whose name is 'SMITH' and 'SCOTT'
Select * from Employee1 where ename in('SMITH' ,'SCOTT');

18.Select all records from Employee1 table whose name not in 'SMITH' and 'SCOTT'

Select * from Employee1 where ename not in('SMITH' ,'SCOTT');

✅ 16) Left Outer Join vs Left Join

There is no difference between LEFT JOIN and LEFT OUTER JOIN — both are the same.
They return all records from the left table and matching rows from the right table.
If no match exists on the right side, NULL is returned.
The keyword OUTER is optional and usually omitted.

---

✅ 17) What is Stored Procedure

A Stored Procedure is a precompiled block of SQL code stored in the database.
It can include logic like IF, LOOP, and INSERT/UPDATE/SELECT.
It accepts input/output parameters and improves performance and security.
Stored procedures are often used for reusable and complex business logic in the DB.

---

✅18) Stored Procedure vs Function

Feature	Stored Procedure	Function
Return	0 or many (via OUT/SELECT)	Exactly 1 value (scalar/table)
Used in SELECT	❌ No	✅ Yes
DML Operations	✅ Allowed	⚠️ Limited/Not recommended
Transaction Control	✅ Yes	❌ No
Use Case	Logic + data operations	Reusable expressions/calculations

19) When I update a record in the Employee (child) table, should something be updated in the Department (parent) table automatically in the MS SQL database?
employee table is having empId(primary key), empName,esal,departmet(foreign key) And department table is having departmentId(primary key),departmentName.

In MS SQL Server, just like in other relational databases, the default behavior is:

❌ No — updating a record in the Employee (child) table does NOT automatically update the Department (parent) table.

🧠 Why?

• Foreign key relationships in SQL Server only support automatic behavior from parent to child, like:

  • ON DELETE CASCADE

  • ON UPDATE CASCADE (on primary/foreign key values)

• Child-to-parent automatic updates are never automatic.

  • SQL Server does not support “reverse cascading” or upward updates.

  • If you want this behavior, you must define it explicitly using a trigger.

✅ Solution: Use a Trigger in MS SQL Server

🎯 Example Scenario:

Whenever an employee is updated (e.g., name or salary), we want to update a field in the Department table — such as LastModified timestamp.

---

🛠 Step-by-Step:

1. Add a LastModified column to the Department table

sql
ALTER TABLE Department ADD LastModified DATETIME;

2. Create a trigger on Employee

sql
CREATE TRIGGER trg_UpdateDepartmentOnEmployeeUpdate
ON Employee
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Department
    SET LastModified = GETDATE()
    WHERE DepartmentId IN (
        SELECT DISTINCT department
        FROM Inserted
    );
END;

---

🧾 Explanation:

• Inserted is a special table in triggers in SQL Server containing the new rows.

• This trigger:

  • Fires after an update on Employee

  • Updates LastModified of the related department(s) that the modified employees belong to

✅ Summary

Question	Answer
Does SQL Server auto-update parent when child is updated?	❌ No
Can you make it happen?	✅ Yes, using a trigger
Is it supported via foreign key directly?	❌ No
Best method in SQL Server?	AFTER UPDATE trigger