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.)
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.
- Scalar value function
- Table value function - Inline Table value function , Multi statement Table value function
- 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