DBMS AND SQL COMMANDS BY LOKRE

DBMS – DATA BASE MANAGEMENT SYSTEM.

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 IDENTIFIED BY ;
Eg)
CREATE USER SUNEETHA IDENTIFIED BY SUNEETHA;


GRANT:

GRANT TO ;

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

REVOKEFROM;

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.......