SQL‎ > ‎

MySql

If you found this page, you might be wondering about
the unorganized and random nature 
of these SQL commands. 

There are also a few distinct mistakes which has its respective error messages. 

That is because this is a copy of my first attempts at SQL. 
I like to experiment & I learn from my mistakes. 
These I/O may help you.


DAY 1

[ LOST THE NOTE FILE ]




DAY 2

                                             
SQL>  CREATE TABLE LIBRARY ( NO INT, TITLE VARCHAR(50), AUTHOR VARCHAR2(30), TYPE VARCHAR2(30), PUB VARCHAR2(20), QTY INT, PRICE INT );

Table created.

SQL> DESC LIBRARY;
 Name                            Null?    Type
 ------------------------------- -------- ----
 NO                                       NUMBER(38)
 TITLE                                    VARCHAR2(50)
 AUTHOR                                   VARCHAR2(30)
 TYPE                                     VARCHAR2(30)
 PUB                                      VARCHAR2(20)
 QTY                                      NUMBER(38)
 PRICE                                    NUMBER(38)


SQL> INSERT INTO LIBRARY VALUES(1,'DATA STRUCTURES', 'LIP', 'DS', 'MCGRAW', 4, 217);

1 row created.

SQL> INSERT INTO LIBRARY VALUES(2, 'COMPUTER STUDIES', 'FRENCH', 'FND', 'GALGOTIA', 2, 75);

1 row created.

SQL> INSERT INTO LIBRARY VALUES(3, 'ADVANCED PASCAL', 'SCHILDT', 'PROG', 'MCGRAW', 4, 350);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(4, 'DBASE DUMMIES', 'PALMER', 'DBMS', 'PUSTAKM', 5, 130);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(5, 'MASTERING C++', 'GUREWICH', 'PROG', 'BPB', 3, 295);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(6, 'GUIDE NETWORK', 'FREED', 'NET', 'ZPRESS', 3, 200);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(7, 'MASTERING FOXPRO', 'SEIGAL', 'DBMS', 'BPB', 2, 135);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(8, 'DOS GUIDE', 'NORTON', 'OS', 'PHI', 3, 175);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(9, 'BASIC FOR BEGINNERS', 'MORTON', 'PROG', 'BPB', 3, 40);

1 row created.

SQL>  INSERT INTO LIBRARY VALUES(10, 'MASTERING WINDOWS', 'COWART', 'OS', 'BPB', 1, 225);

1 row created.



SQL>  SELECT TYPE FROM LIBRARY WHERE (TYPE='PROG' AND PUB='BPB');

TYPE
------------------------------
PROG
PROG


SQL> SELECT * FROM LIBRARY WHERE PRICE>130 ORDER BY QTY;


       NO TITLE                                              AUTHOR
--------- -------------------------------------------------- ------------------------------
TYPE                           PUB                        QTY     PRICE
------------------------------ -------------------- --------- ---------
       10 MASTERING WINDOWS                                  COWART OS                             BPB                          1       225

        7 MASTERING FOXPRO                                   SEIGAL
DBMS                           BPB                          2       135

        5 MASTERING C++                                      GUREWICH
PROG                           BPB                          3       295

        6 GUIDE NETWORK                                      FREED
NET                            ZPRESS                       3       200

        8 DOS GUIDE                                          NORTON
OS                             PHI                          3       175

        1 DATA STRUCTURES                                    LIP
DS                             MCGRAW                       4       217

        3 ADVANCED PASCAL                                    SCHILDT

       NO TITLE                                              AUTHOR
--------- -------------------------------------------------- ------------------------------
TYPE                           PUB                        QTY     PRICE
------------------------------ -------------------- --------- ---------
PROG                           MCGRAW                       4       350


7 rows selected.




SQL> SELECT * FROM LIBRARY ORDER BY PRICE;

       NO TITLE                                              AUTHOR
--------- -------------------------------------------------- ------------------------------
TYPE                           PUB                        QTY     PRICE
------------------------------ -------------------- --------- ---------
        9 BASIC FOR BEGINNERS                                MORTON
PROG                           BPB                          3        40

        2 COMPUTER STUDIES                                   FRENCH
