DDL COMMANDS:
ALTER:
This can be used to add (or) remove columns and to modify the precision of data type.
syntax:
alter table <table name> add <col... datatype>
eg.
alter table student add sdob date;
alter table student drop sdob date;
To change the precision:
alter table student modify id number(5);
Make a column unused:
alter table student set unused column marks
Rename column:
Alter table student rename column name to sname;
Using Truncate:
This can be used to delete table data permanently.
Syntax: truncate table <table name>
truncate table student;
Drop:
this will be used to drop the database object.
syntax:
drop table <table name>
drop table student;
Rename:
it will be used to rename the database object.
syntax:
rename table <table name> to <table name>
rename table student to stud;
Commit:
this will be used to save th work.
Two types of commit:
a. implicit commit
b. explicit commit
Explicit commit:
this will be issued by the user.
syntax: commit or commit work;
Rollback:
this will undo the operation
it will be issued in two methods
1. upto previous commit
2. upto previous rollback
syntax:
roll or roll work
or
rollback or rollback work
Savepoint:
you can use savepoint to rollback portions of your current set of transactions.
syntax:
savepoint <save point name>
eg. savepoint s1;
insert into student values (1, 'brook', 90);
savepoint s2;
insert into student values (2, 'DJ', 80);
savepoint s3;
rollback s2;
USING DCL:
DCL commands are used to grant and revoke the permissions.
Using Grant:
This will be used to grant the privileges to users.
Syntax:
Grant <privileges> on <object name> to <user name>;
grant select on student to seema;
[most of the time grant and revoke will be done by DBA.]
or,
grant all on student to seema; [we have given all the previliges to seema]
grant all on student to seema with grant option;
if you want to allow seema to give grant permissions to other;
revoke select on student from seema;
revoke all on student from seema;
Multiinsert with all fields:
insert all
into student values (3, 'Rabi', 93)
into student values (4, 'Hari', 96)
into student values (5, 'Anup', 90)
select * from dept where deptno = 10;
select greatest (1, 2, 3), greatest(-1, -2, -3) from dual; [dual is a table inside the system, and there is nothing in the table]
initcap: make the first letter capitalized.
CONSTRAINTS (RESTRICTIONS):
Constraints are categorized as follows:
1. domain integrity constraint
a. Notnull constraint
b. check
2. Entity integrity constraint
a. unique
b. primary key
3. Referencial integrity constraint
a. foreign key constraint
- Constraints are always attached to the column not to the table.
- We can add constraints in three ways
a. column level: always with the column definition
b. table level: after the table definition
c. alter level: using alter command
No comments:
Post a Comment