Structured Query Language (SQL) - Basic Concepts

Structured Query Language (SQL) 
It is a non procedural language and it is a database language.

Categories in SQL commands 

There are 3 broad categories in SQL commands. They are namely
• Data Definition language (DDL)
• Data Manipulation Language (DML)
• Transaction Control Commands

Under each category there are many SQL commands. Let us see each in brief.

Commands under Data Definition language (DDL)

This category include creating, dropping and altering table commands

CREATE TABLE is used in creation of table(s).
SYNTAX: CREATE TABLE tablename (colname col definition, colname col definition…);
ALTER TABLE: If you want to modify a table definition we can use this command. This command is used to alter column definition in table.
SYNTAX: ALTER TABLE tablename MODIFY (Column definition);
DROP TABLE: This command is used to remove an existing table permanently from database.
SYNTAX: DROP TABLE tablename;

Commands under Data Manipulation Language (DML)

INSERT: This command is used to insert rows into the table.
SYNTAX: INSERT INTO tablename VALUES (value,value,….);

SELECT: This is used to select values or data from table
SYNTAX: SELECT column name1, column name2, …. FROM tablename;

If we want to select all column values from a table then SQL command use is

SELECT * from tablename ;

DELETE: In order to delete rows from a table we use this command
SYNTAX: DELETE FROM tablename WHERE condition;

Based on the condition specified the rows gets fetched from the table and gets deleted in table. Here the WHERE clause is optional.

UPDATE: This SQL command is used to modify the values in an existing table.
SYNTAX: UPDATE tablename SET columnname = value, columnname = value,….. WHERE condition;

The rows which satisfies the WHERE condition are fetched and for these rows the column values we placed in command above in SET statement gets updated.

Commands under Transaction Control Statements

ROLLBACK – This SQL command is used to undo the current transaction

SYNTAX: ROLLBACK;

SAVEPOINT: This is used to identify a point in a transaction to which we can later rollback.

SYNTAX: SAVEPOINT savepoint_identifier;

COMMIT: This command is used to make all changes permanent in database and also marks the end of transaction.

SYNTAX: COMMIT;

Always it is better to commit changes to database at regular intervals since it will help loss of data or loss of work done when computer shuts due to unavoidable reasons.

Having known about the broad categories of SQL commands let us see some more SQL important terminologies.

Constraints Handling in SQL: 

Constraint Handling can be done in two levels namely:
• Column Level
• Table Level

Some of the Constraints used in SQL are:

The keyword NOT NULL marks a column that the particular column cannot contain null. By default column contains null unless we define the constraint NOT NULL. This is a column level constraint as it can be defined for columns only.


PRIMARY KEY
A column or a group of columns together can be defined as PRIMARY KERY. If a column or a group of columns are defined as a PRIMARY KEYS then the value of the primary key cannot appear more than once in the table. Also those columns defined as primary keys cannot have null values in it.

A table requires a key which uniquely identifies each row in the table. This is entity integrity. The key could have one column, or it could use all the columns. It should not use more columns than necessary. A key with more than one column is called a composite key.

A table may have several possible keys, the candidate keys, from which one is chosen as the primary key. If the rows of the data are not unique, it is necessary to generate an artificial primary key.


REFERENTIAL INTEGRITY
The table on which the column or a combination of columns is defined by this constraint is called the foreign key is called child table. When this constraint is defined a relation is defined between this table which has the foreign key and a table that has the primary key in relationship with this. This is called as referenced key. The table which has the primary key or in other words the referenced key is called as parent table.

SQL Functions to Handle Arithmetic Operations

All these SQL arithmetic functions described below operate on numerical values only and are used to get data as per users required format.

Some of the functions are:

• To find the absolute value of a number we can use the SQL function ABS (number);
• To round a number to a specified number of decimal places SQL command used is
   ROUND (number, number of decimal places to be rounded);
• To convert a char to a number SQL function used is TO_NUMBER (char value);

VIEW

This is an important concept in SQL. A View is nothing but a window of an existing table. In other words it is a logical representation that is created from one or existing base table. After creation of view it can be used just as a base table. That is one can query or select values of data’s from views and it also possible to update a view.
Views are created by suing CREATE VIEW command in SQL
SYNTAX: CREATE VIEW viewname AS SELECT statement

One of the important points to consider while a user uses views is that we have seen that vies are created from a base table and if suppose the base table is deleted after which if a user tries to use or access the view error will occur.

SYNONYMS:

It is possible to refer a table with a different name and this done by using CREATE SYNONYM. It is possible to create synonym for tables as well as views.

SYNTAX: CREATE synonym name FOR username.tablename;

GROUP Functions

We have seen before arithmetic functions which operated on numerical value and returned a single value for each single row taken ass input. But there may be occasions where one might require several rows to be grouped and the result of the grouped rows might be needed. To handle such situations GROUP functions in SQL helps. There are number of group functions available in SQL. Let us see some of them:

Average:

To calculate the average of a group of values SQL defines the function AVG (column name)

Counting number of values:

If we want to count the number of values in a particular column then we can use the SQL function COUNT (column name)

Maximum:

To find the maximum among the column values SQL function MAX (column name) can be used

Minimum:

To find the minimum among the column values SQL function MIN (column name) can be used
There are many more SQL commands and terminologies in SQL but the above gives only an overview of the basics of SQL.

Refence:
http://www.exforsys.com/tutorials/programming-concepts/sql-basic-concepts.html
http://db.grussell.org/sql1.html