SQL Basic

What is SQL?

SQL is used to manage database. You may create database, tables, write query to insert, retrieve and manipulate data. SQL is common language which is used by different database systems like My Sql, MS Sql, MS Access, Oracle, Postgres, IBM DB2, Sybase, Ingres, SqlLite etc... Based on different database system, each of them might have different extensions or their own property with SQL standard.

According to American National Standards Institute (ANSI in 1986), it is the standard language for relational database management systems. SQL has different statements which are used to do different tasks like insert data into database, modify data into database and fetch data from database. In 1987, SQL become ISO (International Standard Organization).

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 are following.

  • Insert Statement
  • Update Statement
  • Delete Statement
  • Select Statement
  • Drop Statement
  • Create Statement

Above statement or commands can be used to do everything with database. In upcoming tutorials we will cover all basic of each above command to do practice with SQL in database.

SQL History

Edgar Frank "Ted" Codd: “Father of Relational Database

Edgar Frank "Ted" Codd (Aug 19, 1923 - April 18, 2003) was an English computer scientist. He was working for IBM and he invented the relational model for database management. Mr. Edgar also gives his valuable time to contribute many important things in computer science as well. But, based on his value contribution, data management was the one major achievement. Later he was known as “Father of Relational Database

In 1974, SQL introduced as Structured Query Language on very basic level. Later a product “System/R” was introduced by IBM in 1978. This product was fully developed based on Ted’s ideas about relational database management.

Finally, SQL was introduced by IBM in 1986 first time and its standardize by ANSI(American National Standards Institute). Later it got identified by specific name known as “Oracle

Advantages of SQL

There are many advantages we get by using SQL into different database systems like My Sql, MS Sql, MS Access etc... Let’s understand few important benefits from following.

  • Create, drop database and tables easily
  • Store data into different tables within database
  • Easily accessible data from database via SQL queries
  • Manipulate data into database by different commands like insert, delete, update etc...
  • Create custom functions, stored procedures, views, triggers etc...
  • Setup permission to tables, views and stored procedures easily
  • Embed with other modules, libraries etc...
  • Describe the data as we want

Where you can use SQL

There are numerous ways where you can use SQL. Few of them you can find from following list.

  • To create database, you can use SQL
  • To create tables, view, stored procedures, triggers, you can use SQL
  • To execute query, you can use SQL
  • To fetch records from table in database, you can use SQL
  • To insert records in table, you can use SQL
  • To delete records from table, you can use SQL
  • To modify records in table, you can use SQL
  • To set custom configurations with tables or fields like permission, primary key, foreign key, constraints, you can use SQL

Commands in SQL

Mainly there are three type of commands used in SQL. They are following.

  • Data Definition Language (Known as DDL)
    (It includes Create, Alter and Drop commands)
  • Data Manipulation Language (Known as DML)
    (It includes Insert, Update, Delete and Select commands)
  • Data Control Language (Known as DCL)
    (It includes Grant and Revoke commands)

Let’s understand all above three types of commands in details with their syntax.

Data Definition Language (Known as DDL)

Commands Details & Syntax
CREATE Create command can be used to create tables, stored procedures, views, user defined functions and triggers.
Syntax:
Create Table [TableName](FieldsName1 DataType(Size), FieldsName2 DataType(Size),FieldsName3 DataType(Size))
ALTER You can use this command to alter table object. For example, if you want to add additional new column into table, you can use this command. There are some restrictions on this command which we will look into upcoming chapter.
Syntax:
Alter Table [Tablename] ADD FieldsName1 DataType(Size)
DROP You can delete any object(Table, Function, Triggers, Procedures etc...) permanent from database.
Syntax:
Drop Table [TableName]

Data Manipulation Language (Known as DML)

Commands Details & Syntax
INSERT When you want to add your records into table, you may use this command.
Syntax:
Insert into [TableName](FieldName1,FieldName2,FieldName3) Values (Value1, Value2, Value3)
UPDATE You can edit records into table by using this command.
Syntax:
Update [TableName] Set FieldName1=Value1, FieldName2=Value2 WHERE [FieldName]=Value
DELETE You can delete records from table by using this command.
Syntax:
Delete from [TableName] Where [FieldName]=Value
SELECT You can retrieve records from one or more table by using this command. You can also write complex queries by using joins to filter data and display that as per your requirements. We will learn more about complex queries into upcoming chapters.
Syntax:
Select Field1, Field2, Field3 from [TableName]

Data Control Language (Known as DCL)

Commands Details & Syntax
GRANT You can use this command to set permission on a database objects.
Syntax:
GRANT privilegename ON objectname TO {username | PUBLIC | Rolename} [with GRANT option];
REVOKE Rollback permission from object.
Syntax:
REVOKE privilegename ON objectname FROM {Username | PUBLIC | Rolename}

Now, you have basic idea about what is SQL and how you can use it. We will learn more about SQL in upcoming chapters.