Getting Started With Oracle Database 10g October 11, 2009

U can login here as( in 10g express edition):-
1.connect system as SYSDBA;
2.enter passwd as manager;
3.startup;

OR

Oracle9i we had an account called SCOTT with password TIGER. Many try to use that to log in but fail. Oracle Database 10g doesn’t come with SCOTT account any more. It instead provides HR account HR password.

But, HR account is locked and cannot be used unless you unlock it. So here are the steps to unlock HR account.

1. Start SQL Plus using Oracle Database 10g Express Edition->Run SQL Command Line.
2. At SQL> prompt, connect to Oracle using SYSTEM account with password given at the time of installation. Oracle password are NOT case sensitive.

CONNECT SYSTEM/password

3. Unlock HR account with the following command.

ALTER USER HR ACCOUNT UNLOCK;

4. > Now, HR account is unlock and ready to be used. So, try to connect to HR account using the following command.

CONNECT HR/HR

It will prompt you to enter new password and current password is expired. This is because of the fact, HR account’s password is expired by default. Don’t worry enter new password for HR. To make life easy, you better enter the same HR as password.

A Brief History of Java and JDBC October 9, 2009

SQL- Queries August 31, 2009

// Good queries for EMP,DEPT Default table in Oracle
1.FIND THE EMPLOYEE NAME WHO IS WORKING IN DEPT NO 30
SQL> SELECT ENAME FROM EMP WHERE DEPTNO=30;

2.FIND THE EMPLOYEE NAME,SALARY WHO IS WORKING IN DEPT NO 20
SQL> SELECT ENAME,SAL FROM EMP WHERE DEPTNO=20;

3.FIND THE NAME ,JOB,SALARY OF THE EMPLOYEE WHO IS NOT A MANAGER
SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE JOB!=’MANAGER’;

4.FIND THOSE EMPLOYEES WHO WERE HIRED BETWEEN 1 MAR 1981 AND 1 JUN 1983
SQL> SELECT ENAME FROM EMP WHERE HIREDATE BETWEEN ’1-MAR-1981′ AND ’1-JUN-1983′;

5.FIND EMPLOYEE NAME WHO WERE HIRED IN 1981
SQL> SELECT ENAME FROM EMP WHERE HIREDATE LIKE’%81′;

6.FIND EMPLOYEE NAME WHOSE NAME ENDS WITH ‘S’
SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE’%S’;

7.FIND EMPLOYEE NAME WHO ARE WORKING IN DEPT NO 20 & 40
SQL> SELECT ENAME FROM EMP WHERE DEPTNO IN(20,40);

8.FIND ENAME,JOB AND DEPTNO WHO ARE CLERK & SALESMAN
SQL> SELECT ENAME,JOB,DEPTNO FROM EMP WHERE JOB IN(‘CLERK’,'SALESMAN’);

9.FIND ENAME WHO ARE MANAGER AND GETTING SALARY MORE THAN 2000
SQL> SELECT ENAME FROM EMP WHERE JOB=’MANAGER’ AND SAL>2000;

10.FIND ENAME WHO ARE WORKING IN DEPTNO 30 ORDER BY SALARY IN DESC. ORDER
SQL> SELECT ENAME FROM EMP WHERE DEPTNO=30 ORDER BY SAL DESC;

11.FIND OUT THE TOTAL SALARY OF ALL THE EMPLOYEES
SQL> SELECT SUM(SAL) FROM EMP;

12.FIND OUT TOTAL AVG OF ALL THE EMPLOYEES WHO ARE WORKING IN DEPTNO 30
SQL> SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30;

13.FIND OUT THE MINIMUM SALARY OF DEPT NO 20
SQL> SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20;

14.FIND OUT THE MAXIMUM HIREDATE
SQL> SELECT MAX(HIREDATE) FROM EMP;

15.FIND OUT THE TOTAL NUMBER OF EMPLOYEES WHO ARE WORKING IN DEPT NO 10
SQL> SELECT COUNT(ENAME) FROM EMP WHERE DEPTNO=10;

16.FIND OUT DEPTNO,TOTAL SALARY OF THOSE DEPT WHERETHERE IS NO SALESMAN AND  TOTAL SALARY OF DEPT IS MORE THAN 8500
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP WHERE JOB!=’SALESMAN’ GROUP BY DEPTNO HAVING SUM(SAL)>8500;

17.FIND ENAME WHO WAS HIRED FIRST
SQL> SELECT ENAME FROM EMP WHERE HIREDATE IN(SELECT MIN(HIREDATE) FROM EMP);

18.FIND TOTAL SALARY FOR THOSE WHO ARE NOT MANAGER
SQL> SELECT SUM(SAL) FROM EMP WHERE JOB<>’MANAGER’;

19.FIND TOTAL SALARY OF EACH DEPT EXCLUDING THE EMPLOYEE WHO ARE NOT SALESMAN AND DISPLAY ONLY THOSE DEPT WHOSE TOTAL>7000
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP WHERE JOB!=’SALESMAN’ GROUP BY DEPTNO HAVING SUM(SAL)>7000;

20.FIND AVG SALARY FOR ALL THE JOB TYPES WITH MORE THAN 2 EMPLOYEES
SQL> SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB HAVING COUNT(JOB)>2;

21.DISPLAY EMP. COUNT FOR EACH JOB CATAGORY
SQL> SELECT JOB,DEPTNO,COUNT(ENAME) FROM EMP GROUP BY JOB,DEPTNO;

22.FIND ENAME WHO WAS HIRED FIRST
SQL> SELECT ENAME FROM EMP WHERE HIREDATE IN(SELECT MIN(HIREDATE) FROM EMP);

23.FIND OUT THE ENAME HAVING MAXIMUM SALARY IN EACH DEPT
SQL> SELECT ENAME FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

24.FIND AVG SALARY FOR THOSE EMPLOYEES WHOSE JOB=’CLERK’
SQL> SELECT AVG(SAL) FROM EMP WHERE JOB=’CLERK’;

25.FIND TOTAL SALARY FOR THOSE EMPLOYEES WHO WERE HIRED IN 1981
SQL> SELECT SUM(SAL) FROM EMP WHERE HIREDATE LIKE’%81′;

26.CREATE TABLE EMP10 HAVING EMPNO(PRIMARY KEY),ENAME(NOT NULL),SAL(SAL=4000),HIREADTE,DEPTNO
SQL> CREATE TABLE EMP10(EMPNO NUMBER(4) PRIMARY KEY,
ENAME CHAR(10) NOT NULL,
SAL NUMBER(4) CHECK(4000),
HIREDATE DATE,
DEPTNO NUMBER(2) REFERENCES DEPT10(DEPTNO));

27.CREATE TABLE ABC USING AS COMMAND
SQL> CREATE TABLE ABC AS(SELECT * FROM EMP);

28.INSERT THE VALUE TO ADDRESS COLUMN
SQL> UPDATE ABC SET ADDRESS=’DELHI’;