SQL Commands

SELECT *
FROM table;		-- Selects all elements from table

SELECT DISTINCT x	-- Selects the column, ommiting any duplicates
FROM table;

DESCRIBE table		-- Describes the chosen table, returning its columns and their respective types
________________________________________________________________________________________________________________________________

Comparison operators: <, >, =, != (or <>)

BETWEEN x AND y				-- Checks if the value is in the interval
WHERE x is BETWEEN 10 and 20

IN (VALUE_LIST)				-- Checks if the value is in the list of values
WHERE x IN (10, 20, 30, 40, 50)

LIKE					-- Checks if the value is like the passed pattern
WHERE x LIKE '_A%'			-- _ means there's one character there, while % represents any number of characters

IS NULL					-- Checks if the value is NULL
WHERE x IS NOT NULL			-- If x is not NULL
________________________________________________________________________________________________________________________________
Logic operators: AND, OR, NOT

WHERE x = 10 AND y = 20			-- If x is 10 and y is 20
WHERE x = 10 OR y = 20			-- If x is 10 or y is 20
WHERE x > 10 AND x <> 20		-- If x is greater than 10 and x isn't 20
WHERE x NOT IN (20, 30, 40, 50)		-- If x is not 20, 30, 40 or 50


The comparison operators go first, then NOT, AND, OR are checked in that order, but brackets can be used to force priorities.
________________________________________________________________________________________________________________________________
Queries can be sorted by using the ORDER BY command:

SELECT name, salary, hiredate, salary*12 annualsalary
FROM employees
ORDER BY hiredate;			-- The oldest employees are listed first

ORDER BY hiredate DESC;			-- The most recently hired employees listed first
ORDER BY annualsalary;			-- Sorts by the alias column
ORDER BY salary, hiredate;		-- Sorts by salary first, and then by hiredate from results with the same salary
________________________________________________________________________________________________________________________________
When we join N tables together, we must have N-1 conditions in the WHERE clausule in order to correctly connect them.
Otherwise, we get the cartesian product, meaning we get A*B rows, where A and B are the amount of rows in the tables.
The command CROSS JOIN placed under the FROM command creates a cartesian product:

SELECT name, salary
FROM employees
CROSS JOIN departments;			-- This is the same as FROM employees, departments


The ON command can be used next to FROM in place of WHERE if we're joining two classes by all attributes with the same name
This is called a NATURAL JOIN.
If there is more than one column with the same name, we can use the keyword USING instead

SELECT name, department_name
FROM employees e, departments d
	ON (e.department_id = d.department_id);

SELECT department_id, department_name, manager_id, location_id
FROM departments
	NATURAL JOIN locations;
	

EQUIJOIN is joining by equality, using the WHERE command:

SELECT name, department_name
FROM employees e, department d
WHERE e.department_id = d.department_id;


Joining by inequalities are called NON EQUIJOINS:

SELECT e.name, e.salary, s.max_salary, s.min_salary
FROM employees e, jobs s
WHERE e.salary BETWEEN s.min_salary AND s.max_salary


Joining a table with itself is called a SELF JOIN

SELECT worker.name || ' works for ' || manager.name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.eployee_id


OUTER JOIN joins two tables like INNER JOIN expanded with unjoined columns from the LEFT or RIGHT side of the table.
FULL OUTER JOIN returns all results which are the result of INNER JOIN and all results of the LEFT and RIGHT join.
________________________________________________________________________________________________________________________________
Single-Row functions can be nested on any level.
________________________________________________________________________________________________________________________________

Functions for working with characters:

Case conversion:
LOWER('TESTstring')		-- Returns teststring
UPPER('TESTstring')		-- Returns TESTSTRING
INITCAP('TESTstring')		-- Returns Teststring

