## COMPLEX SQL QUERIES : PART-1

### How to find The Nth Maximum Salary of an Employee ?

`SELECT DISTINCT SAL FROM EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);`

### How to find the no. of columns for particular table in SQL?

`SELECT COUNT (COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'`

### How to use Exists Clause in SQL?

`SELECT DNAME, DEPTNO FROM DEPT WHERE EXISTS (SELECT * FROM EMP WHEREDEPT.DEPTNO = EMP.DEPTNO) `

### How to Find The Not Null Column Alone In A Table in SQL?

`SELECT COLUMN_NAME FROMUSER_TAB_COLUMNS WHERE NULLABLE = 'N' AND TABLE_NAME = 'COUNTRY'`

### How to delete The Duplicate Rows Alone in A Table in SQL?

`DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM DEPT GROUP BYDEPTNO HAVING COUNT (*) >=1)`

### How to find The Max Salary without MAX Function in SQL?

`1. SELECT DISTINCT SAL FROM EMP1 WHERE SAL NOT IN(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))`
`2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)`

### What is the Alternate for DESC in SQL?

`SELECT COLUMN_NAME NAME, DECODE (NULLABLE,'N','NOT NULL','Y',' ')"NULL", CONCAT (DATA_TYPE, DATA_LENGTH) TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'`

### Provide an example for startwith, connect by and prior in SQL

`SELECT ENAME, JOB, LEVEL, EMPNO, MGR FROM EMP111 CONNECT BY PRIOREMPNO=MGRSTART WITH ENAME = 'RAJA'`
`SELECT EMPNO, LPAD (‘ ‘, 6*(LEVEL – 1)) || ENAME “EMPLOYEE NAME” FROM EMP STARTWITH ENAME=’KING’ CONNECT BY PRIOR EMPNO = MGR`

### How to find the database name in SQL?

`SELECT * FROM GLOBAL_NAME;`

### How to convert the given no to word in SQL?

`SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;`

### How to eliminate duplicate values in a table in SQL?

Choose one of the following queries to identify or remove duplicate rows from a table leaving one record:

Method 1:

`SQL> DELETE FROM table_name A WHERE ROWID > (SELECT min (rowid) FROM table_name BSQL> WHERE A.key_values = B.key_values);`

Method 2:

`SQL> create table table_name2 as select distinct * from table_name1;SQL> drop table_name1;SQL> rename table_name2 to table_name1;`

Method 3: (Credits to Kenneth R Vanluvanee)

`SQL> Delete from my_table where rowid not in (select max (rowid) from my_table group by my_column_name);`

Method 4: (Credits to Dennis Gurnick)

`SQL> delete from my_table t1 where exists (select 'x' from my_table t2 where t2.key_value1 =t1.key_value1 And t2.key_value2 = t1.key_value2 and t2.rowid > t1.rowid);`

### How to generate primary key values for my table?

`How can I generate primary key values for my table?Create your table with a NOT NULL column (say SEQNO). This column can now be populated withunique values:SQL> UPDATE table_name SET seqno = ROWNUM;Or use a sequence generator:SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;SQL> UPDATE table_name SET seqno = sequence_name. NEXTVAL;Finally, create a unique index on this column.`

### How to get the time difference between two date columns?

`Select floor ((date1-date2)*24*60*60)/3600) || ' HOURS ' || floor ((((date1-date2)*24*60*60) -Floor (((date1-date2)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round ((((date1-date2)*24*60*60) -Floor (((date1-date2)*24*60*60)/3600)*3600 - (floor ((((date1-date2)*24*60*60) -Floor (((date1-date2)*24*60*60)/3600)*3600)/60)*60))) || ' SECS ' time_difference from...`

### How does one count different data values in a column?

`Select dept, sum (decode (sex,'M', 1,0)) MALE, sum (decode (sex,'F', 1,0)) FEMALE, count(decode (sex,'M', 1,'F', 1)) TOTAL from my_emp_table group by dept;`

### How does one count/sum RANGES of data values in a column?

`A value x will be between values y and z if GREATEST (x, y) = LEAST (x, z). Look at this example:Select f2, count (decode (greatest (f1, 59), least (f1, 100), 1, 0)) "Range 60-100",       Count (decode (greatest (f1, 30), least (f1, 59), 1, 0)) "Range 30-59",       Count (decode (greatest (f1, 29), least (f1, 0), 1, 0)) "Range 00-29"From my_table group by f2;For equal size ranges it might be easier to calculate it with DECODE (TRUNC (value/range), 0,rate_0, 1, rate_1,).E.g.Select ename "Name", sal "Salary", decode (trunc (f2/1000, 0), 0, 0.0,1, 0.1, 2, 0.2, 3, 0.31) "Taxrate"From my_table;`

### Can one only retrieve the Nth row from a table?

`Ravi Pachalla provided this solution:SELECT f1 FROM t1 WHERE rowid = (SELECT rowid FROM t1 WHERE rownum <= 10 MINUSSELECT rowid FROM t1 WHERE rownum < 10);`
https://techhowdy.com/wp-content/uploads/2018/04/LIst-of-Complex-SQL-Queries-Part-1-1024x614.pnghttps://techhowdy.com/wp-content/uploads/2018/04/LIst-of-Complex-SQL-Queries-Part-1-150x150.pngDatabase Programmingcomplex sql queries for practice in oracle,complex sql queries for practice with answers,complex sql queries to practice,practice complex sql queries onlineIn this blog post we have put together complex SQL Quires that have been gathered through internet research on various Forums. Fell Free to use them to practice complex SQL questions that need answers. this is the part-1 of the Complex SQL Queries post, we will soon update Part-2... 