To study and implement DDL commands and different types of constraints.

1. CREATE: This is used to create a new relation (table)

Syntax: CREATE TABLE (field_1 data_type(size),field_2 data_type(size), .. . );

2. ALTER: This is used to add some extra fields into existing relation.

Syntax: ALTER TABLE relation_name ADD (new field_1 data_type(size) );

(a)ALTER TABLE ...ADD...: ALTER TABLE std ADD (Address CHAR(10));

(b )ALTER TABLE...MODIFY...: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size)

( c) ALTER TABLE..DROP.... ALTER TABLE relation_name DROP COLUMN (field_name);

(d)ALTER TABLE..RENAME...: ALTER TABLE relation_name RENAME COLUMN (OLD field_name) to (NEW field_name);

3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes the records in the table.

Syntax: DROP TABLE relation_name;

4.RENAME: It is used to modify the name of the existing database object.

Syntax: RENAME TABLE old_relation_name TO new_relation_name;

CONSTRAINTS: Constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. It can be specified when the table is created (using CREATE TABLE statement) or after the table is created (using ALTER TABLE statement).

1. NOT NULL: When a column is defined as NOTNULL, then that column becomes a mandatory column. It implies that a value must be entered into the column if the record is to be accepted for storage in the table.

Syntax: CREATE TABLE Table_Name (column_name data_type (size) NOT NULL, );

2. UNIQUE: The purpose of a unique key is to ensure that information in the column(s) is unique i.e. a value entered in column(s) defined in the unique constraint must not be repeated across the column(s). A table may have many unique keys.

Syntax: CREATE TABLE Table_Name(column_name data_type(size) UNIQUE, ….); 3. CHECK: Specifies a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null).

Syntax: CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);

4. PRIMARY KEY: A field which is used to identify a record uniquely. A column or combination of columns can be created as primary key, which can be used as a reference from other tables. A table contains primary key is known as Master Table.
   It must uniquely identify each record in a table.
   It must contain unique values.
   It cannot be a null field. It cannot be a multi port field.
   It should contain a minimum number of fields necessary to be called unique.

Syntax: CREATE TABLE Table_Name(column_name data_type(size) PRIMARY KEY, ….);

5. FOREIGN KEY: It is a table level constraint. We cannot add this at column level. To reference any primary key column from other table this constraint can be used. The table in which the foreign key is defined is called a detail table. The table that defines the primary key and is referenced by the foreign key is called the master table.

Syntax: CREATE TABLE Table_Name(column_name data_type(size) FOREIGN KEY(column_name) REFERENCES table_name);

6. DEFAULT : The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.

Syntax: CREATE TABLE Table_Name(col_name1,col_name2,col_name3 DEFAULT ‘’);

CREATE TABLE Students (
    id integer PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    birth_date DATE
);

CREATE TABLE Students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name varchar(20) NULL,
    address text,
    grades varchar(10),
    phone numeric not null
);
);

OrderID as INTEGER autoincrement not null (Specify OrderID as the Primary Key). OrderNumber as INTEGER. ProductName as Varchar(30) Manufacturename as varchar(30) PersonID as INTEGER not null (Specify PersonID as a Foreign Key referencing the table "Person").

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    OrderNumber INTEGER,
    ProductName Varchar(30),
    Manufacturename varchar(30),
    PersonID INTEGER NOT NULL,
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

rollno as integeprimarykey with autoincrement stu_name as varchar(50) year as varchar(30) parentsoccupation as varchar(30) address as varchar(30)

CREATE TABLE Students (
    rollno INTEGER PRIMARY KEY AUTOINCREMENT,
    stu_name VARCHAR(50),
    year VARCHAR(30),
    parents_occupation VARCHAR(30),
    address VARCHAR(30)
);

ALTER TABLE Student_details
ADD COLUMN Mobilenumber number;

ALTER TABLE employee
ADD COLUMN birth_date date;

ALTER TABLE customer
ADD COLUMN discount DECIMAL(5,2);

INSERT INTO Student_database (fname, lname, Gender, Subject, MARKS)
VALUES 
    ('Emma', 'Johnson', 'Female', 'Physics', NULL),
    ('Samuel', 'Davis', 'Male', 'Chemistry', NULL),
    ('Ethan', 'Moore', 'Male', 'History', NULL),
    ('Olivia', 'Robinson', 'Female', 'ComputerScience', NULL),
    ('Mason', 'Clark', 'Male', 'English', NULL);

INSERT INTO Student_database (fname, lname, Gender, Subject, MARKS) 
VALUES 
    ('Emma', 'Johnson', 'Female', 'Physics', 95),
    ('Samuel', 'Davis', 'Male', 'Chemistry', 82),
    ('Ethan', 'Moore', 'Male', 'History', 78),
    ('Olivia', 'Robinson', 'Female', 'ComputerScience', 92),
    ('Mason', 'Clark', 'Male', 'English', 85);

INSERT INTO new_customers (customer_id, name, email)
SELECT customer_id, name, email
FROM old_customers
WHERE name = 'James';

Thus , to study and implement DDL commands and different types of constraints.