Character Manipulation:
CONCAT('Nice', 'Meme')		-- Returns NiceMeme
SUBSTR('String', 1, 3)		-- Returns Str		REMINDER: The last parameter is how many characters we're selecting
LENGTH('String')		-- Returns 6
INSTR('String', 'r')		-- Returns 3
LPAD(salary, 10, *)		-- Returns ******5000
RPAD(salary, 10, *)		-- Returns 5000******
TRIM('B' FROM 'Banana')		-- Returns anana
________________________________________________________________________________________________________________________________
Functions for working with numbers:

ROUND(57.45678, 3)		-- Rounds to 57.457
ROUND(57.45678, -1)		-- Rounds to 60

TRUNC(57.45678, 3)		-- Truncates to 57.456
TRUNC(57.45678, -1)		-- Truncates to 50

MOD(20, 7)			-- Returns the remainder, in this case 6
________________________________________________________________________________________________________________________________
Dates are saved like CENTURY, YEAR, MONTH, DAY, HOUR, MINTE, SECOND
The default return value for a date is DD-MON-YY
SYSDATE returns the current date and time on the server hosting the database
Subtracting two dates returns the amount of days between them, while adding them returns a new date (usually doesn't work)

Functions for working with dates are:

MONTHS_BETWEEN(date1, date2)		-- Returns the amount of months between the two dates
ADD_MONTHS(date1, x)			-- Adds months
NEXT_DAY(date1)				-- Goes to the next day of the given date
LAST_DAY(date1)				-- Goes to the last day of the month

ROUND(date1, x)				-- Rounds the date
ROUND('01-SEP-95 22:12:34')		-- Returns 02-SEP-95

TRUNC(date1, x)				-- Truncates the date
TRUNC('01-SEP-95 22:12:34')		-- Returns 01-SEP-95
________________________________________________________________________________________________________________________________
Converting one type of data to the other goes like this:
NUMBER --(TO_CHAR)-> CHARACTER --(TO_DATE)-> DATE

And the other way around:
DATE --(TO_CHAR)-> CHARACTER --(TO_NUMBER)-> NUMBER

TO_CHAR('01.01.1998', 'dd.mm.yyyy')	-- Converts the char to a date

Formatting dates:
YYYY		-- Entire year in numbers
YEAR		-- Year as a word
MM		-- Two digits for a month
MONTH		-- Month as a word
DY		-- First three letters of the day of the week
DAY		-- Full name of the day of the week
DD		-- Two digits for the day of the month
DDSPTH		-- Day of the month as a word
HH24:MI:SS AM	-- 15:45:32 PM
DD "of" MONTH	-- 12 of OCTOBER

Formatting characters:
9		-- Number
0		-- Forces the display of zeroes
$		-- $ prefix
L		-- Uses floating point to indicate currency
.		-- Decimal point
,		-- Uses comma to seperate thousands (1,000 instead of 1000)
________________________________________________________________________________________________________________________________
The NVL function creates a placeholder value for values which are null:

NVL(myValue, 0)
NVL(myDate, '01-JAN-97')
NVL(myJob, 'Nothing')
________________________________________________________________________________________________________________________________
The DECODE function is akin to an IF-ELSE block:

SELECT job_id job, salary sal,
				DECODE(job_id,
				       'ANALYST', SAL*1.1,
				       'CLERK', SAL*1.15,
				       'MANAGER', SAL*1.20,
				       SAL) REVISED_SALARY
FROM employees;
________________________________________________________________________________________________________________________________
Group functions:

SELECT [column,] group_function(column)
FROM table
[WHERE condition]		-- Conditions
[GROUP BY column]		-- Grouping results
[HAVING group_condition]	-- Used to create a restriction
[ORDER BY column]		-- Ordering the results

ALL columns which are outside of group functions must be in the GROUP BY statement


AVG(column)		-- Takes the average value of the column
AVG(NVL(column, value))	-- Returns the average of all elements, setting the NULL elements to a certain value

COUNT(column)		-- Returns the amount of rows with a non-null value for the column
COUNT(DISTINCT column)	-- Returns how many distinct elements there are in the column

MAX(column)		-- Grabs the max element form the column
MIN(column)		-- Grabs the min element from the column
STDDEV(column)		-- Returns the standard deviation of the column
SUM(column)		-- Sums the elements of the passed column into a single output
VARIANCE

The HAVING condition is useful for creating restrictions:

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job NOT LIKE 'SALES%'
GROUP BY job_id
HAVING SUM(salary)>5000
ORDER BY SUM(salary)


Group functions can also be nested:

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;


However, adding extra columns when nesting functions usually leads to errors even with the GROUP BY command.

The functions ROLLUP and CUBE are used to form sets of rows which contain additional rows with values of subtotals,
where ROLLUP contains the "more important," and CUBE contains the "less important" grouped columns.
These functions are used within the GROUP BY expression:

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);

