What is Constraints in TSQL?

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.Constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Types of Constraints.

·         NOT NULL, This specify that column does not access NULL values.

·         CHECK constraints enforce domain integrity by limiting the values that can be put in a column. A check constraint can be Boolean values (True, false or unknown). We can add search condition which will be applied while we do insert or update operation on a column. This also enforce that inserted or updated values must be in range which is specified within check.

CREATE TABLE Employee

   (

   Employee_id     bigint PRIMARY KEY,

   Employee_name   varchar(max),

   Emp_address     varchar(max),

   Emp_basic_pay   money,

   CONSTRAINT chk_id CHECK (Employee_id BETWEEN 100 and 1000000 )

   )

·         UNIQUE constraints enforce the uniqueness of the values in a set of columns. This enforce no two rows in a table can have same value for the columns. Primary keys also enforce uniqueness, but primary keys do not allow NULL as one of the unique values.

·     PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key.

CREATE TABLE Employee

   (

   Employee_id     bigint PRIMARY KEY,

   Employee_name   varchar(max),

   Emp_address     varchar(max),

‘   Emp_basic_pay   money,

   CONSTRAINT chk_id CHECK (Employee_id BETWEEN 100 and 1000000 )

   )

•          FOREIGN KEY constraints identify and enforce the relationships between tables.

A foreign key in one table points to a candidate key in another table. (A column or combination    of columns that qualify as a primary key value is referred to as a candidate key.)

CREATE TABLE order_part

  (

order_nmbr      int,

part_nmbr      int

             FOREIGN KEY REFERENCES part_sample(part_nmbr)

            ON DELETE NO ACTION,

          qty_ordered      int

);

GO

You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value. The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point.

 The ON DELETE clause has the following options:

a.  NO ACTION specifies that the deletion fails with an error.

b.  CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also  deleted.

c.  SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL.

d.  SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value

The ON UPDATE clause defines the actions that are taken if you try to update a candidate key value to which existing foreign keys point. This clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options.

What is – DML, DDL, DCL and TCL in TSQL?

In transact SQL , different type of activity performed ,like we can create database , manipulate date , update, delete even control access to database or database objects.

In some case we also need to secure data or control data so we can restrict unauthorized access. All these activity categorized in four parts.  

DML (Data Manipulation Language)

DDL  (Data definition language)

DCL (Data control language)

TCL  (Transactional Control Language)

We have explained brief all about these, also briefed where and how we can used all these in TSQL.

1. DML (Data Manipulation Language)

It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, DELETE and INSERT statements

The following table lists the clauses that are used in multiple DML statements or clauses.

Clause	Can be used in these statements
FROM (Transact-SQL)	DELETE, SELECT, UPDATE
Hints (Transact-SQL)	DELETE, INSERT, SELECT, UPDATE
OPTION Clause (Transact-SQL)	DELETE, SELECT, UPDATE
OUTPUT Clause (Transact-SQL)	DELETE, INSERT, MERGE, UPDATE
Search Condition (Transact-SQL)	DELETE, MERGE, SELECT, UPDATE
Table Value Constructor (Transact-SQL)	FROM, INSERT, MERGE
TOP (Transact-SQL)	DELETE, INSERT, MERGE, SELECT, UPDATE
WHERE (Transact-SQL)	DELETE, SELECT, UPDATE
WITH common_table_expression (Transact-SQL)	DELETE, INSERT, MERGE, SELECT, UPDATE

2.    DDL (Data Definition Language)

It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

·         ALTER STATEMENTS (TRANSACT-SQL)

·         CREATE STATEMENTS (TRANSACT-SQL)

·         DISABLE TRIGGER (TRANSACT-SQL)

·         DROP STATEMENTS (TRANSACT-SQL)

·         ENABLE TRIGGER (TRANSACT-SQL)

·         TRUNCATE TABLE (TRANSACT-SQL)

·         UPDATE STATISTICS (TRANSACT-SQL)

3.    DCL (Data Control Language)

It is used to create roles, permissions, and referential integrity as well it is used to control access to database objects.(Procedures, Functions, tables, views etc)

 Examples: GRANT, REVOKE statements

GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects

4.    TCL (Transactional Control Language)

It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.

BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error

Combining statement with conditional operators

Operators “AND”, “OR”

Sometime we need to combine two or more TSQL statement based on some conditions, that we can achieve using Conditional operator, Boolean   “AND operator” and “OR operator”

