Oracle Database Getting started with Oracle Database
Remarks
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
| 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
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'.
Hello World from PL/SQL
/* 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;
Hello world! from table
Create a simple table
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)
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
commit;
Select your data:
select what, who, mark from my_table where what='Hello';
SQL Query
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;