This code will generate a normal list, sorted by the department_id column, but will add an additional row which sums the total
salary of the entire department for each unique instance of the department_id column.


SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY CUBE(department_id, job_id);

This code will work almost exactly the same, except it will create additional rows which sum by the job_id
instead of the department_id (it will sum by the "less important" column instead)
We can also use both ROLLUP and CUBE in a single GROUP BY statement.

The GROUPING function returns either a 0 or a 1, depending on whether the current row was grouped by the passed column or not:

SELECT department_id, job_id, SUM(salary), GROUPING(department_id), GROUPING(job_id)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);

With this we'll know if the current row was grouped by the dpt_id or the job_id (or both) when the ROLLUP function is used.

If we're working with large amounts of data, we should use GROUPING SETS, which are an upgrade of the GROUP BY function.
Using GROUPING SETS, we can define multiple groups of data in the same query.
They only pass through the data once, and don't need to have the UNION function (it's used automatically)
This means they simplify code and are less time-consuming.

SELECT department_id, job_id, manager_id, AVG(salary)
FROM employees
WHERE department_id < 50
GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id))


Combined columns represent a set of columns which is treated as a single column: ROLLUP(a, (b, c), d)
They are used in the GROUP BY function.

Groups can also be combined like so: GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
________________________________________________________________________________________________________________________________
Subqueries can be used to solve complex problems.
For example: Select the employees whose salary is larger than the employee with the ID 102:

SELECT name
FROM employees
WHERE salary > (SELECT salary
		FROM employees
		WHERE employee_id = 102);
		

The subquery must be enclosed in brackets, and placed to the RIGHT of the comparison operator.
A subquery cannot have the ORDER BY command.
When using subqueries, we should use single-row operators with a single subquery.
In case of multiple operators, we can use multiple subqueries if we only use single-row subqueries.
A subquery can be placed anywhere in the original query.

Single-row subqueries return only one row, and use single-row comparison operators: =, >, >=, <, <=, != (or <>)
Multiple-row subqueries return more than one row, and use multiple-row comparison operators: IN, ANY, ALL

ANY is used when multiple ROWS are returned, while ALL is used when multiple COLUMNS are returned.

We can also compare PAIRS instead of individual values:
SELECT orderID, productID, quantity
FROM items
WHERE (productID, quantity) IN (SELECT productID, quantity
				FROM items
				WHERE orderID = 200)
AND orderID <> 200;


The operator EXISTS and NOT EXISTS can be very useful when it comes to using subqueries. It returns TRUE or FALSE.
This means it can easily be used in the WHERE segment of the query:

SELECT employee_id, first_name
FROM employees e
WHERE EXISTS(SELECT 'X'
	     FROM employees
	     WHERE manager_id = e.employee_id);
________________________________________________________________________________________________________________________________
Set functions are used after the FROM command:

UNION
UNION ALL	-- Displays duplicates
INTERSECT
MINUS		-- If A has it and B doesn't

