SQL Part 2: Data types, Operators and Constraints

 SQL Datatypes:

Exact Numeric SQL Data Types:

bigint = Range from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

int = Range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

smallint = Range from -2^15 (-32,768) to 2^15-1 (32,767)

tinyint = Range from 0 to 255

bit = 0 and 1

decimal = Range from –10^38 +1 to 10^38 -1

numeric = Range from -10^38 +1 to 10^38 -1

money = Range from -922,337,203,685,477.5808 to +922,337,203,685,477.5807

small money = Range from -214,748.3648 to +214,748.3647

Approximate Numeric SQL Data Types:

float = Range from -1.79E + 308 to 1.79E + 308

real = Range from -3.40E + 38 to 3.40E + 38

Date and Time SQL Data Types:

datetime = From Jan 1, 1753 to Dec 31, 9999

smalldatetime = From Jan 1, 1900 to Jun 6, 2079

date = To store a date like March 27, 1986

time = To store a time of day like 12:00 A.M.

Character Strings SQL Data Types:

char = CHAR is fixed lengt. Maximum length of 8,000 characters

varchar = VARCHAR is variable length. Maximum of 8,000 characters

varchar(max) = Maximum length of 231 characters

char vs varchar

CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds. VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.

Unicode Character Strings SQL Data Types:

nchar = Maximum length of 4,000 characters

nvarchar = Maximum length of 4,000 characters

nvarchar(max) = Maximum length of 231 characters

ntext = Maximum length of 1,073,741,823 characters

Binary SQL Data Types:

binary = Maximum length of 8,000 bytes

varbinary  = Maximum length of 8,000 bytes

varbinary(max) = Maximum length of 231 bytes

image = Maximum length of 2,147,483,647 bytes

SQL Operators:

There are are three types of Operators.

• Arithmetic Operators:
• Comparison Operators
• Logical Operators

Arithmetic operators

Operator Description

+ Add values of operands

– Subtract values of operands

* Multiply operand’s values

/ Divide values of operands

% Modulus operation on operands

Comparison operators

In the table below, if condition gets satisfied then “True” Boolean value is returned.

Operator Description

= Determine if the values of operands are equal.

!= Check if the values of operands are not equal.

> Determine if the left operand is more than the right operand.

< Check if the right operand is more than the left operand.

>= Determine if the left operand is more than or equal to the right operand.

<= Check if the right operand is more than or equal to the left operand.

!> Determine if the right operand is not more than the left operand.

!< Check if the left operand is not more than the right operand.

Logical Operators

Operator Description

OR   - Returns true if either operand is true. Else it returns false if both the operands are false.

AND - Returns true if both operands are true. Else it returns false if either or both the operands are false 

NOT - Returns true if condition is false and returns false if the condition is true.

SQL Constraints:

• Constraints are the rules enforced on data columns on table. 
• These are used to limit the type of data that can go into a table. 
• This ensures the accuracy and reliability of the data in the database. 
• Constraints could be column level or table level. 
• Column level constraints are applied only to one column, 
• whereas table level constraints are applied to the whole table. 

Following are commonly used constraints available in SQL: 

1. NOT NULL Constraint: Ensures that a column cannot have NULL value. 

Example:

CREATE TABLE recipes (

  recipe_id INT NOT NULL,

  recipe_name VARCHAR(30) NOT NULL,

  PRIMARY KEY (recipe_id),

  UNIQUE (recipe_name)

);

2. DEFAULT Constraint: Provides a default value for a column when none is specified. 

3. UNIQUE Constraint: Ensures that all values in a column are different. 

4. PRIMARY Key: Uniquely identified each rows/records in a database table. 

UNIQUE KEY vs PRIMARY KEY

• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
• The primary key column cannot have null values while the Unique Key column can have one null value.

5. FOREIGN Key: Uniquely identified a rows/records in any another database table. 

• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
• Foreign key is used to maintain relationship between two tables.
• Foreign Key in a table doesn't have to be unique in said table. BUT, as it is the Primary Key of another table, it must be unique in this table.
• Foreign Key can be NULL or duplicate

Example:

Table1 (Parent Table):

CREATE TABLE EmployeeDetail(EmpId integer PRIMARY KEY, FirstName varchar(255),LastName varchar(255),Salary integer,JoiningDate date, Department varchar(255), Gender varchar(255) );

Table 2 (Child Table):

CREATE TABLE ProjectDetail(ProjectDetailId integer PRIMARY KEY, EmpId integer REFERENCES EmployeeDetail(EmpId), ProjectName varchar(255) );

6. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. 

SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);

7. INDEX: Use to create and retrieve data from the database very quickly.

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. 

Syntax: CREATE INDEX <indexName>

ON <TableName>(LastName);

Please refer below YouTube video for more detail explanation: