Redirecting to

  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) not_null_constraint 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)) unique_constraint 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. primary_key 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). foreign_key 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
Id Name