List of Complex SQL Queries For Practice – Part 2
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
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);
Also Read:
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;
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
a := dbms_pipe.receive_message(‘mahendran’);
dbms_pipe.unpack_message(b);
if a = 0 then
dbms_output.put_line(‘successfully received’);
dbms_output.put_line(b);
else
dbms_output.put_line(‘not received’);
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?
Credits : – Techonthenet provided this solution:
ALTER TABLE table_name
ALTER COLUMN column_name column_type;
Also Read:
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:
ALTER USER <username> IDENTIFIED BY <new_password>
