SQL SERVER BASICS TUTORIAL-PART 1

  DBMS and RDBMS difference:-

    Incase of DBMS(Database management system) there won't be any relationship between the tables..If u make any change in any record ,it will affect the entire database..
 
    Ex: Sybase ,Foxpro.
 
    But incase of RDBMS(Relataional Database Management System) there is a relationship between the tables..Table are related through the key field.
    If you make any change any changes in any of the record , it won't affect the entire database...
 
    In short RDBMS=DBMS+Referential Integrity
 
    ex: Microsoft Sql server, Oracle

 
    Sql Server:Microsoft SQL Server is a relational database management system developed by Microsoft.

    As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications.
 
 

    Sql(Structure query Language):

                             Sql is the  common query language to retrieve and manipulate the data in the database..Micro soft ,Oracle and MySql has its own version of sql.  Actually they are using sql in their products..
 
    Database is a collection of objects(e.g. tables, views,functions and so on).. Tables are used  to store the data in rows and columns..

    (If you look at the object browser by pressing F8 key you can able to view different database objects under the database what you have created..)
 

    Creating the Database:-

              we can create the database either by using object explorer or by typing sql query in the sql query window as shown below:-
 

Syntax to create new Database :-

  Create database DBName

   Example:-

   CREATE DATABASE TestDB

    Once created  to view the completed details about the Database that we have created then supply the below sqlcommand..

   SP_HELPDB TestDB
--------------------------------------------------------------------------------------------------------------------------

 /* BEFORE WE CREATE THE TABLE WE NEED TO CONSIDER

    1)WHAT IS THE TABLE NAME
    2)WHAT ARE THE ATTRIBUTES(COLUMNS) THE PARTICULAR TABLE GOING TO CONTAIN
    3)COLUMNS CAN BE NULL OR NOT NULL
    4)COLUMN LENGTH
    5)CONSTRAINTS
    */
 

   CREATING THE TABLE:-

     we can create the TABLE either by using object explorer or by typing sql query in the sql query window as shown below:-

     SYNTAX TO CREATE TABLE: CREATE TABLE TABLENAME(COLUMNNAME DATATYPE(SIZE))

   CREATING THE SIMPLE TABLE:-

CREATE TABLE Employee(EmpID int,FirstName varchar(50),LastName varchar(50))

TO check whether the table has been created or not with all the details..
SYNTAX:-SP_HELP TABLENAME

SP_HELP Employee

INSERT Employee(EmpID,FirstName,LastName)VALUES(1,'senthil','dhakshna')

--------------------------------------------------------------------------------------------------------------------------

creating the table with identity column:-

Once we mentioned the particular column as identity then can't able to supply the value for that column..    If you try to do so , it will throw error..

CREATE TABLE EmployeeNew(EmpID int identity(100,1), FirstName varchar(50),LastName varchar(50))

 In identity(100,1) => 100-seed value 1-step value (you can mention any VALUES for them)

 Run the below command and see what it says..

INSERT INTO EmployeeNew(EmpID,FirstName,LastName)VALUES(1,'senthil1','dhakshna1')

Error:-
    Cannot insert explicit value for identity column in table 'EmployeeNew' when IDENTITY_INSERT is set to OFF.

TO INSERT THE VALUES IN ABOVE TABLE:-

INSERT INTO EmployeeNew(FirstName,LastName)VALUES('senthil1','dhakshna1')

--------------------------------------------------------------------------------------------------------------------------

If you specify the particular column as not null in the table, then you must supply the value for that column...else it will throw error

CREATE TABLE EmployeeTableWithNotNull(EmpID int not null,FirstName varchar(50),LastName varchar(50))

Now trying to insert a record without supplying value for EmpID and you will get the below error:-

INSERT INTO EmployeeTableWithNotNull(FirstName,LastName)VALUES('senthil1','dhakshna1')

Error:-
       Cannot insert the value NULL into column 'EmpID', table TestDB.dbo.EmployeeTableWithNotNull'; column does not allow nulls. INSERT fails.

So you  must supply value as shown below:-

INSERT INTO EmployeeTableWithNotNull(EmpID,FirstName,LastName)VALUES(1,'senthil1','dhakshna1')

