Prev Next
SQL Datatypes:
Each column in a table is require having a name and data type.
Types of datatype:
1. Exact numeric datatype:
e.g. bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney.
2. Approximate numeric datatype:
e.g. float, real.
3. Date and Time datatype:
e.g. datetime, smalldatetime, date, time.
4. Character strings datatype:
e.g. char, varchar, varchar(max), text.
5. Unicode character strings datatype:
e.g. nchar, nvarchar, nvarchar(max), ntext.
6. Binary datatype:
e.g. binary, varbinary, vabinary(max), image.
7. Misc datatype:
e.g. timestamp, xml, json.
Datatype in sql: sql-datatypes
SQL Comments:
1. Single line comments.
2. Multi line comments.
3. Inline comments.
1. Single line comments:
Single line starting with '-' is a comment and will not be executed.
e.g. - - single line comment
2. Multi line comments:
We can comment out multiple line using '/*' and '*/'. Line starting with '/*' is considered as starting point of comment and ends when '*/' is encountered.
e.g. /*multiline comment start
multiple comments
multiline comment ends*/
3. Inline comments:
Comments can be stated in between the statements and are enclosed in between '/*' and '*/'.
e.g. create database demo /*command for creating database*/
Article on comments: sql-comments
SQL constraints:
Constraints are the rules or restrictions that we can apply on the type of data in a table.
Constraints can be specified at the time of table creation.
Types of SQL constraints:
1. Not Null
2. Unique.
3. Primary key.
4. Foreign key.
5. Check.
6. Default.
7. Index.
1. Not Null constraint:
This constraint ensures that a column cannot have a NULL value.
Create table student (Id int (10), Name varchar (20) not null)
2. Unique constraint:
Ensures that all the values in a column are unique i.e. duplicate values are not allowed.
Create table student (Id int (10) unique, Name varchar (20))
3. Primary key:
-It is a combination of not null and unique.
-only one primary key is allowed in a relation.
-All the values of primary key must be unique.
-value of primary key can't be null.
-Primary key uniquely identifies each record in a relation.
4. Foreign key:
Uniquely identifies tuples in another relation.
when attribute of one relation references the primary key of another table. It is used to create a relationship with other relation (table).
5. Check:
It ensures that value of column meets the condition.
Create table student (Id int (10) primary key, Name varchar (20) not null, Age int (20) not null check (Age > 15))
Therefore, Age column only allows those value to enter which are greater than 15
6. Default:
Sets the default value for a column when no value is specified.
Create table user (Id int (10) not null, Name varchar (20), isActive tinyint (1) default '0'))
7. Index:
It is used to create and retrieve data from the database very quickly.
SQL Constraint: sql-constraints
SQL Creating Roles: sql-creating-roles
SQL Indexing:
Indexes is used by the server to speed up the retrieval of tuples by using pointer. These Indexes are special lookup tables. Index mainly considered as a pointer to the data in table.
'create index' is used to create indexes in tables and duplicate values are allowed.
e.g. create index index_name on table_name (column1, column2, ...);
'create unique index' is used to create unique indexes in tables and duplicate values are not allowed.
e.g. create unique index index_name on table_name (column1, column2, ...);
'drop index' is used to delete an index.
e.g. drop index index_name on table_name
SQL index : sql_create_index
SQL Trigger: sql-trigger-student-database
Introduction to NOSQL : introduction-to-nosql
2. Unique constraint:
Ensures that all the values in a column are unique i.e. duplicate values are not allowed.
Create table student (Id int (10) unique, Name varchar (20))
3. Primary key:
-It is a combination of not null and unique.
-only one primary key is allowed in a relation.
-All the values of primary key must be unique.
-value of primary key can't be null.
-Primary key uniquely identifies each record in a relation.
4. Foreign key:
Uniquely identifies tuples in another relation.
when attribute of one relation references the primary key of another table. It is used to create a relationship with other relation (table).
5. Check:
It ensures that value of column meets the condition.
Create table student (Id int (10) primary key, Name varchar (20) not null, Age int (20) not null check (Age > 15))
Therefore, Age column only allows those value to enter which are greater than 15
6. Default:
Sets the default value for a column when no value is specified.
Create table user (Id int (10) not null, Name varchar (20), isActive tinyint (1) default '0'))
7. Index:
It is used to create and retrieve data from the database very quickly.
SQL Constraint: sql-constraints
SQL Creating Roles: sql-creating-roles
SQL Indexing:
Indexes is used by the server to speed up the retrieval of tuples by using pointer. These Indexes are special lookup tables. Index mainly considered as a pointer to the data in table.
'create index' is used to create indexes in tables and duplicate values are allowed.
e.g. create index index_name on table_name (column1, column2, ...);
'create unique index' is used to create unique indexes in tables and duplicate values are not allowed.
e.g. create unique index index_name on table_name (column1, column2, ...);
'drop index' is used to delete an index.
e.g. drop index index_name on table_name
SQL index : sql_create_index
SQL Trigger: sql-trigger-student-database
Introduction to NOSQL : introduction-to-nosql
