Tuesday, 12 November 2013

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 : 


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 :

  

18. What is a Trigger ?





No comments:

Post a Comment