Boolean Operator: AND (&), OR (||)

Conditional Operator:-  

1.       GREATER THEN EQUAL    (>=) 

                X>Y;

2.       LESS THEN EQUAL ( <=)

Y<X;

3.       EQUAL ( ==)

                X=Z;

4.       GREATER THEN ( >)

                X>Y;

5.       LESS THEN (<)

Y<Z;

6.       NOT EQUAL TO ( <>)

Y<>Z;

The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed.

For example:

SELECT COLUMN1, COUNT (COLUMN2) FROM TABLENAME WHERE "CONDITION1" AND 

"CONDITION2";

The OR operator can be used to join two or more conditions in the WHERE clause. However, either side of the OR operator can be true and the condition will be encountered - hence, the rows will be displayed. With the OR operator, either side can be true or both sides can be true.

For example:

SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY FROM EMPLOYEE WHERE BASIC_PAY >= 15000.00 AND TITLE = 'AVP';

This statement will select the EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY from the employee table where the BASIC pay is greater than or equal to 15000 and the title is equal to AVP. Both of these conditions must be true in order for the rows to be returned in the query. If either is false, then it will not be displayed.

Although they are not required, you can use parenthesis around your conditional expressions to make it easier to read:

For example:

SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY FROM EMPLOYEE WHERE (BASIC_PAY >= 15000.00) AND (TITLE = 'AVP');

Another statement get the result set basic_pay>15000 and title can be AVP or VP.

For example:

SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY FROM EMPLOYEE WHERE (BASIC_PAY >= 15000.00) AND (TITLE = 'AVP' OR TITLE=’VP’);

Aggregate Functions

Aggregate Functions

MIN	Returns the smallest value in a given column
MAX	Returns the largest value in a given column
SUM	Returns the sum of the numeric values in a given column
AVG	Returns the average value of a given column
COUNT	Returns the total number of values in a given column
COUNT(*)	Returns the number of rows in a table

In database management system an aggregate function is a function where the values of multiple rows are grouped together as input and on certain criteria to form a single value. Aggregate functions are used to compute against a numeric column data from SELECT statement. This mostly summarize the results of a particular column of selected data. 

For example:

SELECT AVG (MARKS) FROM STUDENTS;

This statement will return a single result which contains the average value of everything returned in the Marks column from the Students table.

Another example:

SELECT AVG (SALARY) FROM EMPLOYEE WHERE TITLE=’AVP’;

This statement will return the average salary for all employee whose title is equal to AVP.

Example:

SELECT COUNT (*) FROM STUDENTS;

This statement returns the total number of students. However you can also add additional column or distinct values counts.

Example:-

SELECT COUNT (DISTINCT TITLE) FROM EMPLOYEE;

Different types of ANSI standard joins

INNER JOIN (SIMPLE JOIN): This returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN): This returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN): This returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN. The results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

FULL JOIN (or FULL OUTER JOIN): This returns all rows for which there is a match in EITHER of the tables. Theoretically, a FULL JOIN associations the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

CROSS JOIN: This returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

SPACE

USE CASE: When we are working on TSQL or some text based scripting we may need some extra space in between two string, we can add space without space function as well but it is possible we may miss to put space in between string or text. If we have to maintain fixed length of space in between string every time we can use TSQL SPACE function.

SPACE

Returns a string of repeated spaces.

SYNTAX

SPACE ( integer_expression ) 

ARGUMENTS

integer_expression
Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

REMARKS

To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE.

EXAMPLE:-

/*

Considering employee table has three rows;

ID	FIRSTNAME	LASTNAME
1	PRIYA	KUMARI
2	AYUSH	KUMAR
3	RICHA	SINGH

*/

SELECT FirstName +’,’+SPACE(3)+LastName from employee;

PRIYA   KUMARI

AYUSH   KUMAR

RICHA   SINGH

Generate list of all Month name

DECLARE @year INT
SET @year = 2016

;WITH months AS(
    SELECT 1 AS Mnth, DATENAME(MONTH, CAST(@year*100+1 AS VARCHAR) + '01')  AS monthname
    UNION ALL
    SELECT Mnth+1, DATENAME(MONTH, CAST(@year*100+(Mnth+1) AS VARCHAR) + '01') FROM months WHERE Mnth < 12
)
SELECT * FROM months;