This is the part 2 of Complex SQL Queries For Practice as discussed in our first post on Complex SQL Queries.  We have tried to visit many forums and blogs to gather Questions related to Complex SQL Queries and provide them to you in this series of blog post on Complex SQL Queries for Practice.

These Queries are put together from Internet based research and do not belong to particular individual. Our aim is to help people explore different methods of doing tasks in SQL. We thank the SQL Developer Community to constantly providing solutions to such Complex SQL Questions.

# COMPLEX SQL QUERIES - PART 2

1. How Can we only retrieve the Nth row from SQL Database table?
Credits : – Ravi Pachalla provided this solution:
SELECT f1 FROM t1 WHERE rowid = (SELECT rowid FROM t1 WHERE rownum <= 10 MINUS SELECT rowid FROM t1 WHERE rownum < 10);

2. How Can we only retrieve rows X to Y from SQL Database table?

Credits : – Ravi Pachalla provided this solution:
SELECT * FROM tableX WHERE rowid in (SELECT rowid FROM tableX WHERE rownum <= 7
MINUS
SELECT rowid FROM tableX WHERE rownum < 5);

3. How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT *FROM EMP WHERE (ROWID, 0) IN (SELECT ROWID, MOD (ROWNUM, 4) FROMEMP);

Method 2: Use dynamic views (available from Oracle7.2):
SELECT * FROM (SELECT rownum rn, empno, ename FROM EMP) temp WHERE MOD(temp. ROWNUM, 4) = 0;

4. How to select the TOP N rows from SQL Database table?

Credits : – Stack Exchange provided this solution:
SELECT * FROM my_table a
WHERE 10 >= (SELECT COUNT (DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;

5. How to dump or examine the exact content of a database column?

Credits : – Sunder Krishna provided this solution:
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;
DUMP (COL1)
————————————-
Typ=96 Len=4: 65,66,67,32

6. How to Receive Messages At Different Session in SQL

Credits : – StackExchange provided this solution:
declare
a integer;
b varchar2(30);
begin
dbms_pipe.unpack_message(b);
if a = 0 then
dbms_output.put_line(b);
else
end if;
end;

7. How to drop a column from a SQL Database table?

Credits : – W3Schools provided this solution:
ALTER TABLE table_name
DROP COLUMN column_name;

8. How to rename a column in a SQL Database table?

9. How to send messages to Different Sessions in SQL?

Credits : – Ravi Pachalla provided this solution:
Declare
a integer;
b integer;
Begin
a := dbms_pipe.create_pipe(‘Mahendran’);
dbms_pipe.pack_message(‘Mahendran software is a good company’);
b := dbms_pipe.send_message(‘Mahendran’);
if b = 0 then
dbms_output.put_line(‘successfully send’);
else
dbms_output.put_line(‘not send’);
end if;
end;

10. How to change my Oracle User Password?

Credits : – Internet Users provided this solution: