Practical DBMS learning with example Queries

Practical Answer 1.

1)

SQL> create table dept1(deptno number primary key,dname varchar(6) not null,loc varchar(8));

Table created.

SQL> describe dept1;

 Name                                      Null?    Type

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

 DEPTNO                                    NOT NULL NUMBER

 DNAME                                     NOT NULL VARCHAR2(6)

 LOC                                                VARCHAR2(8)

SQL>  create table emp1(empno number primary key,ename varchar(8) not null,deptno number,

  2   job varchar(8)  check( job=UPPER(job)),hiredate date default sysdate,foreign key(deptno)

  3   references  dept1 (deptno));

Table created.

SQL> describe emp1

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER

 ENAME                                     NOT NULL VARCHAR2(8)

 DEPTNO                                             NUMBER

 JOB                                                VARCHAR2(8)

 HIREDATE                                           DATE

 2)

SQL> alter table emp1 add(sal number(8,2) check (sal>0),mgr number,comm number(5,2));

Table altered.

SQL> describe emp1;

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER

 ENAME                                     NOT NULL VARCHAR2(8)

 DEPTNO                                             NUMBER

 JOB                                                VARCHAR2(8)

 HIREDATE                                           DATE

 SAL                                                NUMBER(8,2)

 MGR                                                NUMBER

 COMM                                               NUMBER(5,2)

SQL> alter table dept1 modify(dname varchar(14),loc varchar(13));

Table altered.

SQL> insert into dept1  select * from dept;

4 rows created.

SQL> select * from dept1;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

3)

SQL>  insert into emp1 values(7499,'ALLEN',30,'SALESMAN','20-feb-81',1600,7608,300);

1 row created.

Expriment no 2

1)

SQL> select * from tab

  2  ;

TNAME                          TABTYPE  CLUSTERID

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

BONUS                          TABLE

CLIENTMAST                     TABLE

CLIENTMASTER                   TABLE

DEPT                           TABLE

DEPT1                          TABLE

DEPTIT                         TABLE

EMP                            TABLE

EMP1                           TABLE

EMPIT                          TABLE

EMPLOYEE                       TABLE

LOGSTUD                        TABLE

TNAME                          TABTYPE  CLUSTERID

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

ORDERS                         TABLE

SALGRADE                       TABLE

STUD                           TABLE

SUPPLYMASTER                   TABLE

15 rows selected.

 2)

SQL> select dname from dept1;

DNAME

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

ACCOUNTING

RESEARCH

SALES

OPERATIONS

3)

SQL> select dname,loc from dept;

DNAME          LOC

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

ACCOUNTING     NEW YORK

RESEARCH       DALLAS

SALES          CHICAGO

OPERATIONS     BOSTON

4)

SQL> select job from emp;

JOB

---------

CLERK

SALESMAN

SALESMAN

MANAGER

SALESMAN

MANAGER

MANAGER

ANALYST

PRESIDENT

SALESMAN

CLERK

JOB

---------

CLERK

ANALYST

CLERK

14 rows selected.

5)

SQL> select * from emp where ename='smith';

no rows selected

SQL>  select * from emp where ename='SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20

6)

SQL> SELECT ename,job from emp where deptno=20;

ENAME      JOB

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

SMITH      CLERK

JONES      MANAGER

SCOTT      ANALYST

ADAMS      CLERK

FORD       ANALYST

7)

SQL> select * from emp where deptno=30;

     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

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850

        30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0

        30

      7900 JAMES      CLERK           7698 03-DEC-81        950

        30

6 rows selected.

 8)

SQL> select empno,ename,job from emp where job='MANAGER';

     EMPNO ENAME      JOB

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

      7566 JONES      MANAGER

      7698 BLAKE      MANAGER

      7782 CLARK      MANAGER

 9)

SQL> select ename from emp where comm>sal;

ENAME

----------

MARTIN

10)

SQL> select ename,job,sal from emp where sal>2000;

ENAME      JOB              SAL

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

JONES      MANAGER         2975

BLAKE      MANAGER         2850

CLARK      MANAGER         2450

SCOTT      ANALYST         3000

