Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

BETWEEN operator

The BETWEEN operator is used to select values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

Remember:

The values can be numbers, text, or dates.
The SQL Server BETWEEN condition will return the records where expression is within the range of value 1 and value 2 (inclusive), for more detail check  below example “between with numeric values”

Syntax:

T-SQL expression BETWEEN value1 AND value2; 

SQL Simple Syntax:

SELECT column_names
FROM  table_name
WHERE column_name BETWEEN value 1 AND value 2;


Examples:

Between with date:

Following example returns all product orders between OrderDate “2015/01/10” AND “2015/05/12”

SELECT * FROM [order]
WHERE OrderDate BETWEEN '2015/01/10' AND '2015/05/12';


Between with numeric values:

Following example returns one record which has productid 3, here condition returns the records where expression is within the range of value1 (3) and value2 (3) (inclusive)

SELECT * FROM PRODUCTS WHERE ProductID BETWEEN 3 AND 3


Following example returns 4 records including productid 2 and 5

SELECT * FROM PRODUCTS WHERE ProductID BETWEEN 2 AND 5 

 

 Between operator with text values example:

Following example returns all products with a ProductName beginning with any of the letter BETWEEN ‘N’ and ‘S’

SELECT * FROM Products
WHERE ProductName BETWEEN 'N' AND 'S';

 

Negate between or Not Between Examples:

Following example returns all products with a ProductName not beginning with any of the letter BETWEEN ‘N’ and ‘S’

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'N' AND 'S';

 

 

TOP Clause

TOP Clause:

SELECT TOP Clause is used to return number of specific records.

As a system user if I want to check first ten records from a table which contain huge amount of data i.e fifty thousand records. When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order

Syntax:

    [TOP (expression) [PERCENT]
    [ WITH TIES ]]


Simple SQL Syntax:

SELECT TOP number|percent column_name(s)
FROM TableName;


Example with top ten records:

The following example returns only random 10 records from order table
SELECT TOP 10 * FROM [ORDER] 

Example with Ties & order by:

The following example obtains the top 3 products from all products; DENSE RANK UDF ASSIGN same number for tied record. Specifying WITH TIES makes sure that any products that have number same are also included in the result set, even if doing this exceeds 3 records of products.
SELECT Top 3 WITH TIES  DENSE_RANK() OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName 
FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId order by [No.]

Select with top 3 returned 4 records for this example 


Primary Key Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

1. Primary key creates clustered index on the columns.

2. Primary keys must contain UNIQUE values.

3. A primary key column cannot contain NULL values.


Primary key constraints using SQL Server Management Studio Table Designer:

How to add a PRIMARY KEY constraint?

Select the column in the Table Designer grid, right-click on column DepartmentId, and then click Set Primary Key. A key icon will be added to the column listings to indicate that a primary key has been added, as shown in Figure.


 How to remove a PRIMARY KEY constraint?

Select the column in the Table Designer grid, right-click on column DepartmentId, and then click Remove Primary Key. A key icon will be removed from the column listings to indicate that a primary key has been deleted, as shown in Figure.

 

How to view or modify PRIMARY KEY constraint?

To view or modify that key's settings, right-click the upper grid, click Indexes/Keys to launch the Indexes/Keys dialog box.

You can see in below figure dialog box displays primary key information for column DepartmentId.


Primary Key Constraints using T-SQL:

Create Primary Key:

Column level:

USE [WebShop]
CREATE TABLE [dbo].[Department](
            [DepartmentId] [smallint] IDENTITY(1,1) NOT NULL Primary Key,
            [DeptName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](2000) NULL,
            [CreatedBy] [uniqueidentifier] NOT NULL,
            [CreatedDate] [datetime] NULL,
            [UpdateBy] [nvarchar](256) NULL,
            [UpdateDate] [datetime] NULL
 )

 

Table Level:

USE [WebShop]
CREATE TABLE [dbo].[Department](
            [DepartmentId] [smallint] IDENTITY(1,1) NOT NULL,
            [DeptName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](2000) NULL,
            [CreatedBy] [uniqueidentifier] NOT NULL,
            [CreatedDate] [datetime] NULL,
            [UpdateBy] [nvarchar](256) NULL,
            [UpdateDate] [datetime] NULL
            CONSTRAINT PK_Department  PRIMARY KEY(DepartmentId)
 )

 

Alter Table Statement to create Primary Key

USE [WebShop]
ALTER TABLE [Department]
ADD CONSTRAINT PK_Department PRIMARY KEY(DepartmentId)
GO

 

Alter Table Statement to Drop Primary key

USE [WebShop]
 
ALTER TABLE [Department]
DROP CONSTRAINT PK_Department;
GO