RDBMS -- RELATIONAL DBMS.
DATA --- Collection of Files.
Files .txt (Notepad)
.doc (Ms-Word)
.ppt (Ms- PowerPoint)
.xls (Ms- Excel)
.pdf (Acrobat)
And some more example you can find in the below links for file extensions
http://www.fileinfo.com/filetypes/common
http://en.wikipedia.org/wiki/List_of_file_formats
DATA BASE:
Logical container to store the data in the form of tables, objects & Procedures, functions etc.
ODBC Open Data base Connectivity.
2- Tier Architecture :
FRONT END ODBC BACK END
Java, .Net (Developers) DATABASE
(SQL, MYSQL, DB2, SYBASE, FOXPRO)
Database Developers
It is named as 2-Tier Architecture because it has Front end and Back end.
Example:
Front End -- >> Gmail’s Home page (User Interface page)
Back End -- > > Data stored here (Your emails and data files, attachments etc)
If you’re using java, then it’s JDBC connectivity.
ADO. NET connectivity is for DOT NET.
3 – Tier Architecture:
APPLICATION SOFTWARE
|
|
FRONT END ODBC BACK END
(Developers) DATABASE
(SQL, MYSQL, DB2, SYBASE, FOXPRO)
Database Developers
It is named as 3-Tier Architecture because it has
Application Software, Front end and Back end.
APPLICATION SOFTWARE:
Application software, also known as an application or an "app", is computer softwaredesigned to help the user to perform specific tasks. Examples include enterprise software, accounting software, office suites, graphics software and media players. Many application programs deal principally with documents. Apps may be bundled with the computer and its system software, or may be published separately. Some users are satisfied with the bundled apps and need never install one.
4 – Tier Architecture:
APPLICATION SOFTWARE
(Desktop Applications)
|
|
FRONT END ODBC BACK END
(Developers) DATABASE
(SQL, MYSQL, DB2, SYBASE, FOXPRO)
|
| Database Developers
Web Browser
(ASP. NET, PHP)
It is named as 4-Tier Architecture because it has
Application Software, Front end, Back end and Web Browser.
DATABASE MANAGEMENT SYSTEM (DBMS)
There are 3- types of DBMS:
HDBMS -- Hierarchal DBMS
NDBMS -- Network ’’
RDBMS -- Relational ’’
RDBMS -- 12 Rules (E F CODD RULES (Edgar F. Codd Rules)).
http://en.wikipedia.org/wiki/Types_of_DBMS
RDBMS ----
ORACLE – PL/SQL -- 80% Market (Platform Independent) -12 EF Codd Rules
SQL SERVER -- 20% market
MS ACCESS
MY SQL --
DB2
SYBASE
FOXPRO
PORT GRE SQL
Oracle PL/SQL follows 12 EF codd Rules.
http://en.wikipedia.org/wiki/Codd's_12_rules
DB2, FOXPRO, PORT GRE SQL follows only 4/5 E F CODD RULES.
Platform Independent: Application that works on any platform (UNIX, Linux, MAC and Windows).
GUI – GRAPHICAL USER INTERFACE (Windows 7/XP)
NOT GUI – Eg) Command prompt.
-------------------------------------------------------------------------------------
DDL
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
Examples: CREATE, ALTER, DROP statements
DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
Examples: GRANT, REVOKE statements
TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
Examples: COMMIT, ROLLBACK statements
DML
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.
Examples: SELECT, UPDATE, INSERT statements
-------------------------------------------------------------------------------------
DATA DEFINITION LANGUAGE (DDL)
CREATING A TABLE
Create table tablename
(
Column 1 data type (Size),
Column 2 data type (Size),
);
ALTERING THE TABLE
1. ALTER TABLE tablename ADD (Colname datatype (size), colname datatype (size));
2. ALTER TABLE tablename DROP (Colname datatype (size), colname datatype (size));
3. ALTER TABLE tablename RENAME Column old name TO new name;
MODIFY THE TABLE
ALTER TABLE tablename MODIFY (Column datatype (size));
DROPING THE TABLE
DROP TABLE tablename;
RENAMING THE TABLE
RENAME oldtable TO newtable;
TRUNCATE THE TABLE-Data will be completly
TRUNCATE TABLE tablename;
DESCRIBE
DESC tablename;
-------------------------------------------------------------------------
DATA MANIPULATION LANGUAGE (DML)
INSERTING THE DATA IN A TABLE
To insert records sequentially
INSERT INTO tablename VALUES (Val1, Val2, Val3…);
To insert records randomly
INSERT INTO tablename (Column1, Column2, Column3….) VALUES (Val1, Val2, Val3…)
To insert Multiple Records (Sequentially and Randomly)
INSERT INTO TABLENAME VALUE (&CID,'&CUSTNAME','&BRANCH', ‘&BRANCHID,'&CDOB');
UPDATING THE DATA IN A TABLE
UPDATE tablename SET Colname= value WHERE condition;
Eg)
UPDATE employee SET name= ‘SUNEETHA’ WHERE name=’SUNITHA’;
UPDATE banking SET CUSTNAME= ‘ARIGI’ WHERE CUSTNAME= ’suneetha’; (ERROR)
IN SQL, We can’t update and Insert a query at a time. But, we can do it in PL/SQL if there are many records.
DELETING THE DATA IN TABLE
DELETE FROM tablename WHERE condition;
Eg)
DELETE FROM employee WHERE eno=1;
-----------------------------------------------------------------------------
DATA QUERY LANGUAGE (DQL)
SELECT COMMAND -- > To retrieve the data from table.
SELECT * FROM tablename;
To retrieve a selected columns
SELECT Columns FROM tablename WHERE condition;
Eg)
SELECT eno, name, age, salary FROM emp WHERE eno=1;
---------------------------------------------------------------
TRANSACTIONAL CONTROL LANGUAGE (TCL)
(Or)
DATA TRANSACT LANGUAGE (DTL)
Syntax:
COMMIT;
Commit command saves all transactions permanently.
Syntax:
ROLLBACK;
To undo transactions.
-------------------------------------------------------
DATA CONTROL LANGUAGE (DCL):
USER CREATION
CREATE USER
Eg)
CREATE USER SUNEETHA IDENTIFIED BY SUNEETHA;
GRANT:
GRANT
PRIVILEGES - > Connect, resource
Eg) If you want to give permission to user called “TESTER” then, here is the syntax:
GRANT connect, resource to TESTER;
REVOKE
REVOKE
PRIVILEGES - > Connect, resources
Eg) If you want to take back the permission from user called “TESTER” then, here is the syntax:
REVOKE connect, resource FROM TESTER;
CONNECTING TO DATABASE
1. CONN SUNEETHA/SUNEETHA (SQL PROMPT)
2. USERNAME ---------- (SQL DEVELOPER TOOL)
PASSWORD ----------
DROP THE USER
Drop user USERNAME;
Eg) Drop user SUNEETHA;
---- CONTINUES.......
