Oracle, SQL

To Display last record in a table

select * from (select rownum r, emp.* from emp) where r in (Select count(*) from emp)

Select a range :

select * from (select rownum rn, department_id,department_name from departments) where rn >= 11 and rn <= 20;

Tree Query :

To display A Employee supervisor tree, all possible combinations :

Select * from emp
Connected by prior emp_id = superviosor_id;

To display all employees of a particular supervisor (for e.g 101) :

Select * from emp
Connected by prior emp_id = superviosor_id;
Start with emp_id = 101;

Example of traversing through a tree table

SQL> select sample_number,parent_sample,text_id from sample
2 connect by prior sample_number=parent_sample
3 start with sample_number = 118;


Creating new tables/schema

1. Log into the console via http://targetted-database-host:1158/em. Authenticate as one of the DBA accounts.
2. Select the Administration tab.
3. Select the Storage Tablespaces link.
4. Ensure the Actions drop-down list is set to Add Datafile.
5. Press the Create button.
6. Specify a name for the tablespace. Will appear in uppercase in most locations.
7. Under Extent Management select locally managed.
8. Under Type select permanent with set as default... unchecked.
9. Under Status select read write.
10. Under Datafiles check the use big file tablespaces option.
11. Press the Add button down in the Datafiles section.
* Supply a File Name of the form basename01.dbf. As the database grows more files are likely to be added, thus the use of a sequence number.
* The File Directory should be defaulted already: e.g. /oracle/u02/oradata/sand/, or c:\oracle\oradata\10.2.0\lt50735\lt50735\
* Specify a File Size of 500 megabytes. Do not re-use an existing file.
* Under the Storage section select the Automatically extend... option. Set the increment option to 500 megabytes. Make sure maximum file size is set to Unlimited.
* Press the continue button to accept the file addition. The view returns to the tablespace entry screen.
12. Press the Ok button (upper-right) to create the new tablespace and file, placing it on-line for use.


Create user

$ sqlplus / as sysdba
create user myuser identified by password;
grant create session,create any table to myuser;
alter user myuser default tablespace ;
alter user myuser quota unlimited on ;

login sqlplus with myuser

create tables…, insert data.. ready to go

Implementing auto increment

http://jen.fluxcapacitor.net/geek/autoincr.html

Example Java Code :

String query = "BEGIN insert into Test(desc,timestamp) values (? , ?) returning id into ?; END;";

OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(query);
cs.setDouble(1, "test");
cs.setTimestamp(2, time);
cs.registerOutParameter(3, OracleTypes.NUMBER );
cs.execute();
test_id=cs.getInt(3);
cs.close();

Create sequence and trigger before using the above code as follows :

SQL> create sequence test_id_seq
2 start with 1
3 increment by 1
4 nomaxvalue;
Sequence created.

SQL> create trigger test_id_trigger
2 before insert on test
3 for each row
4 begin
5 select test_id_seq.nextval into :new.id from dual;
6 end;
7 /

Query to find gaps in sequence number in a table :

SELECT (before_gap+1) ||'-' || (after_gap-1)
FROM (SELECT seq_number after_gap, LAG(seq_number,1,0) OVER (ORDER BY seq_number) before_gap from seq_num_table)
WHERE before_gap != 0 AND after_gap - before_gap > 1
ORDER BY before_gap;