Scribd
Upload
4 views10 pages

SQL

The document contains a series of SQL queries executed against an employee database, showcasing various errors and successful queries. It highlights issues such as invalid identifiers and syntax errors while retrieving employee data based on job titles, salaries, and departments. Ultimately, it provides insights into the employee records, including names, job titles, salaries, and hire dates.

Uploaded by

supriya9164dbs
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views10 pages

SQL

The document contains a series of SQL queries executed against an employee database, showcasing various errors and successful queries. It highlights issues such as invalid identifiers and syntax errors while retrieving employee data based on job titles, salaries, and departments. Ultimately, it provides insights into the employee records, including names, job titles, salaries, and hire dates.

Uploaded by

supriya9164dbs
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL> setlect * from emp where job='clerk' and sal>1500;

SP2-0734: unknown command beginning "setlect * ..." - rest of line ignored.

SQL> select * from emp where job='clerk' and sal>1500;

no rows selected

SQL> select ename,hiredate from emp where job='manager' and deptn0=30;

select ename,hiredate from emp where job='manager' and deptn0=30

ERROR at line 1:

ORA-00904: "DEPTN0": invalid identifier

SQL> select ename,hiredate from emp where job='manager' and deptno=30;

no rows selected

SQL> select * from emp where job='salesman' and sal*12>14000;

no rows selected

SQL> SELECT * FROM EMP WHERE DEPTN0 IN(30,ANALYST);

SELECT * FROM EMP WHERE DEPTN0 IN(30,ANALYST)

ERROR at line 1:

ORA-00904: "ANALYST": invalid identifier


SQL> SELECT * FROM EMP WHERE DEPTN0=30 OR JOB='ANALYST';

SELECT * FROM EMP WHERE DEPTN0=30 OR JOB='ANALYST'

ERROR at line 1:

ORA-00904: "DEPTN0": invalid identifier

SQL> SELECT * FROM EMP WHERE JOB='ANALYST',DEPTNO=30;

SELECT * FROM EMP WHERE JOB='ANALYST',DEPTNO=30

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL> ED

Wrote file [Link]

1* SELECT * FROM EMP WHERE JOB='ANALYST'OR DEPTNO=30

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30


7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

8 rows selected.

SQL> SELECT ENAME,SAL,JOB FRPM EMP WHERE SAL>1100 AND JOB='CLERK';

SELECT ENAME,SAL,JOB FRPM EMP WHERE SAL>1100 AND JOB='CLERK'

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE SAL<1100 AND JOB='CLERK';

ENAME SAL JOB

---------- ---------- ---------

SMITH 800 CLERK

JAMES 950 CLERK

SQL> SELECT ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=20 AND SAL>1100 AND SAL*12>12000;

ENAME SAL DEPTNO

---------- ---------- ----------

JONES 2975 20

SCOTT 3000 20

FORD 3000 20

SQL> SELECT ENAME,SAL,DEPTNO,SAL*12 FROM EMP WHERE DEPTNO=20 AND SAL>1100 AND SAL*12>12000;
ENAME SAL DEPTNO SAL*12

---------- ---------- ---------- ----------

JONES 2975 20 35700

SCOTT 3000 20 36000

FORD 3000 20 36000

SQL> SELECT EMPNO,ENAME,DEPTNO,JOB FROM EMP WHERE JOB='MANAGER' AND DEPTNO=20;

EMPNO ENAME DEPTNO JOB

---------- ---------- ---------- ---------

7566 JONES 20 MANAGER

SQL> SELECT * FROM EMP WHERE DEPTNO IN(20,30);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20


11 rows selected.

SQL> SELECT * FROM EMP WHERE JOB='ANALYST', DPTNO=10;

SELECT * FROM EMP WHERE JOB='ANALYST', DPTNO=10

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL> SELECT * FROM EMP WHERE JOB='ANALYST' AND DEPTNO=10;

no rows selected

SQL> SELECT * FROM EMP WHERE JOB='PRESIDENT' AND SAL=4000;

no rows selected

SQL> SELECT ENAME,JOB,DEPTNO FROM EMP WHERE JOB='CLERK' AND DEPTNO IN(10,20);

ENAME JOB DEPTNO

---------- --------- ----------

SMITH CLERK 20

ADAMS CLERK 20

MILLER CLERK 10

SQL> SELECT * FROM EMP WHERE JOB IN('CLERK','MANAGER') AND DEPTNO=10;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL> SELECT * FROM EMP WHERE DEPTNO IN(10,20,30,40);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> SELECT * FROM EMP WHERE EMPNO IN(7902,7839);


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

SQL> SELECT * FROM EMP WHERE JOB IN('MANAGER','SALESMAN','CLERK');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

11 rows selected.

SQL> SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1987';

ENAME HIREDATE

---------- ---------

ALLEN 20-FEB-81
WARD 22-FEB-81

JONES 02-APR-81

MARTIN 28-SEP-81

BLAKE 01-MAY-81

CLARK 09-JUN-81

SCOTT 19-APR-87

KING 17-NOV-81

TURNER 08-SEP-81

ADAMS 23-MAY-87

JAMES 03-DEC-81

FORD 03-DEC-81

MILLER 23-JAN-82

13 rows selected.

SQL> SELECT * FROM EMP WHERE SAL BETWEEN 1250 AND 3000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20


7934 MILLER CLERK 7782 23-JAN-82 1300 10

10 rows selected.

SQL> SELECT ENAME,HIREDATE,DEPTNO FROM EMP WHERE HIREDATE='01-JAN-1981' AND DEPT IN(10,30);

SELECT ENAME,HIREDATE,DEPTNO FROM EMP WHERE HIREDATE='01-JAN-1981' AND DEPT IN(10,30)

ERROR at line 1:

ORA-00904: "DEPT": invalid identifier

SQL> ED

Wrote file [Link]

1* SELECT ENAME,HIREDATE,DEPTNO FROM EMP WHERE HIREDATE='01-JAN-1981' AND DEPTNO IN(10,30);

2 /

SELECT ENAME,HIREDATE,DEPTNO FROM EMP WHERE HIREDATE='01-JAN-1981' AND DEPTNO IN(10,30);

ERROR at line 1:

ORA-00911: invalid character

SQL> SELECT ENAME,HIREDATE,DEPTNO FROM EMP WHERE HIREDATE='01-JAN-1981' AND DEPTNO IN(10,30);

no rows selected

SQL> SELECT EMP.*,SAL*12 FFROM EMP WHERE SAL BETWEEN 1000 AND 4000 AND SAL*12>150;

SELECT EMP.*,SAL*12 FFROM EMP WHERE SAL BETWEEN 1000 AND 4000 AND SAL*12>150
*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> SELECT EMP.*,SAL*12 FROM EMP WHERE SAL BETWEEN 1000 AND 4000 AND SAL*12>150;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SAL*12

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 19200

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 15000

7566 JONES MANAGER 7839 02-APR-81 2975 20 35700

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 15000

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 34200

7782 CLARK MANAGER 7839 09-JUN-81 2450 10 29400

7788 SCOTT ANALYST 7566 19-APR-87 3000 20 36000

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 18000

7876 ADAMS CLERK 7788 23-MAY-87 1100 20 13200

7902 FORD ANALYST 7566 03-DEC-81 3000 20 36000

7934 MILLER CLERK 7782 23-JAN-82 1300 10 15600

11 rows selected.

SQL>

You might also like