DBMS SQL Overview

DBMS SQL Overview

For Relational Databases, SQL is a programming language. It is built using relational algebra and relational calculus of tuples. SQL comes with all big RDBMS distributions as a package.

SQL contains all languages for data definition and data manipulation. Using SQL 's data description properties, the database schema can be designed and modified, while data manipulation properties allow SQL to store and retrieve database data.

Data Definition Language

SQL uses the following set of commands to define database schema −

CREATE

Creates new databases, tables and views from RDBMS.

For example −

Create database phptpoint;
Create table article;
Create view for_students;

DROP

Drops commands, views, tables, and databases from RDBMS.

For example−

Drop object_type object_name;
Drop database phptpoint;
Drop table article;
Drop view for_students;

ALTER

Modifies database schema.

Alter object_type object_name parameters;

For example−

Alter table article add subject varchar;

In the relation article, this command adds an attribute with the subject name of the string type.

Data Manipulation Language

SQL is equipped with a language (DML) for data manipulation. By inserting, updating and deleting its data, DML modifies the database case. For all types of data modification in a database, DML is responsible. In its DML section, SQL contains the following set of commands –

• SELECT/FROM/WHERE
• INSERT INTO/VALUES
• UPDATE/SET/WHERE
• DELETE FROM/WHERE

These basic constructs allow database programmers and users to enter and efficiently retrieve data and information into the database using a number of filter options.

SELECT/FROM/WHERE

• SELECT − This is one of SQL 's simple query commands. It is similar to relational algebra's projection operation. Depending on the situation defined by the WHERE clause, it selects attributes.
• FROM −This clause takes the name of a relation as an argument from which to select / project attributes. If more than one relation name is given, the Cartesian product corresponds to this clause.
• WHERE -This clause specifies the predicate or conditions that must match the attributes to be projected in order to qualify.

For example −

Select author_name
From book_author
Where age > 50;

This command yield the names of authors of a book-author relation whose age is greater than 50.

INSERT INTO/VALUES

This command is used to insert values into the table (relation) rows.

Syntax−

INSERT INTO table (column1 [, column2, column3 ... ]) VALUES (value1 [, value2, value3 ... ])

Or

INSERT INTO article (Author, Subject) VALUES ("anonymous", "computers");

UPDATE/SET/WHERE

This command is used to removing a table (relation) from one or more rows.

Syntax −

DELETE FROM table_name [WHERE condition];

For example −

DELETE FROM article
   WHERE Author="unknown";