SQL SERVER - Notes Part 2

12. What are the keys present in the SQL SERVER ?
        Keys present in Sql Server Click here to read.

13 What are the Constraint's present in the SQL SERVER ?
        Default constraint, Check Constraint, Unique Constraint , Null Constraint.
 
        Default constraint : The name of constraint itself explain the usage , it specifies a default value for a column if the value is not inserted.

        Check Constraint : This constraint  check the user inserted value against the check condition present in the table.

        Unique Constraint : Is use to identify the row in unique like primary key.
 
         Null Constriant : It allows the null values to the value of a column in table.

 EX:
  PRIMARY KEY    -- unique constraint   (for unique identify the row.)

  CHECK(age > 20 AND age < 50) -- check constraint (while user insert a value in                                   a row this check constraint check the value                                       before insert)

  DEFAULT(25000)  -- Default constraint (if user doesnt enter the value for this                      column this default value will be taken and inserted)

  NULL     -- Null constraint allows null value if user doesnt enter the                           value

CREATE TABLE cons_emp

(

 id    INT PRIMARY KEY,   

 age   INT CHECK(age > 20 AND age < 50), 

 ADDRES VARCHAR(100) ,

 salary INT DEFAULT(25000),             

 passportno INT NULL                

)

INSERT INTO cons_emp (id,age) VALUES (1, 25)

Query to find out the constraint present in a DB

SELECT      OBJECT_NAME(OBJECT_ID) AS NAMEOFCONSTRAINT,

            SCHEMA_NAME(SCHEMA_ID) AS SCHEMANAME,

            OBJECT_NAME(PARENT_OBJECT_ID) AS TABLENAME,

            TYPE_DESC AS CONSTRAINTTYPE

FROM  SYS.OBJECTS

WHERE TYPE_DESC LIKE '%CONSTRAINT'

GO

The Database is work around with main four operation CRUD, To do that following keywords are important.

CREATE

DELETE

UPDATE 

SELECT

These keywords are used to operate in tables for data.

Table and Database are the two words where are the keywords are working on.

14. What are objects is used to work on Tables and database to process the data ?

       Function

       Stored Procedure

       Trigger

15 What are Mostly commonly keywords used in Database to do operations ?

       Create Database

       Alter  Database

       Drop Database

16 What are types of stored procedures present in sqlserver and how to create it ?

      Pre defined and user defined , Pre defined means already present in SQL SERVER. User defined means user creating the stored procedure.

Pre Defined Stored Procedures :

  

sp_rename             -- use to rename a object

sp_stored_procedures  -- use to find the stored procedure objects

sp_tables             -- use to find the tables

sp_depends            -- use to find the dependency of a object

sp_helptext           -- use to get the text of a compiled object

sp_addlinkedserver    -- use to add the linked server

sp_addlinkedsrvlogin  -- use to add the login for linked server

sp_who                -- use to find the object running in database and there id

   

User  Defined : 

Click here to read the User defined stored procedure

To exec the SP : Sometimes we dont need to specify the all input parameter just alone execute the stored procedure because it takes default value.

    EXEC sys.sp_tables @table_name = N'', -- nvarchar(384)

        @table_owner = N'', -- nvarchar(384)

        @table_qualifier = NULL, -- sysname

        @table_type = '', -- varchar(100)

        @fUsePattern = NULL -- bit

   

    EXEC sys.sp_tables

    EXEC sys.sp_addlinkedserver @server = 'lnksrv', -- sysname

        @srvproduct = N'', -- nvarchar(128)

        @provider = N'SQLNCLI', -- nvarchar(128)

        @datasrc = N'sqlexpress', -- nvarchar(4000)

        @location = N'', -- nvarchar(4000)

        @provstr = N'', -- nvarchar(4000)

        @catalog = NULL -- sysname

   

    EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = 'lnksrv', -- sysname

        @useself = '', -- varchar(8)

        @locallogin = NULL, -- sysname

        @rmtuser = 'raj', -- sysname

        @rmtpassword = 'pwd' -- sysname

   

17.  What are types of Function present in sqlserver ?

          There are two category first  System- defined and user defined. In that we have three sub categories based on the return type. 

1. Scalar value function
2. Table value function - Inline Table value function , Multi statement Table value function
3. Aggregate function

         

System defined function:

let we see some of the predefined functions 

   -- check value constraint

   SELECT COALESCE(NULL,NULL,2)  -- return the first non null value from the expression if any empty value present at first returns 0

   SELECT ISNULL('raj','rajesh')

   SELECT NULLIF(0,2)

  

   -- Convert function

   SELECT CONVERT(VARCHAR(10),GETDATE(),126)

   SELECT CAST('1' AS INT)

  

   -- string functions

   SELECT REPLACE('ramesh','m','j')

   SELECT SUBSTRING('rajesh',1,3)

   SELECT LEN('rajesh')

   SELECT UPPER('raj')

   SELECT LOWER('jak')  

  

   --trim function

   SELECT LTRIM(' rajesh')

   SELECT RTRIM('rajesh ')

 

  -- Error function

  SELECT ERROR_LINE() 

  SELECT ERROR_MESSAGE()

  SELECT ERROR_NUMBER()

  SELECT ERROR_PROCEDURE()

  SELECT ERROR_SEVERITY()

  SELECT ERROR_STATE()

 

   -- Date time function

   SELECT GETDATE()

   SELECT DATEADD(dd,2,GETDATE())

   SELECT DATEDIFF(dd,GETDATE(),GETDATE()+5)

   SELECT DATENAME(mm,GETDATE())

   SELECT DATEPART(mm,GETDATE())

   SELECT DAY(GETDATE())

  

   -- Aggregate functions

   SELECT COUNT(1) FROM employee

   SELECT MAX(salary)FROM employee

   SELECT MIN(salary)FROM employee

   SELECT AVG(salary)FROM employee

   

User defined Function :

Click here to read the implementation of function

  

18. What is a Trigger ?

Click here to read about trigger.