Oracle Database Introduction

From WikiOD

Remarks[edit | edit source]

Oracle is a relational database management system (RDBMS) originally built by Larry Ellison, Bob Miner, and Ed Oates in the late 70s. It was intended to be compatible with IBM's System R.

Versions[edit | edit source]

Version Release Date
Version 1 (unreleased) 1978-01-01
Oracle V2 1979-01-01
Oracle Version 3 1983-01-01
Oracle Version 4 1984-01-01
Oracle Version 5 1985-01-01
Oracle Version 6 1988-01-01
Oracle7 1992-01-01
Oracle8 1997-07-01
Oracle8i 1999-02-01
Oracle9i 2001-06-01
Oracle 10g 2003-01-01
Oracle 11g 2007-01-01
Oracle 12c 2013-01-01

Hello World[edit | edit source]

SELECT 'Hello world!' FROM dual;

In Oracle's flavor of SQL, "dual is just a convienence table". It was originally intended to double rows via a JOIN, but now contains one row with a DUMMY value of 'X'.

SQL Query[edit | edit source]

List employees earning more than $50000 born this century. List their name, date of birth and salary, sorted alphabetically by name.

SELECT employee_name, date_of_birth, salary
FROM   employees
WHERE  salary > 50000
   AND date_of_birth >= DATE '2000-01-01'
ORDER BY employee_name;

Show the number of employees in each department with at least 5 employees. List the largest departments first.

SELECT department_id, COUNT(*)
FROM   employees
GROUP BY department_id
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC;

Hello world! from table[edit | edit source]

Create a simple table[edit | edit source]

create table MY_table (
   what varchar2(10), 
   who varchar2(10), 
   mark varchar2(10)
);

Insert values (you can omit target columns if you provide values for all columns)[edit | edit source]

insert into my_table (what, who, mark) values ('Hello', 'world', '!' );
insert into my_table values ('Bye bye', 'ponies', '?' );
insert into my_table (what) values('Hey');

Remember to commit, because Oracle uses transactions[edit | edit source]

commit;

Select your data:[edit | edit source]

select what, who, mark from my_table where what='Hello';

Hello World from PL/SQL[edit | edit source]

/* PL/SQL is a core Oracle Database technology, allowing you to build clean, secure, 
   optimized APIs to SQL and business logic. */

set serveroutput on 

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Hello World!');
END;

Credit:Stack_Overflow_Documentation