SQL: how to display nth record from table

To display n^th row from a table say Emp: 

select * from Emp where rownum< (n+1) MINUS  select * from Emp where rownum < n

First select query will return first N rows and second select query will return first N-1 rows and then subtract these two results which gives only n^th row in the table.

It is similar to Minus operation over sets

For example if you want to display 5th row from emp table then:

select * from emp where rownum< 6 MINUS  select * from emp where rownum < 5

SQL Minus Operation

 

 

 

how to select duplicate records in sql

First we do grouping of all the records in the table then display only those records whose count is greater than 1.
For more more details see the Emp table and Output result

Query: Select sal from emp group by sal having count(sal) > 1

Displays all the salaries which are duplicate in Emp table.

SQL

 

How to multiply columns in sql

Temp is a table containing number as column name and if you want to multiply all the values in number column..then simply run the following command.

Select exp(sum(ln(number))) from Temp;

Example:

Number

1

2

3

exp(sum(ln(Number))) = exp(ln1+ln2+ln3) = exp(ln(1*2*3)) = 1*2*3 = 6

How to delete duplicate records in sql

Delete from Student a1 where a1.rowid < (select max(rowid) from Student a2 where a2.name=a1.name);


Explaination:

For each row in Outer query, inner query runs for complete table checking the equal condition and returns maximum row id and if this maximum row id is greater then the row-id of outer query then delete the row. and this is done for each row of outer query.

How to find k th row from bottom in a table in SQL

Refer table click here for this post. Table name is emp, to display kth row from bottom
we need to run the following command

select ename,sal from emp e1 where (select count(*) from emp e2 where e1.rowid <= e2.rowid)=4;

the inner query finds the row which has 4 rows below it using rowid keyword in sql.

How to display first k rows in SQL

To display first k rows from a table make use of rownum keyword which initially has value 0 and gets incremented by one when it moves to next row.

Here is the example to display first three rows from emp table.

select *from emp where rownum < 4

Find maximum salary in each department

we will use the table displayed below for further post unless and otherwise mentioned. table emp contains five column eno(employee number),ename(employee name),sal(employee salary),deptno(employee's department number),mgr(employee's manager number).

the below query displays name of each employee,salary and deptno who are having maximum salary in their department.