FND                            GALGOTIA                     2        75

        4 DBASE DUMMIES                                      PALMER
DBMS                           PUSTAKM                      5       130

        7 MASTERING FOXPRO                                   SEIGAL
DBMS                           BPB                          2       135

        8 DOS GUIDE                                          NORTON
OS                             PHI                          3       175

        6 GUIDE NETWORK                                      FREED
NET                            ZPRESS                       3       200

        1 DATA STRUCTURES                                    LIP

       NO TITLE                                              AUTHOR
--------- -------------------------------------------------- ------------------------------
TYPE                           PUB                        QTY     PRICE
------------------------------ -------------------- --------- ---------
DS                             MCGRAW                       4       217

       10 MASTERING WINDOWS                                  COWART
OS                             BPB                          1       225

        5 MASTERING C++                                      GUREWICH
PROG                           BPB                          3       295

        3 ADVANCED PASCAL                                    SCHILDT
PROG                           MCGRAW                       4       350


10 rows selected.



SQL> SELECT NO, PRICE, PRICE*1.25 FROM LIBRARY;

       NO     PRICE PRICE*1.25
--------- --------- ----------
        1       217     271.25
        2        75      93.75
        3       350      437.5
        4       130      162.5
        5       295     368.75
        6       200        250
        7       135     168.75
        8       175     218.75
        9        40         50
       10       225     281.25

10 rows selected.


SQL> SELECT COUNT(*) FROM LIBRARY WHERE PUB='PHI';

 COUNT(*)
---------
        1


SQL> INSERT INTO LIBRARY VALUES(11, 'SQL FOR DUMMIES', 'NEIL', 'PROG', 'MCGRAW', 5, 3400);

1 row created.


SQL> SELECT * FROM LIBRARY;

       NO TITLE                                              AUTHOR
--------- -------------------------------------------------- ------------------------------
TYPE                           PUB                        QTY     PRICE
------------------------------ -------------------- --------- ---------
        1 DATA STRUCTURES                                    LIP
DS                             MCGRAW                       4       217

        2 COMPUTER STUDIES                                   FRENCH
FND                            GALGOTIA                     2        75

        3 ADVANCED PASCAL                                    SCHILDT
PROG                           MCGRAW                       4       350

        4 DBASE DUMMIES                                      PALMER
DBMS                           PUSTAKM                      5       130

        5 MASTERING C++                                      GUREWICH
PROG                           BPB                          3       295

        6 GUIDE NETWORK                                      FREED
NET                            ZPRESS                       3       200

        7 MASTERING FOXPRO                                   SEIGAL

       NO TITLE                                              AUTHOR
--------- -------------------------------------------------- ------------------------------
TYPE                           PUB                        QTY     PRICE
------------------------------ -------------------- --------- ---------
DBMS                           BPB                          2       135

        8 DOS GUIDE                                          NORTON
OS                             PHI                          3       175

        9 BASIC FOR BEGINNERS                                MORTON
PROG                           BPB                          3        40

       10 MASTERING WINDOWS                                  COWART
OS                             BPB                          1       225

       11 SQL FOR DUMMIES                                    NEIL
PROG                           MCGRAW                       5      3400


11 rows selected.




SQL> SELECT MIN(PRICE) FROM LIBRARY WHERE PRICE<150;

MIN(PRICE)
----------
        40



SQL> SELECT AVG(PRICE) FROM LIBRARY WHERE QTY<3;

AVG(PRICE)
----------
       145

SQL> SELECT COUNT(DISTINCT PUB) FROM LIBRARY;

COUNT(DISTINCTPUB)
------------------
6  



DAY 3


SQL*Plus: Release 8.0.4.0.0 - Production on Mon Jan 1 5:43:51 2001

(c) Copyright 1997 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8 Personal Edition Release 8.0.4.0.0 - Production
PL/SQL Release 8.0.4.0.0 - Production


SQL>  select empno,ename,sal+sal*1700"salary" from emp;

    EMPNO ENAME         salary
--------- ---------- ---------
     7369 SMITH        1360800
     7499 ALLEN        2721600
     7521 WARD         2126250
     7566 JONES        5060475
     7654 MARTIN       2126250
     7698 BLAKE        4847850
     7782 CLARK        4167450
     7788 SCOTT        5103000
     7839 KING         8505000
     7844 TURNER       2551500
     7876 ADAMS        1871100
     7900 JAMES        1615950
     7902 FORD         5103000
     7934 MILLER       2211300

