Oracle Database Part: #1

 

Get Started with Oracle Database ☁

1.0 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'.

1.1 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;

1.2 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';

1.3: 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;

x