Thursday, March 28, 2024
Home » Database » What is Microsoft SQL – Introduction to Microsoft SQL

What is Microsoft SQL – Introduction to Microsoft SQL

  author
Written By Aswin Vijayan
Mack John
Approved By Mack John  
Published On September 29th, 2016
Reading Time 5 Minutes Reading

Microsoft SQL (Structured Query Language) is a computer language that is used for managing a relational database and performing different actions on the data. Initially, SQL was created in 1970 by IBM and standardized by ANSI.

SQL uses set of instructions or commands to interact with the relational database. The user can create database and then manipulate & retrieve data from it.
it consists of commands such as CREATE, ALTER, SELECT, UPDATE, DELETE, INSERT, WHERE, etc. for creating and manipulating the database.

Why Microsoft SQL Is Needed

  • Allows users to create database and drop database.
  • It offers a way to store data in a database and various commands to access it’s data.
  • Enables users to access data present in relational tables.
  • Allows users to describe the structure of the data.
  • Enables users to use database within other languages using SQL modules, libraries, and pre compiler.
  • Enables users to control access to other users on tables, procedures and views by settings the permissions.

Basic Components Of MS SQL

MS SQL has three main components: Data Definition Language, Data Manipulation Language, Data Control Language.

  • Data Definition Language
    DDL defines the structure of the database, it is used for creating and modifying the actual structure of the database. It includes commands like CREATE, ALTER, and DROP.

    CREATE It is used for creating a new table, a view of a table, or another object in database.
    ALTER Alter command is used for modifying an existing database table.
    DROP With the help of DROP command user can delete an entire table and a view of a table.

     

  • Data Manipulation Language
    DML is used for manipulating data of any database. The user can retrieve data from the SQL database, can insert new fields in a database, modify the existing records and can delete records from the existing table.

    SELECT This command is used for accessing data or records from one or more tables.
    INSERT User can insert new records with this command.
    UPDATE It is used for Modifying the existing records.
    DELETE User can delete records from database using this command.

     

  • Data Control Language
    DCL is used for managing access to the data. With this admin can grant permission to the user for accessing the database and also, used for taking away existing permission of the user. This consist two commands:

    GRANT It gives an access to user.
    REVOKE It takes back access granted from user.

Concept Of RDBMS

RDBMS stands for Relational Database Management System which is a database management system based on the relational model introduced by E.F. Codd.
Tables are the most important object of RDBMS, which is a collection of columns and rows contains data entries.
Every table in RDMS has a number of fields, for instance: table Employee consists Fields like ID, NAME, AGE , SALARY, ADDRESS.
If these fields are blank, that means field have NULL value which is very different from zero value

Different Keys OF RDBMS

Primary Keys– It is defined as a field in a table that uniquely identifies rows in the table.

Super Key– the Super key is a combination of columns that uniquely discover any row in RDBMS.

Candidate Key– it is any column or a set of a column that uniquely discover any record of the database without referring to any other data.

Alternate Key– If the table has a number of candidate keys then after selecting one of the theses key as a primary key, the rest of candidate keys are known as an alternate key.

Foreign Key– It is also called referential key which used to define the relationship between two tables.

Composite Key– it combines two or more columns in a table that uniquely identify each row in the table.

Creating And Manipulating A Database In SQL

User can use CREATE DATABASE statement for creating any new database, user can use following syntax for creating a database.

CREATE DATABASE DatabaseName12

and, can enter the values in the table with the help of INSERT Command

INSERT INTO Table_Name
 VALUES (data1, data2, data3, data4……..)

To select data from database, user can use SELECT command

SELECT column_name1, column_name2
 FROM table_name1;

For updating a table user can use UPDATE statement

UPDATE table_name1
 SET column1 =v1, Column2=v2……..
 where column3= v3

For deleting some entries from the database or table user can use DELETE commands

DELETE FROM database_name1
 Where column1= v1;

For deleting the entire database, indexes, tables user can use DROP statement

DROP TABLE table_name
DROP DATABASE database_name1

however, if the user want to delete data present inside the table and the table itself then, they can use TRUNCATE TABLE statement

TRUNCATE TABLE table_name

User can alter or modify the columns of the table with ALTER statement

ALTER TABLE table_name1
 ADD column_name datatype

User can also DELETE a column in a table, using following syntax

ALTER TABLE table_name1
 DROP COLUMN column_name1