SQL Constraints

SQL Constraints are set of rules applied to a SQL tables to validate data. The purpose of SQL Constraints are to ensure the accuracy and reliability of the data inserted/updated in table. If any Constraints is violated, then the operation will be aborted and exception will be thrown.

How to create a SQL constraint?

A SQL constraint can be created in a table in two way

  • While creating a table – with CREATE TABLE statement
  • after a table is created – with ALTER TABLE statement

Commonly used SQL Constraints are described below

NOT NULL Constraint

By default SQL columns accept null values. When we wants to restrict null values from a column then we can apply NOT NULL Constraint. So that it will block null value to that column. in this case an INSERT statement or UPDATE statement cannot be performed without giving a value to particular column.

Example
In the below examples it ensures that Id and Name must not be NULL.
NOT NULL on CREATE table statement

Create table Employee
(
   Id bigint NOT NULL,
   Name varchar(50) NOT NULL,
   Age int
)

NOT NULL on ALTER table statement

alter table Employee alter column Id bigint NOT NULL
alter table Employee alter column Name varchar(50) NOT NULL

UNIQUE Constraint

A UNIQUE Constraint means it ensures all the values in a column is different. Any value that matches the column data type including NULL can be inserted only once in this column. A table can have more than one UNIQUE Constraints.
Example
In the below examples it ensures that Id will not be repeated.
UNIQUE on CREATE table statement

Create table Employee
(
   Id bigint NOT NULL UNIQUE,
   Name varchar(50) NOT NULL,
   Age int
)

UNIQUE on ALTER table statement

alter table users add UNIQUE(Id)

PRIMARY KEY Constraint

PRIMARY KEY contains rules from both NOT NULL and UNIQUE. Which means a PRIMARY KEY column cannot contain NULL value and all values must be different. PRIMARY KEY is used to identify each record with a unique value. A table can have only one PRIMARY KEY. PRIMARY KEY can be applied to a single column or multiple columns together. PRIMARY KEY applied to multiple columns together is called COMPOSITE PRIMARY KEY
Example
Primary key on CREATE TABLE statement

CREATE TABLE users
(
	user_id int PRIMARY KEY,
	username varchar(45) NOT NULL,
	[password] varchar(45) NOT NULL,
	email varchar(45) NOT NULL
)

Primary key on ALTER TABLE statement

ALTER TABLE users ADD PRIMARY KEY(USER_ID)

COMPOSITE Primary key on CREATE TABLE statement

CREATE TABLE users
(
	user_id int NOT NULL,
	username varchar(45) NOT NULL,
	[password] varchar(45) NOT NULL,
	email varchar(45) NOT NULL,
        PRIMARY KEY (user_id ASC, email)
)

COMPOSITE Primary key on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY (user_id ASC, email)

FOREIGN KEY Constraint

FOREIGN KEY Constraint is used to link two tables. A FOREIGN KEY can be only created with the reference of PRIMARY KEY from another table. FOREIGN KEY helps to execute JOIN queries much faster. The FOREIGN KEY containing table is called as Child table and the referenced PRIMARY KEY containing table is called as Parent/referenced table.
Example
Foreign key on CREATE TABLE statement

CREATE TABLE USERADDRESS
(
	Id bigint not null PRIMARY KEY,
	UserId bigint NOT NULL,
	AddresLine1 varchar(200) NOT NULL,
	AddresLine2 varchar(200),
	PIN varchar(12) NOT NULL,
	FOREIGN KEY(UserId) references users(user_id)
)

Foreign key on ALTER TABLE statement

ALTER TABLE USERADDRESS ADD CONSTRAINT FK_USERID_USERADDRESS FOREIGN KEY (UserId) REFERENCES users(user_id)

Drop a Foreign key

ALTER TABLE USERADDRESS DROP FK_USERID_USERADDRESS

CHECK Constraint

CHECK Constraint is used to check the inserted or updated value to the column satisfy any specific condition. It will help to increase the data accuracy.
Example
CHECK on CREATE TABLE Statement

CREATE TABLE users
(
	user_id bigint IDENTITY(1,1) NOT NULL,
	username [varchar](45) NOT NULL,
	password [varchar](45) NOT NULL,
	email [varchar](45) NOT NULL,
	Age int check (Age >= 18),
	PRIMARY KEY (user_id ASC)
)

CHECK on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT CK_USERAGE CHECK (Age >= 18)

Drop a CHECK Constraint

ALTER TABLE users DROP CK_USERAGE

DEFAULT Constraint