14 rows selected.



SQL> select * from emp;

    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 EMPNO,ENAME, SAL+SAL.1.25"sALARY FROM EMP;
ERROR:
ORA-01740: missing double quote in identifier

SQL> select EMPNO,ENAME, SAL+SAL.1.25"sALARY" FROM EMP;
select EMPNO,ENAME, SAL+SAL.1.25"sALARY" FROM EMP
                           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select EMPNO,ENAME, SAL+SAL*1.25 "sALARY" FROM EMP;

    EMPNO ENAME         sALARY
--------- ---------- ---------
     7369 SMITH           1800
     7499 ALLEN           3600
     7521 WARD          2812.5
     7566 JONES        6693.75
     7654 MARTIN        2812.5
     7698 BLAKE         6412.5
     7782 CLARK         5512.5
     7788 SCOTT           6750
     7839 KING           11250
     7844 TURNER          3375
     7876 ADAMS           2475
     7900 JAMES         2137.5
     7902 FORD            6750
     7934 MILLER          2925

14 rows selected.




SQL> SELECT ENAME, SAL FROM EMP;

ENAME            SAL
---------- ---------
SMITH            800
ALLEN           1600
WARD            1250
JONES           2975
MARTIN          1250
BLAKE           2850
CLARK           2450
SCOTT           3000
KING            5000
TURNER          1500
ADAMS           1100
JAMES            950
FORD            3000
MILLER          1300

14 rows selected.



SQL> SELECT ENAME, SAL+SAL*1.25 FROM EMP;

ENAME      SAL+SAL*1.25
---------- ------------
SMITH              1800
ALLEN              3600
WARD             2812.5
JONES           6693.75
MARTIN           2812.5
BLAKE            6412.5
CLARK            5512.5
SCOTT              6750
KING              11250
TURNER             3375
ADAMS              2475
JAMES            2137.5
FORD               6750
MILLER             2925

14 rows selected.



SQL>  SELECT ENAME, SAL+SAL*1.25 "BONUS" FROM EMP;

ENAME          BONUS
---------- ---------
SMITH           1800
ALLEN           3600
WARD          2812.5
JONES        6693.75
MARTIN        2812.5
BLAKE         6412.5
CLARK         5512.5
SCOTT           6750
KING           11250
TURNER          3375
ADAMS           2475
JAMES         2137.5
FORD            6750
MILLER          2925

14 rows selected.



SQL> SELECT * FROM EMP WHERE JOB='SALESMAN';

    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
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30




SQL> SELECT JOB, ENAME FROM EMP ORDER BY ENAME;

JOB       ENAME
--------- ----------
CLERK     ADAMS
SALESMAN  ALLEN
MANAGER   BLAKE
MANAGER   CLARK
ANALYST   FORD
CLERK     JAMES
MANAGER   JONES
PRESIDENT KING
SALESMAN  MARTIN
CLERK     MILLER
ANALYST   SCOTT
CLERK     SMITH
SALESMAN  TURNER
SALESMAN  WARD

14 rows selected.


SQL> SELECT * FROM CUSTOMERS WHERE (NOT LOCATION='SHIMLA' OR RATING<=100);
SELECT * FROM CUSTOMERS WHERE (NOT LOCATION='SHIMLA' OR RATING<=100)
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>  CREATE TABLE CUSTOMERS ( RATING NUMBER(4), LOCATION VARCHAR2(30));

Table created.

SQL> INSERT INTO CUSTOMERS
  2  VALUES ( 105, 'JAMMU');

1 row created.

SQL> INSERT INTO CUSTOMERS
  2  VALUES ( 87, 'SHIMLA');

1 row created.

SQL> INSERT INTO CUSTOMERS
  2  VALUES (78, 'BABU');

1 row created.


SQL> SELECT * FROM CUSTOMERS WHERE RATING>100 OR LOCATION='SHIMLA';

   RATING LOCATION
--------- ------------------------------
      105 JAMMU
       87 SHIMLA