When using SET operators, the columns in SELECT must have the same amount of elements and the same types.
Brackets can be used to control the order of operations.
The ORDER BY function can only be used at the end of the query.
And the names of columns from the first SELECT command will be used as names of the resulting columns.
The result is sorted in ascending order, except for the UNION ALL command.
________________________________________________________________________________________________________________________________
Hierarchy:

Top-Down and Bottom-Up selects are made possible by forming a hierarchy.
This can be done for example with employees and their managers, then the managers of the managers and so on...
Top-Down example:
SELECT last_name||' reports to '||
PRIOR last_name "Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;


Bottom-Up example:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH  employee_id = 105
CONNECT BY PRIOR manager_id = employee_id;

As we can see, the difference in Top-Down and Bottom-Up is in the order of the columns in the CONNECT BY PRIOR clause:
CONNECT BY PRIOR column1 = column2
-- Top-Down: column1 = parent, column2 = child
-- Bottom-Up: column1 = child, column2 = parent

We can also display the structure visually using the LPAD function:
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*4)-4,' ') AS org_chart
FROM employees
START WITH last_name='King' 
CONNECT BY PRIOR employee_id=manager_id;


We can also avoid entire branches of the hierarchy tree:
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*4)-4,' ') AS org_chart
FROM employees
START WITH last_name='King' 
CONNECT BY PRIOR employee_id=manager_id AND last_name!='Higgins';	-- Higgins and his underlings won't be displayed
________________________________________________________________________________________________________________________________
Privileges and User Access:

A new user can be created using the CREATE USER function:
CREATE USER testuser
IDENTIFIED BY testpassword;


The new user won't be able to do anything, so we need to grant him access to some commands:
GRANT create table, create sequence, create view
TO testuser;


To save time and manage lots of users at once, we can create our own custom roles.
These roles can have their own access level, and we can assign the role to any user we want later on:

CREATE ROLE manager;

GRANT create table, create view
TO manager;

GRANT manager to testuser;


The database administrator (DBA) can create users and edit their passwords. To edit passwords, we use the ALTER USER command:
ALTER USER testuser
IDENTIFIED BY newpassword;


The creator of an object has full access to that object, along with the ability to grant privileges to others over that object.
GRANT select
ON mytable
TO user1, user2;

GRANT update(first_name, last_name)
ON mytable
TO user1, manager;

GRANT select, insert
ON mytable
TO user1
WITH GRANT OPTION;		-- Now the user can grant privileges to other users as well!

GRANT select
ON mytable
TO PUBLIC;			-- Now EVERYONE can use the command SELECT on mytable!


As the administrator giveth, the administrator may also taketh away. This is done with the REVOKE command:
REVOKE select, insert
ON mytable
FROM user1;

Note: This will also remove any privileges the revoked user has given others with the WITH GRANT OPTION
________________________________________________________________________________________________________________________________
Working with Tables:

We can create a table using the CREATE TABLE command.
When creating a table, we should add certain constraints to the table itself as well as its columns:
CREATE TABLE testtable (
	column1		NUMBER(5,0),
	column2		VARCHAR2(20)	UNIQUE, NOT NULL,
	column3		DATE,
	
	CONSTRAINT tt_c1_pk PRIMARY KEY (column1),
	CONSTRAINT tt_c3_fk FOREIGN KEY (column3) REFERENCES employees(hire_date),
	CONSTRAINT tt_c1_chkval CHECK (column1 BETWEEN 10000 AND 99999)
);

If we forget to add a certain constraint, we can do so with the ALTER TABLE command:
ALTER TABLE testtable
ADD CONSTRAINT c3_nn NOT NULL(column3);

We can also disable constraints, or fully remove them:
ALTER TABLE testtable
DISABLE CONSTRAINT tt_c1_chkval;

ALTER TABLE testtable
DROP CONSTRAINT tt_c1_pk;

ALTER TABLE testtable
ENABLE CONSTRAINT tt_c1_chkval;


