Featured
-
How Regression Testing Detects Integral Errors In Business Processes
Humans are forever changing and evolving and so to
by kristina.rigina -
Get Display Banner Advertising Opportunity on FindNerd Platform
“Do you have a product or service that nee
by manoj.rawat -
Android O Released with Top 7 New Features for App Developers
Android was founded by Andy Rubin, Rich Miner, Nic
by sudhanshu.tripathi -
Top 5 Features That Make Laravel the Best PHP Framework for Development
Laravel is a free open source Web Framework of PHP
by abhishek.tiwari.458 -
Objective C or Swift - Which Technology to Learn for iOS Development?
Swift programming language is completely based on
by siddharth.sindhi
Tags
Remote Oracle DBA Basics to Know When Moving to the Cloud
The job of database administrators is becoming more and more hectic day by day with the expanse of data to be handled, especially when considering the large volumes of data that modern businesses have to deal with today. The inputs through online...
Except Clause in SQL
Except Clause:
It combines two select statements and return tuples from first SELECT statement which are not returned by second SELECT statement. It is used to achieve Distinct and Not In queries operation in a single clause(Except).
S...
INTERSECT Clause
INTERSECT Clause:
It works similar like union clause as it is used to combine two SELECT statements, but it returns tuples only from first SELECT statement which are common to tuples in the second SELECT statement.
Syntax:
S...
DECODE function
DECODE function:
DECODE function works similar to if-else statement or case statement. DECODE compares expression to each search value one by one and returns the result of that search which is equal to the expression. If there is no match than...
Check Constraint
Check Constraint:
It is used to restrict the range of values that can be inserted in a column. If a column contain check constraint then only a particular range of values can be inserted in that column.
Example:
CREATE TABLE Employee(
...
Adding multiple rows with a single Insert Statement
INSERT ALL Statement:
With the help of INSERT ALL statement we can add multiple rows with a single INSERT statement. We can use this statement to insert multiple rows in one or more than one tables. It eliminates the need of writing insert sta...
EXISTS Operator
EXISTS Operator:
Exists operator is a special type of operator which is used in a subquery. It is used to check whether a subquery returns something or not. If a subquery returns some row then outer query get executed otherwise the whole query...
SELECT INTO Statement
SELECT INTO Statement:
SELECT INTO statement is used to copy data from one table into another table. We can provide a condition while using SELECT INTO statement.
Syntax:
SELECT columnname(s)
INTO new_tablename
FROM t...
BETWEEN and NOT BETWEEN Operator
BETWEEN Operator:
It is used to fetch values which lies within a range given in BETWEEN operator.
Syntax:
SELECT columnname(s)
FROM tablename
WHERE columnname BETWEEN value1 AND value2;
Example:
SELECT * FROM Employee
WHERE ...
Case Expression in SQL
Case Statement:
Case Statement is used to provide IF-ELSE functionality in a SQL query to fetch result. We provide multiple conditions and according to condition matches the value will change for that that column value.
Syntax:
SELECT ...
TOP Clause in SQL
SELECT TOP clause:
TOP clause is used to fetch specific
number of records from a table.
This clause is very handy when we
have huge table of thousands of
record in it . And fetching data
from these tables are quite
time-consuming , in ...
Dual Table
Dual table:
DUAL table is a dummy table which
contains one row and one column and
by default it is present in
database.It contains a single
VARCHAR2(1) column known as DUMMY
with a value "X".
It can be accessed by all users but
the ...
How to use Right Join on two tables
Right Join:
Right Join is used to join two tables and it return all rows from right table(table 2) and matching rows from table 1(left table). The result is NULL for unmatched rows for table 1(left table). Right Join keyword is used to apply R...
Aggregate Functions
Aggregate Functions:
Oracle has many built-in functions which are used to process numbers or strings. They are very useful as we can directly use them , we just have to write the aggregate function name and in braces the column name on which w...
How to use Left Join
Left Join:
Left Join is used to join two tables and it return all rows from table 1 and matching rows from table 2. The result is NULL for unmatched rows for table 2. Left Join keyword is used to apply Left Join on two tables.
Syntax for Le...
Truncating a table
TRUNCATE TABLE Statement:
TRUNCATE Statement is used to remove all records from a table. It is a Data Definition Language (DDL) Statement as it deletes all records and only structure remains. It is similar to delete statement but without where...
Null Values
Null Values:
Null values denotes missing data in a table or a database. If a column does not have any NOT NULL constraint then for that column there can be NULL values. By default a column can have NULL values.
We cannot use comparison oper...
Combining Results of two or more SELECT Statements
Combining Results of two or SELECT Statements using UNION OPERATOR:
UNION Operator is used to combine the results of two or more SELECT statements. But UNION operator does not return duplicate rows so to return duplicate rows UNION ALL operato...
Sub Queries
Sub Queries:
Sub Queries are those query which comes within another query and are written after the WHERE clause.They are also known as Inner query or nested query.
It is used to return records that are used in the main query as a conditio...
DELETE Query
DELETE Statement:
It is used to delete existing records from a table. It can delete all records from a table if where condition is not given and if where condition is given than it will delete selected records which will meet the condition.
...
Having Clause
Having Clause:
The Having clause is used to filter the group result. As we cannot use Where to filter the Group By clause results so Having provide the same functionality as Where. It filter the result fetched by Group By clause. It comes a...
Group By Statement
Group By :
It is used to group the result-set by one or more column. It is used in conjunction with aggregate functions provided by Structured Query Language . If order by is used then Group By must come before Order By clause.
Syntax1:
...
Crreating Virtual Table using CREATE VIEW statement
CREATING VIEWS:
Views are virtual tables which are created from other tables .It is beneficial for those situation when we want to show limited columns to users. It can be created from one or more tables.
Syntax:
CREATE VIEW viewname AS ...
Pattern Matching Using Like Operator
LIKE Operator:
It is used to search for pattern in a table or a database . It is used with where condition. It is similar to REGEXP of MySQL.
Syntax:
SELECT columnname(s) FROM tablename WHERE columnname LIKE pattern-condition;
Examp...
Updating a column value
Updating a column value using Update Statement:
UPDATE statement is used to change the value for a column or to change multiple records using a condition. It is a data manipulation language command which are used for managing data.
Syntax:
...
To add a primary key using ALTER command
Adding a primary key using ALTER Command:
Alter command is generally used to modify table . It can be used to add columns or to remove columns. Using Alter command we can also add or remove a key from a table.
Syntax:
ALTER TABLE tablena...
To check the structure of a table
DESCRIBE Statement:
It is used to view the structure of a table means it shows name and data type of columns.
Syntax 1:
DESC tablename;
Syntax 2:
DESCRIBE tablename;
Example:
DESC Employee;
Column
Datatype
...
Eliminating Duplicates using Distinct Keyword
DISTINCT Keyword:
It is used to fetch different values from a table. It removes duplicate records from the result set and used when there can be multiple duplicate tuples in a table.
Syntax:
SELECT DISTINCT columnname FROM tablename;
...
ALIAS Keyword
ALIAS Keyword:
It is used to give temporary names to table and columns. It is very useful for situations where column or table names are too long.
Synatx:
SELECT columnname AS alias_name FROM tablename;
This syntax is used ...
ORDER BY Clause
Sorting the result using Order By clause:
ORDER BY clause is used to sort the data fetched using select statement either in ascending order or in descending. By default data fetched is sorted in ascending order.
Syntax:
Select columnname...
Copying Data from one table into another
INSERT INTO SELECT statement:
INSERT INTO SELECT statement is used to copy data from existing table into another table.
Syntax-1:
INSERT INTO tablename2 SELECT * FROM tablename1;
Syntax-2
INSERT INTO tablename2 (columnname(s)) ...
Inner Join
Using Inner Join:
It selects rows from both tables as long as there is a match between the column on which join is applied. Inner join is used to fetch data from more than one table.
Syntax:
SELECT columnname(s) FROM tablename1 INNER JO...
Pattern Matching using REGEXP
REGEXP:
It is similar to LIKE which is used to fetch data based on regular expression from a table.
Syntax:
SELECT columnname(s) FROM tablename WHERE columnname REGEXP pattern;
Example 1:
SELECT name FROM Employee WHERE name R...
DROP Statement
DROP Statement:
With the help of drop statement we can easily delete a table or a database and it is a DML(Data Manipulation Language) command.
Syntax to drop a table:
DROP table tablename;
Example:
DROP table Employee;
It ...
IN operator
IN Operator use:
It allows us to put multiple values in a where clause . With the help of this we can compare multiple values wtih where.
Syntax:
Select columnname(s) from Tablename where columnname IN(value1,value 2, .... value n);
...
Fetching Second Highest Salary
If we want to find highest salary we can use max() function simply.
Example:
SELECT max(salary) FROM Employee;
It will return the highest salary from Employee table.
But if we need to find the second highest salary then we have...
Fetch limited number of rows from a table using ROWNUM
If you want to Fetch limited numbers of rows from a table in oracle you can use Rownum.
Or in other words we can say that
It is used to return specific number of rows in a query, we can use ROWNUM in Oracle and LIMIT in MySql.
ROWNUM Synta...
Resolving physical dataguard lag online using duplicate database
Purpose --
To overcome physical standby lag online using duplicate from active database. The standby lag is huge, “incremental from scn” backup size if huge & taking long time There is no enough disk space on the Primary/standb...
NVL fuction in Oracle
NVL function helps you convert a null to an actual value.
Syntax:- NVL (expr1, expr2)
In the syntax:
expr1 is the source value or expression that may contain a null
expr2 is the target value for converting the null
Guidelines for N...
Single Row Function in Oracle (Character Function)
These functions operate on single rows only and return one result per row. There are different types of
single-row functions.
Character
Number
Date
Conversion
Single-row functions are used to manipulate data items. They accept...
Restricting data in Oracle
This blog will help you to restrict data from a database.
You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condi...