The SAVEPOINT Command is used to temporarily save a transaction so that we can roll back to the point whenever required. 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
SAVEPOINT Savepoint_name;
Example showing COMMIT.SAVEPOINT and ROLLBACK in the student table.
INPUT Table: Student

Admno	Name	Gender	Age	Place
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
103	Ayush	M	18	Delhi
101	Adarsh	M	18	Delhi
104	Abiandh	M	18	Chennai

INSERT INTO student VALUES (107, 'Beena', 'F', 20, 'Cochin');
The insert command insert the new record no 107 in the student table
COMMIT;

Admno	Name	Gender	Age	Place
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
103	Ayush	M	18	Delhi
101	Adarsh	M	18	Delhi
104	Abiandh	M	18	Chennai
107	Beena	F	20	Cochin

UPDATE student SET Name = "Mini WHERE Admno=105;
Student table is updated for the Admission No 105
SAVEPOINT A;
Output table student after save point A:
a) The save point A will save the updation.

Admno	Name	Gender	Age	Place
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
103	Ayush	M	18	Delhi
101	Adarsh	M	18	Delhi
104	Abiandh	M	18	Chennai
107	Beena	F	20	Cochin

INSERT INTO student VALUES (108, 'Jisha', 'F', 19, 'Delhi');
New value inserted into the table student as 107/'jisha'/'f'/19/'Delhi' 

Admno	Name	Gender	Age	Place
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
103	Ayush	M	18	Delhi
101	Adarsh	M	18	Delhi
104	Abiandh	M	18	Chennai
107	Beena	F	20	Cochin
108	Jisha	F	19	Delhi

ROLLBACK TO A;
Output Table: Student after Rollback A 

Admno	Name	Gender	Age	Place
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
103	Ayush	M	18	Delhi
101	Adarsh	M	18	Delhi
104	Abiandh	M	18	Chennai
107	Beena	F	20	Cochin

b) The Roll back command restore the database to the last committed state.

INPUT TABLE: Student

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	17	Bangalore
103	Ayush	M	18	Dellhi
104	Abinandh	M	18	Chennai
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
107	Hema	F	17	Chennai

The IN Keyword is used to specify a list of values which must be matched with tihe record values In other words it Is used to compare a column with more than one vslue. It is similar to ar OR condition.
Example:
SELECT Admno, Name, Place FROM Student WHERE Place IN ("Chennal", "Delhi")

Admno	Name	Place
100	Ashish	Chennai
101	Adarsh	Delhi
103	Ayush	Dellhi
104	Abinandh	Chennai
105	Revathi	Chennai
107	Hema	Chennai

