SQL

Rank in SQL

How to use row_number in SQL | SQL RANK() function | dense_rank SQL

Leave a Comment / 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 … Continue Reading

Index in Oracle

Index in SQL | What is Index in SQL | How to create Index in SQL

Leave a Comment / SQL

INDEX in SQL

Index in SQL is the most powerful and useful aspect of SQL. It helps in improving the performance of database queries and provides direct and fast access to database rows. The users can’t see indexes they are just used to speed up searches and queries.

Why Index?

In the current scenario, databases are getting larger day by day. The total number of rows reached millions and billions. when we retrieve a few rows out of these huge number of rows then it will take a lot of time to retrieve these rows. To save time SQL INDEX is being used.

An index stores data in index column(s) and each value has a location of the rows that … Continue Reading

SQL CASE WHEN statement

Oracle CASE statement || SQL CASE statement with 2 Example

Leave a Comment / SQL

Oracle CASE statement

The SQL CASE statement is a logical statement that helps in putting values based on logical arguments. The CASE statement goes through conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If no conditions meet the criteria then NULL value will return.

CASE Syntax in SQL

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In the above SQL CASE syntax, we can see it starts with CASE then WHEN followed by condition1. If this condition is met then result1 … Continue Reading

view in SQL

What is View in SQL || 2 ways to create view SQL

Leave a Comment / SQL

What is View in SQL

View in SQL is a virtual table that is basically a SQL dynamic table. It is created by SQL query and used for quick reference as carrying a long query in SQL is difficult, so we store it in the view as per our comfort. The main task of a view is to retrieve data and hide the complexity.

How to create a View by new view

To create a view in SQL we will first write a SQL Query like below.

select * from employees
where JOB_ID in ('SA_REP','SH_CLERK');

In the above query, we filtered our data for two JOB_ID instead of using complete SQL table. There are many situations wherein we don’t … Continue Reading

SQL GROUP BY CLAUSE

Group by SQL function || Group by in SQL with examples

Leave a Comment / SQL

Group by SQL function

Group by in SQL is a function by which we are grouping column values against record groups. It helps in aggregating columns data and getting summary of values. Group by SQL function is a very useful and frequently used SQL function.

  • The GROUP BY clause groups records into summary rows.
  • GROUP BY returns one record for each group.
  • GROUP BY is used with aggregrates like COUNT, MAX, SUM, SUM and AVERAGE.

Example for Group by in SQL

In the below example SQL GROUP BY count we used SQL GROUP BY clause to find out COUNT of JOB_ID in the EMPLOYEES table.

Group by SQL function
Group by SQL function

We used SELECT STATEMENT to get data from table, then we … Continue Reading

unpivot in sql

Pivot and Unpivot in SQL || How to convert rows to Columns in sql

Leave a Comment / SQL

What is Pivot and Unpivot in SQL

In this article, we will learn about Pivoting and unpivoting data in SQL. But before how to do Pivot and Unpivot in SQL we must understand what is pivot and unpivot in SQL. In SQL Oracle, Pivot and Unpivot are relational operators that are used to transform one SQL table into another SQL  table in order to achieve a simpler view of the data. In simple terms, we can say that SQL Pivot operator converts the unique values from one column values which are in rows into different columns. Basically, it convert rows to columns in SQL. The SQL Unpivot operator does the opposite that is it transforms the different columns … Continue Reading

JOINs in SQL | How to JOIN two tables in SQL

Leave a Comment / SQL

What are JOINs in SQL?

JOINs in SQL are feature by which we combine rows of two SQL tables through SQL columns of both tables. The column that we used to combine both SQL tables must have common records. To understand this we will take an example as below.

Let’s suppose we have table EMPLOYEES wherein we have details of all employees in the organization. The details of EMPLOYEES SQL table are illustrated in the below image.

JOINs in SQL
JOINs in SQL

We also have another table JOBS in which we have details of minimum and maximum salary by designation.

SQL JOBS table
SQL JOBS table

Now if we want to get salary slabs for each employee we need to combine both the oracle tables. … Continue Reading

SELECT statement for SQL

1 Comment / SQL

What is a Select Statement for SQL?

SELECT statement for SQL, sometimes referred to as a Query, allows you to retrieve the records you want from the database so that you can manipulate or analyze them further. In this article, we’ll cover how to write a SELECT statement in SQL and look at some examples of how they are used. When writing your own SELECT statement for SQL, it’s important to consider two factors as mentioned below:

  • the source (where you are pulling your data from) and
  • the columns (which data fields you want).

These can both be changed using clauses in your statement.

What is Select Query

A select query is used to get data from a … Continue Reading

SQL INSERT INTO 1

Insert query in SQL | SQL insert statement in Oracle

2 Comments / SQL

Insert query in SQL

Introduction to Oracle INSERT statement

In our previous post, we learned about how to create a table in SQL by CREATE TABLE statement. After CREATE TABLE we will learn how to INSERT INTO data in SQL table. To insert a new row into a table, you can use the Oracle INSERT statement as follows:

INSERT INTO table_name (column_list)
VALUES( value_list);

In the above statement, we can see COLUMN_LIST is the name of columns in which we wanted to insert the data. While value_list shows values that we wanted to INSERT INTO the columns of SQL table mentioned above. Also, it should be noted that every value and column name should be separated by comma.

If … Continue Reading

How to create a table in SQL | SQL tutorial for beginners |SQL create table

3 Comments / SQL

How to create a table in SQL

In this tutorial, we will learn how to create a table in SQL. For creating a new oracle table we will use CREATE TABLE statement in the oracle database. It must be noted that we should have privilege to CREATE TABLE in sql schema. Also the sql table should have quota for table space.

SYNTAX for CREATE TABLE
CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    ...
 );

In the above SQL code we can see

  • CREATE TABLE : It is the main clause under which other information is put.
  • schema_name.table_name : We will mention schema name and then table name which we wanted to create. There are some
… Continue Reading