SELECT * FROM EmployeeTableWithNotNull

--------------------------------------------------------------------------------------------------------------------------
ALTER TABLE:-If you wan to want to add new column or to modify the table datatype  you can use alter table command...

To add a new column with employee table:-

ALTER TABLE Employee add  Age int

Now supply the below command, to check whether column has been added or not..

SELECT * FROM Employee


           UPDATE COMMAND IS USED TO UPDATE THE EXISTING RECORD IN THE TABLE:-

UPDATE Employee SET Age=22 where EmpID=1

Now supply the below command to view the records:--

SELECT * FROM Employee

TO DROP THE TABLE COMPLETELY FROM THE DATABASE:-

DROP TABLE Employee

TO CHECK WHETHER TABLE HAS BEEN DROPPED OR NOT:-

sp_help Employee

-----------------------------------------------------------------------------------------------------------------------------
/*
  CONSTRAINTS ARE USED TO MAINTAIN THE DATA INTEGRITY IN THE TABLE..
  TYPES: PRIMARY KEY,FOREIGN KEY,UNIQUE,DEFAULT,CHECK

  *BOTH THE PRIMARY KEY AND UNIQUE CONSTRAINTS ARE USED TO MAINTAIN THE UNIQUENESS(DOES NOT ALLOW DUPLICATE VALUES) IN THE COLUMN.
  *THE DIFFERENCE IS THAT WE CAN HAVE ONLY ONE PRIMARY KEY PER TABLE.BUT WE CAN HAVE AS MANY AS UNIQUE CONSTRAINT.
  *PRIMARY KEY DOESN'T ALLOW NULL VALUES BUT UNIQUE ALLOW NULL VALUES BUT ONLY ONCE..
  * WE CAN CREATE THE CONSTRAINT WHILE CREATING THE TABLE ITSELF OR AFTER CREATING TABLE ALSO BY USING ALTER TABLE COMMAND...
*/

--------------------------------------------------------------------------------------------------------

PRIMARY KEY CONSTRAINT:-

CREATING THE CONSTRAINT WHILE CREATING THE TABLE ITSELF:-

   CREATE TABLE tablename(COLUMNNAME datatype constraint pkPrimaryKey ConstraintType(columnName))

Example:-

CREATE TABLE EmployeeWITHPRIMARYKEY(EmpID int  constraint pkeyEmPID Primary Key clustered(EmpID),FirstName varchar(50),LastName varchar(50))

NOW TRY TO INSERT NULL VALUE AND DUPLICATES IN THE EMPID COLUMN IN THE ABOVE TABLE AND CHECK IT OUT...

INSERT INTO EmployeeWITHPRIMARYKEY(FirstName,LastName) VALUES('Visnu','Senthil')

Cannot insert the value NULL into column 'EmpID', table'TestDB.dbo.EmployeeWITHPRIMARYKEY'; column does not allow nulls. INSERT fails.


 WE CAN EVEN ADD THE AFTER THE TABLE HAS BEEN CREATED AS SHOWN BELOW:-

SNYTAX:
            ALTER TABLE TABLENAME ADD constraint CONSTRAINTNAME primary key(COLUMNNAME)

Example:-

ALTER TABLE EmployeeNew ADD constraint pEmpID primary key(EmpID)

--------------------------------------------------------------------------------------------------------------------------

Unique constraint:-

 CREATE TABLE EmployeeWithUniqueConstraint(EmpID int,FirstName varchar(50),LastName varchar(50),RegNo int constraint ukRegNo Unique(RegNo))

 INSERTING NULL VALUE IN THE REGNO COLUMN..IT WILL ALLOW ..BELOW IS THE VALID ONE...

 INSERT INTO EmployeeWithUniqueConstraint(EmpID,FirstName,LastName) VALUES(1,'PAT','THOMSON')

