Example for creating Database
CREATE DATABASE Test;
Example for creating Table
CREATE TABLE Student(student_id INT, name VARCHAR(100), age INT);
ALTER command
alter command is used for altering the table structure, such as,
to add a column to existing table
to rename any existing column
to change datatype of any column or to modify its size.
to drop a column from the table.
ALTER Command: Add a new Column
ALTER TABLE student ADD address VARCHAR(200);
ALTER Command: Add multiple new Columns
ALTER TABLE student ADD father_name VARCHAR(60), mother_name VARCHAR(60), dob DATE;
ALTER Command: Add Column with default value
ALTER TABLE student ADD( dob DATE DEFAULT '01-Jan-99')
ALTER Command: Modify an existing Column
ALTER TABLE student MODIFY( address varchar(300));
ALTER Command: Rename a Column
ALTER TABLE student RENAME address TO location;
ALTER Command: Drop a Column
ALTER TABLE student DROP( address);
TRUNCATE command
TRUNCATE TABLE student;
DROP command
DROP TABLE student;
RENAME query
RENAME TABLE student to students_info;
INSERT command
INSERT INTO table_name VALUES(data1, data2, ...)
Insert value into only specific columns
INSERT INTO student(id, name) values(102, 'Alex');
Insert NULL value to a column
INSERT INTO Student VALUES(102,'Alex', null);
Insert Default value to a column
INSERT INTO Student VALUES(103,'Chris', default)
UPDATE command
UPDATE student SET age=18 WHERE student_id=102;
Updating Multiple Columns
UPDATE student SET name='Abhi', age=17 where s_id=103;
DELETE command
Delete all Records from a Table
DELETE FROM student;
Delete a particular Record from a Table
DELETE FROM student WHERE s_id=103;
COMMIT command
COMMIT;
ROLLBACK command
ROLLBACK TO savepoint_name;
SAVEPOINT command
SAVEPOINT savepoint_name;
Using Savepoint and Rollback
Following is the table class,
id name
1 Abhi
2 Adam
4 Alex
Lets use some SQL queries on the above table and see the results.
INSERT INTO class VALUES(5, 'Rahul');
COMMIT;
UPDATE class SET name = 'Abhijit' WHERE id = '5';
SAVEPOINT A;
INSERT INTO class VALUES(6, 'Chris');
SAVEPOINT B;
INSERT INTO class VALUES(7, 'Bravo');
SAVEPOINT C;
SELECT * FROM class;
NOTE: SELECT statement is used to show the data stored in the table.
The resultant table will look like,
id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris
7 Bravo
Now let's use the ROLLBACK command to roll back the state of data to the savepoint B.
ROLLBACK TO B;
SELECT * FROM class;
Now our class table will look like,
id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris
Now let's again use the ROLLBACK command to roll back the state of data to the savepoint A
ROLLBACK TO A;
SELECT * FROM class;
Now the table will look like,
id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
Using GRANT and REVOKE
GRANT CREATE TABLE TO username;
Grant all privilege to a User
GRANT sysdba TO username
Grant permission to create any table
GRANT CREATE ANY TABLE TO username
Grant permission to drop any table
GRANT DROP ANY TABLE TO username
To take back Permissions
REVOKE CREATE TABLE FROM username
Using the WHERE SQL clause
DELETE FROM table_name WHERE [condition];
SELECT s_id,
name,
age,
address
FROM student WHERE s_id = 101;
AND & OR operator
AND operator
SELECT * FROM Emp WHERE salary < 10000 AND age > 25
OR operator
SELECT * FROM Emp WHERE salary > 10000 OR age > 25