SQL> CREATE TABLE EXAMPLE ( NUM INT );

Table created.


SQL> DESC EXAMPLE;
 Name                            Null?    Type
 ------------------------------- -------- ----
 NUM                                      NUMBER(38)



SQL> INSERT INTO EXAMPLE VALUES 111111111111111111111111111111111111111111111111111111111111111
111111111111111111111111111111111111111111111111111111111111;
INSERT INTO EXAMPLE VALUES 11111111111111111111111111111111111111111111111111111111111111111111
                           *
ERROR at line 1:
ORA-01426: numeric overflow


SQL> ALTER TABLE EXAMPLE MODIFY (  NUM NUMBER(3000) );
ALTER TABLE EXAMPLE MODIFY (  NUM NUMBER(3000) )
                                         *
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)



SQL> ALTER TABLE EXAMPLE
  2  ADD ( DECI DEC);

Table altered.


SQL> DESC EXAMPLE
 Name                            Null?    Type
 ------------------------------- -------- ----
 NUM                                      NUMBER(38)
 DECI                                     NUMBER(38)


SQL> INSERT INTO EXAMPLE VALUES ( 3, 3.14 )
  2  ;

1 row created.

SQL> SELECT * FROM EXAMPLE
  2  ;

      NUM      DECI
--------- ---------
        3         3

SQL> ALTER TABLE EXAMPLE
  2  MODIFY ( DECI DEC(5,2) );
MODIFY ( DECI DEC(5,2) )
         *
ERROR at line 2:
ORA-01440: column to be modified must be empty to decrease precision or scale



SQL> DELETE FROM EXAMPLE WHERE NUM=3;

1 row deleted.
SQL> SELECT COUNT(*) FROM EMP;

 COUNT(*)
---------
       14

SQL> SELECT JOB, COUNT(*) FROM EMP;
SELECT JOB, COUNT(*) FROM EMP
       *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> SELECT * FROM EMP;
    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 COUNT FROM EMP;
SELECT COUNT FROM EMP
       *
ERROR at line 1:
ORA-00904: invalid column name


SQL> SELECT AVG(SAL) FROM EMP WHERE JOB='SALESMAN';

 AVG(SAL)
---------
     1400

SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP WHERE JOB='SALESMAN;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP WHERE JOB='SALESMAN';
SELECT JOB, SAL, AVG(SAL) FROM EMP WHERE JOB='SALESMAN'
       *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY SAL;
SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY SAL
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY JOB;
SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY JOB
            *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;

JOB        AVG(SAL)
--------- ---------
ANALYST        3000
CLERK        1037.5
MANAGER   2758.3333
PRESIDENT      5000
SALESMAN       1400

SQL> SELECT JOB FROM EMP GROUP BY JOB;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

SQL> SELECT JOB, AVG(SAL) FROM EMP;
SELECT JOB, AVG(SAL) FROM EMP
       *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> SELECT JOB FROM EMP;

JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK

14 rows selected.

SQL> SELECT JOB, MIN(SAL) FROM EMP GROUP BY JOB;

JOB        MIN(SAL)
--------- ---------
ANALYST        3000
CLERK           800
MANAGER        2450
PRESIDENT      5000
SALESMAN       1250

SQL> SELECT JOB, MAX(SAL) FROM EMP;
SELECT JOB, MAX(SAL) FROM EMP
       *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> SELECT MAX(SAL) FROM EMP;

 MAX(SAL)
---------
     5000

SQL> SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB='SALESMAN';

JOB        MAX(SAL)
--------- ---------
SALESMAN       1600

SQL> SELECT 4.3 * 2.3 FROM DUAL;

  4.3*2.3
---------
     9.89

SQL> SELECT SYSTDATE FROM DUAL;
SELECT SYSTDATE FROM DUAL
       *
ERROR at line 1:
ORA-00904: invalid column name


SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
01-JAN-01

SQL> SELECT GETDATE() FROM DUAL;
SELECT GETDATE() FROM DUAL
       *
ERROR at line 1:
ORA-00904: invalid column name

SQL> SELECT ENAME, "GETS THE COMMISION" , SAL*0.25 " ", '%' " "
  2  FROM EMP
  3  WHERE NOT JOB='PRESIDENT'
  4  ORDER BY ENAME;
