• Login
  • Register
  • Profile

All About Oracle

INDEX

Special Syntax

/
It is used to execute the previous query.

Desc
To see the properties of your table type:
describe employee;
OR
desc employee;

Tab
It is a data dictionary. This will show you all the tables present into the database.

Math Functions

ABS()
Returns the Absolute value of the given number
SQL> select abs(-5) from dual;
Output: 5

CEIL()
Returns the smallest integer greater than or equal to the data.
SQL>SELECT CEIL(123.55) FROM DUAL;
OUTPUT: 124

FLOOR()
Returns the largest integer less than or equal to data.
SQL> SELECT FLOOR(123.5) FROM DUAL;
OUTPUT: 123

LOG()
Returns the log value
SQL>SELECT LOG(10,2) FROM DUAL;
OUTPUT: 0.301029996

MOD()
RETURNS THE MODULUS OF THE NUMBER.
SQL>SELECT MOD(101,2) FROM DUAL;

ROUND()
Returns the number to a decimal places to which the data is rounded.
SQL> SELECT ROUND(123.55,1) FROM DUAL;
OUTPUT: 123.6

String Operation

LENGTH(str)
Returns the length of the string str.
SQL> SELECT LENGTH('helloworld') from dual;
Output: 10

LOWER(str)
Returns the string str by converting all characters into lower case.
SQl> SELECT LOWER('HELLOWORLD') from dual;
Output: 'helloworld'

LPAD(str,len,padstr)
Returns the string left-padded string to length len
SQL> SELECT LPAD('hi',4,'??') from dual;
Output: '??hi'

RPAD(str,len,padstr)
Returns the string by, right-padded with the string padstr to a length of len characters.
SQl> SELECT RPAD('hello',10,'you') from dual;
Output: 'helloyouyo'

LTRIM(str)
Returns the string by removing the leading space
SQL> SELECT LTRIM(' hello') from dual;
Outpout: 'hello'

RTRIM(str)
Returns the string str by removing the trailing space characters.
SQL> SELECT RTRIM('helloworld ') from dual;
Output: 'helloworld'

REPLACE(str,from_str,to_str)
Returns the string by replacing all the matching values by str.
SQL> SELECT REPLACE('www.expertprogramming.org', 'w', 'Ww') from dual;
Output: 'WwWwWw.expertprogramming.org'

REVERSE(str)
Returns the string reversing the characters.
SQl> SELECT REVERSE('abc') from dual;
Output: 'cba'

SUBSTR()
Returns the string starting from len1 to the length len2.
SQL>SELECT SUBSTR('Helloworld',5,4) from dual;
Output: 'owor'

INSTR(str,substr)
Returns the position of the first substring substr in string str.
SQL> SELECT INSTR('helloworld', 'world') from dual;
Output: 6

Trim(str)
Returns the string by removing the leading and trailing space characters.
SQL>SELECT TRIM(' helloworld ') from dual;
Output: 'helloworld'

TRUNC()
Returns the number by truncating decimal places for truncation.
SQL> SELECT TRUNC(123.55,1) FROM DUAL;
OUTPUT: 123.5
The limit of truncation is 6 decimal places.

UPPER(str)
Returns the string str by converting all characters into upper case.
mysql> SELECT upper('hello');
Output: 'HELLO'

Date and Time Functions

ADD_MONTHS
Returns the date by adding the months into it.
SELECT ADD_MONTHS('12-JAN-1995',3) FROM DUAL;
OUTPUT: 12-APR-1995

LAST_DAY(date)
Returns the last date of the given month.
SELECT LAST_DAY('5-dec-1991') from dual;
OUTPUT: 31-dec-1991

MONTHS_BETWEEN
Returns the number of months in between 2 dates.
SELECT MONTHS_BETWEEN('31-JAN-2005','31-JAN-2000') FROM DUAL;
OUTPUT: 36

NEXT_DAY
It returns the next date of the given day.
SELECT NEXT_DAY('26-NOW-1991','WEB') FROM DUAL;
OUTPUT: 27-NOV-1991

TO_CHAR
Converts a number or date to a String.
>SELECT to_char(SYSDATE,'dd-mm-yyyy') from dual;
Output: 02-07-2015

SYSDATE
Returns the current date and time of the system.
>SELECT to_char(SYSDATE,'hh:mi:ss') from dual;
Output: 12:14:35

ROUND
Returns the rounded value of the given date.
SELECT to_char(ROUND(to_date('1-Mar-1991'),'CC'),'DD-MM-YYYY') from dual;
Output: 01-01-2001

TO_DATE
Converts a string to a Date.
>SELECT to_date('04/06/1994','dd-mm-yyyy') from dual;
Output: 04-JUN-94

Operations

Create User
Before using a Database first we need to create a USER. To create a user we use "CREATE" command.

Syntax:
CREATE user identified by ;
Example:
CREATE user john identified by smith;

But keep this in mind that username and password should not be in quotes, else it will throw an error.

After that you need to grant him permission that what privileges he must have.
Syntax:
GRANT all privileges to < userNM >;
Example:
GRANT all privileges to john;

This will give him to do all the actions by providing all the privileges.

Connectivity
After creating a user we can use it in the following manner.
SQL> connect < userNM >;
Enter password:< password >;

Create Table
To create a table you need CREATE TABLE command.
Syntax:
CREATE TABLE employee
(
ID int not null auto_increment primary key,
firstName varchar(30),
lastName varchar(30),
age int,
gender varchar(13)
);

Then press ENTER.
The table will be created.

Inserting value
There are 3 ways to insert values into the table.
First:
INSERT INTO employee
values
("Krish", "Sharma", 21, "male");

Second:
INSERT INTO employee
(firstName, lastName, age, gender)
values
("Krish", "Sharma", 21, "male");

Third:
This method asks values from the user to enter.
INSERT INTO employee
values
(&firstName, &lastName, &age, &gender);
Enter firstName: 'krish'
Enter lasttName: 'sharma'
Enter age: 21
Enter gender: 'male'

/

This will add the record into the table.

select * from employee;
Now if you want to print on the screen all the information about your table.

Alter Table
If you wish to add or remove columns then in that case Alter table is used. After adding the column initially all the rows will be filled as null.

Example:
ALTER TABLE employee add salary number(10,2) not null;
This will add a new column salary into employee table.

It is also used to modify the attributes.
ALTER TABLE employee modify salary number(12,2);
But keep this in mind modify command works only to increasing.

Alter table is also used for renaming the columns.
Example:
ALTER TABLE employee rename empname To name;

Renaming table
Example:
SELECT empname as name
FROM employee;

This will allow you to rename the column name for temporarily
DROP
Drop command drops the table permanently.
Syntax:
DROP table ;

Example:
DROP table employee;

Update Statement
Many time there is a need to modify the data in that case Update Statement is used.
UPDATE emp set salary=10000 where name in ('john', 'krish');

We can also compare it with NULL.
UPDATE emp set salary=10000 where salary is null;

Queries

SELECT
It is used to select the list of attributes requested into the query.
Example:
SELECT empname, age, salary
FROM employee;

This will select empname, age and salary from employee table and display it.

But to do this operation we need the name of the table on which the query to be fired. In that case we use FROM.

Example:
SELECT *
FROM employee;
This will display all the values from employee table.

WHERE
This, is used to set a filter if you want some specific records.
For example, you can retrieve only the record which has an id as 25;
SELECT * FROM employee WHERE id=42;

ORDER BY
You can set the order of the record into alphabetical or numeric by using ASCENDING OR DESCENDING.

SELECT * FROM employee ORDER BY empname;

By default, the order is ASCENDING. You can also specify that the order must be DESCENDING:
SELECT * FROM employee ORDER BY empname ASC;
SELECT * FROM employee ORDER BY empname DESC;

DISTINCT
The DISTINCT keyword can be used to eliminate all duplicate rows from the result set:
SELECT DISTINCT * FROM employee;

OR

SELECT DISTINCT empname FROM employee;

If you are selecting a PRIMARY KEY or it has a UNIQUE index. In that case DISTINCT is useless.

Like Operation
It is used for pattern matching into the relation.
Like operators can be used for searching different patterns such as:
1) Percentage(%) for matching any substring.
2) underscore(_) for matching any character.

Example:
SELECT * from customer where cust_name like 'Jim%';
This will select all the customers whose name starts with 'Jim' followed by some string.

If you want to retrieve by using characters
SELECT * from customer where cust_name like '___';

This will select all the customers whose names are of exactly 3 characters.

Example:
SELECT * from addr where cust_name like '%main%';
It will select all the details of the customers where street name contains 'Main' in its center.

AGGREGATE Functions
These functions take collection of values as a input and return a single value output. But keep this in mind aggregate function works only on numbers.

They are,
AVG
MIN
MAX
SUM
COUNT

AVG
Its gives the average value of the column.
SQL>SELECT AVG(salary) from emp;

MIN
It shows a row with minimum value.
SQL>SELECT MIN(salary) from emp;
It will display the minimum salary

MAX
It shows a row with maximum value.
SQL>SELECT MAX(salary) from emp;
It will display the maximim salary

SUM
It displays the final result by adding all the values.
SQL>SELECT SUM(salary) from emp;
It will display the addition of salary of all the employees.

COUNT
It counts the number of rows present into the table.
SQL>SELECT COUNT(salary) from emp;

GROUP BY
Using a group by clause we can group the table on set of columns using aggregate functions. Keep this in mind that group by works only with aggregate functions.
The record which have the same value for that field will be grouped in one record.
Example:
SELECT city, MAX(age), MIN(age), AVG(age) GROUP BY city;

HAVING
The HAVING clause declares a filter for the records which are computed by the GROUP BY clause.

HAVING decides what computed records are returned, so it can operate on the results of aggregate functions.

Example:
SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY city HAVING MAX(age) > 80;

It must group all records, because can't decide the max age of each city before the GROUP BY clause is execute. Later, it returns only the record with a MAX(age)>80.

SET Operations

UNION and UNION All
Following query will return all the records from both tables.

(SELECT * FROM deposit)
UNION
(SELECT * FROM withdraw);

UNION is the same as UNION DISTINCT.
If you type only UNION, then it is considered that you are asking for distinct records. If you want all records, you have to use UNION ALL.
Example:
(select empno,name from emp)
UNION
(select cust_No,cust_name from customer);

INTERSECT
This combines two queries and returns only those rows which common in both tables.
Example:
(SELECT name FROM emp) INTERSECT (SELECT name FROM customer);
It will return the common rows in both tables.

MINUS
This compares both the tables and returns only the rows from table one which is unique.
Example:
(SELECT name FROM emp)
MINUS
(SELECT cust_name FROM customer);

Our Sponsors

Have any suggestions?