Structured Query Language

                  Structured Query Language 

SQL (Structured Query Language ) is a query language that is basically used to make a connection with the database and work with the database. According to ANSI(American National Standards Insititute), it is the standard language for relational database management systems.

SQL statements are used to perform tasks such as update data on a database or retrieve data from a database. Some common relational database management systems that use SQL are Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

SQL is not case sensitive.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".

city, state, high, and low are the columns. The rows contain the data for this table:

Weather
city	state	high	low
Banaras	Uttar Pradesh	100	89
Dehradun	Uttrakhand	104	96
Mumbai	Maharashtra	89	70
Lajpat Nagar	Delhi	78	60
Leh	Jammu & Kashmir	82	56

Selecting Data

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:

select "column1"
  [,"column2",etc] 
  from "tablename"
  [where "condition"];
  [] = optional

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.

The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where. It basically helps to put certain conditions while retrieving data.

Conditional selections used in the where clause:

=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal to
LIKE

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

select city, state,high,low
   from weather
   where state LIKE 'De%';

This SQL statement will match any city name that starts with "De''. Strings must be in single quotes.

The above query will give result as :

city	state	high	low
Lajpat Nagar	Delhi	78	60

In a similar manner, you can use a thousand or lacs of conditions while selecting data from the database.

We will be working with the above-given table in this complete tutorial.

You can download and install MySQL from the oracle website.

In order to install MySQL, visit the official documentation.

Creating Tables

The create table statement is used to create a new table. Here is the format of a simple create table statement:

create table "tablename"
("column1" "data type",
 "column2" "data type",
 "column3" "data type");

Format of create table if you were to use optional constraints:

create table "tablename"
("column1" "data type" 
         [constraint],
 "column2" "data type" 
         [constraint],
 "column3" "data type" 
        [constraint]);
 [ ] = optional

Note: You may have as many columns as you'd like, and the constraints are optional.

Example:

create table weather
(city varchar(15),
 state varchar(20),
 high number(3),
 low number(3);

To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table and a closing parenthesis after the end of the last column definition. Make sure you separate each column definition with a comma. All SQL statements should end with a ";".

The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).

Data types specify what the type of data can be for that particular column. If a column called "city", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.

Here are the most common Data types:

char(size)	Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size)	Variable-length character string. Max size is specified in parenthesis.
number(size)	Number value with a max number of column digits specified in parenthesis.
date	Date value
number(size,d)	Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

What are the constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table.

Inserting record into a Table

The insert statement is used to insert or add a row of data into the table.

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.

insert into "tablename"
 (first_column,...last_column)
  values (first_value,...last_value);

In the example below, the column name city will match up with the value 'Gandhi Nagar', and the column name state will match up with the value 'Gujrat'.

Example:

insert into weather
  (city, state, high,low)
  values ('Gandhi Nagar', 'Gujrat', 89,62);

Note: All strings should be enclosed between single quotes: 'string'

Updating Records

The update statement is used to update or change records that match the specified criteria. This is accomplished by carefully constructing a where clause.

update "tablename"
set "columnname" = 
    "newvalue"
 [,"nextcolumn" = 
   "newvalue2"...]
where "columnname" 
  OPERATOR "value" 
 [and|or "column" 
  OPERATOR "value"];

 [] = optional

Examples (you can update multiple records by using various conditions) :

update weather
  set high = 98
  where city ='Lajpat Nagar'; 

Deleting Records
The delete statement is used to delete records or rows from the table.
delete from "tablename"

where "columnname" 
  OPERATOR "value" 
[and|or "column" 
  OPERATOR "value"];

[ ] = optional
Examples:
delete from weather;
Note: if you leave off the where clause, all records will be deleted! (So avoid this)
delete from weather
  where city = 'Lajpat Nagar';

delete from weather
  where city = 'Gandhi Nagar' or state = 'Delhi';
To delete an entire record/row from a table, enter "delete from" followed by the table name, followed by the where clause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.

Drop a Table (Delete a table)
The drop table command is used to delete a table and all rows in the table.
To delete an entire table including all of its rows, issue the drop table command followed by the tablename. drop table   is different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. Dropping the table removes the table definition as well as all of its rows.
drop table "tablename"
Example:
drop table weather;

Create a Database
The create database command is used to create a database which further contains different tables.
To create an entire databae which includes different tables of different data we can issue a create database command. therefore, can use that database for creating table inside it.
create database "databasename"
Example:
create database nature;

After this you have to use a command "use nature" in order to use a particular database nature.
where nature is the name of database.


Show all Databases
The  show databases command is used to show all databases which exists in the server.
show databases


If you have any query regarding structured query language ask your doubts in comments or mail me at feel.the.coder@gmail.com