# 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.

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?

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

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?

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?

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?

Want to display output like :

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?

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

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

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?

Create index NI_Student_NAME on Student_Master(NAME);

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

CREATE UNIQUE INDEX UI1_Roll on Student_Master(Roll_No);

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

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.

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.

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.

## 4 Replies to “What are important Advanced SQL Exercises?”

1. Fareeth says:

Hai, I having 4 yrs exp plsql developer. Could you please send me the scenario based sql and plsql question.
fareeth87@gmail.com

1. Sure Fareeth..
I sent you interview questions.

2. Saleem Khan says:

Hi, Amit S,

Please send me SQL queries & scenario-based SQL queries & questions for the PostgreSQL Database.

Thanks.

1. Sure saleem . Kindly check your inbox …