KING       PRESIDENT       5000

FORD       ANALYST         3000

6 rows selected.

11)

SQL> select ename,sal as monthly,sal*12 as annual from emp;

ENAME         MONTHLY     ANNUAL

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

SMITH             800       9600

ALLEN            1600      19200

WARD             1250      15000

JONES            2975      35700

MARTIN           1250      15000

BLAKE            2850      34200

CLARK            2450      29400

SCOTT            3000      36000

KING             5000      60000

TURNER           1500      18000

ADAMS            1100      13200

ENAME         MONTHLY     ANNUAL

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

JAMES             950      11400

FORD             3000      36000

MILLER           1300      15600

14 rows selected.

12)

SQL> select ename,sal from emp where deptno=10;

ENAME             SAL

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

CLARK            2450

KING             5000

MILLER           1300

13)

SQL> select dname as department,deptno as department_no from dept;

DEPARTMENT     DEPARTMENT_NO

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

ACCOUNTING                10

RESEARCH                  20

SALES                     30

OPERATIONS                40

14)

SQL> select ename,mgr from emp where job='MANAGER';

ENAME             MGR

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

JONES            7839

BLAKE            7839

CLARK            7839

15)

SQL>  select empno,ename FROM EMP where job='ANALYST';

     EMPNO ENAME

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

      7788 SCOTT

      7902 FORD

Experiment No 3

1)

SQL> select ename from emp where empno>mgr;

ENAME

----------

SCOTT

TURNER

ADAMS

JAMES

FORD

MILLER

6 rows selected.

2)

SQL> select ename,job from emp where job='MANAGER' and deptno !=10;

ENAME      JOB

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

JONES      MANAGER

BLAKE      MANAGER

3)

SQL> select ename,job from emp where job not in('CLEARK','SALESMAN','ANALYST');

ENAME      JOB

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

SMITH      CLERK

JONES      MANAGER

BLAKE      MANAGER

CLARK      MANAGER

KING       PRESIDENT

ADAMS      CLERK

JAMES      CLERK

MILLER     CLERK

8 rows selected.

SQL> select ename from emp where deptno=20 and job!='clerk' and job!='salesman' and job!='analyst';

ENAME

----------

SMITH

JONES

SCOTT

ADAMS

FORD

6)

SQL> select ename,sal from emp where sal between 1000 and 2000;

ENAME             SAL

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

ALLEN            1600

WARD             1250

MARTIN           1250

TURNER           1500

ADAMS            1100

MILLER           1300

6 rows selected.

13)

SQL> select ename,job,comm from emp where comm is null;

ENAME      JOB             COMM

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

SMITH      CLERK

JONES      MANAGER

BLAKE      MANAGER

CLARK      MANAGER

SCOTT      ANALYST

KING       PRESIDENT

ADAMS      CLERK

JAMES      CLERK

FORD       ANALYST

MILLER     CLERK

10 rows selected.

14)

SQL> select ename,job,comm from emp where comm is not null

  2  ;

ENAME      JOB             COMM

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

ALLEN      SALESMAN         300

WARD       SALESMAN         500

MARTIN     SALESMAN        1400

TURNER     SALESMAN           0

15)

SQL> select ename,deptno from emp where deptno in(10,20);

ENAME          DEPTNO

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

SMITH              20

JONES              20

CLARK              10

SCOTT              20

KING               10

ADAMS              20

FORD               20

MILLER             10

8 rows selected.

16)

SQL> select ename,hiredate from emp where hiredate>'01-jan-81' and deptno=10;

ENAME      HIREDATE

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

CLARK      09-JUN-81

KING       17-NOV-81

MILLER     23-JAN-82

17)

SQL> select ename,deptno from emp where deptno !=10;

ENAME          DEPTNO

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

SMITH              20

ALLEN              30

WARD               30

JONES              20

MARTIN             30

BLAKE              30

SCOTT              20

TURNER             30

ADAMS              20

JAMES              30

FORD               20

11 rows selected.

Expriment no 4

1)

SQL> select count(ename) from emp;

COUNT(ENAME)

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

          14

2)

