YouOracle

How to use PL/SQL Decode?

2010-09-15T00:32:00.000-07:00

DECODE(expression, searchedValue, returnValues ...) function is used to search a value in a column or expression as it is done in IF-ELSE statements.

SELECT id, DECODE(id, 1, 'First row', 2, 'Second row') FROM students;

How to use NULLIF?

2010-09-12T21:48:00.000-07:00

NULLIF(a, b) function compares a and b, if they are equal then it returns NULL, else returns a.

SELECT NULLIF('Zeynal', name) FROM students;

How to get Oracle database name?

2010-08-26T02:36:00.000-07:00

We can get current database name from select v$database table.

SELECT name FROM v$database;

How to create a role?

2010-08-25T22:42:00.000-07:00

Example: Create role named changer on table students that will be able to update it. And give this role to user zeynal and david.

CREATE ROLE changer;
GRANT UPDATE ON students TO changer;
GRANT changer TO zeynal, david;

How to revoke privileges of a user on an object?

2010-08-25T21:53:00.000-07:00

Example: Get SELECT, UPDATE privileges on students table back those were given to user zeynal.

REVOKE SELECT, UPDATE ON students FROM zeynal;

Note: If we revoke a privilege from user who had distibuted this role to others by WITH GRANT OPTION, revoke will get this privilige from those people also.

How to give grant on an object?

2010-08-25T21:44:00.000-07:00

Example: Give SELECT, UPDATE grants on students table to user zeynal. And also he will be able to grant this roles to anyone.

GRANT SELECT, UPDATE ON students TO zeynal WITH GRANT OPTION;

Note: if we want to give all privileges we can use ALL clause,

GRANT ALL ON students TO zeynal WITH GRANT OPTION;

How to create new user at Oracle?

2010-08-25T21:34:00.000-07:00

Example: Create new user named zeynal with password mypass

CREATE USER zeynal IDENTIFIED BY mypass;

How to use Subqueries?

2010-08-25T03:43:00.000-07:00

Subqueries are used to return a set of rows or a single scalar value to the outer query for several purposes.

Oracle example table

2010-08-25T03:28:00.000-07:00

Here is my example Oracle tables. I will refer to these tables at my example SQLs.

Table: students

ID	NAME	SURNAME	BIRTHDAY	STARTYEAR	GPA	COUNTRYCODE
1	John	Smart	1/1/1987	9/1/2002	3.43	UK
2	Kamil	Vəliyev	8/13/1984	9/1/2003	3.23	AZ
3	David	Brown	3/2/1980	9/1/2004	2.33	US
4	Fad	Hown	4/12/1985	9/1/2003	3.44	UA
5	Sam	Jackson	4/5/1988	9/1/0207	3.12	ZW
6	Dawn	Dane	2/3/1988	9/1/2004	3.2	TN
7	Emre	Yüce	8/15/1987	9/1/2010	2.3	TR
8	Arif	Salamov	12/11/1983	9/1/2008	3.2	AZ
9	Sad	Matten	2/13/1988	9/1/2005	4.3	TT
10	Zeynal	Zeynalov	6/9/1984	9/1/2001	3.5	AZ

CREATE TABLE  "STUDENTS" 
   ( "ID" NUMBER, 
 "NAME" NVARCHAR2(30) NOT NULL ENABLE, 
 "SURNAME" NVARCHAR2(30) NOT NULL ENABLE, 
 "BIRTHDAY" DATE, 
 "STARTYEAR" DATE, 
 "GPA" NUMBER, 
 "COUNTRYCODE" CHAR(2), 
  CONSTRAINT "STUDENTS_PK" PRIMARY KEY ("ID") ENABLE
   )
