Introduction to Commercial Database Query Language :

Commercial database systems require more user-friendly query languages. And as for commercial purpose the best suited option is SQL (Structure Query Language), because it contain facilities for designing and modifying the database. Structure Query Language(SQL) is a programming language used for storing and managing data in RDBMS. SQL was the first commercial language introduced for E.F Codd's Relational model. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as the standard database language. SQL is used to perform all type of data operations in RDBMS. The uses of SQL include modifying database table and index structures; adding, updating and deleting rows of data; and retrieving subsets of information from within a database for transaction processing and analytics applications. Queries and other SQL operations take the form of commands written as statements. SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s. Also known as SQL databases, relational systems comprise a set of tables containing data in rows and columns. Each column in a table corresponds to a category of data. For example, customer name or address while each row contains a data value for the intersecting column. The statements (SQL) used in database languages are categorized as data definition language(DDL), Data Control language (DCL), and Data Manipulation Language(DML).

DDL (Data Definition Language) : 

DDLs are used to define the metadata of the database. Data Definition Language(DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER and DROP. It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and authorization information for each table and physical storage structure of each table on disk. Some DLL statements and its usage:

DDL Statement Usage
CREATECreate schema objects
ALTERAlter schema objects
DROPDelete schema objects
RENAMERename schema objects
CommentAdd a comment to the data dictonary
TRUNCATERemove all records from a table

DML (Data Manipulation Language ) :

A Data Manipulation Language is a language that enables users to access or manipulate data as organized by the appropriate data model. It is a language that provides a set of operations to support the basic manipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the database. The portion of a DML that involves information retrieval is called a query language. A query is a statement requesting the retrieval of information. Some DML statements and its usage :

DML Statement Usage
DELETERemove rows from tables or views
INSERTAdd new rows of data into table or views
SELECTRetrive data from one or more tables
UPDATEChange column values in existing rows of a table or view
LOCK TABLEConcurrency Control
CALLCall a PL/SQL or Java subprogram
MERGEUPSERT operation (insert or update)
EXPLAIN PLANinterpretation of the data access path

DCL ( Data Control Language) :

A Data Control Language is used to control privilege in Database. To perform any operation in the database, such as for creating tables, sequences or views we need privileges. A privilege can either be granted to a User with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc. In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command. Some DCL statements and its usages :

DCL Statement Usage
GRANTGives user access privileges to database
REVOKETake back permissions from user

Next Topic : 

No comments:

Post a Comment