Practical DBMS learning with example Queries

Practical Answer 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

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

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

1 row created.

Expriment no 2

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.

SQL> select dname from dept1;

DNAME

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

ACCOUNTING

RESEARCH

SALES

OPERATIONS

SQL> select dname,loc from dept;

DNAME LOC

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

ACCOUNTING NEW YORK

RESEARCH DALLAS

SALES CHICAGO

OPERATIONS BOSTON

SQL> select job from emp;

JOB

---------

CLERK

SALESMAN

MANAGER

SALESMAN

MANAGER

ANALYST

PRESIDENT

SALESMAN

CLERK

JOB

---------

CLERK

ANALYST

CLERK

14 rows selected.

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

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

ENAME JOB

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

SMITH CLERK

JONES MANAGER

SCOTT ANALYST

ADAMS CLERK

FORD ANALYST

SQL> select * from emp where deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7698 BLAKE MANAGER 7839 01-MAY-81 2850

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7900 JAMES CLERK 7698 03-DEC-81 950

6 rows selected.

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

EMPNO ENAME JOB

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

7566 JONES MANAGER

7698 BLAKE MANAGER

7782 CLARK MANAGER

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

SQL> select ename from emp where empno>mgr;

ENAME

----------

SCOTT

TURNER

ADAMS

JAMES

FORD

MILLER

6 rows selected.

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

ENAME JOB

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

JONES MANAGER

BLAKE MANAGER

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

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

SQL> select count(ename) from emp;

COUNT(ENAME)

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

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

COUNT(JOB)

----------

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

YEARLY_COMP

-----------

67200

SQL> select avg(sal) from emp;

AVG(SAL)

----------

2073.21429

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

AVG_YR_COMP

-----------

16800

SQL> select min(sal) from emp;

MIN(SAL)

----------

800

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.

SQL> select max(sal) from emp;

MAX(SAL)

----------

5000

Experiment No 6

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)

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',

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

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

Table altered.

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

c02 ABC2 nagpada2 byculla2 mumbai maharas 2 good2 2000

c03 ABC3 nagpada3 byculla3 mumbai maharas 3 good3 3000

CLIE NAME ADDRESS1 ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE

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

AGE

----------

c04 ABC4 nagpada4 byculla4 mumbai maharas 4 good4 4000

c05 ABC5 nagpada5 byculla5 mumbai maharas 5 good5 5000

SQL> DELETE FROM CLIENTMAST WHERE AGE>60;

1 row deleted.

Thursday, May 1, 2014

Practical DBMS learning with example Queries

No comments:

Post a Comment

Followers

Popular Posts