DEFAULT Constraint is used to set a default value to a column in a table. When an INSERT statement is not inserting value to DEFAULT Constraint column, then the default value will be inserted into that column.
Example
DEFAULT on CREATE TABLE statement

CREATE TABLE [dbo].[users]
(
	user_id bigint IDENTITY(1,1) NOT NULL,
	username [varchar](45) NOT NULL,
	password [varchar](45) NOT NULL DEFAULT 'Admin',
	email [varchar](45) NOT NULL,
	Age int,
	PRIMARY KEY (user_id ASC)
)

DEFAULT on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT PWD_DEF DEFAULT 'Admin' for [password]

Drop a DEFAULT Constraint

ALTER TABLE users DROP PWD_DEF

What are the main differences between DML vs DDL?

The main difference between DML vs DDL is – one for managing data in database and other for define database. DML stands for Data Manipulation Language and DDL stands for Data Definition Language.

    1. DML statements are used to manage the data in a database just like insert, update, delete etc.
      insert into table(col1, col2, col3) value(val1, val2, val3)
    2. DDL statements are used to create and define database structure just like create, alter, drop etc.
      create table tablename (col1 datatype, col2 datatype, col3 datatype)
    3. DML statements work with rows of data in table. For example, insert will create one or more rows in a table, where DDL works on database objects like table or views etc. For example, create will create a new object of table or view or procedure etc.
    4. Most of the DML support where clause and having clause for filtering data and order by and group by functions. But DDL does not support these functions.
    5. Transactions can be applied to DML statements. Which means, COMMIT and ROLLBACK are supported in DML statements. But DDL does not support Transactions. Which means once a table is created it cannot be roll backed. It need another DDL statement of DROP to remove the table from database.
    6. DDL statements cannot fire any triggers. But DML can fire triggers.

Joins in SQL

SQL JOINS

Joins in SQL are used to get related data from different tables. SQL joins are very useful feature in relational database while writing logical queries. There are different types of joins in SQL. They are

  • Inner Join (Join)
  • Outer Join
  • Cross Join
  • Self Join

Inner Join (Join)

Inner Join is used to retrieve only the matching records from both tables. The un-matching records will be omitted from result. The Join in SQL without any keywords like Inner, Outer, Left, Right or Cross is considered as Inner Join.

Outer Join

It retrieves either all rows from one table and only matching rows from other table or all rows from all tables. Doesn’t bother about matching. There are three kinds of outer join. They are.

Left Outer Join or Left Join

When we want all the rows from left table and only matching rows from right table, then we can use Left Outer or Left Join. If there is no matching records, it will return null values.

Right Outer Join or Right Join

When we need to fetch all the records from right table and only matching rows from left table, then we have to use Right outer or Right Join. If there is no matching rows in left table, then it will return null values.

Full Outer Join or Full Join

This combines both Left Outer join and Right Outer joins together. It returns records from both table when condition matches. If no match, then will show null. Concluding Outer Join, it can be said as it will return complete records from any of Left table or Right table and only matching from other table or from both(FULL) tables as matching together and not matching as null.

SQL JOINS
Types of JOINS in SQL

Cross Join

It is the Cartesian product of tables involved in join. Which means, each records from both tables will be combined to return result. This It is one of the least used join.

Self Join

Joining a table with itself is called Self Join It is mainly used with a table having hierarchy structure data in it like Employee Table with Manager_Id. Here Manager is also an employee and other employee will have a higher employee as manager. So in that situation, to get a manager of an employee, we need to join the same table with itself to get employee and manager. first table will act as employee table and second joining will act as Manager table.

Joins in SQL Based on Operators

Based on the operator used for joining tables, it can be classified into two types.

Equi Join

If we use only the = in join clause, then it is called Equi Join

Theta Join

When we use operators like >, <, >=, <= is known as Theta Join

Interface in OOPS

Introduction

Interface is introduced to achieve multiple inheritance. Multiple classes cannot be inherited in C#, because classes have definitions for members, so the compiler will get confused with members which to invoke.

Ex:

    public class A
    {
        public int Add(double a, double b)
        {
            return System.Convert.ToInt32(a + b);
        }
    }
    public class B
    {
        public int Add(double a, double b)
        {
            int x = System.Convert.ToInt32(a);
            int y = System.Convert.ToInt32(b);
            return x + y;
        }
    }
    public class C : A, B
    {
        // not possible in C#.
    }
    public class D
    {
        public void main()
        {
            A a = new A();
            B b = new B();
            C c = new C();

            // will return 12 (5.55+6.55=12.10, So while converting .10 will be ejected bcz less than .50)
            int x = a.Add(5.55, 6.55);

            // will return 13(5.55 will be taken as 6 bcz .55 is greater than .50 also 6.55 will be taken as 7 So 6 + 7 = 13) not possible in C#
            int y = b.Add(5.55, 6.55);

            // in this case the compiler cannot take Decision on which method to invoke, because Both A and B have Add and function differently.
            int z = c.Add(5.55, 6.55);
        }
    }

