Data Processing

  • Batch Processing
  • Online Processing
  • Real-time processing

What is DBMS?

Where does it fit in?

Services provided by DBMS

  • Data Mgmt
  • Data definition
  • Transaction Support
  • Concurrency control
  • Recovery
  • Security & Integrity
  • Utilities

Types of DBMS/ Database Technologies

  • Hierarchical – IMS
  • Network – IDMS
  • Relational – Oracle, DBMS

SQL – Structured Query Language

Datatypes:

  • Integer
  • Float
  • Char
  • Varchar2
  • Date

Operators:

  • Arithmetic (+,-,*,/)
  • Logical – AND, OR
  • Relational  – (>, <, <=, >=)

 

 

DDL – Data Definition Language

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • GRANT
  • REVOKE

DML – Data Manipulation Language

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

DCL – Data Control Language

  • COMMIT
  • ROLLBACK

 

CREATE TABLE <table name> (<Column name> <data type> constraints,

<Column name> <data type> constraints,

<Column name> <data type> constraints….)

ALTER TABLE <table name> (ADD/DROP <column name>)

DROP TABLE <table name>

INSERT INTO <table name> VALUES (<data1>, <data2>…)

INSERT INTO <table name> (column list) VALUES (Values list)

UPDATE <table name> SET <column name> = value [WHERE <condition>]

DELETE FROM <table name> [WHERE condition]

SELECT * FROM <table name>

SELECT <column name(s)) FROM <table name>

 

 

Aggregate Functions

Sum()

Avg()

Max()

Min()

Count()

Sub Queries

  • Independent
  • Correlated

Relational Algebra (RA) Operations:

  • UNION
  • INTERSECT
  • MINUS
  • Restriction
  • Projection
  • JOIN

JOINS:

  • Inner Join
  • Equi Join
  • Outer Join

ü  Left Outer

ü  Right Outer

  • Self Join

Inner Join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

 

 

 

Left Outer Join

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Right Outer Join

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Full Outer Join

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;