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;