To avoid such situations, interface is used. In interface members does not have definition, the must have methods and properties can be declared. So the inheriting class can define the member as required. As in class the default Access modifier for the interface is internal. But the default Access modifier of the interface member is not like in class. In interface. The default Access modifier is PUBLIC, but in class it is PRIVATE.Interface

Implicit Implementation

    interface iA // internal interface
    {
        int Add(double a, double b); // public method.
    }
    interface iB
    {
        int Add(double a, double b);
    }
    class C : iA, iB
    {
        public int Add(double a, double b)
        {
            return System.Convert.ToInt32(a + b);
        }
    }

This is implicit implementation of interfaces. Even if both interfaces have “Add” method, single method definition will satisfy both interfaces.

    class D
    {
        void main()
        {
            iA ia = new C();
            // will point to “Add” method in class C.
            ia.Add(5.55, 6.55);

            iB ib = new C();
            // will point to “Add” method in class C.
            ib.Add(5.55, 6.55);

            C c = new C();
            // will point to “Add” method in class C.
            c.Add(5.55, 6.55);
        }
    }

So there is no confusion about pointing method.

Explicit Implementation

Even if you need to implement each interface method separately, it can be done by explicit implementation. But explicitly implemented method cannot be invoked directly from derived class. Because we cannot specify any access modifiers for explicitly implemented methods. It will be private by default. It can be either accessed by.

  1. Another method in derived class
  2. Creating object for interface type.
ANOTHER METHOD IN DERIVED CLASS
    interface iA // internal interface
    {
        int Add(double a, double b); // public method.
    }

    interface iB
    {
        int Add(double a, double b);
    }
    class C : iA, iB
    {
        // Explicit implementation of “iA” interface method
        int iA.Add(double a, double b)
        {
            return System.Convert.ToInt32(a + b);
        }
        // Explicit implementation of “iB” interface method
        int iB.Add(double a, double b)
        {
            int x = System.Convert.ToInt32(a);
            int y = System.Convert.ToInt32(b);
            return x + y;
        }
        // Exposing “iA” interface method through another public method
        public int AddBeforeConverting(double a, double b)
        {
            iA ia = this;
            return ia.Add(a, b);
        }

        // Exposing “iB” interface method through another public method
        public int AddAfterConvertion(double a, double b)
        {
            iB ib = this;
            return ib.Add(a, b);
        }
    }

Here we use different method names, so no confusion.

CREATING OBJECT FOR INTERFACE TYPE
    interface iA // internal interface
    {
        int Add(double a, double b); // public method.
    }

    interface iB
    {
        int Add(double a, double b);
    }
    class C : iA, iB
    {
        // Explicit implementation of “iA” interface method
        int iA.Add(double a, double b)
        {
            return System.Convert.ToInt32(a + b);
        }
        // Explicit implementation of “iB” interface method
        int iB.Add(double a, double b)
        {
            int x = System.Convert.ToInt32(a);
            int y = System.Convert.ToInt32(b);
            return x + y;
        }
    }
    class D
    {
        void main()
        {
            iA ia = new C();
            // will return 12 as it convert after addition
            ia.Add(5.55, 6.55);

            iB ib = new C();
            // will return 13 as it convert numbers before addition
            ib.Add(5.55, 6.55);

            C c = new C();
            // Cannot use like it, method won’t be available in the object.
            c.Add(5.55, 6.55);
        }
    }

Interface

  • Interface is defined with the keyword “interface”.
  • Interfaces are used in design level.
  • Object for interface cannot be created directly. But it can be created by calling derived class constructor(as did with abstract class).
  • Interfaces only can have declaration, but not definition

Ex:

Public interface iA
{
    String Father; // field is not allowed in interface
    String Name{get;set;} // property is allowed in interface
    Int Add(int a, int b);
}
  • Member fields cannot be used in interface.
  • Access modifiers cannot be used with interface members.
  • All the interface members must be implemented in the derived classes.