All constraints can be viewed by checking the USER_CONSTRAINTS and USER_CONS_COLUMNS tables.

After creating a table, we can add comments to the table and its columns like so:
COMMENT ON TABLE mytable IS 'This is a sample comment';
COMMENT ON COLUMN mytable.column1 IS 'This is another comment!';


Comments can be viewed by using:
SELECT * FROM user_tab_comments;
SELECT * FROM user_col_comments;


As time goes on, we can update the table using the UPDATE command:
UPDATE testtable t
SET column1 = (SELECT first_name
	       FROM employees e
	       WHERE t.column1 = e.employee_id);
	       

We can also delete things from the table:
DELETE FROM employees e
WHERE employee_id = (SELECT employee_id
		     FROM emp_history
		     WHERE employee_id = e.employee_id);


The WITH command is used when a query is used multiple times in a complex query. It imporeves performance.
WITH dept_costs AS (
	SELECT  d.department_name, SUM(e.salary) AS dept_total
	FROM    employees e, departments d
	WHERE   e.department_id = d.department_id
	GROUP BY d.department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg
				     		  FROM   dept_costs)
	SELECT *
	FROM   dept_costs
	WHERE  dept_total > (SELECT dept_avg
		             FROM avg_cost)
	ORDER BY department_name;


Columns can also be set as unused:
ALTER TABLE mytable
SET UNUSED (column1, column2);
________________________________________________________________________________________________________________________________
Views:

Views are used to restrict access to data, to simpily complex queries.
CREATE VIEW testview		-- Also works as CREATE OR REPLACE testview
AS SELECT employee_id EMPLOYEE NUMBER, last_name NAME, job_id
FROM employees
WHERE department_id = 90;


Complex views usually contain group functions and data from multiple tables.
DML operations can only be used with simple views, not complex ones.
It's impossible to delete items if a view contains group functions, GROUP BY command, the keyword DISTINCT, or a pseudo-column.
It's impossible to update items if a view contains any of the above, columns defined with queries, or a ROWNUM pseudo-column.
It's impossible to add new rows if the view contains any of the above, or if there are NOT NULL columns in the base table
outside of the view.

We can also ensure that any updates follow the given constrains using the WITH CHECK OPTION command:
CREATE OR REPLACE VIEW testview
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT tt_chk;


A view can also be made read-only using the WITH READ ONLY command:
CREATE OR REPLACE VIEW testview
AS SELECT *
FROM employees
WHERE department_id = 20
WITH READ ONLY;


Views are deleted like so:
DROP VIEW testview;


Inline views are subqueries with an alias which can be used in an SQL command.

ROWNUM can be used to list the top or bottom rows depending on how we rank them
SELECT ROWNUM as Rank, Name, Pay
FROM (SELECT last_Name Name, salary Pay
      FROM employees
      ORDER BY salary DESC)
WHERE ROWNUM <= 3;
________________________________________________________________________________________________________________________________
Sequences:

Sequences are automatically generated unique numbers. They're objects which are shareable between users.
They are usually used to generate the primary key of the table.

CREATE SEQUENCE testseq
INCREMENT BY 1
START WITH 1000
MAXVALUE 9999
NOCACHE
NOCYCLE

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;


NEXTVAL returns the next number in a sequence, incrementing it in the process.
CURRVAL returns the last generated value of a sequence.

INSERT INTO department(department_id, department_name, location_name)
VALUES (testseq.NEXTVAL, 'Hello', 'There');


And to check the current number of the sequence:
SELECT testseq.CURRVAL
FROM dual;

We can check for the next value of a sequence by querying the USER_SEQUENCES table, especially for NOCACHE sequences.

Sequences can be altered with the ALTER SEQUENCE command, and deleted with the DROP SEQUENCE command.
________________________________________________________________________________________________________________________________
Indexes and synonyms:

