User talk:Ansuman2011

create table department465617 (dept_id number(6) primary key, dept_name varchar2(20) NOT NULL, dept_location varchar2(20)); insert into department465617(dept_id, dept_name) values(105,'sales'); desc department465617

insert into department465617(dept_id, dept_name) values(201,'hr') select * from department465617; select * from department465617 WHERE dept_id in (101,104); select * FROM department465617 order by dept_id desc; select dept_name, dept_location from department465617 drop table department465617; desc department465617;

desc department465617 select * from department465617

create table employee465617 (eid number(5) primary key, ename varchar2(20) NOT NULL, esalary number(7,2) check (esalary>10000),dept_id number(5), constraint empfk02 foreign key(dept_id) references department465617(dept_id));

desc employee465617

select * from employee465617

select * from department465617

insert into employee465617 values(65681,'rajesh', 23000, 103);

update employee465617 set esalary= esalary+2000 WHERE eid=65617;

alter table employee465617 add constraint cnst_emp_chek check (age>18); alter table employee465617 drop column age;

select ename from employee465617;

delete from employee465617 where eid=65681; create table emp465617 (eid number(5), ename varchar2(20) NOT NULL, esalary number(7,2) ,dept_id number(5));

desc emp465617 drop table emp465617 desc employee465617

alter table employee465617 rename column eid to empid;

desc department465617; desc emp465617; select * FROM department465617; select * FROM emp465617;

SELECT e.empid EMPLOYEE_ID, d.dept_name AS DEPARTMENT_NAME FROM emp465617 e, department465617 d where e.dept_id = d.dept_id;

SELECT * FROM emp465617 e, department465617 d where e.dept_id = d.dept_id;

SELECT * FROM emp465617 E, department465617 WHERE E.ename='chandan';

create table empgrade (grade varchar2(1) NOT NULL, start_range number(5), end_range number(6));

desc empgrade; insert into empgrade values('b',10000,15000);

select * from empgrade;

select e.ename, j.grade from emp465617 e, empgrade j where e.esalary between j.start_range and j.end_range; create table employee_mngr (eid number(6) primary key, ename varchar2(20) NOT NULL, manager_id number(20));

desc employee_mngr;

insert into employee_mngr values(8723,'rahul',8446); insert into employee_mngr values(8727,'joy',6952); insert into employee_mngr values(8466,'bikash',8727); insert into employee_mngr (eid, ename) values (6592,'rahul');

select * from employee_mngr;

select e1.ename, e2.ename from employee_mngr e1, employee_mngr e2 where e1.manager_id =e2.eid;

update employee_mngr set manager_id=6592 WHERE ename='rahul';

desc department465617; desc emp465617; select * FROM department465617; select * FROM emp465617;

SELECT e.empid EMPLOYEE_ID, d.dept_name AS DEPARTMENT_NAME FROM emp465617 e, department465617 d where e.dept_id = d.dept_id;

SELECT * FROM emp465617 e, department465617 d where e.dept_id = d.dept_id;

SELECT * FROM emp465617 E, department465617 WHERE E.ename='chandan';

create table empgrade (grade varchar2(1) NOT NULL, start_range number(5), end_range number(6));

desc empgrade; insert into empgrade values('b',10000,15000);

select * from empgrade;

select e.ename, j.grade from emp465617 e, empgrade j where e.esalary between j.start_range and j.end_range; desc employee_details;

create table employee_mngr (eid number(6) primary key, ename varchar2(20) NOT NULL, manager_id number(20));

desc employee_mngr;

insert into employee_mngr values(8723,'rahul',8446); insert into employee_mngr values(8727,'joy',6952); insert into employee_mngr values(8466,'bikash',8727); insert into employee_mngr (eid, ename) values (6592,'rahul');

select * from employee_mngr;

select e1.ename, e2.ename from employee_mngr e1, employee_mngr e2 where e1.manager_id =e2.eid;

update employee_mngr set manager_id=6592 WHERE ename='rahul';

select e1.ename as employee, e2.ename as manager from employee_mngr e1, employee_mngr e2 where e1.manager_id =e2.eid;

create or replace view emp_mngr_view as select e1.ename as employee, e2.ename as manager from employee_mngr e1, employee_mngr e2 where e1.manager_id =e2.eid;

DESC EMP_MNGR_VIEW;

select * FROM EMP_MNGR_VIEW;

select * FROM EMP_MNGR_VIEW where manager='JOY';

create table employee_mngr01 (eid number(6) primary key, ename varchar2(20) NOT NULL, manager_id number(20));

insert into employee_mngr01 select * from employee_mngr; select * from employee_mngr01; delete from employee_mngr; insert into employee_mngr01(eid,ename) select eid,ename from employee_mngr;

select * from employee_mngr;

SELECT max(esalary) from empoyee465617; -

create table department_465599 (dpt_id number(6) primary key, dpt_name varchar2(20) not null, dlocation varchar2(20));

create table Employee_465599 (emp_id number(6) primary key, emp_name varchar2(20) not null, esalary number(7,2) check (esalary>10000), dpt_id number(5), constraint emp_fkey foreign key (dpt_id) references department_465599(dpt_id));

select * from Employee_465599;

desc department_465599;

insert into department_465599 values(101,'Finance','Cuttack'); insert into department_465599 values(102,'IT','Kolkata'); insert into department_465599 values(201,'Accounts','Mumbai'); insert into department_465599 values(401,'Purchase','Chennai');

