What are important Advanced SQL Exercises?

In my previous article i have already given multiple complex sql queries with answers. In this article i would like to give some most important Advanced SQL exercises in detail. The Advanced SQL exercises are the multiple queries which are useful for not only the interview but also the real scenarios.

What you will find in this article?

1. Most Important Advanced SQL Exercises
2. 20 examples of Advanced SQL Exercises

Most Important Advanced SQL Exercises :

Query 1 : What is query to fetch last record of Students_Master table?

Answer :

Select * from Students_Master where Rowid= select max(Rowid) from Students_Master;

Query 2 : What is query to find database name in Oracle?

Query:

There are 3 queries with using which you can find the database name.

SELECT Ora_Database_Name FROM DUAL;

SELECT * FROM GLOBAL_NAME;

SELECT Name from V$DATABASE;

Query 3 :What is Query to display Nth Record from Student_Master table?

Answer :

select distinct Marks from Student_Master a where 3 >= (select count(distinct Marks) from Student_Master b where a.Marks<= b.Marks) order by a.Marks desc;

Query 4 : What is query to find out first record from Student_Master table?

Query :

Select * from Student_Master where Rownum =1;

Query 5 : What is query to find second highest salary of employee with using self join?

Query :

Select * from Employees_Master a where 2 = select count (distinct salary) from Employee_Master where a.salary <= b.salary;

Query 6 : What is PL SQL block to print reverse number?

Answer :

Declare

num1 number:=&num1;           ---Declaring the input number rev_num number:=0;                      ---Declaring

——–Reverse number as 0  

Begin           ---Start of PL/SQL block

while(num1>0)   --Check condition that number is greater than 0

Loop

rev_num=rev_num*10+mod(num1,10);      ---Reverse condition

num1=num1/10;                  ---Reverse condition

End loop;

Dbms_Output.Put_Line('Reverse of Number'||num1||'is'||rev_num);

End;

Query 7 : Write a query to display odd rows from the Student_Master table?

Query:

Select * from(Select rownum as rno,E.* from Student_Master E) where Mod(rno,2)=1;

Query 8 : Write a query to display even number rows from Student_Master table?

Query :

Select * from(Select rownum as rno,E.* from Student_Master) where Mod(rno,2)=0;

Query 9 : What is query to find out the constraint name from oracle table?

Query :

SELECT Table_Name, Constraint_Name FROM User_Constraints;

Query 10 : What is query to find information of student where roll number is not assigned.

Query:

Select * from Student where Roll_no Not in(Select Roll_Number from Student_Master);

Query 11 : how to display 3 to 7 number records from Student_Master table?

Query:

Select * from (Select rownum as ‘No_of_Row’, E.* from Student_Master E)

Where No_of_Row between 3 and 7;

Query 12 : What is query to find out the number of student having marks between 75% to 85%.

Query :

Select count(*) from Student_Master where percentage between 75 and 85;

Query 13 :

If marks column contain the comma separated values from Student table. How to calculate the count of that comma separated values?

Student Name	Marks
Rahul	30,130,20,4
Ramesh	100,20,30
Rokul	140,10

Want to display output like :

Student Name	Marks Count
Rahul	4
Ramesh	3
Rokul	2

Answer:

Select Student_name, regexp_count (marks,’,’) + As “Marks Count” from Student_Master;

Query 14 : Write a query to find out selected tables from user table?

Answer :

SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE ‘Student%’;

The above query will display all tables whos name start with Student.

Query 15 : List the students data whose name start with A and surname start with S.

Query :

Select * from Student_Master where Student_name like ‘A%’ and Surname like ‘S%’;

Query 16 : What is query to create normal index on Name column of student table?

Answer :

Create index NI_Student_NAME on Student_Master(NAME);

Question 17 : What is query to create unique index on roll_no column.

Answer :

CREATE UNIQUE INDEX UI1_Roll on Student_Master(Roll_No);

Question 18 : What is query to create Employee table with range partition.

Answer :

Create table Employee(emp_no number(2),

Salary number(2))

partition by range(Salary)

(partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(maxvalue));

Query 19 : What is query to display last 50% records of Student_Master table.

Answer :

Select rownum,E.* from Student_Master E

minus

Select rownum,E.* from Student_Master E where rownum<=(Select count(*)/2) from Student_Master);

Query 20: What is query to create duplicate table of Student_Master table.

Answer :

Create table Student_Master_Duplicate as Select * from Student_Master;

These are above some most important advanced SQL exercises which are really very useful in real industry programming. I hope you like this article. If you like this article or if you have any concerns with the same kindly comment in comments section.