Indexes are used to speed up queries by using pointers, thus reducing I/O operations.
They are database objects which aren't dependant on the table for which they were made.
Created indexes are automatically updated by the database.

Indexes are created automatically when we use the PRIMARY KEY or UNIQUE constraints, or manually:
CREATE INDEX testindex
ON mytable(last_name)


Indexes are best used if there's a column (or multiple columns) which is used often in the WHERE or JOIN commands.
They are also used if a column has a wide range of values, or contains a large amount of NULL values.
Also used for when a table has huge amounts of data, but its queries only return 2-4% of the data at one time.

Indexes shouldn't be used if the table is small, if its columns are rarely used in the WHERE command or if it's updated often.

Indexes can be viewed in the USER_INDEXES or the USER_IND_COLUMNS views.

Function-based indexes are made using columns of a table, constants, or functions:
CREATE TABLE test(coll NUMBER);
CREATE INDEX testindex ON test(coll, coll+10);
SELECT coll+10 FROM test;


Indexes can be deleted using: DROP INDEX testindex;


Synonyms are just different names for same objects.
They simplify access and are used to avoid writing the entire structure of the database for each queried object:
CREATE SYNONYM testsyn
FOR myobject;


They are deleted by using the DROP SYNONYM command: DROP SYNONYM testsyn;
________________________________________________________________________________________________________________________________
Functions:

Functions in SQL are created like so:
CREATE [OR REPLACE] FUNCTION function_name
	(argument1 [mode1] datatype1,
	argument2 [mode2] datatype2,argument2 [mode2] datatype2,
	. . .)
RETURN datatype
IS|AS
PL/SQL Block;


CREATE FUCTION is used to create a function, which we then place into the .sql file and compile it in SQL*Plus:
CREATE OR REPLACE FUNCTION get_sal
(v_id  IN emp.empno%TYPE)
RETURN NUMBER
IS
	v_salary emp.sal%TYPE :=0;
BEGIN
	SELECT sal
	INTO v_salary
	FROM emp         
	WHERE empno = v_id;
	RETURN (v_salary);
END get_sal;
/


After we create the function and save it as a .sql file, we can call it like so:
START get_salary.sql
VARIABLE g_salary number
EXECUTE :g_salary := get_sal(7934)
PRINT g_salary


Functions can be called:
- In the SELECT command (assuming it doesn't contain COMMIT, ROLLBACK, SAVEPOINT or other DML commands)
- As a condition in the WHERE or HAVING command
- In the CONNECT BY, START WITH, ORDER BY and GROUP BY commands
- In the VALUES part of the INSERT command
- In the SET part of the UPDATE command
________________________________________________________________________________________________________________________________
Triggers:

Triggers are blocks of code which run implicitly once the event they're tied to happens.
A trigger can be a database trigger or an application trigger.
A trigger is used to centralize global operations or execute connected actions.

The trigger consists of:
Trigger timing:
	-Tables: BEFORE and AFTER
	-Views: INSTEAD OF
Trigger event: INSERT, UPDATE, DELETE
Table/View name
Trigger type: Row/Expression
WHEN command
Trigger body:
	- DECLARE
	- BEGIN
	- EXCEPTION
	- END


Trigger sample:
CREATE OR REPLACE TRIGGER osiguraj_emp
BEFORE INSERT ON emp
BEGIN
IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '18')
	THEN RAISE_APPLICATION_ERROR (-20500, 'You can insert a new row only during work days and after-hours!.');
END IF;
END;
/


Triggers can also be altered, and enabled/disabled en-masse:
ALTER TRIGGER testtrigger ENABLE | DISABLE
ALTER TABLE testtable ENABLE | DISABLE ALL TRIGGERS
ALTER TRIGGER testtrigger COMPILE
DROP TRIGGER testtrigger


Triggers are implemented for the purposes of security, monitoring, data integrity, referential integrity, table replication,
event logging and data calculation.
________________________________________________________________________________________________________________________________