Explore topic-wise InterviewSolutions in .

SQL Tutorial
This SQL Tutorial PROVIDES all basic and advanced concepts of SQL. This tutorial helps for beginners as well as for professionals.
SQL is a query language we cannot say it is a database. STRUCTURED Query Language (SQL) is basically used to do operations on database and operations are like deleting, creating and MODIFYING tables, VIEWS, udf ETC.

Introduction to SQL
As we know from last chapter that SQL is a standard language for accessing and manipulating databases. Here we will cover what is SQL and What we can do with SQL and some information about RDBMS.

No first know What is SQL?
SQL basically stands for mainly is Structured Query Language
SQL helps in accessing and manipulate databases
SQL become standard of ANSI in 1986 and ISO in 1987


Now we go to what we can do with SQL
We can retrieve data from a database tables
We can insert records in database tables
We can update records in database tables
We can delete records from a database tables
We can create new tables, database, views and stored procedures
We can create views in databases
We can set permissions on tables and another part of databases
We can do different filters and queries on the databases

Now we will Cover what is RDBMS

As we all know RDBMS stands for Relational Database Management System.
RDBMS is basis for all modern database system like MS SQL Server,IBM DB2, Oracle, MySQL and Access.

RDBMS stored data in database objects which is called tables. And this tables is a collection of related data entries which consists of columns and rows.

To under stand tables we take a example of below student tables

Id-------Name---------Age--------Sex
1-------Kamal--------15----------M
2-------Rita-----------14----------F
3-------Adam---------14----------M
4-------Stef-----------15----------F


Now comes to tables part
table is broken up into smaller entities which is called fields.The fields in the student table consist of Id, Name, Age and Sex. A FIELD is a COLUMN in a table which is designed to maintain a specific information on each RECORD in the table.

We can also called record as a row which is every individual entry that exists in a table. A record is a HORIZONTAL entity in a table.

On the other column is a vertical entity in a table what have all the information associated with specific field in table.

RDBMS VS DBMS
When we target on differnce between DBMS and RDBMS we will came to know what is DBMS and RDBMS. Although both are used for stroing information in database but both uses different techniques and methods.

Below are the 8 main DIFFERENCE between DBMS and RDBMS
(1)RDBMS:- The main and most important feature in RDBMS is normalization that have separate RDBMS from DBMS.
(1)DBMS:-And Normalization is not present in DBMS.

(2)RDBMS:-It will store data in tabular form.
(2)DBMS:- On the other hand this will store data as a file.

(3)RDBMS:-These are the four main example of RDBMS mysql, postgre, SQL Server, Oracle etc.
(3)DBMS:-The file systems that is used now a days is XML this is the example of DBMS.

(4)RDBMS:-One more main feature of RDBMS is support of distributed database.
(4)DBMS:- DBMS not support distributed database.

(5)RDBMS:-It is basically designed to handle large ammount of data which also supports multiple users.
(5)DBMS:-DBMS is basically used for small organization whihc deal with small data. Which usually support single user.

(6)RDBMS:- Here data values are stored in the ORM of tables and tables identifiers called primary key.
(6)DBMS:- Here data is stored in either a hierarchical form or a navigational form.

(7)RDBMS:-RDBMS defines the integrity constraint for the purpose of Atomocity, Consistency, Isolation and Durability property.
(7)DBMS:-DBMS will not apply any security or data manipulation.

(8)RDBMS:-As we know when we store data in table there is some relation between these tables are happen and this relationship is also stored in the form of a table as well.
(8)DBMS:-DBMS uses file SYSTEM to no question arise of any relation in between the tables.


After reading above difference you will now get the information about the DBMS and RDBMS. And you will also know that RDBMS is the extension of DBMS. There are lots of THIRD party tools available in market who supports both DBMS and RDBMS.

