Page 262 - Information_Practice_Fliipbook_Class11
P. 262

The revised  CREATE TABLE statement for the table  EMPLOYEE would appear as follows:

        CREATE TABLE EMPLOYEE (
        ID INT PRIMARY KEY,
        FName VARCHAR(20) NOT NULL,

        LName VARCHAR(20) NOT NULL,
        Gender CHAR(1) NOT NULL,
        Address VARCHAR(30),
        City VARCHAR(20),
        Pin_Code CHAR(6),

        DOB DATE,
        Salary INT NOT NULL DEFAULT 10000,
        Dept_No SMALLINT

        );#EMPLOYEE
        While inserting a tuple into the table EMPLOYEE, if the user does not provide any value for the attribute Salary, then
        it will be set as 10000. The default value of the character type must be enclosed within single quotes or double-quotes.

        CHECK constraint
        The CHECK constraint restricts the values that an attribute can take.  For example, it would be reasonable to limit the
        values of Gender to 'M', 'F', and 'O', indicating male, female, and other. A CHECK constraint to achieve
        this may be included as part of the statement specified as follows:

        CHECK(Gender = 'M' OR Gender = 'F' OR Gender = 'O')
        The revised  CREATE TABLE statement for the table  EMPLOYEE would appear as follows:

        CREATE TABLE EMPLOYEE (
        ID INT PRIMARY KEY,
        FName VARCHAR(20) NOT NULL,

        LName VARCHAR(20) NOT NULL,
        Gender CHAR(1) NOT NULL
        CHECK(Gender = 'M' OR Gender = 'F' OR Gender = 'O'),
        Address VARCHAR(30),
        City VARCHAR(20),
        Pin_Code CHAR(6),

        DOB DATE,
        Salary INT NOT NULL DEFAULT 10000,
        Dept_No SMALLINT
        );#EMPLOYEE
        CHECK constraint may also be used to specify that an attribute would take one of the several specified values or a
        numeric value in a specified range. For example, the constraint to perform a check on valid values of Gender may also
        be written as follows:

        CHECK(Gender IN ('M', 'F', 'O'))
        As another example, Salary may be restricted to lie between 8,000 and 1,00,000 as follows:

        CHECK(Salary BETWEEN 8000 AND 100000)


          248  Touchpad Informatics Practices-XI
   257   258   259   260   261   262   263   264   265   266   267