The NOT IN keyword displays only these records that do not match in the list.
Example:
SELECT Admno, Name, Place FROM Student WHERE Place NOT IN ("Chennai, "Delhi")
It will display students only from places other than "Chennai" and "Delhi"

Admno	Name	Place
102	Akshith	Bangalore
106	Devika	Bangalore

Input Table The BETWEEN keyword defines a range of values the record must fall into to make the condition true. The range may include an upper value and a lower value between which the criteria must fall into.
SELECT Admno, Name, Age, Gender FROM student WHERE Age BETWEEN 18 AND 19;
Output:

Admno	Name	Gender	Age	Place
100	Adarsh	M	18	Delhi
103	Ayush	M	18	Delhi
104	Abinandh	M	18	Chennai
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore

The NOT BETWEEN is reverse of the BETWEEN operator where the records not satistying the condition are displayed.
Command:
SELECT Admno, Name, Age FROM Student WHERE Age NOT BETWEEN 18 AND 19;
Output:

Admno	Name	Age
100	Ashish	17
102	Akshith	17
107	Hema	17

The ALTER command is used to alter the table structure like adding in column, renaming the existing column, change the data type of any column or size of the column or delete the column from the table. It is used in the way:
ALTER TABIE ADD ccolumn-name>
;
To add a new column "Address" of type "char" to the student table, the command is used as ALTER TABLE Student ADD Address char:
To modify existing column of table, the ALTER TABLE Command can be used with MODIFY clatuse likewise:
ALTER MODIFY
;
ALTER TABLE Student MODIFY Address char (25);
The above command will modify the address column of the student table to now hold 25 characters. The ALTER command can be used to rename an existing column:
ALTER RENAME old-column-name TO new-column-name;
Example.
ALTER TABLE Student RENAME Address To City;
The ALTFR command can also be used to remove a column or all columns, for example to remove a particular column, the DROP COLUMN is used with well the ALTER TABLE to remove a particular field, the command can be used as:
ALTER DROP COLUMN ;
To remove the column city from the student table, the command is used as:
ALTER TABLE Student DROP COLUMN city;

Column constraint:
Column constraint apply only to individual column.
Table constraint:
Table constraint apply to a group of one or more columns. The Syntax for a table created with constraint is given as below:
CREATE TABLE
([]

(< data type >[]
,[.........]) .......
);
Following is an example for student table with "NOT NULL" column constraint. This constraint enforces a field to always contain a value.
CREATE TABLE student
(
Admno integer
Name char(20) NOT NULL,
Gender char (1),
Age integer,
Place Char (10),
);
The above command creates a table "Student" in which the field Admno of integer type is defined NOT NUL Ln Name of char type is defined as NOT NULL which means these two field must have values. The fields Gender, Age and Place do not have any constraints

 

 

The WHERE clause is used to filter the records. It helps to extract only those records which satisfy a given condition. For example in the student table, to display the list of Student of age 18 and above in alphabetical order of their names, the command is
INPUT TABLE: Student

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	17	Bangalore
103	Ayush	M	18	Delhi
104	Abinandh	M	18	Chennai
105	Revathi	F	19	Chennai
106	Devika	F	19	Banglore
107	Hema	F	17	Chennai

SELECT 8 FROM student WHERE Age >=18 ORDER By Name;
OUTPUT: Display the list of student ≥ 18

Admno	Name	Gender	Age	Place
104	Abinandh	M	18	Chennai
101	Adarsh	M	18	Delhi
103	Ayush	M	18	Delhi
106	Devika	F	19	Bangalore
105	Revathi	F	19	Chennai

To display the list of student in the descending order of names of those students of age 18 and above the command is
SELECT * FROM student WHERE Age >= 18 ORDER BY Name DESC;
OUTPUT: ORDER By Descending

Admno	Name	Gender	Age	Place
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
103	Ayush	M	18	Delhi
101	Adarsh	M	18	Delhi
104	Abinandh	M	18	Chennai

One of the most important tasks when working with SQL is to generate Queries and retrieve data. A Query is a command given to get a desired result from the database table. The SELECT command is used to query or retrieve data from a table in the database. It is used to retrieve a subset of records from one or more tables. The SELECT command can be used in various forms:
Syntax of SELECT command:
SELECT <column-list>FROM<table-name>;
Table-name is the name of the table from which the information is retrieved.
Column - list includes one or more columns from which data is retrieved.
INPUT TABLE: Student

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	17	Bangalore
103	Ayush	M	18	Delhi
104	Abinandh	M	18	Chennai
105	Revathi	F	19	Chennai
106	Devika	F	19	Bangalore
107	Hema	F	17	Chennai

To select the Admission Number and Name of the student command:
SELECT Admno, name FROM student;
Output Table:

Admno	Name
100	Ashish
101	Adarsh
102	Akshith
103	Ayush
104	Abinandh
105	Revathi
106	Devika
107	Hema

To view all the fields and rows of the table the SELECT command can be given as
SELECT * FROM STUDENT;
It will display all the fields, and records of the table student.

The UPDATE Command updates some or all values in a database. It can update one or more records in a table. The UPDATE Comand specifies the rows to be changed using the WHERE clause and the new data using the SET Keyword. 
the command is used as follows:
UPDATE < table-name> SET column-name = value, column-name = value,....
WHERE condition;
Example:
UPDATE student SET Age = 20 WHERE Place = " Bangalore";
The table will be as updated as

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	20	Bangalore
103	Ayush	M	18	Delhi

To update multiple fields, multiple field assignment can be specified with the SET clause separated by comma.
UPDATE Student SET age = 18, place= 'Chennai'
WHERE Admno = 102;
The command modifies the record in

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	18	Chennai
103	Ayush	M	18	Delhi

The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row so no field argument is needed. The DELETE command is used as follows:
DELETE FROM table- name WHERE condition;
For example to delete the record whose admission number is 104 in the command is given as follows,
DELETE FROM student WHERE Admno=104;
The following record is deleted from the student table.
To delete all the rows of the table, the command is used as:
DELETE * FROM student;
The table will be empty now and could be destroyed using the DROP command.

INSERT command:
The INSERT command helps to add new data to the database or add new records to the table. The command is used as follows:
INSERT INTO <table-names>  [column-list] VALUES (values);
Example:
INSERT INTO student (Admno, Name, Gender, Age, Place)
VALUES( 100, 'Ashish', 'M', 17, 'Chennai');
INSERT INTO student (Admno, Name, Gender, Age, Place)
The order of values must match the order of columns in the CREATE TABLE command. Specifying the column names is optional if data is to be added for all columns. The command to add values into the students table can also be used in
INSERT INTO student values (102, 'Akshith', 'M', '17', 'Bangalore');
The command inserts the record into the student table.
To add data to only some columns in a record by specifying the column name and their data it can be done by:
INSERT INTO student (Admno, Name, Place)
VALUES(103, 'Ayush', 'Delhi');
The above commands add the following with default values 'M' for Gender and Age as 18
INSERT INTO student (Admno, Name, Place)
VALUES(104, 'Abinandh', 'Chennai');
The student table will have the data as:

Adno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	17	Bangalore
103	Ayush	M	18	Delhi
104	Abinandh	M	18	Chennai

The fields that are given in the INSERT Command will take default values, if it is defined for them, otherwise NULL value will be stored.

The data in a database is stored based on the kind of value stored in it. This is identified as the data type of the data or by assigning each field a data type. All the values in a given field must be of same type.
The ANSI SQL standard recognizes only Text and Number data type, while some commercial programs use other data types like Date and Time etc. The ANSI data types are

Data Type	Description
Char (character)	Fixed Width string value - Values of this type is enclosed in single quotes, for ex. Anu's will be written as 'Anu' 's'.
Varchar	Variable width character string. This is Similar to char except the size of the data entry vary considerably.
dec (Decimal)	It represents a fractional number such as 15.12, 0.123 etc. Here the size argument consist of two parts: precision and scale. The precision indicates how many digits the number may have and the scale indicates the maximum number of digits to the right of the decimal point. The size (5,2) indicates precision as 5 and scale as 2. The scale cannot exceed the precision.
Numeric	it is same as decimal except that the maximum number of digits may not excees the precision argument.
int (Integer)	It represents a number without a decimal point. Here the size argument is not used.
Small int	It is same as integer but the default size may be smaller than Integer.
float	It represents a floating point number in base 10 exponential notation and may define a precision up to a maximum of 64.
real	It is same as float, except the size argument is not used and may define a precision up to a maximum of 64.
double	same as real except the precision may exceed 64.

TCL commands:
(i) COMMIT command: The COMMIT command is used to permanently save any transaction to the database. When any DML commands like INSERT. UPDATE. DELETE commands are used. the changes made by these commands are not permanent. It is marked permanent only after the COMMIT command is given from the SQL prompt. Once the COMMIT command is given. the changes made cannot be rolled back. The COMMIT command is used as COMMIT;
(ii) ROLLBACK command:
The ROLLBACK command restores the database to the last committed. state. It is used with SAVEPOINT command to jump to a particular savepoint location. The syntax for the ROLLBACK command is: ROLLBACK TO save point name;
(iii) SAVEPOINT command:
The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required. The different states of our table can be saved at anytime using different names and the rollback to that state can be done using the ROLLBACK command.
SAVEPOINT savepoint_name;

(i) TRUNCATE command: The TRUNCATE command is used to delete all the rows from the table. the structure remains and the space is freed from the table. The syntax for TRUNCATE command is:
TRUNCATE TABLE table-name;
For example to delete all the records of the student table and delete the table the SQL statement is given as follows:
TRUNCATE TABLE Student;
The table Student is removed and the space is freed.
(ii) DROP TABLE command: The DROP TABLE command is used to remove a table from the database. If you drop a table. all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back. so be careful while using DROP TABLE command. But there is a condition for dropping a table; it must be an empty table.
Remove all the rows of the table using DELETE command. The DELETE command is already explained.
To delete all rows, the command is given as;
DELETE * FROM Student;
Once all the rows are deleted. the table can be deleted by DROP TABLE command in the following way:
DROP TABLE table-name;
For example to delete the Student table:
DROP TABLE Student;

ALTER Command:
(i) 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. It is used in the following way:
(ii) ALTER TABLE <table-name>  ADD  <column-name><data type> <size>;
(iii) To add a new column "Address" of type 'char' to the Student table, the command is used as
(iv) ALTER TABLE Student ADD Address char;
To modify, existing column of table, the ALTER TABLE command can be used with MODIFY clause like wise:
(v) ALTER < table-name > MODIFY < column-name > < data type > < size >;
ALTER TABLE Student MODIFY Address char (25);
(vi) The above command will modify the address column of the Student table to now hold 25 characters.
(vii) The ALTER command can be used to rename an existing column in the following way:
(viii) ALTER < table-name > RENAME old-column- name TO new-column-name;
(ix) For example to rename the column Address to City, the command is used as :
(x) ALTER TABLE Student RENAME Address TO City;
(xi) The ALTER command can also be used to remove a column or all columns. for example to remove a particular column, the DROP COLUMN is used with the ALTER TABLE to remove a particular field, the command can be used as:
ALTER < table-name > DROP COLUMN < column-name >;
(xii) To remove the column City from the Student table, the command is used as :
ALTER TABLE Student DROP COLUMN City;

The various processing skills of SQL are:
(i) Data Definition Language (DDL): The SQL DDL provides commands for defining relation schemas (structure), deleting relations, creating indexes and modifying relation schemas.
(ii) Data Manipulation Language (DML): The SQL DML includes commands to insert, delete, and modify tuples in the database.
(iii) Embedded Data Manipulation Language: The embedded form of SQL is used in high level programming languages.
(iv) View Definition: The SQL also includes commands for defining views of tables.
(v) Authorization: The SQL includes commands for access rights to relations and views of tables.
(vi) Integrity: The SQL provides forms for integrity checking using condition.
(vii) Transaction control: The SQL includes commands for file transactions and control over transaction processing.