SQL-certification path

OLTP and OLAP

OLTP – Online Transaction processing – inserts , updates and deletes very efficient and fast, OLAP (Online Analytical processing – Data Warehouse) – data insert not efficient, reading data is efficient

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without

Most db starts their life as OLTP,

Challenges in balncing number of indexes , usefulness and to keep data useful for both query types.

When the data volume (business grows) increases it becomes impossible to manage with just OLTP and the solution is to maintain two different database structures which makes data retrieval efficient.

Thats how OLAP is born when business get big and we need to improve effieciency of retrieval.

CRUD- create, read, update and delete (insert, select, update and delete)

ACID – Atomicity, Consistency,Isolation and Durability

Normalization – Process of organizing data to minimize redundancy and remove ambiguity.

Commit – SQL command that marks transaction successful

rollback – SQL command that marks transaction unsuccessful

Datatypes – An attribute that specifies the type of data that the object can hold

(DDL and DML )Statement Types

DDL – CREATE, ALTER, DROP and TRUNCATE
DML – SELECT,INSERT, UPDATE and DELETE