SELECT ENAME, "GETS THE COMMISION" , SAL*0.25 " ", '%' " "
              *
ERROR at line 1:
ORA-00904: invalid column name


SQL> SELECT ENAME, "GETS THE COMMISION" , SAL*0.25 " ", '%'
  2  FROM EMP;
SELECT ENAME, "GETS THE COMMISION" , SAL*0.25 " ", '%'
              *
ERROR at line 1:
ORA-00904: invalid column name


SQL> SELECT ENAME, ' GETS THE COMMISION ' " ", SAL*0.12, '%'
  2  FROM EMP
  3  ORDER BY ENAME;

ENAME                            SAL*0.12 '
---------- -------------------- --------- -
ADAMS       GETS THE COMMISION        132 %
ALLEN       GETS THE COMMISION        192 %
BLAKE       GETS THE COMMISION        342 %
CLARK       GETS THE COMMISION        294 %
FORD        GETS THE COMMISION        360 %
JAMES       GETS THE COMMISION        114 %
JONES       GETS THE COMMISION        357 %
KING        GETS THE COMMISION        600 %
MARTIN      GETS THE COMMISION        150 %
MILLER      GETS THE COMMISION        156 %
SCOTT       GETS THE COMMISION        360 %
SMITH       GETS THE COMMISION         96 %
TURNER      GETS THE COMMISION        180 %
WARD        GETS THE COMMISION        150 %

14 rows selected.

SQL> COPYING TABLE TO NEW TABLE
unknown command beginning "COPYING TA..." - rest of line ignored.




DAY 4


SQL> SELECT * FROM EMP;

    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 AVG(SAL) FROM EMP WHERE JOB='SALESMAN';

 AVG(SAL)
---------
     1400


SQL> SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;

JOB        AVG(SAL)
--------- ---------
ANALYST        3000
CLERK        1037.5
MANAGER   2758.3333
PRESIDENT      5000
SALESMAN       1400


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP WHERE JOB='SALESMAN;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP WHERE JOB='SALESMAN';
SELECT JOB, SAL, AVG(SAL) FROM EMP WHERE JOB='SALESMAN'
       *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY SAL;
SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY SAL
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY JOB;
SELECT JOB, SAL, AVG(SAL) FROM EMP GROUP BY JOB
            *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT JOB, MIN(SAL) FROM EMP GROUP BY JOB;

JOB        MIN(SAL)
--------- ---------
ANALYST        3000
CLERK           800
MANAGER        2450
PRESIDENT      5000
SALESMAN       1250



SQL> select EMPNO,ENAME, SAL+SAL*1.25 "sALARY" FROM EMP;

    EMPNO ENAME         sALARY
--------- ---------- ---------
     7369 SMITH           1800
     7499 ALLEN           3600
     7521 WARD          2812.5
     7566 JONES        6693.75
     7654 MARTIN        2812.5
     7698 BLAKE         6412.5
     7782 CLARK         5512.5
     7788 SCOTT           6750
     7839 KING           11250
     7844 TURNER          3375
     7876 ADAMS           2475
     7900 JAMES         2137.5
     7902 FORD            6750
     7934 MILLER          2925

14 rows selected.



SQL> SELECT * FROM EMP WHERE JOB='SALESMAN';

    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
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30



SQL> SELECT COUNT(*) FROM EMP;

 COUNT(*)
---------
       14


SQL> SELECT JOB, COUNT(*) FROM EMP;
SELECT JOB, COUNT(*) FROM EMP
       *
ERROR at line 1:
ORA-00937: not a single-group group function



SQL> SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB;

JOB        COUNT(*)
--------- ---------
ANALYST           2
CLERK             4
MANAGER           3
PRESIDENT         1
SALESMAN          4


SQL> SELECT * FROM EMP GROUP BY DEPTNO;
SELECT * FROM EMP GROUP BY DEPTNO
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

   DEPTNO
---------
       10
       20
       30


SQL> SELECT * FROM EMP GROUP BY DEPTNO;
SELECT * FROM EMP GROUP BY DEPTNO
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

   DEPTNO
---------
       10
       20
       30

