Structured Query Language (SQL)

 Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation.

§  SQL is used to query, insert, update and modify data.

§  Most relational databases support SQL, which is an added benefit for database administrators (DBAs), as they are often required to support databases across several different platforms.

§  First developed in the early 1970s at IBM by Raymond Boyce and Donald Chamberlin, SQL was commercially released by Relational Software Inc. (now known as Oracle Corporation) in 1979.

§  The current standard SQL version is voluntary, vendor-compliant and monitored by the American National Standards Institute (ANSI).

§  Most major vendors also have proprietary versions that are incorporated and built on ANSI SQL, e.g., SQL*Plus (Oracle), and Transact-SQL (T-SQL) (Microsoft).

§  SQL comprises both data definition and data manipulation languages.

§  Using the data definition properties of SQL, one can design and modify database schema, whereas data manipulation properties allows SQL to store and retrieve data from database.

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 tutorialspoint;

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 tutorialspoint;

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;

Data Manipulation Language:

SQL is equipped with data manipulation language (DML). DML modifies the database instance by inserting, updating and deleting its data. DML is responsible for all froms data modification in a database. SQL contains the following set of commands in its DML section -

§  SELECT/FROM/WHERE

§  INSERT INTO/VALUES

§  UPDATE/SET/WHERE

§  DELETE FROM/WHERE

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

SELECT/FROM/WHERE

·         SELECT: This is one of the fundamental query commands of SQL. It is similar to the projection operation of relational algebra. It selects the attributes based on the condition described by WHERE clause.

·         FROM : This clause takes a relation name as an argument from which attributes are to be selected/projected. In case more than one relation names are given, this clause corresponds to Cartesian product.

·         WHERE :This clause defines predicate or conditions, which must match in order to qualify the attributes to be projected.

For example -

Select author_name

From book_author

Where age > 50;

This command will yield the names of authors from the relation book_author whose age is greater than 50.

INSERT INTO/VALUES

This command is used for inserting values into the rows of a table (relation).

Syntax-

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

INSERT INTO table VALUES (value1, [value2, ... ])

For example -

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

UPDATE/SET/WHERE

This command is used for updating or modifying the values of columns in a table (relation).

Syntax -

UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition]

For example -

UPDATE tutorialspoint SET Author="webmaster" WHERE Author="anonymous";

DELETE/FROM/WHERE

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

Syntax -

DELETE FROM table_name [WHERE condition];

For example -

DELETE FROM tutorialspoints

WHERE Author="unknown";

MORE ABOUT SQL

---

Advantages:

• Portable:SQL is run in programs in mainframes, PCs, laptops, servers and even mobile phones. It runs in local systems, intranet and internet. Databases using SQL can be moved from device to another without any problems.
• Used with any DBMS system with any vendor: SQL is used by a all the vendors who develop DBMS.
• SQL Standard: First standard for SQL was put up in 1986 by ANSI (American National Standards Institute) and ISO (International Standards Organization). It was later expanded in 1989 and in 1992 and 1999.
• Used for relational databases: SQL is widely used for relational databases.
• Easy to learn and understand: SQL mainly consists of English statements and it is very easy to learn and understand a SQL query.
• Interactive language: SQL can be used to communicate with the databases and get answers to complex questions in seconds.
• Both as programming language and interactive language:SQL can do both the jobs of being a programming as well as an interactive language at the same time.
• Complete language for a database: SQL is used to create databases, manage security of a database. It can also be used for updating, retrieving and sharing data with users.
• Multiple data views: By use of SQL, different views of structure and content of a database can be provided for different users.
• Client/Server language: SQL is used for linking front end computers and back end databases. Thus, providing client server architecture.
• Dynamic database language: By the use of SQL database structure can be changed in a dynamic fashion even when the contents of the database are accessed by users at the same time.
• Supports object based programming: SQL supports the latest object based programming and is highly flexible.
• Supports enterprise applications: SQL is the database language which is used by businesses and enterprises throughout the globe. For an enterprise application it is a perfect language for a database.
• Integrates with Java: SQL integrates with Java by using an API known as JDBC (Java Database Connectivity).
• Used in internet: SQL is used in three tiered Internet architecture. The architecture includes a client, application server and a database.
• Used by IBM and Microsoft: SQL is used by major players like IBM and Microsoft. IBM used SQL in DB2; and Microsoft in ODBC (Open Database Connectivity), SQL server and ADO (ActiveX Data Objects).

Disadvantages:

• Difficulty in Interfacing: Interfacing an SQL database is more complex than adding a few lines of code.
• More Features Implemented in Proprietary way: Although SQL databases conform to ANSI & ISO standards, some databases go for proprietary extensions to standard SQL to ensure vendor lock-in.