SQL>  select count(job) from emp where job='CLERK' and hiredate>'13-jan-81';

COUNT(JOB)

----------

         3

3)

SQL> select sum(sal)*12 as yearly_comp from emp where job='SALESMAN';

YEARLY_COMP

-----------

      67200

5)

SQL> select avg(sal) from emp;

  AVG(SAL)

----------

2073.21429

6)

SQL> select avg(sal)*12 as avg_yr_comp from emp where job like 'SALESMAN';

AVG_YR_COMP

-----------

      16800

7)

SQL> select min(sal) from emp;

  MIN(SAL)

----------

       800

 8)

SQL> select ename from emp order by ename;

ENAME

----------

ADAMS

ALLEN

BLAKE

CLARK

FORD

JAMES

JONES

KING

MARTIN

MILLER

SCOTT

ENAME

----------

SMITH

TURNER

WARD

14 rows selected.

9)

SQL> select max(sal) from emp;

  MAX(SAL)

----------

      5000

Experiment No 6

1)

SQL>  create table clientmast(client_no varchar(4) primary key check(client_no like 'c%')

  2  ,name varchar(4) not null,address1 varchar(8),address2 varchar(8),city varchar(8)

  3  ,state varchar(8),pin_code number,remark varchar(5),bal_due number(7,2)

  4  ,check(name=upper(name)));

Table created.

SQL> describe clientmast;

 Name                                      Null?    Type

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

 CLIENT_NO                                 NOT NULL VARCHAR2(4)

 NAME                                      NOT NULL VARCHAR2(4)

 ADDRESS1                                           VARCHAR2(8)

 ADDRESS2                                           VARCHAR2(8)

 CITY                                               VARCHAR2(8)

 STATE                                              VARCHAR2(8)

 PIN_CODE                                           NUMBER

 REMARK                                             VARCHAR2(5)

 BAL_DUE                                            NUMBER(7,2)

4)

SQL> insert into clientmast values('c01','ABC1','nagpada1','byculla1','mumbai','maharas',01,'good1',

1000);

1 row created.

 1* insert into clientmast values('c02','ABC2','nagpada2','byculla2','mumbai','maharas',02,'good2',

5)

SQL> SELECT * FROM CLIENTMAST;

CLIE NAME ADDRESS1 ADDRESS2 CITY     STATE      PIN_CODE REMAR    BAL_DUE

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

c01  ABC1 nagpada1 byculla1 mumbai   maharas           1 good1       1000

c02  ABC2 nagpada2 byculla2 mumbai   maharas           2 good2       2000

c03  ABC3 nagpada3 byculla3 mumbai   maharas           3 good3       3000

c04  ABC4 nagpada4 byculla4 mumbai   maharas           4 good4       4000

c05  ABC5 nagpada5 byculla5 mumbai   maharas           5 good5       5000

6)

SQL> ALTER TABLE CLIENTMAST ADD(AGE NUMBER(2,2));

Table altered.

7)

SQL>  UPDATE CLIENTMAST SET AGE=35;

5 rows updated.

SQL>  UPDATE CLIENTMAST SET AGE=65 WHERE NAME='ABC5';

1 row updated.

SQL> SELECT * FROM CLIENTMAST;

 

CLIE NAME ADDRESS1 ADDRESS2 CITY     STATE      PIN_CODE REMAR    BAL_DUE

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

       AGE

----------

c01  ABC1 nagpada1 byculla1 mumbai   maharas           1 good1       1000

        35

c02  ABC2 nagpada2 byculla2 mumbai   maharas           2 good2       2000

        35

c03  ABC3 nagpada3 byculla3 mumbai   maharas           3 good3       3000

        35

CLIE NAME ADDRESS1 ADDRESS2 CITY     STATE      PIN_CODE REMAR    BAL_DUE

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

       AGE

----------

c04  ABC4 nagpada4 byculla4 mumbai   maharas           4 good4       4000

        35

c05  ABC5 nagpada5 byculla5 mumbai   maharas           5 good5       5000

        65

SQL> DELETE FROM CLIENTMAST WHERE AGE>60;

1 row deleted.