SQL : You tell oracle which information you want it to select, insert, update, delete.
select and from: query example: select birthday from classmates; You will use 4 primary keywords while selecting information from an table: select, from, where, order by
Where: Use of this keyword must be followed by one or more logical tests with AND, or OR , the logical connectors. The preference for AND > OR.
(file attached)
Logical Test against single values:
- comparators: =,>=,<=,^=,!=,<>
- pattern matching: LIKE 'M_o%g' , % is any number of any characters, _ is 1 character.
- IS NULL, IS NOT NULL: special comparators for checking equivalence with NULL. Comparators wont work
(file attached)
Logical Tests against a List of values: Logical Test against List of Values: IN, NOT IN, BETWEEN 9 AND 10, BETWEEN 'A' AND 'D', NOT BETWEEN 6 AND 9
order by: orders the results. this keyword should be followed by one or more column names(comma separated), each followed by asc (default) or desc. Sorting priority followed from left to right. (high to low)
(file attached)
Some notes about using subqeuries with operators
One query to rule them all , of that which has been taught till now:
create or replace view abctest as
select
"EMPNO",
"ENAME",
"JOB",
"DEPTNO"
from "EMP"
where
ename > 'A'
and ename <= 'D'
or deptno IN
( select deptno
from dept
where dname LIKE 'A%'
or deptno BETWEEN 0 and 10
) order by ename, job desc
Views are dynamic, and always reflect data present in the actual tables
Data types in Oracle: NUMBER, CHAR, DATE, VARCHAR2, LONG, RAW, LONG RAW.
(file attached)
String functions
some example and usage for understanding
(file attached)
(file attached)
(file attached)
(file attached)
(file attached)
(file attached)
(file attached)
(file attached)
Functions for NUMBER data type:
(file attached)
group value functions are invariant to NULL values and ignore them.
SysDate shows todays date. ex. select SysDate from DUAL;
(file attached)
(file attached)
Understanding groups
(file attached)
(file attached)
for above query
having can be used when filter is based on calculated group function like sum, avg , efc.
putting double quotation marks around aliases, forces oracle to store and match the name in mixed case. So the alias will become case sensitive. By default when no quotation marks are present all names are stores in upper case
(file attached)
Example of a list of Columns being compared at once in a where clause
when we use the test say , W.lodging = lodging , We exclude all rows with lodging NULL in our results
to avoid this use: NVL(W.lodging,'whatever') = NVL(lodging,'whatever')
this query also excludes rows with null values as the IN keyword ignores NULL values
Note for correlated subqueries:
sql chooses the variable related to the closest select statement
(file attached)
truncate command cannot be rolled back or commited
update COMFORT set Midnight = Midnight + 1, Noon = 20.5, Sampledat = value/subquery
where City = value/subquery
(file attached)
also possible
candidate key : any combination of 1 or more columns, the values of which uniquely identify the table
primary key: a candidate key which cannot have a null value. Only one primary key is possible for a table
create table tablename ( columnname datatype constraints,..., contstraints(columnname1,columnname2,...),... );
constraints - UNIQUE , CHECK, NOT NULL, PRIMARY KEY, FOREIGN KEY
drop table tablename;
alter table tablename add/modify ( columnname datatype constraint, .. )
Triggers :
** Row level Triggers : executed once for each row into transaction
** Statement-Level Triggers : executes once for each transaction
Triggers:
create or replace trigger triggername
before / after
delete / insert / update
on tablename
for each row
when condition
begin
end
(file attached)
Normal forms:
(file attached)
In the first normal form each field contains a single value. A field may not contain a set of values or a nested record.
(file attached)
2NF: every non-candidate-key attribute depends on the whole candidate key
(file attached)
(file attached)
BCNF acts differently from 3NF only when there are multiple overlapping candidate keys.
(file attached)
Procedures
(file attached)
(file attached)
Cursors
https://www.reddit.com/r/explainlikeimfive/comments/dyheyc/eli5_normal_form_in_databases/ 1NF Each column stores one value
2NF
- it is in 1NF
- It does not contain any partial dependencies.
3NF
- It is 2NF
- No non prime attributes has any transitive dependency on primary key
every non-key attribute must provide a fact about the key , the whole key , and nothing but the key