Some Basic Oracle DDL and DCL Commands

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

Java for Beginners - Things You Should Do:

Things you should do:
Install JDK and JRE.
Install Eclipse EE (Neon is Good)
Install Oracle XE 11g.
Install Oracle SQL Developer
Install Apache Tomcat

Oracle SQL Command Line Tricks for Beginners

This is Oracle SQL Command Line Tricks for beginners. Simply do the following:

1. First Open "Run SQL Command Line"
2. Type conn username/password
 For example: conn SYSTEM/vijaya
or simply you can type: conn
Then, it will ask for username. It will show "Enter user-name:", type "vijaya"
Then, it will ask for password. Enter your password: Vijaya
Voila! You're connected.

Type:
select * from student
It will display "2" because you didn't enter semicolon after sql query. enter semicolon.

Then, you will see the table data from student.

If you don't have any table, you can simply create a database using:
create table student(
id int not null,
name varchar(20) not null,
marks int,
primary key (id)
);

To insert new data, you can simply do the following:
insert into student values (1, 'Vijaya', 80);

Or, you can insert by doing:

insert into student values (&id, '&name', &marks);
then, it will display:
Enter value for id: (enter your id) eg. 2
Enter value for name: (enter your name) eg. Hari
Enter value for marks: (enter your marks) eg. 78
Hit Enter,
then it will display:
1 row created.

If you want to commit your changes, type: commit; and hit enter.
then, you will see "commit complete" message.

If you want to insert multiple rows into the table, you can simply type forward slash "/" and hit enter.
It will repeat the same message again:
Enter value for id: (enter your id)
Enter value for name: (enter your name)
Enter value for marks: (enter your marks)
Hit Enter.

This is tested on Windows 10, Oracle XE 11g.