insert into department_465599 (dpt_id, dpt_name) values(501,'HR');

select * from department_465599 order by dpt_id desc; select * from department_465599 where dpt_id in(201,401,101); select * from department_465599 where dpt_id between 201 and 501; select * from department_465599 where dpt_name='Finance'; select * from department_465599 where dlocation like '_u%'; select * from department_465599 where dlocation like '%at%'; select * from department_465599 where dlocation is not null;

update employee_465599 set esalary= esalary+2000; update department_465599 set dpt_id=201 where dpt_id=102; select dpt_name,dlocation from department_465599;

drop table department_465599;

select * from department_465599 where dpt_id <> 201; select * from department_465599 where dpt_name like '%e%' select * from employee_465599 where esalary between 13000 and 15000

-- alter table employee_465599 add eage varchar(2); alter table employee_465599 add constraint cnst check (eage>18);

alter table employee_465599 drop column eage; delete from employee_465599 where emp_id=1112; desc employee_465599 alter table employee_465599 modify emp_name varchar2(50) select * from employee_465599; alter table employee_465599 rename column Ename to emp_name;

-

select * from employee_465599 e, department_465599 d where e.dpt_id = d.dpt_id

ansu
ansuman

controller
import java.sql.*; import java.io.*; abstract class Student{ int ID; String name; String type; void setID(int id){ ID=id; }	int getID{ return ID; }	void setName(String nam){ name=nam; }	String getName{ return name; }	void setType(String type){ this.type=type; }	String getType{ return type; } }

class ParttimeStudent extends Student{

}

class RegularStudent extends Student{ }

class Controller{ public static void main(String args[]){ Student student=null; char ch='a'; int choice=-1; Model model=new Model; InputStreamReader isr=new InputStreamReader(System.in); BufferedReader br=new BufferedReader(isr); View view =new View; do{ view.showMenu; try{ choice=Integer.parseInt(br.readLine); }			catch(IOException ie){ System.out.println(ie); }			if(choice==1){ student=view.insertMenu; boolean b=model.insert(student); view.displayMessage(b); }			else if(choice==2){ int id=view.retrieveMenu; ResultSet rs=model.retrieve(id); try{ view.show(rs.next,rs); }				catch(SQLException sqe){ System.out.println(sqe); }			}			ch=view.continueMenu; }while(ch=='y'); } } class View{ public void showMenu{ System.out.println("1.Insert value"); System.out.println("2.View Details"); System.out.println("Enter your choice:"); }	public Student insertMenu{ int id=-1; String name=null; String type=null; Student student=null; try{ InputStreamReader isr=new InputStreamReader(System.in); BufferedReader br=new BufferedReader(isr); System.out.print("Insert ID:"); id=Integer.parseInt(br.readLine); System.out.print("Insert Name:"); name=br.readLine; System.out.print("Insert type:"); type=br.readLine; if(type.equals("regular")){ student=new RegularStudent; student.setID(id); student.setName(name); student.setType(type); }			else{ student=new ParttimeStudent; student.setID(id); student.setName(name); student.setType(type); }		}		catch(IOException sqe){ System.out.println(sqe); }		finally{ return student; }	}	public int retrieveMenu{ int id=-1; InputStreamReader isr=new InputStreamReader(System.in); BufferedReader br=new BufferedReader(isr); try{ System.out.print("Insert ID:"); id=Integer.parseInt(br.readLine); }		catch(IOException ie){ System.out.println(ie); }		return id; }	public char continueMenu{ char ch='a'; InputStreamReader isr=new InputStreamReader(System.in); BufferedReader br=new BufferedReader(isr); System.out.println("Do you want to continue?y/n"); try{ ch=(char)br.read; }		catch(IOException ie){ System.out.println(ie); }		return ch; }	public void show(boolean b,ResultSet rs){ if(b==true){ try{ System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"   "+rs.getString(3)); }			catch(SQLException sqe){ System.out.println(sqe); }		}		else{ System.out.println("Invalid ID"); }	}	public void displayMessage(boolean b){ if(b==true){ System.out.println("Record successfully inserted"); }		else{ System.out.println("Unble to insert record"); }	} }

class Model{ DAO dao=DAO.getInstance; public boolean insert(Student student){ int id=student.getID; String name=student.getName; String type=student.getType; int n=dao.insertRecord(id,name,type); if(n!=0) return true; else return false; }	public ResultSet retrieve(int id){ ResultSet rs=dao.retrieveRecord(id); return rs; } }

class DAO{ Connection con; Statement stmt; public static DAO getInstance{ return new DAO; }	private DAO{ try{ Class.forName("oracle.jdbc.driver.OracleDriver"); con=DriverManager.getConnection("jdbc:oracle:thin:@172.0.0.15:1521:orcl","scott","tiger"); stmt=con.createStatement; }		catch(ClassNotFoundException cfe){ System.out.println(cfe); }		catch(SQLException sqe){ System.out.println(sqe); }	}	public int insertRecord(int id,String name,String type){ int n=-1; try{ n=stmt.executeUpdate("insert into student_details_3687624 values("+id+",'"+name+"',"+"'"+type+"'");		}		catch(SQLException sqe){			System.out.println(sqe);		}		return n;	}	public ResultSet retrieveRecord(int id){		ResultSet rs=null;		try{			rs=stmt.executeQuery("select * from student_details_3687624 where ID="+id);		}		catch(SQLException sqe){			System.out.println(sqe);		}		finally{			return rs;		}	} }