SQL> SELECT * FROM EMP GROUP BY DEPTNO;
SELECT * FROM EMP GROUP BY DEPTNO
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

   DEPTNO
---------
       10
       20
       30

SQL> SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;

   DEPTNO  COUNT(*)
--------- ---------
       10         3
       20         5
       30         6

SQL> SELECT ENAME, SAL "RS" FROM EMP;

ENAME             RS
---------- ---------
SMITH            800
ALLEN           1600
WARD            1250
JONES           2975
MARTIN          1250
BLAKE           2850
CLARK           2450
SCOTT           3000
KING            5000
TURNER          1500
ADAMS           1100
JAMES            950
FORD            3000
MILLER          1300

14 rows selected.

SQL> SELECT ENAME, 'RS' SAL FROM EMP;

ENAME      SA
---------- --
SMITH      RS
ALLEN      RS
WARD       RS
JONES      RS
MARTIN     RS
BLAKE      RS
CLARK      RS
SCOTT      RS
KING       RS
TURNER     RS
ADAMS      RS
JAMES      RS
FORD       RS
MILLER     RS

14 rows selected.


SQL> SELECT ENAME, 'RS', SAL FROM EMP;
ENAME      'R       SAL
---------- -- ---------
SMITH      RS       800
ALLEN      RS      1600
WARD       RS      1250
JONES      RS      2975
MARTIN     RS      1250
BLAKE      RS      2850
CLARK      RS      2450
SCOTT      RS      3000
KING       RS      5000
TURNER     RS      1500
ADAMS      RS      1100
JAMES      RS       950
FORD       RS      3000
MILLER     RS      1300

14 rows selected.


SQL> SELECT ENAME, SAL, 'RUPEES' FROM EMP;

ENAME            SAL 'RUPEE
---------- --------- ------
SMITH            800 RUPEES
ALLEN           1600 RUPEES
WARD            1250 RUPEES
JONES           2975 RUPEES
MARTIN          1250 RUPEES
BLAKE           2850 RUPEES
CLARK           2450 RUPEES
SCOTT           3000 RUPEES
KING            5000 RUPEES
TURNER          1500 RUPEES
ADAMS           1100 RUPEES
JAMES            950 RUPEES
FORD            3000 RUPEES
MILLER          1300 RUPEES

14 rows selected.


SQL> SELECT ENAME, SAL, 'RUPEES' "" FROM EMP;
ERROR:
ORA-01741: illegal zero-length identifier



SQL> SELECT ENAME, SAL, 'RUPEES' " " FROM EMP;

ENAME            SAL
---------- --------- ------
SMITH            800 RUPEES
ALLEN           1600 RUPEES
WARD            1250 RUPEES
JONES           2975 RUPEES
MARTIN          1250 RUPEES
BLAKE           2850 RUPEES
CLARK           2450 RUPEES
SCOTT           3000 RUPEES
KING            5000 RUPEES
TURNER          1500 RUPEES
ADAMS           1100 RUPEES
JAMES            950 RUPEES
FORD            3000 RUPEES
MILLER          1300 RUPEES

14 rows selected.


SQL> SELECT COUNT(DISTINCT JOB) FROM EMP;

COUNT(DISTINCTJOB)
------------------
                 5

SQL> SELECT * FROM EMP WHERE (JOB='CLERK' OR JOB='SALESMAN');

    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
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     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

8 rows selected.



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

    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
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     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

8 rows selected.


SQL> SELECT * FROM EMP WHERE JOB NOT IN ('PRESIDENT', 'ANALYST', 'MANAGER');

    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
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     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

8 rows selected.


DAY 5




SQL> SELECT * FROM EMP;
    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> UPDATE EMP SET ENAME='NEIL MATHEW' WHERE ENAME='KING';
UPDATE EMP SET ENAME='NEIL MATHEW' WHERE ENAME='KING'
       *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> DESC EMP;
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL>  UPDATE EMP SET ENAME='NEIL' WHERE JOB='PRESIDENT';

1 row updated.

SQL> SELECT * FROM EMP;

    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 NEIL       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> UPDATE EMP
  2  SET ENAME='KING'
  3  WHERE ENAME='NEIL';

1 row updated.

SQL> SELECT * FROM EMP;

    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.

Comments