Now supply the below command to view the records:--

  SELECT * FROM EmployeeWithUniqueConstraint

  NOW AGAIN TRY INSERT THE NULL VALUE FOR REGNO COLUMN AS SHOWN BELOW..IT WILL FAIL..BCOZ UNIQUE ALLOW NULL VALUE ONLY ONCE--U WILL GET THE BELOW ERROR:-

      Violation of UNIQUE KEY constraint 'ukRegNo'. Cannot insert duplicate key in object 'dbo.EmployeeWithUniqueConstraint'.

  INSERT INTO EmployeeWithUniqueConstraint(EmpID,FirstName,LastName) VALUES(2,'RICKY','MARTYN')

     TO ADD THE UNIQUE CONSTRAINT AFTER CREATING THE TABLE:-

 CREATE TABLE EmployeeUNIQUE(EmpID int not null  ,FirstName varchar(50),LastName varchar(50),RegNo int )

 ALTER TABLE EmployeeUNIQUE add constraint ukeyRegNo UNIQUE (RegNo)

 -------------------------------------------------------------------------------------------------------------------------
 /*
Foreign Key constraint is used to maintain the relationship between two or more tables...TO enforce the referential integrity between the tables we can use foreign key constraint..

       Here we have two table Department and EmployeeWITHForeignKey .Our intention is We need to restrict the user to enter the department id which is present in the Department table..We should not allow the user to enter any other id in to the EmployeeWITHForeignKey table.We can achieve this by creating foreign key constraint as shown below.

*/

CREATE TABLE Department (DeptID int primary key clustered,DeptName varchar(50))
INSERT Department (DeptID,DeptName)VALUES(1,'MARKETTING'),(2,'SALES'),(3,'IT')

                                   
CREATE TABLE EmployeeWITHForeignKey(EmpID int,FirstName varchar(50),LastName varchar(50),DeptID int foreign key References Department(Deptid))



INSERT INTO EmployeeWITHForeignKey(EmpID,FirstName,LastName,DeptID) VALUES (1,'Pat','williams',1)

Now supply the below command to view the records:--

SELECT * FROM EmployeeWITHForeignKey

    NOW TRY TO INSERT THE ID WHICH IS NOT PRESENT IN DEPARTMENT TABLE--
INSERT INTO EmployeeWITHForeignKey(EmpID,FirstName,LastName,DeptID) VALUES (1,'Pat','williams',5)

error:-

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__EmployeeW__DeptI__1367E606". The conflict occurred in database "TestDB", table "dbo.Department", column 'DeptID'.

--------------------------------------------------------------------------------------------------------------------------
Default Constraint  is used to supply the default value to the column if value is not specified to that column...

CREATE TABLE DepartmentWithDefaultConstraint (DeptID int primary key clustered,DeptName varchar(50),Location varchar(50) default 'Chennai')

INSERT DepartmentWithDefaultConstraint (DeptID,DeptName) VALUES(1,'SALES')

Now supply the below command to view the records:--

SELECT * FROM DepartmentWithDefaultConstraint

--------------------------------------------------------------------------------------------------------------------------


 Check constraint  is used to validate the data in the column...In the location column we need to restrict the user to enter only chennai and bangalore as  the values .If it is not ,it won't get inserted..


CREATE TABLE DepartmentWithCheckConstraint (DeptID int primary key clustered,DeptName varchar(50),Location varchar(50) constraint chkCity Check(Location in('chennai','bangalore')))

INSERT DepartmentWithCheckConstraint (DeptID,DeptName,Location) VALUES(2,'IT','MUMBAI')

    The INSERT statement conflicted with the CHECK constraint "chkCity". The conflict occurred in database "TestDB", table "dbo.DepartmentWithCheckConstraint", column 'Location'.

--------------------------------------------------------------------------------------------------------------------------

Creating the table with OnDeleteCascade Option: 

CREATE TABLE DepartmentWithOnDeleteCascade (DeptID int primary key clustered,DeptName varchar(50))

CREATE TABLE EmployeeWITHOnDeleteCascade(EmpID int,FirstName varchar(50),LastName varchar(50),DeptID int foreign key References DepartmentWithOnDeleteCascade(Deptid) on delete cascade)

delete from DepartmentWithOnDeleteCascade where DeptID=1

Now supply the below command to view the records:--


select * from EmployeeWITHOnDeleteCascade

--------------------------------------------------------------------------------------------------------------------------