Java

Oracle & Java

RDBMS & Java Tutorials will hit to you est scores

 create table tb_bill_personal as
 select name, regno,course, scheme ,current_sem,sl_no,bill_no,sem,bill_date,
 particulars,amount ,actual_fee,balance
 from (
 select y.name name,x.bill_date,x.bill_no, x.sem,
 rank() over (partition by x.bill_date order by x.bill_no) as sl_no,
 x.particulars,x.amount,x.regno,
 y.course,y.current_sem,y.scheme,
 X.ACTUAL_FEE actual_fee,X.BALANCE balance
 from tb_billing x, tb_personal y
  where x.regno = y.regno
 and x.bill_date = '14-DEC-2012'
 )
  order by bill_date desc,bill_no
 /

update tb_billing set bill_date = '02-JUL-05' WHERE BILL_DATE = '02-JUL-13';
 update tb_billing set bill_date = '01-JAN-05' WHERE BILL_DATE = '01-JAN-14';

select max(bill_no)+1 from tb_billing where bill_date = (
select max(bill_date) from tb_billing)
/














create table tb_daily_billing as select * from tb_billing where 1=2;

create table tb_daily_billing_receipt as select * from tb_billing where 1=2;


create table tb_balance (regno number,
course varchar2(20),
sem varchar2(10),
particulars varchar2(20),
status varchar2(20),
amount number);

  create table tb ( regno number,name varchar2(40),course varchar2(40),
 current_sem varchar2(100),current_year varchar2(100));

  create table tb_backup ( regno number,name varchar2(40),course varchar2(40),
 current_sem varchar2(100),current_year varchar2(100));

drop table test;

create table test (sl_no int,regno number,name varchar2(40),course varchar2(40), current_sem varchar2(100),current_year varchar2(100))

insert into test
(select
row_number() over (partition by course,current_sem order by course,current_sem,regno) sl_no,
regno,name,course,current_sem,current_year from tb_personal )

update tb_personal set sl_no = (select
row_number() over (partition by x.course,x.current_sem order by x.course,x.current_sem,x.regno) sl_no)
from tb_personal x) where regno = x.regno;

create sequence bill_seq start with  10000 increment by 1;










SELECT ename, sal, NTILE(4) OVER (ORDER BY sal DESC)
AS quartile FROM emp
WHERE deptno = 20;

SELECT deptno, ename, sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "Rank"
FROM emp;

10    KING    5000    1
10    CLARK    2450    2
10    MILLER    1300    3
20    SCOTT    3000    1
20    FORD    3000    1
20    JONES    2975    3
20    ADAMS    1100    4
20    SMITH    800    5
30    BLAKE    2850    1
30    ALLEN    1600    2
30    TURNER    1500    3
30    WARD    1250    4
30    MARTIN    1250    4
30    JAMES    950    6

SELECT deptno, ename, ROW_NUMBER()
OVER (PARTITION BY deptno ORDER BY empno) AS emp_id
FROM emp

10    CLARK    1
10    KING    2
10    MILLER    3
20    SMITH    1
20    JONES    2
20    SCOTT    3
20    ADAMS    4
20    FORD    5
30    ALLEN    1
30    WARD    2
30    MARTIN    3
30    BLAKE    4
30    TURNER    5
30    JAMES    6

SELECT SYSTIMESTAMP FROM DUAL

10/25/2012 8:56:43.218000 PM +05:30


SELECT TO_NUMBER('-AusDollars100','L9G999D99',
' NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars''
') "Amount"
FROM DUAL;

-100

SELECT PARTICULARS,TO_CHAR(AMOUNT,'99,999.99') FROM TB_BILLING WHERE REGNO = 29301231 
UNION
SELECT PARTICULARS,TO_CHAR(TO_NUMBER(NULL),'99,999.99') AMOUNT FROM TB_COLUMN


BOOK_FEE   
BUS_FEE   
EXAM_FEE   
FINE_FEE   
HOSTEL_FEE   
SEM_FEE    1000
SEM_FEE    2000
SEM_FEE    4000
SEM_FEE   



PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts
WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance
WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;





create table tb_report (sl_no number, text varchar2(80));





select rownum,sem,particulars,amount,nvl(regno,x) regno, bill_no,
bill_date
from
(
select DISTINCT SEM sem,
tb_bill.PARTICULARS particulars,
TB_BILLING.AMOUNT amount,
tb_billing.regno regno,
tb_billing.bill_no,
tb_billing.bill_date
from
tb_bill,
tb_billing
where tb_bill.particulars
= tb_billing.particulars(+)
order by particulars desc
) x, (select distinct regno x from tb_billing )
/

---

select 'Rupees '|| (to_char(to_date(sum(amount),'j'), 'jsp'))||' Only' amount,nvl(regno,0) regno
 from (
 select DISTINCT SEM sem, tb_bill.PARTICULARS particulars,TB_BILLING.AMOUNT amount,
 tb_billing.regno regno
 from tb_bill, tb_billing
 where tb_bill.particulars
 = tb_billing.particulars(+)
 order by particulars desc )
where nvl(regno,0)<>0
group by regno

---
select * from tb_personal
---


select rownum,SEM,PARTICULARS,
max(decode(particulars,
'SEM_FEE',amount,
'BOOK_FEE',amount,
'BUS_FEE',amount,
'EXAM_FEE',amount,
'HOSTEL_FEE',amount,
'FINE_FEE',amount,
null)) Amount
from (select * from tb_billing order by particulars desc)
group by rownum,SEM,particulars order by particulars desc
/


 select rownum, 'Rupees '|| (to_char(to_date(sum(amount),'j'), 'jsp'))||'Only' amount,nvl(regno,0) from (
 select DISTINCT SEM sem, tb_bill.PARTICULARS particulars,TB_BILLING.AMOUNT amount,
 tb_billing.regno regno
 from tb_bill, tb_billing
 where tb_bill.particulars
 = tb_billing.particulars(+)
 order by particulars desc ) where nvl(regno,0)<>0
group by regno,rownum
/


select dept.*
          from dept, emp
        where dept.deptno
              = emp.deptno
select DISTINCT SEM, tb_bill.PARTICULARS,TB_BILLING.AMOUNT
          from tb_bill, tb_billing
        where tb_bill.particulars
              = tb_billing.particulars(+)
/
SEM        PARTICULARS                                                                                              AMOUNT
---------- ---------------------------------------------------------------------------------------------------- ----------
III        BOOK_FEE                                                                                                    100
III        BUS_FEE                                                                                                     200
III        HOSTEL_FEE                                                                                                 1000
III        OTHER_OR_MISC_FEE                                                                                          1000
III        SEM_FEE                                                                                                     100
III        SEM_FEE                                                                                                     900
           EXAM_FEE
           FINE_FEE

8 rows selected.


select DISTINCT SEM||ROWNUM||
 tb_bill.PARTICULARS,TB_BILLING.AMOUNT
          from tb_bill, tb_billing
        where tb_bill.particulars
              = tb_billing.particulars(+)
/