After reading Abstract class and Interface, most of the people will have a confusion on why we have both abstract class and interface, and where to use them, because both have many similar features. It has specific reasons to have both together.

  • When we have a normal base class to inherit, then we cannot inherit another abstract class too.
    class A
    {
        private string _name;
        public string GetName()
        {
            return _name;
        }
    }
    abstract class B
    {
        public abstract void SetName(string Name);
    }

    interface iC
    {
        void SetName(string Name);
    }
    class D : A, B
    {
        // This is not possible, because multiple inheritance on class is Not possible in C#
            }
    class D : A, iC
    {
        //This is possible. Here only one class is inherited and another one is interface
    }

In the above situation we need to use interface instead of abstract class.

  • When we are in need of some method declarations and some methods with method definition, then we have only one choice of Abstract class.(some function in a fixed manner and some function according to the consumer requirement on implementation).

There may be more reasons, so please refer the internet also. As said above, the abstract class and interface are used in the design level. So it is mostly used in design patterns.(if you wish to learn more about design patterns, can refer to internet) For more references about OOPS: MSDN – OOPS(C#)

Refer for more OOPS Concepts

Abstract classes in OOPS

Abstract Classes

  • Abstract classes are defined with the keyword ‘abstract’.
  • They are used in design level.
  • Object cannot be created to abstract classes directly. But object can be created to type of abstract class by calling derived class constructor
Abstract Class
Every animal have its specific type just like Wolf, Gold Fish, Dog etc. So assume as No object for Animal class, but inheriting Animal class to any other type of animal we can get the base behavior of animal in those type of animals.

Ex:

        abstract class A
        {
            public abstract int Add(int a, int b);
        }

        class B : A
        {
            public override int Add(int a, int b)
            {
                return a + b;
            }
        }

        class C
        {
            public C()
            {
                A a = new A();  // it is not possible, because there is no constructor for abstract class.
                A a = new B();  // This is possible, because B is not abstract class, so It has it’s default constructor.
                a.Add(10, 20);  // will return 30.
            }
        }
  • Abstract classes can have abstract methods and properties as well as non abstract members. But a normal class cannot contain abstract members.
  • An abstract member should be implemented in its derived classes.
  • An abstract method or property will not be having it’s definition/implementation in abstract class.
  • It is not mandatory to have at least single abstract member in an abstract class. We can have defined methods only in an abstract class.

Ex:

        public abstract class A
        {
            public string Add(string a, string b)
            {
                return a+b; // A normal method
            }
            public static int Add(int x, int y, int z)
            {
                return x + y + z;
            }
        }

Abstract class can have a normal method, but it cannot be used without inheritance. Because object cannot be created for abstract class. At the same time a static method can be used in abstract class and it can be used without inheritance. Because static members doesn’t need object to invoke.

From the above example, “public string Add(string a, string b)” method need inheritance to be invoked at the same time “Public static int Add(int x, int y, int z)” method can be used directly with class name

Ex:

        public abstract class A
        {
            // Abstract property.
            public abstract string FirstName { get; set; }
            // Abstract method.
            public abstract int Add(int a, int b);
            public string Add(string a, string b)
            {
                return a + b; // A normal method
            }
            public static int Add(int x, int y, int z)
            {
                return x + y + z;
            }
        }

        public class B : A
        {
            private string _FirstName;
            public override string FirstName
            {
                get { return _FirstName; }
                set { _FirstName = value; }
            }
            public override int Add(int a, int b)
            {
                return a + b;
            }
        }
        public class C
        {
            public C()
            {
                A b = new B();

                b.Add("Mathew ", "Markose"); //will return “Mathew Markose” as result
                A.Add(10, 20, 30); // will return 60 as result
            }
        }

Refer for more OOPS Concepts

Access Modifiers in C#

Access Modifiers / Access Specifiers are keywords used in object oriented programming to restrict access to classes or class members like methods and variables. It is a part of programming syntax to achieve the encapsulation of components. There are different kinds of Access Modifiers with different kind of behaviors. Access Modifiers in C# are

  • Public
  • Private
  • Internal
  • Protected
  • Protected Internal
  • Sealed

Public

It can be accessed from anywhere as object or for inheritance. In C# public is default for interfaces

Private

It is only accessible inside the same class. When an object of the class is created, private methods and variables won’t be accessible. In the case of inheritance, private properties and methods are inherited, but won’t be accessible. Nothing can be created as private direct under namespace. Which means a class direct under namespace cannot be given private modifier. Only nested class can be assigned as private. In C# private is default for class members.

Internal

This is just like public, but scope only inside the assembly. Outside the assembly, It won’t be available for inheritance as well as for object. In C#, internal is default for a class direct under namespace.

Protected

It is only available for inheritance, will not be accessible from object of the class. Which means, a protected member can be used only inside the child class not directly from parent class. Scope for inheritance is anywhere:- accessible from outside of the assembly also possible. A class direct under namespace cannot be created as protected.

Protected Internal

This is the union of Protected and Internal. It can be accessed from object only inside the assembly, but accessible for inheritance globally. As protected, no element direct under namespace can be created as protected internal.

Sealed

Sealed modifier is used to protect the class or class member from being inherited. A class with sealed modifier cannot be inherited, but it can inherit another class. But in case of class members, it can only be used to overridden members. Which means a new member cannot be create as sealed. So it protect class members fro multi level inheritance.

    class A
    {
        String name;
    }
    sealed class B : A
    {
        int Age; //this is possible B can inherit A
    }
    class C : B
    {
        DateTime DOB;   // This will return error on compile time, bcz, sealed class B cannot be inherited.
    }

Summary

Refer for more OOPS Concepts

What is Encapsulation in OOPS?

Encapsulation is the process of wrapping method and attributes into single unit and hiding unnecessary data by using access modifiers.

public class A
{
    Private int Id;
    Private string _Name;
    Public string Name
    { 
        get { return _Name;}
        set{_Name = value;}
    }
    Public void SaveandPrint()
    {
        Save();
        Print();
    }
    Private void Save()
    {
        // Call the ADO.Net method to save data
    }
    Private void Print()
    {
        // create the object of report and print data
    }
}

Here the object expose only the information of SaveandPrint(). But internally it is split into two methods as Save() & Print() and it is not exposed to end user. Also it wrap the variable and function into single unit called class. While creating object also it keep in a single unit.

What is Abstraction in OOPS?

The meaning of abstract is existing in thoughts or as idea but not having a physical or concrete existence. It’s mainly used in design level. Abstract classes and interfaces are best examples for abstraction. We can specify the abstract actions which does not have an implementation in the same class but in the derived class.

public abstract class A
{
    Public abstract int Add(int a, int b);
}
Public class B : A 
{
    Public override int Add(int a, int b)
    { 
        Return a+b;
    }
}

What is Polymorphism in OOPS?

Polymorphism is a Greek word which means many-forms. That means look same but act different. There are two types of polymorphism in .Net

  • Static Polymorphism
  • Dynamic Polymorphism

Static Polymorphism

Method overloading and Operator overloading are static polymorphisms. Method overloading is having the same method name with different types of parameters or different order of parameters. Method overloading cannot be done by changing only the return type.

public int Add(int a, int b)
{
     a++; // Operator overloading
     Return a+b;
}
// Method overloading. Same method name with different parameters
Public string Add(string a, string b)
{
    Return a+b;
}

It is also called Early Binding. Because call to these functions are decided at compile time itself.

Dynamic Polymorphism

Overriding a virtual method is an example for dynamic polymorphism. It is also known as Late Binding. Because calls to these functions will be decided at runtime only.

public class Animal
{
    Public virtual void Eat()
    {
        //eat by mouth
    }
}
Public class Human
{
    Public override void Eat()
    {
        //Human put food into mouth by hand
        //eat by mouth(by calling base method)
    }
}

Here both method signature are same, so the compiler won’t be knowing which method to be called, it will be decided on runtime.

What is Inheritance in OOPS?

Inheritance

Inheritance is one of the main feature of OOPS. It helps to reuse the code of one class into another by inheriting. Inherited class is called as ‘Base’ or ‘Parent’ class and inheriting class is called as ‘Derived’ or ’Child’ class. By inheriting, the child class can use the properties and functions of base class. This is just like a son/daughter getting some of the behavior from his/her parents. There are different types of inheritance.

  • Single Inheritance
  • Multi Level inheritance
  • Multiple inheritance
  • Hierarchical Inheritance
  • Hybrid Inheritance

Single Inheritance

A single derived class from single base class.

Single Inheritance

Multi Level inheritance

A child class is derived from another child class.The second child class will have all inheritable properties and functions of both base and first child classes.

Multi Level inheritance

Multiple Inheritance

A child class is derived from more than one base class. It is not possible to inherit multiple classes in C#. It is because there is a problem called Diamond Ring problem, which causes conflicts between members of classes. But to overcome the problem, Interfaces are introduced.

Multiple Inheritance

Hierarchical Inheritance

More than one child classes from single base class.

Hierarchical Inheritance

Hybrid Inheritance

It is the combination of all the inheritance. As it is the combination of all inheritance, it include multiple inheritance also, so it’s not supported in C#.

Hybrid Inheritance
References