View allAll Photos Tagged SQL
How to rank in SQL
This tutorial shows you how to rank in SQL your data. We will first understand what is ranking function in SQL are then dense_rank SQL. Then also understand how to use row_number in SQL. We will first create SQL table and then insert our data to understand these three functions in SQL i.e. rank function SQL, oracle dense_rank, oracle row_number.
Create table in SQL
First, we will create table SQL emp_name which will have 8 columns, and the first column has a primary key. To create a table in SQL we will use CREATE TABLE statement. We have mentioned Query SQL create table as below. You can read more on how to create SQL table from this link.
CREATE TABLE emp_name (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
Describe function in SQL
After creating the table use describe function in SQL to check if SQL table was created correctly. Check the below image to understand it.
how to use row_number in SQL
INSERT in SQL
As we have created Oracle table now the time has come to INSERT INTO table in oracle. We will use the below SQL query to insert in SQL table. You can read more on SQL INSERT from this link.
INSERT INTO emp_name VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp_name VALUES (7499,'ALLEN','SALESMAN',7698,to_date(