SQL Syntax
There are some unique set of rules and guidelines which are called syntax in SQL. Below are the basic SQL syntax.
-SQL is not case sensitive and SQL KEYWORDS is generally written in uppercase.
-We can perform most of the action in DATABASE with the HELP of SQL statements.
-SQL statement are basically dependent on text lines and we can place a single SQL statement on one or multiple text lines.
-SQL basically depends on relational algebra and tuple relational calculus.


Now something about SQL statement
SQL statements are started with SQL commands or keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP etc. and all the SQL statement ends with semicolon.

SELECT "column_name" FROM "table_name";

Now question raise why we use semicolon after SQL statements:

We used semicolon to SEPARATE SQL statements. It is called standard way to separate SQL statements. And by adding semicolon we can ADD more then one query in sql statements.

Now come to SQL Commands

Below are the some important SQL command:

SELECT: it used to get data from a database.

UPDATE: it used to updates data in database.

DELETE: it used to deletes data from database.

CREATE TABLE: it is used creates a new table.

ALTER TABLE: it is basically used to modify the table.

DROP TABLE: it is used to delete a table.

CREATE DATABASE: it is used to creates a new database.

ALTER DATABASE: It is mainly used to modify a database.

INSERT INTO: It is used to insert new data into a database.

CREATE INDEX: It is mainly used to create an index (search key).

DROP INDEX: It is used to delete an index.

SQL Data Type
In SQL data type defines what kind of value a column can contain in each and every tables or in another defination we can say data types are bascially used to represent the nature of data that we stored or saved in database table. Let SUPPOSE we need store some string values in any column then we declare that as string.

Below are the 3 different classification of data types.
(1)String Data Types
(2)Numeric Data Types
(3)Date and time Data types

List of different Data Types in SQL Server

SQL Server String Data Type
(1)CHAR(Size):-It have fixed width character string data type and this fixed width is defined in place of Size. And size is upto 8000 Characters.
(2)varchar(Size):-It is also a character string data types with fixed length which is defined in Size. And maximum size is 8000 characters.
(3)varchar(max):-It is also same as above and is character string data types but the maximum size is approx 107374124 characters.
(4)text:-Same as above is character string data types and about size is up to 2GB of text data.
(5)nchar(Size):-It is used to store fixed width unicode string data type but size is upto 4000 charcters.
(6)nvarchar:-Its a variable width Unicode string data type but have maximum 4000 characters.
(7)ntext:-Its a variable width Unicode string data type and size is up to 2GB of text data.
(8)binary(n):-Its a fixed width binary string data types and size is upto 8000 bytes.
(9)varbinary:-Its a variable width binary string data type and size is upto 8000 bytes.
(10)IMAGE:-Its a variable width binary string data type and size is upto 2GB.

SQL Server Numeric Data Type
(11)BIT:-Its a interger which can 0,1 or null.
(12)tinyint:-Its have whole numbers which is upto 0 to 255.
(13)Smallint:-Its used to store whole number and range is from -32768 to 32767.
(14)Int:-Its also used for whole number and range is in between -2,147,483,648 and 2,147,483,647.
(15)Bigint:-Its also used fro whole number and rane is in between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
(16)real:-Its a floating precision number data from -3.40E+38 to 3.40E+38.
(17)money:-Its used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.

SQL Server Date and Time Data Type
(18)datetime:-Its used to specify date and time combination and range is from January 1, 1753 to December 31, 9999 and cotains accuracy of 3.33 milliseconds.
(19)datetime2:-Same as above but range starts from january 1, 0001 to December 31, 9999 and accuracy is upto 100 nanoseconds.
(20)date:-Its used to store only data and range is from January 1, 0001 to December 31, 9999.
(21)time:-Its used to store only time with accuracy of 100 nanoseconds.
(22)timestamp:-Its store a unique number when a new row created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable.

SQL Server Other Data Type
(23)Sql_variant:-Its used for data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.
(24)XML:-It stores XML data and maximum limit is 2GB.
(25)cursor:-Its store a reference to a cursor used for database operations.
(26)table:-Its store result set for later processing.
(27)uniqueidentifier:-It stores GUID (Globally unique identifier).