/

Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(1, 'John', 'Smart', TO_DATE('01/01/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.43, 'UK');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(2, 'Kamil', 'Vəliyev', TO_DATE('08/13/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.23, 'AZ');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(3, 'David', 'Brown', TO_DATE('03/02/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2.33, 'US');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(4, 'Fad', 'Hown', TO_DATE('04/12/1985 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.44, 'UA');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(5, 'Sam', 'Jackson', TO_DATE('04/05/1988 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/0207 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.12, 'ZW');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(6, 'Dawn', 'Dane', TO_DATE('02/03/1988 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.2, 'TN');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(7, 'Emre', 'Yüce', TO_DATE('08/15/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2.3, 'TR');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(8, 'Arif', 'Salamov', TO_DATE('12/11/1983 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.2, 'AZ');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(9, 'Sad', 'Matten', TO_DATE('02/13/1988 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4.3, 'TT');
Insert into STUDENTS
(ID, NAME, SURNAME, BIRTHDAY, STARTYEAR, GPA, COUNTRYCODE)
Values
(10, 'Zeynal', 'Zeynalov', TO_DATE('06/09/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/01/2001 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.5, 'AZ');
COMMIT;

How to create synonym?

2010-08-25T03:24:00.000-07:00

We can create SYNONYM to ease access to objects with short name.

Example: Create synonym st for students table.

CREATE [PUBLIC] SYNONYM st FOR students;

PUBLIC keyword is used to create public synonym for everyone's access.

How to create index on a table?

2010-08-25T03:12:00.000-07:00

Example: Add index named INDEX_NAME to students table on name and surname columns.

CREATE INDEX INDEX_NAME ON students(name, surname);

How to use SQL MERGE?

2010-08-17T04:07:00.000-07:00

Example: Merge old students table rows into new students table. If the student with same ID at old table exists in new table then update its name, else insert entire row into new table.

MERGE INTO newStudents new
USING oldStudents old
ON(new.ID = old.ID)
WHEN MATCHED THEN
UPDATE SET new.name = old.name
WHEN NOT MATCHED THEN
INSERT VALUES(old.ID, old.name)

How to enable or disable Constraints?

2010-08-17T03:20:00.000-07:00

Example: Enable/disable primary key StudentsPrimaryKey on students table.

ALTER TABLE students ENABLE CONSTRAINT StudentsPrimaryKey

ALTER TABLE students DISABLE CONSTRAINT StudentsPrimaryKey

How to add primary key to a table?

2010-08-17T02:38:00.000-07:00

Example: Add primary key to students table on ID column.

ALTER TABLE students ADD CONSTRAINT "StudentsPrimaryKe" PRIMARY KEY (ID)

How to name Oracle objects?

2010-08-17T02:12:00.000-07:00

Oracle objects can be named according to the below rules:

Object name must start with a letter.
There may be use of #, _, $, [0-9], [a-z], [A-Z] in the name
Oracle reserved keywords can not be used as name

How to use SQL ROWID?

2010-08-17T01:00:00.000-07:00

ROWID is a hexadecimal string that is unique for each row in a table.

Example: Get ROWID and name of students.

SELECT ROWID, name FROM students

Output:

ROWID		NAME
AAADa6AAEAAAAFFAAA		John
AAADa6AAEAAAAFHAAA		Kamil
AAADa6AAEAAAAFHAAB		David
AAADa6AAEAAAAFHAAC		Fad
AAADa6AAEAAAAFHAAD		Sam
AAADa6AAEAAAAFHAAE		Dawn
AAADa6AAEAAAAFHAAF		Emre
AAADa6AAEAAAAFHAAG		Arif
AAADa6AAEAAAAFHAAH		Sad
AAADa6AAEAAAAFIAAA		Zeynal

How to use SQL DELETE, TRUNCATE, DROP?

2010-08-17T00:09:00.000-07:00

DELETE: remove specified rows from a table. You can delete all rows by not writing WHERE statement. It can be undo by ROLLBACK.

DELETE FROM students WHERE studentID IN (10, 20, 30);

TRUNCATE: remove all rows from a table. It can not be undo by ROLLBACK.

TRUNCATE TABLE students;

DROP: remove table, all indexes and grants over that table. It can not be undo by ROLLBACK.

DROP TABLE students;

How to use SQL ALTER TABLE?

2010-08-16T23:14:00.001-07:00

We can use ALTER TABLE keyword in order to do several changes on an existing table.

Add a column
Rename a table
Add / Drop / Rename / Modify a column
Add / Drop / Enable / Disable a constraint

How to use SQL ANY, SOME, ALL?

2010-08-15T23:24:00.000-07:00

ANY, SOME, ALL keywords are used at WHERE part of SQL statements to compare values with set of values.

Example: Get list of students had got grade more than 50 or 70.

SELECT name, grade FROM students WHERE grade > ANY(50, 70);
SELECT name, grade FROM students WHERE grade > SOME(50, 70);

Get list of students had got grade more than both 50 and 70.

SELECT name, grade FROM students WHERE grade > ALL(50, 70);

How to use PL/SQL TRIM?

2010-08-12T03:27:00.000-07:00

TRIM('CharacterToBeTrimed', 'StringToBeTrimed') function cleans entered character from front and end of supplied string.

Example: Clean 'A' characters from front and of names of students

SELECT TRIM('A' FROM name) FROM students

How to use PL/SQL NVL?

2010-08-11T00:17:00.000-07:00

NVL function is used to handle NULL values in a column.

NVL(columnName, valueIfColumnValueIsNotNull)

Example: Get student list with studentName, examGrade (if it is null put 50 instead)

SELECT studentName, NVL(examGrade, 50) FROM studentGrades

Note: An other way without NVL is to use CASE:

SELECT CASE WHEN examGrade IS NULL THEN 50 ELSE examGrade END FROM studentGrades

What is DUAL table for?

2010-08-05T22:31:00.000-07:00

DUAL is dummy table with 1 column (DUMMY - varchar2(1) - nullable) and 1 row 'X'

Test below queries to see details.

SELECT * FROM DUAL

DESCRIBE DUAL

Oracle tips?

2010-08-05T21:43:00.000-07:00

When you successfully exit SQL*Plus, execute one of DDL, DCL statements (even if they fail) or use ROLLBACK TO SAVEPOINT X there will occure implicit COMMIT for the open transaction.
When there is system crash there will occur automatic commit.
Subqueries are processed from most inner one to the most outer one.
If we create a synonym for an object in a schema, any user granted on this object can also access this object by using synonym for that object.
If we modify default value of an existing column on a table, default value will be used for inserts after that point, rows those have been added before with NULL value on the column will not be change.
Constraints can be added at creation time of tables and also after creation.

How to use SQL JOIN?

2010-08-05T21:31:00.000-07:00

JOINs bind 2 or more tables according to specified columns.

Types of joins:

Equijoins
Outer joins: FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, +
Inner joins
Self joins (recursive joins)
Hierarchical joins: START WITH ... CONNECT BY PRIOR
Natural joins

How to use PL/SQL CASE?

2010-08-03T02:52:00.000-07:00

CASE expression look like switch statements as in programming languages.

Example: Categorize students according to their exam grades.

SELECT
studentName,
CASE
WHEN studentGrade < 50 THEN 'Weak'
WHEN studentGrade < 90 THEN 'Good'
WHEN studentGrade < 100 THEN 'Well'
END FROM studentGrades