(i) The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data.

(ii) For Example: SELECT DISTINCT Place FROM Student;
    Will display the following data as follows:
    Place
    Chennai
    Banglore
    Delhi

 

(i) The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required.
(ii) The different states of our table can be saved at any time using different names and the rollback to that state can be done using the ROLLBACK command.
(iii) SAVEPOINT savepoint_name:
      UPDATE student SET Name = 'Mini' WHERE Admno=105;
      SAVEPOINT A;

Data Definition Language:
(i) Create Command: To create tables in the database.
CREATE TABLE Student
(Admno integer,
      Name char(20),
      Gender char(1),
      Age integer,
      Place char(10),
);
(ii) Alter Command: The ALTER command is used to alter the table structure like adding a column, renaming the existing column, change the data type of any column or size of the column or delete the column from the table.
ALTER TABLE <table-name> ADD <column-name><data type><size>;
To add a new column "Address" of type 'char' to the Student table, the command is used as
ALTER TABLE Student ADD Address char;
(iii) Drop Command: The DROP TABLE command is used to remove a table from the database.
The table can be deleted by DROP TABLE command in the following way:
DROP TABLE table-name;

ALTER TABLE <table-name> ADD <column- name><data type><size>;
Example: ALTER TABLE Student MODIFY Address char (25)

 

 

Constraint:
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. Constraint is a condition applicable on a field or set of fields.
Primary key constraint:
(i) This constraint declares a field as a Primary key which helps to uniquely identify a record.
(ii) It is similar to unique constraint except that only one field of a table can be set as primary key.
(iii) The primary key does not allow NULL values.
 

Table constraint:
Table constraint apply to a group of one or more columns. The Syntax tor a table created with Constraint is given as below:
CREATE TABLE <table_name>
(<column name> <datatype>[<size>]
<column constraint>,
(<column name> <data type> [<size>]
<column constraint).....
<table constraint>(<column name>, [<column name>..........])............);
Following is an example for employee table with "NOT NULL' column constraint. This constraint enforces a field to always contain a value.
CREATE TABLE employee (No int NOT NULL PRIMARY KEY,
NAME varchar(50), Age int (10), Address varchar(50)
PhoneNo int(10), Pincode int (6) PRIMARY KEY (NAME, PhoneNo);
The above command creates a table "employee" in which the field No of integer type is defined NOT NULL, which means this field must have values. The field NAME is of char type. The fields NAME, PhoneNo, have table constraint.

1. SELECT * FROM student WHERE gender = Male GROUP BY gender;
2. SELECT * FROM student WHERE Age > 18 ORDER BY DESC;

SQL commands are divided into five categories:

Data Definition Language:
(i) The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema.
(ii) It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.
(iii) The DDL provides a set of definitions to specify the storage structure and access methods used by the database system.
DDL performs the following functions:
(i) It should identify the type of data division such as data item, segment, record and database file.
(ii) It gives a unique name to each data item type, record type, file type and data base.
(iii) It should specify the proper data type.
(iv) It should define the size of the data item.
(v) It may define the range of values that a data item may use.
(vi) It may specify privacy locks for preventing unauthorized data entry.
Commands:

CREATE	To create tables in the database.
ALTER	Alters the structure of the database
DROP	Data tables from database.
TRUNCATE	Remove all records from a table, also release the space occupied bv those records.

Data Manipulation Language:
This is a computer programming language used for adding, removing and modifying data in a database. This language comprises the SQL-data change statements, which modify stored data but not the schema of the database table
Data Manipulation includes
(i) Insertion of new information into the database
(ii) Retrieval of information stored in a database
(iii) Deletion of information from the database.
(iv) Modification of data stored in the database
The DML is basically of two types:
Procedural DML - Requires a user to specify what data is needed and how to get it.
Non-Procedural DML - Requires a user to specify what data is needed without Specifying how to get it.
Commands:

INSERT	Inserts data into a table
UPDATE	Updates the existing data within a table
DELETE	Deletes all records from a table, but not the space occupied by them.

Data Control Language:
(i) A Data Control Language (DCL) is a programming language used to control the access of data stored in a database. It is used for controlling privileges in the database (Authorization).
(ii) The privileges are required for performing all the database operations such as creating sequences, views of tables etc.
Commands:

GRANT	Grants permission to one or more users to perform specific tasks.
REVOKE	Withdraws the access permission given by the GRANT statement.

Transactional Control Language:
Transactional control language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements.
Commands:

COMMIT	Saves any transaction into the database permanently.
ROLL BACK	Restores the database to last commit state.
SAVE POINT	Temporarily save a transaction so that you can rollback.

Data query language:
The Data Query Language consist of commands used to query or retrieve data from a database. One such SQL command in Data Query Language is

SELECT

It displays the records from the table.

(i) SELECT * FROM Employee ORDER BY PAY DESC;
(ii) SELECT * FROM Employee WHERE ALLOWANCE
BETWEEN 5000 AND 7000;
(iii) DELETE FROM Employee WHERE DESIG='Mechanic';
(iv) ALTER TABLE employee ADD Age Varchar(50);
(v) SELECT * FROM Employee WHERE DESIG='Operator';

Constraints ensure database integrity, therefore known as database integrity constraints. The different type of constraints are:

1. Unique Constraint:
(i) This constraint ensures that no two rows have the same value in the specified columns. For example UNIQUE constraint applied on Admno of student table ensures that no two students have the same admission number and the constraint can be used as:
CREATE TABLE Student:
(
Admno integer NOT NULL UNIQUE, 
Name char (20) NOT NULL,
Gender char (1),
Age integer,
Place char (10),
);
(ii) The UNIQUE constraint can be applied only to fields that have also been declared as NOT NULL.
(iii) When two constraints are applied on a single field, it is known as multiple constraints. In the above Multiple constraints NOT NULL and UNIQUE are applied on a single field Admno, the constraints are separated by a space and the end of the field definition a comma(,) is added. By adding these two constraints the field Admno must take some value ie. will not be NULL and should not be duplicated.
2. Primary Key Constraint:
(i) This constraint declares a field as a Primary key which helps to uniquely identify a record. It is similar to unique constraint except that only one field of a table can be set as primary key.
(ii) The primary key does not allow ULI values and therefore a field declared as primary key must have the NOT NULL constraint.
Example:
CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARY KEY,
Name char(20)NOT NULL,
Gender char(1),
Age integer,
Place char(10),
);
3. Default Constraint:
(i) The DEFAULT constraint is used to assign a default value for the field. When no value is given for the specified field having DEFAULT constraint, automatically the default value will be assigned to the field.
Example:
CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARY KEY,
Name char(20)NOT NULL, .
Gender char(1),
Age integer DEFAULT = "17':
Place char(10),
);
4. Check Constraint:
(i) This constraint helps to set a limit value placed for a field. When we define a check constraint on a single column, it allows only the restricted values on that field. Example showing check constraint in the student table:
CREATE TABLE Students
(
Admno integer NOT NULL PRIMARY KEY
Name char(20)NOT NULL,
Gender char(1),
Age integer (CHECK< = 19), 
Place char(10),
);
5. Table Constraint:
(i) When the constraint is applied to a group of fields of the table, it is known as Table constraint. The table constraint is normally given at the end of the table definition.
CREATE TABLE Student
(
Admno integer NOT NULL,
Firstname char(20),
Lastname char(20),
Gender char(1),
Age integer,
Place char(10),
PRIMARY KEY (Firstname, Lastname) 
);