T-SQL/MSBI Knowledge Share Videos

Database Overview

Why do we need Database ?

If we go back some 70 or 80 years ago or still older days every business data was processed in book or paper, which was a hectic job to maintain the data. It was very difficult to find the old data recorded in the book or paper. To get details of particular record need to search each and every single page and book , which used to take lot of time. Later as the technology improved people began to store data in file system, where the data was not in an organized format. In 1960's on wards IBM came up with a concept called Database.

In simple terms Database is an organized collection of data where the data can be easily accessed, managed and updated.More over data can be secured. The data was so organized that all the related data was kept in respective tables and a relationship was established between the tables and so called Relational Database Management System (RDBMS).

Here the data is stored in the form of table which consists of columns and rows. Columns are known as Attributes and rows are known as Records.

Note :  In any application there will be two things, one the front end and the other back end. Front end is java, .Net, php etc where as back end is the database i.e. Oracle,Sql Server,mysql etc.

Example table is shown below :

What is SQL?

SQL is structured Query Language which is a computer language for storing, manipulating and retrieving data stored in relational database.
SQL is a standard language for relational database system. All relational database management systems like MySQL, MS Access, Oracle, Sybase, SQL Server uses SQL as standard database language.
Also they are using different dialects, Such as :
  • MS SQL Server using T- SQL,
  • Oracle using PL/SQL etc
Many companies used SQL as the DB language and developed there own Databases like :
Microsoft's MSSQL, MS Access
Oracle's Oracle DB
and so on....

SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be classified into groups based on their nature:

DDL - Data Definition Language: As the name implies, Definition means the structure of the object. Create an object structure, alter the object structure and drop the object structure.

CREATECreates a new table, a view of a table, or other object in database
ALTERModifies an existing database object, such as a table.
DROPDeletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language: As the name implies, Manipulation means making changes in the table. i.e., insert a new record in the table ,update the existing record, delete the table record.

INSERTCreates a record
UPDATEModifies records
DELETEDeletes records

DCL - Data Control Language:As the name implies, it will control the access to object. Whether user is allowed to access the object or not.

GRANTGives a privilege to user
REVOKETakes back privileges granted from user

DQL - Data Query Language: As the name implies, it is used to retrieve the data from the table.

SELECTRetrieves certain records from one or more tables

TCL - Transaction Control Language: As the name implies, it controls the transactions on the table data. Suppose  if you have updated to any new value, and if you want the old value back then you can use this TCL commands.

Rollback any DML operation performed.
Commit any DML operation.
Begins any transaction.
Ends any transaction.