Page 151 - Informatics_Practices_Fliipbook_Class12
P. 151

While writing SQL statements, the symbol # marks the beginning of a comment that extends to the end of the line.
                   A C-style comment is enclosed between /* and */. A comment may also begin with -- (double hyphen), followed by
                   a whitespace or control character such as space, tab, or end of line (EOL),  and extends to the end of the line.



                  1.  The primary key can be defined as a constraint as shown below:

                      CONSTRAINT EMPLOYEE_Key PRIMARY KEY(ID)
                  2.  Syntax for the constraint statement is given below:
                      CONSTRAINT ConstraintName PRIMARY KEY(attribute_1, [attribute_2],...)
                  3.   Note that the primary key constraint includes the name of at least one attribute included in the primary key. In
                      general, several attributes may be included in the primary key constraint.
                      CREATE TABLE EMPLOYEE (
                          ID INT NOT NULL UNIQUE,
                          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,
                          Dept_No SMALLINT,
                          CONSTRAINT EMPLOYEE_Key PRIMARY KEY(ID)
                      ); #EMPLOYEE
                        Note that the EMPLOYEE_Key is the name of the primary key constraint on ID. A named constraint may be
                      modified or dropped later by referring to its name.

            Next, we discuss some more SQL constraints:

            4.4.3 DEFAULT constraint

            A default value for an attribute may be specified using the DEFAULT constraint. For example, if most of the employees
            have a salary of  10000, it would be wise to set it as the default salary for an employee as follows:
                   Salary INT NOT NULL          DEFAULT 10000
            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


                                                                                        Database Query using SQL  137
   146   147   148   149   150   151   152   153   154   155   156